In part 3 of this series on simplifying ADO.NET data access we will finally switch gears and start providing a cleaner approach. Under the hood we will continue to use ADO.NET but it will be wrapped in a provider-agnostic layer that doesn’t require any of the boilerplate code that we are used to seeing with ADO.NET.

Design Philosophy

The core philosophy behind simplifying data access is to reuse as much of ADO.NET as possible. Unfortunately the design doesn’t make that simple. One of the biggest limitations is that most ADO.NET types are disposable. This makes even simple queries more complex because of the using statement. Our goal is to eliminate the need for this altogether. For symmetry purposes we will end up creating new types for the core ADO.NET objects. But unlike ADO.NET these types are provider-agnostic. There will be a provider-specific implementation but, for the most part, ADO.NET already provides that. Here’s the base new types and their ADO.NET equivalence.

• ConnectionManager (DbConnection) – Manages connections to a database and executing commands.
• DataCommand (DbCommand) – Represents a command to execute on the database.
• DataParameter (DbParameter) – Represents a parameter to a command.
• DataTransaction (DbTransaction) – Represents a database transaction.

ConnectionManager

ConnectionManager represents a connection to a database. The underlying connection is opened and closed as needed. As such there is no need for a using statement. In the cases where a connection needs to be kept open other mechanisms will handle the connection management. For the most part this type is simply a wrapper around a connection string with functionality to execute commands. Here is the base code.

public abstract class ConnectionManager
{
    protected ConnectionManager ( string connectionString )
    {
        if (connectionString != null)
            m_connString = connectionString.Trim();
    }

    public DataSet ExecuteDataSet ( DataCommand command )
    {
        using (var conn = CreateConnectionData())
        {
            return ExecuteDataSetCore(conn, command);
        };
    }

    public int ExecuteNonQuery ( DataCommand command )
    {
        using (var conn = CreateConnectionData())
        {
            return ExecuteNonQueryCore(conn, command);
        };
    }

    public DbDataReader ExecuteReader ( DataCommand command )
    {
        using (var conn = CreateConnectionData())
        {
            var dr = ExecuteReaderCore(conn, command);

            //The reader is now responsible for connection cleanup
            conn.Detach();

            return dr;
        };
    }

    public object ExecuteScalar ( DataCommand command )
    {
        using (var conn = CreateConnectionData())
        {
            return ExecuteScalarCore(conn, command);
        };
    }

    public T ExecuteScalar<T> ( DataCommand command )
    {
        var result = ExecuteScalar(command);

        return ((result != null) && (result != DBNull.Value)) ? (T)Convert.ChangeType(result, typeof(T)) : default(T);
    }

    public void FillDataSet ( DataCommand command, DataSet ds, string[] tables )
    {
        using (var conn = CreateConnectionData())
        {
            FillDataSetCore(conn, ds, command, tables);
        };
    }

    public void UpdateDataSet ( DataCommand insertCommand, DataCommand deleteCommand,
                                DataCommand updateCommand, DataSet ds, string table )
    {
        table = (table ?? "").Trim();
        if (String.IsNullOrEmpty(table) && (ds.Tables.Count > 0))
            table = ds.Tables[0].TableName;

        using (var conn = CreateConnectionData())
        {
            UpdateDataSetCore(conn, insertCommand, deleteCommand, updateCommand, ds, table);
        };
    }

    protected abstract DbCommand CreateCommandBase ( DataCommand command );

    protected abstract DbConnection CreateConnectionBase ( string connectionString );

    protected abstract DbDataAdapter CreateDataAdapterBase ();

Notice that it is an abstract class. This is the only place where client code needs to be aware of the database provider. Clients will create a provider-specific implementation but, even then, it can be hidden (using T4 or similar). The provider is responsible for creating the ADO.NET-specific types for the manager. Any number of managers can be created as needed by clients since none of them require cleanup.

For the most part the code simply wraps the underlying call to ADO.NET using standard boilerplate code. The public methods open the connection to the database and the –Core methods create the underlying database command and execute it. Here’s what one of the methods looks like.

protected virtual object ExecuteScalarCore ( ConnectionData conn, DataCommand command )
{
    using (DbCommand cmd = PrepareCommandCore(conn, command))
    {
        conn.Open();

        return cmd.ExecuteScalar();
    };
}

They are protected and virtual so that derived types can make changes if needed.

Currently this type does not support transactions. We will add support for this later. It will cause most of the connection-related code to alter but not significantly.

ConnectionData

ConnectionData is a simple wrapper type that manages an open connection to a database. It is used simply to separate connection management from the rest of ConnectionManager. It also provides a convenient structure for passing around in overridable methods.

public sealed class ConnectionData : IDisposable
{
    internal ConnectionData ( DbConnection connection )
    {
        m_connection = connection;
    }

