Ever wanted to support different databases from different providers? Tired of writing different layers for different providers? Too much pain to maintain the code? Not anymore.

How many times have you been asked to support a different database when you have already written your data access layer for a specific database? We all know how painful it is. Every time you are asked to do so there is very less code that you can actually reuse. So what could be done minimize changes?

Normally our data access layer is tightly coupled with the database. Today when watching a screencast from DNRTV by Jean-Paul S. Boodhoo on design patterns he demoed a feature in .NET framework 2.0 called DbProviderFactory. He then dived into many other patterns but I will concentrate only on DbProviderFactory.

.NET 2.0 has factory classes which can determine the database type from a provided data provider name. So let me show you a quick example. To use this feature you have to add reference to the System.Configuration assembly.

Lets create an interface for our db provider class.

Provider Factory

ITestDbProviderFactory.cs

namespace NTierTest.Data
{
    using System.Data; 

    /// <summary>
    /// Database Provider Factory Class
    /// </summary>
    public interface ITestDbProviderFactory
    {
        #region Public Methods 

        /// <summary>
        /// Creates an <see cref="IDbCommand"/>
        /// </summary>
        /// <returns></returns>
        IDbCommand CreateCommand(); 

        /// <summary>
        /// Creates an <see cref="IDbConnection"/>
        /// </summary>
        /// <returns></returns>
        IDbConnection CreateConnection(); 

        #endregion
    }
}

 

The ITestDbProviderFactory is a very simple interface which has two methods. CreateConnection will return a IDbConnection and CreateCommand will create a IDbCommand object.

Note we are returning abstract types. The provider specific classes like SqlConnection Or OracleConnection implement these abstract types.

Now lets create a class which will implement this interface

TestDbProviderFactory.cs

 

namespace NTierTest.Data
{
    using System.Configuration;
    using System.Data;
    using System.Data.Common;

    /// <summary>
    /// A DbProvider Class that uses AbstractFactoryPattern To Return the Connection and Command
    /// This implementation uses Mono State Pattern
    /// </summary>
    public class TestDbProviderFactory : ITestDbProviderFactory
    {
        #region Constants and Fields

        /// <summary>
        /// .NET FrameWork 2.0 Abstract Provider Factory
        /// It is marked static .. to ensure one instance through out the application
        /// </summary>
        private static DbProviderFactory _frameworkProviderFactory;

        /// <summary>
        /// Loaded From The Configuration File
        /// </summary>
        private static ConnectionStringSettings _settings;

        #endregion

        #region Constructors and Destructors

