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

Yesterday in a project I had to build a feature where use can change the sql server connection while deployment. I wanted to build something exactly like the sql server management studio login dialog box. So first thing I had to do is retrieve a list of sql server instances on a network. After a little bit of googling I found two solutions.

1) Enumerate SQL Server Instances Using SQLDMO:

To do this you have to add reference to SQLDMO.dll in your Program Files\Microsoft SQL Server\80\Tools\Binn

[csharp]public static List GetAllSqlServerInstances() {
/* Enumerate sql server instances using SQLDMO */
NameList sqlList = null;
Application sqlApp = null;

var sqlServers = new List();

try {
sqlApp = new ApplicationClass();
sqlList = sqlApp.ListAvailableSQLServers();
foreach (string server in sqlList)
sqlServers.Add(server);
return sqlServers;
} catch {
return null;
} finally {
if (sqlList != null)
sqlList = null;
if (sqlApp != null)
sqlApp = null;
}
}[/csharp]

If you choose to do it with SQLDMO then you have to bundle SQLDMO dependencies with your package or the client computer should have sql server tools installed.

2) Obtain List of SQL Server Instances Using ODBC (SQLBrowseConnect):

Fortunately the second method that I used uses ODBC’s SQLBrowseConnect and which is probably installed by default on all windows operating system. The article that I found on CodeProject was exactly what I was looking for after facing the SQLDMO dependency problem.

To obtain a list of sql server instances using SQLBrowseConnect add the SQLInfoEnumerator.cs to your project and put do something like this:

[csharp]public static List GetAllSqlServerInstances() {
/* Enumerates sql server instances using ODBC */
var sie = new SQLInfoEnumerator();
return sie.EnumerateSQLServers().ToList();
}[/csharp]

And to obtain a list of databases in a particular sql server instance do this:

[csharp]public static List GetAllDataBases(string server, string userName, string password) {
SQLInfoEnumerator sie = new SQLInfoEnumerator();
sie.SQLServer = server;
sie.Username = userName;
sie.Password = password;
return sie.EnumerateSQLServersDatabases().ToList();
}[/csharp]

Links:

SQLInfoEnumerator.cs

The Esnips Link Generator Is Here

Yesterday I read a post in esnips forum claiming that IndianRaga has used his ideas to create his esnips link generator and has not given him credits.

So before anyone else hops in and claims that I have used his ideas to create the link generator, I will explain why and how did I create my link generator.

One of my friend Saurabh from esnips who read my previous post about esnips download trick told me that the old tricks are not working anymore. So I fired firefox navigated to esnips tried the previous nsdoc trick, it didn’t work.

I then looked into the source and found script reference to the javascript file that contained methods to create the MP3 Widget (Link : http://res1.esnips.com/3rd/media/music12.js)
It contains definition for two type of mp3 widget
Read the rest of this entry »

Update (01/09/2009)

Dear Users,

Its been a while since I started blogging. Apart from my other posts, this particular eSnips related post has grabbed more attention from music lovers, even though there are many similar post elsewhere in the internet. I am thankful to all my readers for all the heartwarming response they gave to this post.

But yesterday I got a letter from Logia Media Ltd stating that this post voilates the privacy rights, the copyrights of third parties and makes illegal use of eSnips trade name and marks.

So as of today I am removing this application from this site, but I am leaving this post and the other post about eSnips intact as a memorial.

Thanks again for all your support.

The old tricks are not working anymore.

You can use the download link generator below to generate the esnips download link. At the time I am writing this post the link generator works fine for mp3, wma files. When downloading Mp3 files click on the Mp3 radio button, in case of other media file types choose others.

If you face any problem then plz post a comment or mail me.
You will need flash player 9 installed to view this file ..

DO NOT USE GOOGLE CHROME

Read Why & How I created this Link Generator

Click here to download the file.

If you have a website and want to add a link to this post then go ahead and add it.

When you click the download button it may be blocked by the browser. Please allow popups if it happens to you.

Bandwidth Exceeded

If you face bandwidth problem then please try downloading the file after a day or two.

I guess esnips has alloted bandwidths to files and as many people are downloading the same file the alloted bandwidth for that file has exceeded. You can still download other files that have not exceeded their bandwidths ..

So try again later

Update: (30/04/2008)

Thank you for your feedbacks. There was a small error that I have fixed now the generated link works fine as before.

Update: (05/06/2008)

I have modified the link generator. It is working fine for me now. Please empty your browser cache and then refresh the browser to avoid pulling the old generator from your cache.
Please Select MP3 Radio Button. If you face any problem while downloading any file then try to generate and download again. If you are still unable to download the file then please leave a comment with the esnips link that you are trying to download.

Permanent Solution:

Need a permanent solution? Get a good download manager like Internet Download Manager. It will automatically grab the file for you. In fact I use the download manager to find out what is the final URL and then generate the URL using program.

Update : 09/11/2008

Watch the HD version on Vimeo.

I have finally made a small tutorial on how to download songs from esnips using Internet Download Manager & Video Download Helper. This was my first time making a screecast kind of thing so the video size is larger. I hope its not an issue for you guys or else I have to reencode the video. I made this on a widescreen pc and had no idea what are the best practices for making a video tutorial. Please watch it in fullscreen mode because its a HD video. If you guys have any problem then please post your comments.