Database-independent Data Access Layer

We all know that well-written applications separate the various areas of responsibility into multiple layers, right? Generally speaking, the less each of those layers depend on specific functionality of the others the better. One of those areas of responsibility that warrants separation is data access. There are numerous tools about to generate data access code for you these days but, even if you're more of a roll-your-own kinda person, there's still help at hand. ADO.NET provides the tools to write data access code that is independent of the underlying data source and it has done since version 2.0.

The lynchpin of this functionality is the DbProviderFactory class. It provides methods to generate instances of all the usual ADO.NET objects, e.g. connections, commands and data adapters, for any specific ADO.NET provider.

Let’s start with a whirlwind tour of the System.Data and System.Data.Common namespaces. Amongst other things, the System.Data namespace contains the interfaces that declare the set of functionality required by ADO.NET data access objects, e.g. IDataReader for all data readers and IDbConnection for all connections. The System.Data.Common namespace contains classes that provide a basic concrete implementation of these interfaces, e.g. DbDataReader implements IDataReader and DbConnection implements IDbConnection. Each data source-specific ADO.NET provider will then declare a set of classes that inherit those from System.Data.Common to provide a data source-specific set of functionality, e.g. SqlClient.SqlDataReader inherits DbDataReader and OleDb.OleDbConnection inherits DbConnection.

The System.Data.Common namespace also includes the DbProviderFactory class, which provides an abstract implementation of an ADO.NET object factory. Each data source-specific ADO.NET provider should inherit this class and provide its own data source-specific implementation, e.g. SqlClientFactory and OleDbFactory. Third party ADO.NET providers can and should do the same thing, e.g. MySql.Data.MySqlClient.MySqlClientFactory.

Now, ADO.NET factory classes are singletons. Each concrete factory implementation will have a static/Shared Instance field that you can use to get the one and only instance of that type. Doing so directly defeats the purpose somewhat though. You will normally get a DbProviderFactory instance using the DbProviderFactories helper class, which is also a member of the System.Data.Common namespace. The DbProviderFactories class includes two static/Shared methods: GetFactoryClasses will return a DataTable containing information about all ADO.NET factories available and GetFactory will return an instance of a specific ADO.NET factory class.

To see how this works, try creating a new Windows Forms application, adding a DataGridView to the default form and then binding that grid to the result of the DbProviderFactories.GetFactoryClasses method:

C#

this.dataGridView1.DataSource = DbProviderFactories.GetFactoryClasses();

VB

Me.DataGridView1.DataSource = DbProviderFactories.GetFactoryClasses()

If you run that application you will see a row each for factories named “Odbc Data Provider”, “OleDb Data Provider”, “OracleClient Data Provider” and “SqlClient Data Provider”. If you’ve installed Microsoft SQL Server CE then you’ll also see a row for a factory named “Microsoft SQL Server Compact Data Provider”. If you’ve installed some third party data provider then you’ll see a row for that too. For instance, if you’ve installed MySQL’s Connector/Net then you’ll see a row for a factory named “MySQL Data Provider”. If you’ve installed Oracle’s ODAC then you’ll see a row for a factory named “Oracle Data Provider for .NET”.

To get an appropriate instance of the DbProviderFactory class for your data source you would normally call the DbProviderFactories.GetFactory method. You can pass that method either a DataRow from the DataTable returned by the GetFactoryClasses method or else one of the values from the InvariantName column of that table. For instance, you could pass the string “System.Data.SqlClient” to the GetFactory method and it would return the value of the SqlClientFactory.Instance field.

This provides us with two simple and logical ways to create an ADO.NET factory. Firstly, we can call GetFactories and display the results to the user, e.g. in a ComboBox, for them to choose a provider:

C#

this.comboBox1.DisplayMember = "Name";

this.comboBox1.DataSource = DbProviderFactories.GetFactoryClasses();

VB

Me.ComboBox1.DisplayMember = "Name"

Me.ComboBox1.DataSource = DbProviderFactories.GetFactoryClasses()

We can then call GetFactory and pass the row they selected as an argument:

C#

DataRow providerRow = ((DataRowView)this.comboBox1.SelectedItem).Row;

DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow);

VB

Dim providerRow As DataRow = DirectCast(Me.ComboBox1.SelectedItem, DataRowView).Row

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(providerRow)

Alternatively, we can store the invariant name of our desired provider somewhere, e.g. in the config file, and pass that to GetFactory as an argument:

C#

string invariantName = Properties.Settings.Default.ProviderFactoryInvariantName;

DbProviderFactory factory = DbProviderFactories.GetFactory(invariantName);

VB

Dim invariantName As String = My.Settings.ProviderFactoryInvariantName

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(invariantName)

