Idiom for using SqlDataReader as a resource

Question

Following on from this question, I find myself writing the following code over and over again:

SqlCommand command = new SqlCommand();
// Code to initialize command with what we want to do
using (SqlConnection connection = openConnection())
{
    command.Connection = connection;
    using (SqlDataReader dataReader = thisCommand.ExecuteReader())
    {
        while (dataReader.Read())
        {
            // Do stuff with results
        }
    }
}

It's rather tedious to have to nest the two using statements. Is there a way to tell SqlDataReader that it owns the command, and tell the command that it owns the connection?

If there was a way of doing that then I could write a helper method which could be called like so:

// buildAndExecuteCommand opens the connection, initializes the command
// with the connection and returns the SqlDataReader object. Dispose of the
// SqlDataReader to dispose of all resources that were acquired
using(SqlDataReader reader = buildAndExecuteCommand(...))
{
    // Do stuff with reader
}

Or do I have to bite the bullet and write my own wrapper over SqlDataReader?

Answer 1

you'll have to build the wrapper by yourself. or you can use an ORM if that's an option.

Answer 2

One thing would be to write a method which did the disposal for you, calling back into a delegate with each result. For example:

using (SqlConnection connection = openConnection())
{
    command.Connection = connection;
    ExecuteReaderWithCommand(command, reader =>
    {
        // Do stuff with the result here.
    });
}

Then ExecuteReaderWithCommand would be something like:

public static void ExecuteReaderWithCommand(SqlCommand command,
    Action<SqlDataReader> action)
{
    using (SqlDataReader dataReader = thisCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            action(reader);
        }
    }
}

You could make this an extension method on SqlCommand if you wanted to. Heck, you could go to town and make it open the connection for you as well if you wanted... the more you can abstract away the idea of "open / use / close", the better.

Answer 3

You can write something like this, and tell dataReader to close connection after use:

SqlCommand command = new SqlCommand();
command.Connection = openConnection();
using (SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
    while (dataReader.Read())
    {
        // Do stuff with results
    }
}

However it is better to explicitly close connection, because exception can occur between connection opening and ExecuteReader.

Answer 4

Why not look at the Enterprise Library DAAB?

Here's a code sample from the documentation, adjusted for your scenario:

Database db = DatabaseFactory.CreateDatabase();

using (IDataReader reader = db.ExecuteReader(CommandType.Text, "SQL here..." ))
{
   while (reader.Read())
    {
        action(reader);
    }
}

Answer 5

Using a delegate such as Action<T> is of course an option, but I used have a set of overloads similar to the one below since .NET 1.0. The caller uses a using block to dispose the returned reader, which in turn disposes the connection.

public IDataReader ExecuteReader(string commandText, CommandType commandType, 
                                      IDbDataParameter[] parameters)
{
    IDbConnection connection = CreateConnection();
    try
    {
        IDbCommand command = CreateCommand(commandText, connection);
        command.CommandType = commandType;
        AppendParameters(command, parameters);
        connection.Open();
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch
    {
        connection.Close();
        throw;
    }
}