Adventures in Worstpracticeville

This morning, I wrote ~4500 lines of code in about two minutes.


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".


"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)   {

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")


...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


Comments on this entry:

# re: Adventures in Worstpracticeville

Left by mochuck at 7/4/2012 7:51 PM
Why do you use the term shedload when you say FUCK all the time

# re: Adventures in Worstpracticeville

Left by Peter Bowditch at 7/4/2012 8:08 PM
Ummm. Errr.

I've actually done the same thing myself. But don't tell anyone.

Excel is a mighty sharp tool for doing things that it was never meant to do.

# re: Adventures in Worstpracticeville

Left by shev at 7/4/2012 8:19 PM
Nice idea, but you could have done the same in about 10-20 lines of code, by saving it as a csv or xml, dropping the file on the webserver or embedding it as a resource in the assembly (obviously the 1st choice is better), then parse the csv in a loop adding the lookups.

# re: Adventures in Worstpracticeville

Left by jason at 7/4/2012 8:28 PM

That actually would have been pretty cool, but then I'm sure the story would have been less amusing to me. Maybe next time.

# re: Adventures in Worstpracticeville

Left by jason at 7/4/2012 8:33 PM
Those interested in Shev's solution:

# re: Adventures in Worstpracticeville

Left by shev at 7/4/2012 8:42 PM
On reflection, i think your solution is more appropriate, sort of thumbing your nose at the imposition. In other words, if you were a c# programmer asked to do that job, you should/would do it my way (assuming the database change wasn't an option). But if you are a sharepoint admin and lazy/cheap management force you to do something outside your job description, why not mock them :-)

# re: Adventures in Worstpracticeville

Left by Kimmo at 9/5/2012 2:30 AM
Should've left some easter egs for the next poor bastard.
Comments have been closed on this topic.
Vaccination Saves Lives: Stop The Australian Vaccination Network
Say NO to the National School Chaplaincy Program