Although this may come as a surprise to some, as I work at a company which happens to produce simple tools for database professionals, I am not the world's biggest fan of databases. "Don't get me wrong", as the poet said: they are extraordinarily powerful and highly necessary. Many splendid online games, in which I have been known to occasionally indulge, would not function without a rather spiffy database behind the scenes, doing the grunt work of serving up interesting data concurrently to millions around the globe.

However, I usually find my more direct encounters with databases in the day to day course of developing desktop software to be, if not vexing, then at least very much a case of rolling up the sleeves, getting a cup of coffee, and bending my brain to that database's particular way of working. Or more specifically, the way of working with the particular API de jour.

Currently that happens to be .NET's SqlClient namespace, and I have one question. Why is it so difficult to call a stored procedure from .NET code?

Here's an example. I'll assume for brevity that I already have a nice SqlConnection set up. I'm going to call a hypothetical "hello world" procedure that will display the name with which it is supplied.

I should mention that I don't intend to exhaustively explain the workings of the SqlClient classes for the purposes of this

internal void sayHello(string name)
{
    using ( SqlCommand command = new SqlCommand( "EXECUTE sp_HelloWorld @Name", m_Connection )
    {
       command.Parameters["Name"] =  name;
       command.ExecuteNonQuery();
    }
}

So here we're being fairly nice. We're using SqlParameter objects rather than munging the command string together by hand: this protects against SQL injection attacks, and should avoid us having to manually massage .NET types into the correct format for a SQL query. We're taking advantage of the type-agnostic approach to using the Parameters collection, where it accepts an object and will automatically figure out the correct SQL type based on the underlying .NET type. Shiny.

What's so hard about that, you may ask? Well, not a lot, in the simple case. Provided whenever I call a stored procedure I want to have this nice little block of code, rather than having a generalised abstraction. Which, incidentally, I don't. And provided I can trust the SqlParameters collection to correctly marshal my data types into their nice SQL equivalents. Which, it appears, I can't.

All I'd like to do is have a function as follows:

internal void ExecuteStoredProcedure(string name, params object[] args);

This works like string.Format(): you can pass a variable number of arguments after the stored procedure name. So I can say:

ExecuteStoredProcedure("sp_HelloWorld", name);

Well, this turns out to be easy enough. Here's some code for you. It's rather rough, ready and unoptimised, to make the example simpler. But to make things interesting, it does take two slightly different but equivalent approaches to creating parameter names. And it has some hard coded limits. I know, I need slapping for this, but it's easily fixable, and it's Friday afternoon.

internal int ExecuteNonQueryStoredProcedure(string procedureName, params object[] args)
{
    return ExecuteNonQueryCommand(GetExecuteCommand(procedureName, args), args);
}

internal string GetExecuteCommand(string procedureName, object[] args)
{
    string command;
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("EXECUTE {0}", procedureName);
    int nArgsSoFar = 0;
    if (args != null)
    {
        // longhand
        //
        for (int iArg = 0; iArg < args.Length; iArg++)
        {
            if (nArgsSoFar != 0)
                sb.AppendFormat(", @P{0}", iArg);
            else
                sb.AppendFormat(" @P{0}", iArg);

            nArgsSoFar++;
        }
    }
    command = sb.ToString();
    return command;
}

internal int ExecuteNonQueryCommand(string template, params object[] args)
{
    using (SqlCommand command = CreateSqlCommand(template, args))
    {
        return command.ExecuteNonQuery();
    }
}

internal SqlCommand CreateSqlCommand(string query, params object[] sqlParameters)
{
    SqlCommand command = new SqlCommand(query,m_Connection);
    if (sqlParameters != null)
    {
        int iParam = 0;
        string[] paramNames = new string[] { "P0", "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13", "P14", "P15", "P16", "P17", "P18", "P19" };
        if ( sqlParameters.Length > 20 )
            throw new ArgumentException(Localize.Me("Too many arguments supplied to SQL command creator; maximum is 20."));

        foreach ( object arg in sqlParameters )
        {
            object o = arg;

            SqlParameter param = new SqlParameter(paramNames[iParam],o);
            iParam++;

            command.Parameters.Add(param);
        }
    }
    return command;
}

So, that's all fine and splendid.

Or so I thought, until I tried to use it.

Now mostly this works very nicely, as it should since it's not being particularly clever. The problem occurred when I tried to pass a NULL value to a stored procedure in this fashion:

ExecuteStoredProcedure("sp_HelloWorld",null);

Now naturally if my stored procedure isn't expecting this, it should complain. However, we didn't get that far. .NET threw a tantrum way before then:

RedGate.HelloWorld.HelloTest.Test1 : System.Data.SqlClient.SqlException : Prepared statement '...' expects parameter @P0, which was not supplied.

Having discovered the source of this, I'm more than a teensy bit vexed by it.

It seems that the SqlParameter class, whilst able to freely handle int, DateTime, string, wildebeast, diplodocus and assorted other standard goodies, has a bit of a problem with null. It doesn't realise that a null string should be passed in as DBNull.Value. Which seems a fraction catastrophically dim of it.

The solution is easy enough. We can intercept parameters before they hit SqlParameter, and massage nulls into DBNull.Value ourselves:

internal SqlCommand CreateSqlCommand(string query, params object[] sqlParameters)
{
    SqlCommand command = new SqlCommand(query,m_Connection);
    if (sqlParameters != null)
    {
        int iParam = 0;
        string[] paramNames = new string[] { "P0", "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13", "P14", "P15", "P16", "P17", "P18", "P19" };
        if ( sqlParameters.Length > 20 )
            throw new ArgumentException(Localize.Me("Too many arguments supplied to SQL command creator; maximum is 20."));

        foreach ( object arg in sqlParameters )
        {
            object o = arg;
      
            if ( o == null )
               o = DBNull.Value;

            SqlParameter param = new SqlParameter(paramNames[iParam],o);
            iParam++;

            command.Parameters.Add(param);
        }
    }
    return command;
}

And Bob is a close relative.

As usual though, I'm left beating my head against a table, wondering why, why, and even, why, this occurs? Am I missing something intrinsically difficult about this? Now I'm sure someone's going proffer the explanation that database NULL is distinct from, say, a C++ NULL, which is in fact just a pointer to memory location zero. Even assuming I agree that such a distiction is worth making, it doesn't apply here. a C++ null is a genuine null reference. Now at a purely semantic level, this is may be arguably not the same as a valid reference to a nullable type with a value of null. But, forgive me, I really don't care in the slightest. All I wish to express, clearly and succinctly, is the absence of a value, and I expect to be able to do that in the standard fashion for my language of choice. After all, if I don't have to convert between DateTime and SqlDateTime, which on earth should I care which flavour of null this API happens to prefer?

I think the phrase is, Do What I Mean.