Encapsulating and Disposing SqlClient Objects In Order

Question

I am trying to layer my Sql client object calls such that they get disposed of reliably. Something like this:

Open database connection -> Create command -> Read results -> close command -> close database connection

So far this has succeeded when I do all of these things in the same method.

The problem is this is error prone. And a mess to read through.

When I try to create a common method to handle this that cleans up everything and returns a reader the connection gets closed before the reader starts.

//closes connection before it can be read...apparently the reader doesn't actually have any data at that point ... relocating to disposable class that closes on dispose
public SqlDataReader RunQuery(SqlCommand command)
{
    SqlDataReader reader = null;
    using (var dbConnection = new SqlConnection(_dbConnectionString))
    {
        try
        {
            dbConnection.Open();

            command.Connection = dbConnection;

            reader = command.ExecuteReader();  // connection closed before data can be read by the calling method
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
        }
        finally
        {
            dbConnection.Close();
        }
    }

    return reader;
}

I can get around this by creating my own class that implements IDispose (etc) but then when I wrap it with the same using statement it takes up just as many lines as a database connection using statement.

How can I take care of the data base connection in a repeatable class that takes care of all these artifacts and closes the connection?

Answer 1

so there's no way to make a reusable method that tucks away all/most of the nested using statements?

There is a specific pattern supported for returning a DataReader from a method, like this:

static IDataReader GetData(string connectionString, string query)
{
    var con = new SqlConnection(connectionString);
    con.Open();
    var cmd = con.CreateCommand();
    cmd.CommandText = query;
    var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    return rdr;
}

Then you can call this method in a using block:

    using (var rdr = GetData(constr, sql))
    {
        while (rdr.Read())
        {
            //process rows
        }
    } // <- the DataReader _and_ the connection will be closed here