Simplifying ADO.NET Data Access, Part 6

Time to finish up the series on simplifying data access using ADO.NET. We have simplified everything around ADO.NET except for actually retrieving the results. This is probably the most complicated part of ADO.NET given the need for cleaning up the reader, enumerating the results and reading the actual data. We can simplify all this logic down to a single line (plus any action to take for each row).

ExecuteQueryWithResults

To simplify the discussion we will limit results to a single result set coming from the database. This is pretty standard. If we wanted to support multiple result sets then we could expand the syntax and perhaps chain on multiple actions but that is left for an exercise. To make the actions easier we will also rely on the DataReaderExtensions we added in an earlier article. Let’s start with the standard ADO.NET you might right today.

using (var dr = source.ExecuteReader(cmd))
{
    while (dr.Read())
    {
        roles.Add(new Role(dr.GetInt32OrDefault("Id")) 
                             { Name = dr.GetStringOrDefault("Name") });                            
    };
};

We will eliminate the need for the boilerplate code to clean up the reader and enumerate the results. All we, the client, really care about is the logic around converting the current record to usable data so that is where the focus should be.

source.ExecuteQueryWithResults(cmd, r =>
    new Role(r.GetInt32OrDefault("Id")) { Name = r.GetStringOrDefault("Name") }
    );

To set this up we will add a new method to ConnectionManager that takes a command to execute and a function to translate the current reader’s record to a type defined by the generic parameter of the method. The method itself is straightforward as it simply executes the code given earlier but calls a function instead of hard coding any sort of record processing results.

public TResult[] ExecuteQueryWithResults<TResult>(DataCommand command, Func<DbDataReader, TResult> converter)
{
    if (converter == null)
        throw new ArgumentNullException("converter");

    var items = new List<TResult>();
    using (var dr = ExecuteReader(command))
    {
        while (dr.Read())
            items.Add(converter(dr));
    };

    return items.ToArray();
}

The included code has a couple of overloads to support passing user-provided data, retrieve only a single record and a few other options. Notice that the method returns an array rather than IEnumerable. This was a purposeful decision. An array makes it clear that the results will all be processed and the connection closed before the method returns. This eliminates potential issues with cleanup or multiple record sets being open at the same time.

Transactions

The final remaining simplification to ADO.NET is not really a simplification but rather consistency– transactions. A transaction is about as simple as you get but to be consistent (and provider agnostic) we provide a wrapper around a transaction.

DataTransaction is a wrapper around a provider transaction. It does provide a useful feature in that it raises an event when the transaction is completed (rolled back or committed). This is useful in cases where code is waiting on a commit to get back a primary key or something similar.  The basic code is provided below.

public class DataTransaction : IDisposable
{
    public DataTransaction ( DbTransaction innerTransaction ) : this(innerTransaction, true)
    { }

    public DataTransaction ( DbTransaction innerTransaction, bool closeConnection )
    {
        m_inner = innerTransaction;
        m_autoClose = closeConnection;
    }

    public event EventHandler Committed;

    public event EventHandler RolledBack;

    public void Commit ( )
    {
        CommitBase();
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    public void Rollback ( )
    {
        RollbackBase();
    }        
}

The bulk of the code is for checking state, handling errors and cleaning things up. Look at the included code for more information.

Adding transaction support to the remaining code mostly involves adding overloads to allow the transaction to be passed in. ConnectionManager needs to be updated to begin the transaction.

public DataTransaction BeginTransaction ()
{
    return BeginTransactionCore(IsolationLevel.ReadCommitted);
}

protected virtual DataTransaction BeginTransactionCore (IsolationLevel level)
{
    DbConnection conn = null;
    try
    {
        conn = CreateConnectionBase(ConnectionString);

        return new DataTransaction(CreateTransactionBase(conn, level), true);
    } catch (Exception)
    {
        if (conn != null)
            conn.Dispose();

        throw;
    };
}

The real work happens when the underlying connection is created by the provider-specific connection manager. It is updated to be transaction aware.

private ConnectionData CreateConnectionData ( DataTransaction transaction )
{
    ConnectionData data = null;

    try
    {
        if (transaction != null)
            data = new ConnectionData(transaction.InnerTransaction);
        else
            data = new ConnectionData(CreateConnectionBase(ConnectionString));

        PrepareConnectionCore(data);
        return data;
    } catch
    {
        if (data != null)
            data.Dispose();

        throw;
    };
}

Because we already set up ConnectionData to support transactions originally the remaining code does not have to change.

The transaction itself is created by the provider-specific connection manager. For most ADO.NET providers we can implement this via the DbProviderFactory.

protected override DbTransaction CreateTransactionBase (DbConnection connection, IsolationLevel level)
{
    if (connection.State != ConnectionState.Open)
        connection.Open();

    return connection.BeginTransaction(level);
}

Final Thoughts

This completes this series. There are some enhancements that could be made if you are interested. Here’s some things that I’ve either done or considered doing.

• Adding support for async operations
• Exposing schema information for a stored procedure or table (supported by ADO.NET but requires provider-specific coding)
• Adding support for multiple record sets

DataAccessDemo.zip