        /// <summary>
        /// Static constructor will be called only once through out the application
        /// </summary>
        static TestDbProviderFactory()
        {
            _settings = ConfigurationManager.ConnectionStrings[1];

            _frameworkProviderFactory = DbProviderFactories.GetFactory(_settings.ProviderName);
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Returns a <see cref="IDbCommand"/> Object
        /// </summary>
        /// <returns>A Command Object</returns>
        public IDbCommand CreateCommand()
        {
            return _frameworkProviderFactory.CreateCommand();
        }

        ///<summary>
        /// Return a <see cref="IDbConnection"/> Object
        ///</summary>
        ///<returns>Connection object</returns>
        public IDbConnection CreateConnection()
        {
            IDbConnection connection = _frameworkProviderFactory.CreateConnection();
            connection.ConnectionString = _settings.ConnectionString;

            return connection;
        }

        #endregion
    }
}

 

In this class we have created a private static instance of DbProviderFactory class which is a .net abstract factory class for doing provider related operations. We will use this class to create a provider specific connection and command object.

/// <summary>
/// Static constructor will be called only once through out the application
/// </summary>
static TestDbProviderFactory()
{
    _settings = ConfigurationManager.ConnectionStrings[1];

    _frameworkProviderFactory = DbProviderFactories.GetFactory(_settings.ProviderName);
}

We also have a private static ConnectionStringSettings object. We will use the ConfigurationManager class to get the connection string from the web.config/app.config file.

connectionString="Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />

 

We initialize the ConnectionStringSettings object and the DbProviderFactory object in the static constructor. To ensure one copy of these variables. The same could be done using Singleton pattern. But I prefer using the Monostatic pattern because we can acheive the same thing that we can acheive using the singleton pattern and the client will not know that he is using a single copy of the varialbles internally.

_settings = ConfigurationManager.ConnectionStrings[1]; reads the connection string settings from the configuration file. Notice I am getting the second connection string setting. Even if you have only one connection string setting defined in the web.config ASP.NET internally has a connection string already configured for the default asp.net database aspnet.mdf even if it does not exist.

On line number 49 we are instantiating the DbProviderFactory object using the System.Data.Common.DbProviderFactories class. We give the provider name to the GetFactory method of this class. This line will create a provider specific DbProviderFactory object for us.

I already have a connection string configured in my web.config file

So in the CreateConnection method we use the _frameworkProviderFactory.CreateConnection() method to get an IDbConnection object and then set the ConnectionString property of the created object to the ConnectionString property of the static ConnectionStringsSetting object.

public IDbCommand CreateCommand()
{
    return _frameworkProviderFactory.CreateCommand();
}

public IDbConnection CreateConnection()
{
    IDbConnection connection = _frameworkProviderFactory.CreateConnection();
    connection.ConnectionString = _settings.ConnectionString;

    return connection;
}

Data Access Layer

Now its time to write a data access layer. The interface for our data access layer contains only one method GetAllEmployees, which will return a DataTable.

IEmployeeRepository.cs

namespace NTierTest.Core
{
    using System.Data;

    /// <summary>
    /// Employee repository
    /// </summary>
    public interface IEmployeeRepository
    {
        #region Public Methods

        /// <summary>
        /// Gets all employees.
        /// </summary>
        /// <returns>Employee data table</returns>
        DataTable GetAllEmployees();

        #endregion
    }
}

 

After implementing this interface here is my data access layer

EmployeeRepository.cs

namespace NTierTest.Data
{
    using System;
    using System.Data;

    /// <summary>
    /// Employee tasks
    /// </summary>
    public class EmployeeRepository : IEmployeeRepository
    {
        #region Constants and Fields

        private ITestDbProviderFactory _providerFactory;

        #endregion

        #region Constructors and Destructors

        /// <summary>
        /// Initializes a new instance of the EmployeeRepository class.
        /// Using poor mans dependency injection.
        /// </summary>
        public EmployeeRepository()
            : this(new TestDbProviderFactory())
        {
        }

