Playing with C# Func<T, TResult> and ADO.NET

I’ve always liked the look of the generic Func and I think the possibilities behind it are awesome. However, it’s usually difficult for me to find a good place to use it. I’m probably just not that used to using it yet and I am missing out on some huge advantages. Ignorance is not bliss sometimes :)

So anyhow, I was playing with the good ole ExecuteScalar & ExecuteNonQuery commands on the IDbCommand interface and came up with something kinda cool. At least I thought so…

I tend to use a DbHelper when I’m doing easy things instead of using NHibernate, Linq2Sql, etc, etc. Well here’s what I started with:

public class DatabaseHelper
{
public static T ExecuteScalar<T>(string connstring, string sql, params IDataParameter[] parameters)
{
    using (var oConn = new SqlConnection(connstring))
    {
        oConn.Open();
        using (var oCmd = new SqlCommand(sql, oConn))
        {
            oCmd.Parameters.AddRange(parameters);
            return (T) oCmd.ExecuteScalar();
        }
    }
}

public static void ExecuteNonQuery(string connstring, string sql, params IDataParameter[] parameters)
{
    using (var oConn = new SqlConnection(connstring))
    {
        oConn.Open();
        using (var oCmd = new SqlCommand(sql, oConn))
        {
            oCmd.Parameters.AddRange(parameters);
            oCmd.ExecuteNonQuery();
        }
    }
}
}

Obviously not pretty and a TON of duplicated code. VERY SMELLY!

So the first thing I did was create a DatabaseCriteria class to help with the number of params I’m passing and it looks like this:

public class DatabaseCriteria
{
    public DatabaseCriteria(string connstring, string sqlStatement, params IDataParameter[] parameters)
    {
        ConnectionString = connstring;
        SqlStatement = sqlStatement;
        Parameters = parameters;
    }
    public string ConnectionString { get; set; }
    public string SqlStatement { get; set; }
    public IDataParameter[] Parameters { get; set; }
}

That’s a little better and it’s easy to modify the existing code that calls my helpers. The next thing I did was create small factories for my connection & command so I wasn’t bound to the Sql Server version and I could easily swap out databases. The builders look like this:

private static IDbConnection BuildConnection(string connstring)
{
    return new SqlConnection(connstring);
}

private static IDbCommand BuildCommand(string sqlStatement, IDataParameter[] parameters, IDbConnection connection)
{
    var command = new SqlCommand(sqlStatement, (SqlConnection)connection);
    command.Parameters.AddRange(parameters);
    return command;
}

Obviously I’m using some SqlCommand features like the AddRange and I’m casting straight to the SqlConnection, but that code is only in one spot and it’d be easy to setup an IoC tool or something. I want to keep this blog simple though, so I’ll leave it as is for now.

Okay, so now let’s get to the fun stuff! Here’s my new ExecuteSql generic method:

private static T ExecuteSql<T>(DatabaseCriteria dbCriteria, Func<IDbCommand, T> func)
{
    using (var oConn = BuildConnection(dbCriteria.ConnectionString))
    {
        oConn.Open();
        using (var oCmd = BuildCommand(dbCriteria.SqlStatement, dbCriteria.Parameters, oConn))
        {
            return func.Invoke(oCmd);
        }
    }
}

Okay, so this accepts a DatabaseCriteria object and a generic Func that invokes an action on IDbCommand and returns T. It also takes advantage of the two factories I made and obviously the using statement.

Updated: 1/26/2010 8:16PM
Thanks to the comments of Thilak Nathen, I’ve made an improvement to the ExecuteScalar<T> method. Before him bringing it to my attention, my code could’ve thrown the dreaded “Object reference not set to an instance of an object” message. I think we all have grown to hate that message. Sadly it’s easy to check for and we sometimes just look over it. If you’re interested, the ExecuteScalar before the update was just one line and it was: return ExecuteSql(dbCriteria, x => (T) x.ExecuteScalar());

Thanks Thilak!

Now my DatabaseHelper looks like this:

public class DatabaseHelper
{
    public static T ExecuteScalar<T>(DatabaseCriteria dbCriteria)
    {
        var result = ExecuteSql(dbCriteria, x => x.ExecuteScalar());
        return (result is T) ? (T)result : default(T);
    }

    public static void ExecuteNonQuery(DatabaseCriteria dbCriteria)
    {
        ExecuteSql(dbCriteria, x => x.ExecuteNonQuery());
    }

    private static T ExecuteSql<T>(DatabaseCriteria dbCriteria, Func<IDbCommand, T> func)
    {
        using (var oConn = BuildConnection(dbCriteria.ConnectionString))
        {
            oConn.Open();
            using (var oCmd = BuildCommand(dbCriteria.SqlStatement, dbCriteria.Parameters, oConn))
            {
                return func.Invoke(oCmd);
            }
        }
    }

    private static IDbConnection BuildConnection(string connstring)
    {
        return new SqlConnection(connstring);
    }

    private static IDbCommand BuildCommand(string sqlStatement, IDataParameter[] parameters, IDbConnection connection)
    {
        var command = new SqlCommand(sqlStatement, (SqlConnection)connection);
        command.Parameters.AddRange(parameters);
        return command;
    }
}

I think there could be a little more clean up here, but basically I think it’s pretty clean. There is pretty much zero code duplication and no dependencies on implementations other than in my two builders. You should also notice that everything is private other than the ExecuteScalar and the ExecuteNonQuery because I only want people to use those two methods. Anyhow, I like it! Hopefully you do too.

Thanks for reading!