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.

Advertisements

Command-Line into ClickOnce

 

The company I’m with right now has totally bought into ClickOnce. I’ll admit that before I started working here I’d never heard of it. In theory it seems like a nice idea, and generally it seems to work well, but like a lot of Microsoft products, it’s kind of stagnating. I get the impression Microsoft is calling it done on this one, and if you google it, you’ll get some great results like these.

I’m not here right now to discuss the merits or downsides of ClickOnce, I’m just going to describe my (painful) experiences attempting to get command-line access into my application.

I’m a big fan of shortcuts, especially AutoHotKey, so I thought I’d create a shortcut to some of the most-used functions. I’ve done it before and it worked great, how much different could this be…

First things first, ClickOnce doesn’t tell you where it’s installed, so part one of this, is find the application you’ve deployed. The best way I could find to do this was to go to the Start Menu and locate the shortcut. Normally, this would be just a shortcut, and point you in the right direction, right? Nope, it’s actually an application reference, which it turns out, you can use to reference the real app.

On my Win 8.1 system I found this shortcut here – C:\Users\**YOU**\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\MyAwesomeApp.

The application I was trying to use here was a WinForms app, so YMMV if you’re trying this in WPF or straight up in a console app. Hopefully it’s similar enough that you can hack along here with me.

OK, locate the application entry point, in WinForms this is Program.cs. You’ll need to add a check for the args being passed in and, in my case, attach a console app so the user can see what’s actually happening as you’re working.

static class Program
{
    [DllImport("kernel32.dll")]
    private static extern bool AttachConsole(int dwProcessId);
    private const int ATTACH_PARENT_PROCESS = -1;

    static void Main()
    {
        Initialize();
    }

    private static void Initialize()
    {
        // Read the command line arguments
        string[] args = AppDomain.CurrentDomain.SetupInformation.ActivationArguments.ActivationData;

        if (args == null || args.Length == 0)
        {
            Application.Run(new Form1());
        }
        else
        {
            // Only attach the console when using the command line
            AttachConsole(ATTACH_PARENT_PROCESS);

            CmdLineHandler cmdLineObject = new CmdLineHandler(args[0].Split('!'));
        }
    }
}

So, as you can see, I’m PInvoking the console control (who doesn’t love a good pinvoke). In Initialize(), I’m checking to see if any args are being passed to the ClickOnce app using

AppDomain.CurrentDomain.SetupInformation.ActivationArguments.ActivationData;

This way I can start up the regular old forms app if no args are passed, or jump to the console app if there are. Then I’ve created a nice CmdLineHandler class to handle all the string parsing magic to make this work.

Here’s the important part, I’m serious, don’t skim over this part – you CANNOT use comma or space to separate your args when command-lining into a ClickOnce app. OK, I think I’ve bolded enough to make that stand out. For some reason, the comma in your args get’s swallowed. I’m not sure where it happens, but I know it happened every single time (I tried a bunch of different ways). Feel free to give it a try, hey, if you get it working, let me know!

OK, so I’m splitting those input args by !, which looks weird, but works fine. Once you’ve got the args, it’s up to you, I’m sure you’ll be fine.

Here’s the final command to my app

C:\Users\**YOU**\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\MyAwesomeApp -option1!yes!-option2!no

Again, I’m not endorsing ClickOnce, or this really, but if you’re stuck in this situation at work, this is my workaround.

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!