Searching In SQL

My current job has me working in a massive old SQL server environment a lot of the time. I’m trying to update an old VB6 application that has hundreds of stored procedures doing random pieces of the work along the way.

Since this is my first time using SQL in any meaningful way, I’m not 100% sure if this is a normal setup, but in the server there are 100s of tables and also 100s of stored procedures, oh and just for fun, there are 100s of functions of each type. This server was created a long time ago and not a lot of thought was put into the naming of anything, which leads me to having a nightmare finding anything.

Here’s an example of a couple of stored procedures I have

usp_GetSomethingImportant

usp_Get_SomethingImportant

usp_Get_Something_Important

Every time the underscore is in a different position, all the sorting moves them around, so even though these 3 stored procedures are used together they’re sorted differently!

Never mind the fact that there are multiple different types of Functions in SQL (I’m not exactly sure why or what the differences are, but they exist).

So, why am I telling you all of this, is it just to complain? Well, partly (it is ridiculous), but mostly I’m here to talk about my favorite new SQL helper utility!

It’s called SQL Hunting Dog. I honestly can’t believe this isn’t a feature just built into SQL, but here we are. Microsoft seems to have major blind spots.

Hunting Dog lets you search a sql database for anything based on the name. I know, right, soooooooo simple, but there it is. Once you find the thing you’re looking for, it can either do it’s own Select or Modify query, or it can expand all the folders in the sql explorer section to jump you right to the thing you’re looking for. That’s my favorite feature, as then you have access to all the usual SQL magic (create to, modify etc).

Also, if you’re into it (you probably are if you’re reading this blog), the source code is available on BitBucket (the link is on the website), so if there’s something you’re not completely on board with, feel free to tinker away.

If you don’t already have this, you should get it.

Expression Trees

If you’re anything like me, you’re lazy.

I choose a lazy person to do a hard job. Because a lazy person will find an easy way to do it.

– Bill Gates

Apparently, in the world of programming, being lazy is a virtue, so maybe by passing this along I’m being virtuous?!?

I came across a library I had to implement this week that had function calls down to some hardware. Now since this library was a wrapper for a C (or C++) library, and whoever wrote it didn’t want to put a lot of work into it, every function called would return an ErrorCode enum.

Now if I was to be a copy and paste wizard, I’d just write myself about 20 of these

var errorCode = HARDWARE.GetSomeInformation(1, true, ref why???);

if (errorCode != ErrorCodes.None)
    Throw new CustomErrorException();

Obviously, that sucks. Nobody wants to do this. If you want to do this, you’re crazy. Even if you’ve got all the time in the world, or you’re a Regex master, or you’re using a tool called NimbleText you still shouldn’t do this. I think this is what people refer to as brittle code. If anything ever changes, or you realize you wanted to do something else with that error code, you’ve got to change it in 20 places (or more, how big is this library anyway?).

I decided to wrap this error call in a nice function delegate that would allow me to put all my ErrorCode handling in one place and just get on with stuff. Here’s what I ended up with

..
HARDWAREWrapper(() => HARDWARE.GetSomeInformation(1, true, ref why???));
..

private void HARDWAREWrapper(Func<ErrorCodes> functionToCall)
{
    var errorCode = functionToCall();

    if (errorCode != ErrorCodes.None)
        Throw new CustomErrorException();
}

OK, much better, now if anything in those function calls changes generically, or I decide to add more information to my exception we’re all good. It’s just one spot. Single responsibility principle? Not sure if that exactly counts, but my goal today is to sound smart by using lots of programming words (let me know how you think I’m doing).

So, here we go with the downside of this, since this is a generic call, it’s a little harder to figure out what exactly caused the exception in the first place. Since any code could be in that function (as long as it returns ErrorCodes) it’s a little harder to debug, especially if this is in the field and all.

Luckily I came across something pretty sweet called an Expression. I can wrap the Func<ErrorCodes> in this Expression to allow me access to the actual text of the function for debugging purposes. I’m sure that’s not what it was designed for, but it’s what I’m using it for today. Here’s the code –

..
HARDWAREWrapper(() => HARDWARE.GetSomeInformation(1, true, ref why???));
..

private void HARDWAREWrapper(Expression<Func<ErrorCodes>> expression)
{
    var functionToCall = expression.Compile();

    var errorCode = functionToCall();

    if (errorCode != ErrorCodes.None)
    {
        Logger.LogtoFile(expression.ToString();
        Throw new CustomErrorException();
    }
}

As you can see, the call to the function doesn’t actually change, even though it’s wrapped in an Expression, but it does allow you to log the function delegate contents to file. With Expression.ToString() I get access to the line HARDWARE.GetSomeInformation(1, true, ref why???) in text format.

Awesome. OK, that’s all I’ve got. If anyone out there is listening, let me know what you think!

Naming things is hard

There are only two hard things in Computer Science: cache invalidation and naming things.

— Phil Karlton

I thought this was a good way to get rolling on this blog. I’ve always been really bad at introducing myself (in real life too) so just go with it. If it helps, just picture someone standing up in a room full of people sweating profusely and mumbling while reading this.

Naming things is really hard. This isn’t just in relation to computer science either. My wife and I spent months picking out our kids names (I think we only decided on the 2nd kids name on his due date).

I spend (probably too much) time every day sitting in front of my computer trying to think of a good name for something. When I finally decide that it sounds good, I plow onwards until I reach my next naming problem. The really bad part is that you end up with a lot of code that looks like this –

UnitBuilder unitBuilder = new UnitBuilder();

The new name you come up with is so good, you end up using it for an instance and a class. I’m not going to throw away gold like that.

Naming this blog was hard too. I think it took me close to a week to even come up with one name. They were all terrible of course, I mean how do you name something that can be about anything to do with programming. I don’t know about you, but I think the best programming blog name has to be CodingHorror. My rejects included NeverendingCoding and StumblingThroughCode. I even tried this whole blogging thing once before and came up with the super clever name of EternallyRefactoring (I told my wife and she didn’t know what that meant, probably a red flag).

Somehow, I thought I’d found a good one that nobody else was using, CodeBlock.wordpress.com, but of course that was already taken, along with CodeBlog, AnotherCodeBlog and countless other “clever” ideas that I had. Anyway, here’s where I ended up. The idea for me is that you insert a breakpoint into your coding day to read my blog. Of course, if you’re a web dev you might be wondering what the hell a breakpoint is anyway.

Here’s a little about me. I’m a self-taught software developer. I didn’t get started until I was around 24, learning Java first and then moving into .NET with VB and C#. I’m now working full-time as a software developer in a small department of a large company, doing WPF and C# exclusively. I like to mess around with side projects in my spare time. I’ve developed a simple kids learning game for Android and iOS, and I have a couple of projects on GitHub that I’ve cobbled together when I find time.

This blog is probably going to be a stream of consciousness. Like I said, I tried this blogging thing once before and it didn’t really work out that well. I felt a lot of pressure to try and blog regularly and for all my blogs to be well researched. Not this time my friends. I’m going to be writing about things I’ve stumbled across at work or things I think people need to take a look at. That’s it. Very little research. If you’re looking for that, you’re in the wrong place bud.

Alright, thanks for stopping by!