All most every application there is a need to store data some where permanently. You can use file system, xml file or database. In database relational format is very popular. The example of relation database management systems (RDBMS) are Microsoft Sql Server, Oracle, My Sql, Microsoft Office Access etc. To work with databases .NET Framework provides ADO.NET.

The main topic of this post is to create such an application which is not dependent on a specific database. For example imagine that I have created an application for my client which uses Microsoft Sql Server, after some day the client wants to change the database to Oracle. Then what should I do? Do I change the total data access layer for that?

There is some solution for that. I mean we should develop our application with that in our mind. In ADO.NET there are two types of classes. One is called data consumer classes (System.Data namespace) and another is called data provider classes (System.Data.SqlClient, System.Data.OracleClient, System.Data.OleDb etc). In data provider class there is a namespace called System.Data.Common. In this namespace there are some abstract classes which actually inherited by the data provider classes in ADO.NET. For example System.Data.SqlClient.SqlConnection class is inherits System.Data.Common.DbConnection class. Again System.Data.Common.DbConnection class implements System.Data.IDbConnection interface. So if we use this abstract class and interface then we do not need to know much about the actual implementations. Our application do not depends on a specific database. That is the benefit of interface programming.

Let’s create an application to test this fetcher. I am opening Visual C# 2008 Express Editions and creating a new Console application. The name of my application is ConsoleApplication1. Now add a new folder in the project and give a name ‘Data’. Now add a new Microsoft Sql Server Express Edition database file in to it. Name it Institute.mdf. Create a new table in the database called Students table. Add four columns in the Students table.

Now open the table and store some data inside that.

Now open Microsoft office access 2003 and create a same database with same table. But insert only the first two row in access database.

After that open the Program.cs file in your solution explorer and write some code to fetch data from database. We are going to retrieve data from database but keep in mind that our code will be totally independent of any database

// Program.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using ConsoleApplication1.DataAccessLayer;

namespace ConsoleApplication1
{
    namespace DataAccessLayer
    {
        class DataAccess : IDisposable
        {
            // All are interface and abstract class types.
            private IDbConnection _con;
            private IDbCommand _cmd;
            private IDataReader _dr;
            private DbProviderFactory _factory;

            private ConnectionStringSettings _conStngInstitute;

            private bool _isDisposed;

            public DataAccess()
            {
                // Get the connection string from configuration file.
                _conStngInstitute = ConfigurationManager.ConnectionStrings["conInstitute"];

                // DbProviderFactories is a static class. I am using this class to return a
                // DbProviderFactory type object using DbProviderFactories.GetFactory() method.
                _factory = DbProviderFactories.GetFactory(_conStngInstitute.ProviderName);

                // Now use DbProviderFactory object to create connection and command objects.
                _con = _factory.CreateConnection();
                _con.ConnectionString = _conStngInstitute.ConnectionString;
                _cmd = _con.CreateCommand();

                _isDisposed = false;
            }

            // Database independent work and returns a IDataReader type object to the caller.
            public IDataReader ExecuteReader()
            {
                if (!IsDisposed)
                {
                    _cmd.CommandText = "SELECT * FROM Students";

                    _con.Open();
                    _dr = _cmd.ExecuteReader();

                    return _dr;
                }
                else
                {
                    throw new ObjectDisposedException("Object is already disposed.");
                }
            }

            //
            // These codes are for deterministic life cycle management of this class.
            //

            public bool IsDisposed
            {
                get
                {
                    lock (this)
                    {
                        return _isDisposed;
                    }
                }
            }

            public void Dispose()
            {
                if (!IsDisposed)
                {
                    lock (this)
                    {
                        CleanUp();
                        _isDisposed = true;
                        GC.SuppressFinalize(this);
                    }
                }
            }

            protected virtual void CleanUp()
            {
                if (_con != null)
                {
                    _con.Close();
                    _con.Dispose();
                }

                if (_cmd != null)
                {
                    _cmd.Dispose();
                }

                if (_dr != null)
                {
                    _dr.Close();
                    _dr.Dispose();
                }
            }
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (DataAccess dal = new DataAccess())
            {
                // Call to DAL.
                IDataReader dr = dal.ExecuteReader();

                // Get the ordinals.
                int studentIdOrdinal = dr.GetOrdinal("StudentId");
                int nameOrdinal = dr.GetOrdinal("Name");
                int subjectOrdinal = dr.GetOrdinal("Subject");
                int ageOrdinal = dr.GetOrdinal("Age");

                // Read data from data reader and show in console.
                while (dr.Read())
                {
                    Console.WriteLine("Id: {0}\nName: {1}\nSubject: {2}\nAge: {3}\n",
                        dr.GetValue(studentIdOrdinal), dr.GetString(nameOrdinal),
                        dr.GetString(subjectOrdinal), dr.GetValue(ageOrdinal));
                }
            }

            Console.ReadKey(true);
        }
    }
}

Here I am using interface types to write database independent code. The actual specification will create in the runtime by the DbProviderFactory class depending on the connection string which is written in the App.config file. DbProviderFactories.GetFactory() return an instance of DbProviderFactory type. Then I create connection and command using DbProviderFactory class.

Now comment the sql server connection string and uncomment the access connection string and run the program. You can see the data from access database. Now comment the access connection string and uncomment the sql server connection string and run the application, you can see the data from sql server database.

In this way we can create database independent applications. Just you have to provide a database with same schema and compatible data types. Just change the connection string in configuration file and you are ready to go. No need to recompile the code.

Download full code: Database independent application code from Sky Drive