Mad Props!

On a recent .NET Rocks panel show, Ayende complained that the abstract classes to access databases (for example, IDbCommand) were lacking some fundamental pieces that make the concrete classes (eg SqlCommand) easier to use. The example he cited was the AddWithValue method, to quickly add a parameter to an SqlCommand along with its value.

I know how he feels; the act of calling CreateParameter, then setting the parameter's name and value, then calling Parameters.Add is a lot more code than it should be for such a simple operation.

So I've gone ahead and made myself a quartet of handy extension methods to give me similar functionality to the AddWithValue method. In fact, my version is a little more powerful, since it accepts nullable types and passes DBNull if the value isn't specified. Here's the code:

internal static class DbCommandExtensions
{
    internal static int AddInputParameter<T>(this IDbCommand cmd, 
        string name, T value) 
    {
        var p = cmd.CreateParameter();
        p.ParameterName = name;
        p.Value = value;
        return cmd.Parameters.Add(p);
    }

    internal static int AddInputParameter<T>(this IDbCommand cmd, 
        string name, Nullable<T> value) where T : struct
    {
        var p = cmd.CreateParameter();
        p.ParameterName = name;
        p.Value = value.HasValue ? (object)value : DBNull.Value;
        return cmd.Parameters.Add(p);
    }

    internal static int AddInputParameter(this IDbCommand cmd, 
        string name, string value) 
    {
        var p = cmd.CreateParameter();
        p.ParameterName = name;
        p.Value = string.IsNullOrEmpty(value) ? DBNull.Value : (object)value;
        return cmd.Parameters.Add(p);
    }

    internal static IDbDataParameter AddOutputParameter(this IDbCommand cmd, 
        string name, DbType dbType)
    {
        var p = cmd.CreateParameter();
        p.ParameterName = name;
        p.DbType = dbType;
        p.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(p);
        return p;
    }
}

So this gives us essentially two new methods on IDbCommand, "AddInputParameter" and "AddOutputParameter". You use them like this:

var cmd = _connection.CreateCommand();

cmd.AddInputParameter("@id", eventDate);
var idParam = cmd.AddOutputParameter("@id", DbType.Guid);

Pretty straight forward, I think, and it saves a lot of code. It also hasn't broken any of our tests, because all the expectations we've set on our mocks are still being met.