So, once we have our factory object, what do we do with it? Well, we use it to create all our data access objects, including connections, commands, parameters, data adapters and more. For instance, the following code might form part of your repository layer:

C#

private readonly DbProviderFactory _factory;

public string ConnectionString { get; set; }

public Repository(string factoryName)

{

    this._factory = DbProviderFactories.GetFactory(factoryName);

}

public Repository(DataRow factoryRow)

{

    this._factory = DbProviderFactories.GetFactory(factoryRow);

}

public DbConnection GetConnection()

{

    return this.GetConnection(this.ConnectionString);

}

public DbConnection GetConnection(string connectionString)

{

    DbConnection connection = this._factory.CreateConnection();

    connection.ConnectionString = connectionString;

    return connection;

}

public DbParameter GetParameter()

{

    return this._factory.CreateParameter();

}

public DbParameter GetParameter(string parameterName,

                                object value)

{

    DbParameter parameter = this.GetParameter();

    parameter.ParameterName = parameterName;

    parameter.Value = value;

    return parameter;

}

public DbCommand GetCommand()

{

    return this._factory.CreateCommand();

}

public DbCommand GetCommand(string commandText)

{

    DbCommand command = this.GetCommand();

    command.CommandText = commandText;

    return command;

}

public DbCommand GetCommand(string commandText,

                            IDictionary parameters)

{

    DbCommand command = this.GetCommand(commandText);

    foreach (string parameterName in parameters.Keys)

    {

        command.Parameters.Add(this.GetParameter(parameterName,

                                                 parameters[parameterName]));

    }

    return command;

}

public DataTable GetDataTable(string procName,

                              IDictionary parameters)

{

    DbCommand command = this.GetCommand(procName,

                                        parameters);

    DbConnection connection = this.GetConnection();

    command.Connection = connection;

    command.CommandType = CommandType.StoredProcedure;

    connection.Open();

    DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo |

                                                CommandBehavior.CloseConnection);

    DataTable table = new DataTable();

    table.Load(reader);

    reader.Close();

    return table;

}

VB

Private ReadOnly _factory As DbProviderFactory

Private _connectionString As String

Public Property ConnectionString() As String

    Get

        Return Me._connectionString

    End Get

    Set(ByVal value As String)

        Me._connectionString = value

    End Set

End Property

Public Sub New(ByVal factoryName As String)

    Me._factory = DbProviderFactories.GetFactory(factoryName)

End Sub

Public Sub New(ByVal factoryRow As DataRow)

    Me._factory = DbProviderFactories.GetFactory(factoryRow)

End Sub

Public Function GetConnection() As DbConnection

    Return Me.GetConnection(Me.ConnectionString)

End Function

Public Function GetConnection(ByVal connectionString As String) As DbConnection

    Dim connection As DbConnection = Me._factory.CreateConnection()

    connection.ConnectionString = connectionString

    Return connection

End Function

Public Function GetParameter() As DbParameter

    Return Me._factory.CreateParameter()

End Function

Public Function GetParameter(ByVal parameterName As String, _

                             ByVal value As Object) As DbParameter

    Dim parameter As DbParameter = Me.GetParameter()

    parameter.ParameterName = parameterName

    parameter.Value = value

    Return parameter

End Function

Public Function GetCommand() As DbCommand

    Return Me._factory.CreateCommand()

End Function

Public Function GetCommand(ByVal commandText As String) As DbCommand

    Dim command As DbCommand = Me.GetCommand()

    command.CommandText = commandText

    Return command

End Function

Public Function GetCommand( _

ByVal commandText As String, _

ByVal parameters As IDictionary(Of String, Object)) As DbCommand

    Dim command As DbCommand = Me.GetCommand(commandText)

    For Each parameterName As String In parameters.Keys

        command.Parameters.Add(Me.GetParameter(parameterName, _

                                               parameters(parameterName)))

    Next parameterName

    Return command

End Function

Public Function GetDataTable( _

ByVal procedureName As String, _

ByVal parameters As IDictionary(Of String, Object)) As DataTable

    Dim command As DbCommand = Me.GetCommand(procedureName, parameters)

    Dim connection As DbConnection = Me.GetConnection()

    command.Connection = connection

    command.CommandType = CommandType.StoredProcedure

    connection.Open()

    Dim reader As DbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or _

                                                       CommandBehavior.CloseConnection)

    Dim table As New DataTable()

    table.Load(reader)

    reader.Close()

    Return table

End Function

The GetDateTable method takes the name of a stored procedure and a dictionary of parameter values keyed on name as arguments. It then invokes other methods that use the factory to create a connection and a command with parameters. The DbProviderFactory class can also generate connection string builders, data adapters and command builders, so you can implement many other scenarios in a similar fashion. In this way you can build up a complete data access layer with nary a reference to any specific data source.