Monday, July 6, 2009

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:


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


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:


this.comboBox1.DisplayMember = "Name";
this.comboBox1.DataSource = DbProviderFactories.GetFactoryClasses();


Me.ComboBox1.DisplayMember = "Name"
Me.ComboBox1.DataSource = DbProviderFactories.GetFactoryClasses()

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


DataRow providerRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow);


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:


string invariantName = Properties.Settings.Default.ProviderFactoryInvariantName;
DbProviderFactory factory = DbProviderFactories.GetFactory(invariantName);


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:


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)
    return command;
public DataTable GetDataTable(string procName,
                              IDictionary parameters)
    DbCommand command = this.GetCommand(procName,
    DbConnection connection = this.GetConnection();
    command.Connection = connection;
    command.CommandType = CommandType.StoredProcedure;
    DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo |
    DataTable table = new DataTable();
    return table;


Private ReadOnly _factory As DbProviderFactory
Private _connectionString As String
Public Property ConnectionString() As String
        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, _
    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
    Dim reader As DbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or _
    Dim table As New DataTable()
    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.


Anonymous said...

One of the best DAL articles I've read. Simple and a great intro to the dbFactory - I'm very happy to know about it. Thanks.

SeeSharpWriter said...

Hello, that is an interesting post. I like your idea with the dictionary. Though your approach gives a lot of freedom, I think further abstraction is available and make the DAL even simplier to use. Please check my post on designing Data Access Layer and Business Access Layer I have recently written. The framework I have written about database access basically does the same as your code, but on higher level of abstraction. I would appreciate if you told me what you think about it.

Anonymous said...

Genial post and this post helped me alot in my college assignement. Thanks you as your information.

Anonymous said...

[ ... ] link is being shared on Twitter right now. @zenx, an influential author, said RT @1ndus: Xtreme [ ... ]

Anonymous said...

Great, but what about DbParameters? SQL servers requires "@param1" and Oracle ":param1": so when filling the CommandText, the right prefix have to be used. I cannot see an implementation code for this.

Or do you always use a if/switch before typing the queries?

jmcilhinney said...

There's nothing that I'm aware of that will tell you what the parameter format is for a particular provider. If you wanted to write generic code then you store the parameter prefix/format in the same location as the provider name.