    internal ConnectionData ( DbTransaction transaction )
    {
        m_transaction = transaction;
    }

    public DbConnection Connection
    {
        get { return m_connection ?? m_transaction.Connection; }
    }

    public DbTransaction Transaction
    {
        get { return m_transaction; }
    }

    public DbConnection Detach ()
    {
        var conn = m_connection;
        m_connection = null;

        return conn;
    }

    public void Dispose ()
    {
        Dispose(true);
    }

    public void Open ()
    {
        if ((m_connection != null) && (m_connection.State == ConnectionState.Closed))
            m_connection.Open();
    }

    private void Dispose ( bool disposing )
    {
        if (disposing)
        {
            try
            {
                if ((m_connection != null) && (m_connection.State != ConnectionState.Closed))
                    m_connection.Close();
            } catch
            { /* Ignore */
            } finally
            {
                m_connection = null;
            };
        };
    }

    private DbTransaction m_transaction;
    private DbConnection m_connection;
}

DataCommand

This type is core to the simplification process. What really burdens ADO.NET is the fact that a command is provider-specific. Different providers may use different database types, support different parameter directions and require naming conventions for parameters. DataCommand hides all this behind a simple class.

public class DataCommand
{
    public DataCommand ( string commandText, CommandType type )
    {
        CommandText = commandText;
        CommandType = type;
    }

    public string CommandText { get; private set; }

    public TimeSpan CommandTimeout { get; set; }

    public CommandType CommandType { get; private set; }

    public UpdateRowSource UpdatedRowSource { get; set; }
}

The command ultimately sent to the database is generated by the provider. The provider-specific ConnectionManager type is responsible for mapping the command to the supported format.

It may seems simple now but we will be expanding on it in the next article. The current version does not support parameters but we will address that later.

DbProviderFactoryConnectionManager

To demonstrate using ConnectionManager we need to create a provider-specific implementation. For this article I will use SQL Server but any provider will work. I have personally used this same code for Oracle without any issues. For now all derived types will basically do nothing but create provider-specific implementations of ADO.NET. To simplify the creation of these types we can create a generic implementation that relies on DbProviderFactory from ADO.NET.

public class DbProviderFactoryConnectionManager : ConnectionManager
{
    protected DbProviderFactoryConnectionManager ( DbProviderFactory factory, string connectionString ) : base(connectionString)
    {
        Factory = factory;
    }

    protected DbProviderFactory Factory { get; private set; }

    protected override DbConnection CreateConnectionBase ( string connectionString )
    {
        DbConnection conn = null;
        try
        {
            conn = Factory.CreateConnection();
            conn.ConnectionString = connectionString;

            return conn;
        } catch (Exception)
        {
            if (conn != null)
                conn.Dispose();

            throw;
        };
    }

    protected override DbDataAdapter CreateDataAdapterBase ()
    {
        return Factory.CreateDataAdapter();
    }

    protected override DbCommand CreateCommandBase ( DataCommand command )
    {
        DbCommand cmdDb = Factory.CreateCommand();

        cmdDb.CommandText = command.CommandText;
        cmdDb.CommandTimeout = (int)command.CommandTimeout.TotalSeconds;
        cmdDb.CommandType = command.CommandType;
        cmdDb.UpdatedRowSource = command.UpdatedRowSource;

        return cmdDb;
    }
}

Now to create a manager for any ADO.NET-supported database we can create a simple derived type (or possibly use the generic type).

public class SqlConnectionManager : DbProviderFactoryConnectionManager
{
    public SqlConnectionManager ( string connectionString ) : base(SqlClientFactory.Instance, connectionString)
    {
    }
}

At this point we can now call ADO.NET-supported databases without a lot of code. The provider-specific type will get more complex when we add parameter support but for now it is simple. Here is how we might use the final version of the current code.

var cm = new SqlConnectionManager(m_connectionString);
var cmd = new DataCommand("SELECT Id, Name FROM Roles", CommandType.Text);

using (var dr = cm.ExecuteReader(cmd))
{
    while (dr.Read())
    {
        …
    };
};

We still have a reader in here but that will go away later. We have successfully eliminated one set of using statements and converted our code to a provider-agnostic base. We could generalize this code further by injecting ConnectionManager into Database rather than creating it as needed. The remaining code would remain unchanged. As a demonstration we will convert Database into extension methods off of ConnectionManager and modify Databases to return ConnectionManager instead.

public static IEnumerable<Role> GetRoles ( this ConnectionManager source )
{
    var cmd = new DataCommand("SELECT Id, Name FROM Roles", CommandType.Text);

    using (var dr = source.ExecuteReader(cmd))
    {
        …
    };

    return roles;
}

Next

We have the basics for executing database calls without all the boilerplate code but we still have a ways to go.

• No parameter support
• Readers still require too much code
• No transaction support

Download the Demo Code

Download the Library Code