views:

233

answers:

5

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?

A: 

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

Mladen Prajdic
+3  A: 

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.

Jon Skeet
+1, a lambda would be the cleanest. I was thinking the same thing, but as usual Mr Skeet is quick on the draw :-)
Dan F
I agree with "the more you can abstract away the idea of "open / use / close"- that's what DAAB does to some degree.
RichardOD
Yeah... using .NET 2.0 though so the delegate syntax ends up being pretty clunky. *sigh*
Paul Hollingsworth
+1  A: 

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.

arbiter
A: 

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);
    }
}
RichardOD
Because ADO.NET is suitable for most needs, and ofter there is no necessity in [yet] another lib.
arbiter
@Arbiter. I never said use, I said look at. It solves the problem. It is really down to Paul to decide whether having "another lib" is a problem.One of the design goals of DAAB is to prevent developers repeatedly writing the same code.
RichardOD
Agreed, decision is always up to author :)
arbiter
A: 

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;
    }
}
Joe