Connection Management

The Data Access class maintains a connection property and a provider name, which are used to open the connection, perform the data operation and close the connection. In most cases you can just create the object and call a single method to perform a data operation and the connection management is handled for you.

Connections are opened and closed automatically

By default connections are opened and closed automatically as needed. So when you run a SQL command like Execute or ExecuteNonQuery or Query<T> a connection is opened, the command is run and then the connection is closed.

This is true of most commands that are truly transactional and don't return a stream of data.

DataReader and IEnumerable Results leave Connections open

The exception are DataReader and IEnumerable results which require that the connection stays open. The result types are not retrieved all at once, but rather are read as the data is enumerated and so connections have to stay open until you've used all of the data.

The easiest way to deal with this is to wrap the enumerable data access into a using(){ } block that will automatically .Dispose() of the connection when you reach the end of the block. You can also explicitly call .Dispose() on the connection, or explicitly call data.CloseConnection() to ensure the connection is closed.

// ensure that the connection will be closed after you're done reading
using (var data = new SqlDataAccess("WebStoreConnection"))
{
    var reader = data.ExecuteReader("select * from customers");

    Assert.IsTrue(reader.HasRows);
                
    while (reader.Read())
    {
        Console.WriteLine((string)reader["LastName"] + " " + (DateTime)reader["Entered"]);
    }
}

Transactions Keep Connections Open

If you use transactions to wrap multiple commands that have to be processed and succeed in a single atomic operation, a connection is opened and kept open until the transaction is either committed or rolled back. Calling .CloseConnection() in this scenario has no effect.

To start a transaction, call .BeginTransaction() before a request to force a connection to be opened. While a Transaction is active in the context of a Data Access object the connection is kept open, until .CommitTransaction() or .RollbackTransaction() is called or the object is disposed of.

using (var data = new SqlDataAccess("WebStoreConnection"))
{
    data.BeginTransaction();

    // connection stays open for both commands
    data.ExecuteNonQuery("delete from lineitems where invId={0}",invId);
    data.ExecuteNonQuery("delete from invoices where Id={0}",invId);
    
    data.CommitTransaction();
    
    invId = 555;  // different invoice
    
    // new connection retrieved for each command (no transaction - not recommended for this op)
    data.ExecuteNonQuery("delete from lineitems where invId={0}",invId);
    data.ExecuteNonQuery("delete from invoices where Id={0}",invId);
    
}  // implicitly rolls back if CommitTransaction() has not been called

SqlDataAccess Lifetime Management

I recommend that for each operation or set of transacted operations you perform, you create a new instance of the Data Access object and use a using(){ } block around the operation. This ensures the object is created and connections are proeprly cleaned up even if there is an exception or other error that causes the application to jump out of the processing path.

It's not required but will make it less likely you'll run into trouble with hanging orphaned connections in your applications.