SQL ADO.NET shortcut extensions (old school!)

As much as I love me some ORM's (I've used LINQ to SQL quite a bit, and for the MSDN/TechNet Profile and Forums we're using NHibernate more and more), there are times when it's appropriate, and in some ways more simple, to just throw up so old school ADO.NET connections, commands, readers and such. It still feels like a pain though to new up all the stuff, make sure it's closed, blah blah blah. It's pretty much the least favorite task of writing data access code. To minimize the pain, I have a set of extension methods that I like to use that drastically reduce the code you have to write. Here they are...

public static void Using(this SqlConnection connection, Action<SqlConnection> action)
{
    connection.Open();
    action(connection);
    connection.Close();
}

public static SqlCommand Command(this SqlConnection connection, string sql)
{
    var command = new SqlCommand(sql, connection);
    return command;
}

public static SqlCommand AddParameter(this SqlCommand command, string parameterName, object value)
{
    command.Parameters.AddWithValue(parameterName, value);
    return command;
}

public static object ExecuteAndReturnIdentity(this SqlCommand command)
{
    if (command.Connection == null)
        throw new Exception("SqlCommand has no connection.");
    command.ExecuteNonQuery();
    command.Parameters.Clear();
    command.CommandText = "SELECT @@IDENTITY";
    var result = command.ExecuteScalar();
    return result;
}

public static SqlDataReader ReadOne(this SqlDataReader reader, Action<SqlDataReader> action)
{
    if (reader.Read())
        action(reader);
    reader.Close();
    return reader;
}

public static SqlDataReader ReadAll(this SqlDataReader reader, Action<SqlDataReader> action)
{
    while (reader.Read())
        action(reader);
    reader.Close();
    return reader;
}

It has been awhile since I've really revisited these, so you will likely find opportunity for further optimization. The bottom line here is that you can chain together a bunch of these methods to make a much more concise database call, in terms of the code on your screen, anyway. Here are some examples:

public Dictionary<string, string> Get()
{
    var dictionary = new Dictionary<string, string>();
    _sqlHelper.GetConnection().Using(connection =>
        connection.Command("SELECT Setting, [Value] FROM Settings")
            .ExecuteReader()
            .ReadAll(r => dictionary.Add(r.GetString(0), r.GetString(1))));
    return dictionary;
}

or...

public void ChangeName(User user, string newName)
{
    _sqlHelper.GetConnection().Using(connection =>
        connection.Command("UPDATE Users SET Name = @Name WHERE UserID = @UserID")
            .AddParameter("@Name", newName)
            .AddParameter("@UserID", user.UserID)
            .ExecuteNonQuery());
}

The _sqlHelper.GetConnection() is just some other code that gets a connection object for you. You might have an even cleaner way to take that step out entirely. This looks more fluent, and the real magic sauce for me is the reader bits where you can put any kind of arbitrary method in there to iterate over the results.

4 Comments

  • If you are going to have an extension method called using, you should at least have it use a using statement or mimic what a using statement does.

    using(connection){
    connection.Open();
    action(connection);
    connection.Close();
    }

    -- OR --

    try {
    connection.Open();
    action(connection);
    connection.Close();
    } finally {
    connection.Dispose();
    }

  • By using SqlConnection/SqlCommand/etc, you're limiting these functions to SQL Server solutions. Try using the System.Data.Common.Db... types or the System.Data.IDb... interfaces instead.

    Your ReadAll and ReadOne methods seem quite strange. Why are you returning a closed SqlDataReader to the caller? Why do you need a closure to return the data as a list? Why do you even need to worry about calling ExecuteReader in the first place?

    I think it would be cleaner to have Select and SelectOne methods on the command. Your "Get" method would then become:

    public IDictionary Get()
    {
    using (var connection = GetConnection())
    using (var command = connection.Command("SELECT Setting, [Value] FROM Settings"))
    {
    return command
    .Select(r => new { Setting = r.GetString(0), Value = r.GetString(1) })
    .ToDictionary(s => s.Setting, s => s.Value);
    }
    }

    The Select and SelectOne methods would take care of opening the connection (if necessary), calling ExecuteReader with the correct CommandBehavior, iterating the results and disposing of the data reader:

    private static IEnumerable SelectIterator([NotNull] DbCommand command, CommandBehavior behavior, [NotNull] Func selector)
    {
    using (DbDataReader reader = command.ExecuteReader(behavior))
    {
    foreach (IDataRecord record in reader)
    {
    yield return selector(record);
    }
    }
    }

    public static IEnumerable Select(this IDbCommand command, Func selector)
    {
    if (null == command) throw new ArgumentNullException("command");
    if (null == selector) throw new ArgumentNullException("selector");

    CommandBehavior behavior = CommandBehavior.SingleResult;
    if (null != command.Connection && ConnectionState.Open != command.Connection.State)
    {
    command.Connection.Open();
    behavior |= CommandBehavior.CloseConnection;
    }

    return SelectIterator(command, behavior, selector);
    }

    public static T SelectOne(this IDbCommand command, Func selector)
    {
    if (null == command) throw new ArgumentNullException("command");
    if (null == selector) throw new ArgumentNullException("selector");

    CommandBehavior behavior = CommandBehavior.SingleResult | CommandBehavior.SingleRow;
    if (null != command.Connection && ConnectionState.Open != command.Connection.State)
    {
    command.Connection.Open();
    behavior |= CommandBehavior.CloseConnection;
    }

    return SelectIterator(command, behavior, selector).FirstOrDefault();
    }

  • It's funny you say old school, but often I prefer to live in a world of IDbConnections with DataReaders. Not that data frameworks are bad, but I find I can always do precisely what I want to do down to the detail if I take that route.

  • I understand the desire to use the interfaces instead, but really, I never, ever use something that's not SQL Server. That's fine if you want that optimization, but for cut-and-paste code (I'd never commit this to a library), it's fine.

    And sure, you can put the execution of the reader as an extension of command. I did not in this case, in the event that I want to mess with some other members of the reader before grabbing the data. YMMV. Optimize as needed.

Comments have been disabled for this content.