This morning, I wrote ~4500 lines of code in about two minutes.
Skeptical?
I thought you would be, so here's the curious tale of how I deviated from best practice land and wrote 4500 lines of C# code using Excel formulas.
It began with an email from a member of the public to the organisation for which I work. To protect the person's identity, we will call her... Mrs Mittens, and we shall paraphrase what she had to say.
Mrs Mittens wrote:
"I live in a particular street in Rathernicesuburb"
So far so good
"When I came to your website, however, and was asked to select my street and suburb from a drop-down list, I noticed that my street is categorised as Neighbouringsuburb."
Uh oh.
"This is simply not good enough." continued Mrs Mittens, "I live in Rathernicesuburb and will not put up with being labelled as a resident of Neighbouringsuburb, which while nice is not as nice as Rathernicesuburb".
Ah.
"THIS MUST BE FIXED" yelled the management team.
"Yikes", responded the Geospatial data team. "Yes, it's true that while the website arbitrarily places every street in the area into a given suburb, there are numerous streets which are actually in more than one suburb, including, we can confirm, that of Mrs Mittens, who does in fact live in Rathernicesuburb, as do her neighbours, and everyone along her side of the street."
"YES BUT HOW DOES THAT FIX IT?? MRS MITTENS IS BESIDE HERSELF!!" yelled the management team, displaying the usual levels of calm analytical thought.
And so the problem came to me.
I'm not the web developer. The web developer left some months ago. I'm a SharePoint Administrator, officially. However, I have the knowin' of the magical incantations and the See-Sharps and the Eye-Eye-Esses and the databasings. So web stuff now comes to me by default.
"Here's a problem. Fix it. Oh, and here's a spreadsheet of all the streets in the area, and all the suburbs they could appear within. Good luck, soldier. Oh, and do it now. We mean it. NOW."
So off I went into the code, hoping to find how this particular drop-down list works. The list had about 1500 streets, each categorised as a given suburb. My first thought was that this would be drawn from a database somewhere using C#, which would render into an ASPX page, neatly separating logic, markup and data, in what is thought of as a programming ideal. It's how I would have done it.
Boy, was I ever wrong?
When I found the dropdown, here's how my predecessor had built it (changed slightly to protect the innocent)
public void AddSuburb(string StreetAndSuburb) {
dropDown.AppendOption(StreetAndSuburb);
}
AddSuburb("Alpha Street - Nicesuburb");
AddSuburb("Beta Avenue - Nicesuburb");
AddSuburb("Delta Crescent - Verynicesuburbindeed");
AddSuburb("Gamma Lane - Crappysuburb");
AddSuburb("Epsilon Gardens - Runawayyou'llbekilledville");
AddSuburb("Pi Row - Suburbwherewepoisonallthetrees")
AddSuburb("LOADS MORE STREETS HERE");
...and about 1500 further lines of hardcoded C#, including typographic errors and suburb transpositions. Obviously things like typos were never looked for thoroughly, and would only come to light if a person from a given street decided to contact my employer, and could be bothered to actually report the issue.
This is, in my opinion, what is often termed a coding horror
So, what was I to do? I had a 4500-line Excel Spreadsheet with the real suburbs in it, and 1500 lines of C# code to replace, and a management team breathing down my neck to get this done ASAP.
Best practice would suggest I should redevelop the whole thing, so that it would draw from a central database, then bind that data to a WebControl. Sadly, I don't have sufficient permissions in this website's DB to create new tables. As I mentioned earlier, I'm not the web developer. Also, I'd need to test it properly. And make sure the production DB was up to date. And probably write an interface or sync script to manage the tables. And I have other work to do, work which is actually included in my job description.
So I had a brainwave.
I wrote an Excel formula.
Here it is
Yep. I took the A and B columns, concatenated them together with the AddSuburb() call, and pasted that formula all the way down the C column.
I then copied the new AddSuburb() calls which appeared in colum C, and pasted all 4500 lines into the C# source in place of the 1500 previous lines. Then I hit "build", confirmed it worked in test and sent it off to management for the once-over.
And it worked.
And it took me about three minutes
Of course, I'm guilty of the most horrendous bad practice. And I'm guilty of perpetuating the poor practice of a predecessor. But I'm also perversely pleased that I came up with a trick that saved me a shedload of time and managed to make the situation no worse at all.
So there we are. I'm an evil not-best-practice hacker, a cowboy of the highest order. It's confirmed.
But CERN just announced 5-sigma evidence for the existence of the Higgs Boson, in comic sans, so frankly, no-one should care.
posted @ Wednesday, July 4, 2012 7:12 PM