        /// <summary>
        /// Initializes a new instance of the EmployeeRepository class.
        /// </summary>
        /// <param name="provider"></param>
        public EmployeeRepository(ITestDbProviderFactory provider)
        {
            this._providerFactory = provider;
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Gets all employees.
        /// </summary>
        /// <returns>Employee data table</returns>
        public DataTable GetAllEmployees()
        {
            try
            {
                DataTable rawData = new DataTable();

                // Get the connection from the provider factory
                using (IDbConnection connection = this._providerFactory.CreateConnection())
                {
                    // create the command object using the conneciton object
                    IDbCommand command = connection.CreateCommand();

                    // Inline query has been used for the sake of this demo
                    // use stored procedure
                    command.CommandText = "Select * From Employees";
                    command.CommandType = CommandType.Text;

                    // open the connection
                    connection.Open();

                    // execute reader
                    IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                    // load dataset from reader
                    // since reader cannot be used when the connection is closed I am using datatable
                    using (reader)
                    {
                        rawData.Load(reader);
                    }
                }
                // return the data
                return rawData;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        #endregion
    }
}

 

We need a connection and command object to read data from our database, thats why I have created an instance of our provider factory. The data access layer will use this provider factory class to get the appropriate connection and command object.

private ITestDbProviderFactory _providerFactory;

 

Because our data access layer will be called from the service layer. I have created two constructor. One take a TestProviderFactory object and the empty constructor is used to feed the dependencies. This technique is called poor man’s dependency injection.

public EmployeeRepository() : this(new TestDbProviderFactory())
{
}

public EmployeeRepository(ITestDbProviderFactory provider)
{
    this._providerFactory = provider;
}

In the GetAllEmployees method I create a DataTable object then get the connection from the _providerFactory.CreateConnection method. The CreateConnection method to our surprise return the correct connection object, in this case a SqlConnection.

Then I create IDbCommand object using the create connection, this way I don’t have to configure the connection property of the command.

I have used inline SQL query just for this demo sake. We should not use this in production. We should use stored procedure because sql for SQL Server and Oracle are different.

// create the command object using the conneciton object
IDbCommand command = connection.CreateCommand();

// Inline query has been used for the sake of this demo
// use stored procedure
command.CommandText = "Select * From Employees";
command.CommandType = CommandType.Text;

// open the connection
connection.Open();

// execute reader
IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

 

I use the DataTable.Load method to load the reader into the data table. Because the reader needs an open connection we cannot send reader between layers unless we have a persistent connection. Our service layer will use this data table object to create employee collection object.

// load dataset from reader
// since reader cannot be used when the connection is closed I am using datatable
using (reader)
{
    rawData.Load(reader);
}

 

This completes our data access layer. Now lets write the service layer.

Service Layer

IEmployeeService.cs

namespace NTierTest.Core
{
    using System.Collections.Generic;

    ///<summary>
    ///Employee Service contract
    ///</summary>
    public interface IEmployeeService
    {
        #region Public Methods

        /// <summary>
        /// Gets all employees.
        /// </summary>
        /// <returns>List of Employees</returns>
        IList<IEmployee> GetAllEmployees();

        #endregion
    }
}

The IEmployeeService interface is contains only one method GetAllEmployees which returns an IList of IEmployee.

EmployeeService.cs

namespace NTierTest.Service
{
    using System;
    using System.Collections.Generic;
    using System.Data;

    ///<summary>
    ///Does all the employee related tasks
    ///</summary>
    public class EmployeeService : IEmployeeService
    {
        //Method...

        #region Public Methods

        /// <summary>
        /// Gets all employees.
        /// </summary>
        /// <returns>An <see cref="IList{T}"/> of <see cref="IEmployee"/></returns>
        public IList<IEmployee> GetAllEmployees()
        {
            IEmployeeRepository employeeRepository = new EmployeeRepository();

            IList<IEmployee> resutls = new List<IEmployee>();

            foreach (DataRow row in employeeRepository.GetAllEmployees().Rows)
            {
                resutls.Add(
                    new Employee(
                        row["FirstName"].ToString(),
                        row["LastName"].ToString(),
                        DateTime.Parse(row["BirthDate"].ToString())));
            }

            return resutls;
        }

        #endregion
    }
}

As you can see I create an instance of the EmployeeRepository class and make a call to the GetAllEmployees method and iterate over all the rows and then create Employee objects and add them to a IList IEmployees. Simple isn’t it?

Now what? Its all ready lets see it in action. I have already written a test to test the features.

namespace NTierTest.Test
{
    using System;
    using System.Collections.Generic;

    /// <summary>
    /// Employee Service Test Harness
    /// </summary>
    [TestFixture]
    public class EmployeeTestServiceTest
    {
        #region Public Methods

        /// <summary>
        /// Gets all employees_ should get all employees.
        /// </summary>
        [Test]
        public void TestGetAllEmployeesShouldGetAllEmployees()
        {
            IEmployeeService service = new EmployeeService();
            IList<IEmployee> employees = service.GetAllEmployees();

            foreach (IEmployee employee in employees)
            {
                Console.WriteLine(employee.FirstName);
            }

            Assert.IsTrue(employees.Count > 0);
        }

        #endregion
    }
}

The test is very simple it creates an instance of the EmployeeService, Gets a list of employees and outputs it to the console.

I know this is not an actual way to do TDD. I am a beginner and I wanted to show that it actually creates instance of the correct provider, thats why I have not mocked anything.

Test Result

As you can see everything works as expected. Try it out yourself and say what do you think? Got thoughts? shout it out.

Download Source Code

DbProvider Test