Provider Factory

Providers factory provides us indepandent database access where we can connect to any database sources(SQLServer,DB2,Oracle,MS-Access).
Provider Factory allows programmers to write their own implementation for accessing database.

namespace DatabaseProvider
{
    /// <summary>
    ///
    /// Design Pattern: Factory.
    /// </summary>
    public abstract class Database
    {
        private string connectionString;       
        private DbProviderFactory dbProviderFactory;
        private string schema;
        private string dataProvider;
        private bool status;

        protected Database() { }    

        public DbProviderFactory DbProviderFactory
        {
            get { return dbProviderFactory; }
        }      
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }       
        public string Schema
        {
            get { return schema; }
        }

        public string DataProvider
        {
            get { return dataProvider; }
        }
           
        public DbConnection GetConnection()
        {
            DbConnection newConnection = null;
            try
            {
                try
                {
                       ProviderFactory = DbProviderFactories.GetFactory(this.ProviderType);
                       dbConnection = ProviderFactory.CreateConnection();
                       dbConnection.ConnectionString = this.ConnectionString;                    
                }
                catch
                {
                    throw;
                }
            }
            catch
            {
                if (newConnection != null)
                    newConnection.Close();

                throw;
            }

            return newConnection;
        }

        public DbCommand CreateCommand()
        {
            return dbProviderFactory.CreateCommand();
        }

        public DbCommand CreateCommand(string sQueryString, DbConnection connection)
        {
            try
            {
                // Create the DbCommand.
                DbCommand command = this.CreateCommand();
                command.CommandText = sQueryString;
                command.Connection = connection;

                return command;
            }
            catch
            {
                throw;
            }
        }      

        public DbDataAdapter CreateDataAdapter()
        {
            return dbProviderFactory.CreateDataAdapter();
        }
        public DbDataAdapter CreateDataAdapter(string sQueryString, DbConnection connection)
        {
            try
            {
                // Create the DbCommand.
                DbCommand command = this.CreateCommand();
                command.CommandText = sQueryString;
                command.Connection = connection;

                // Create the DbDataAdapter.
                DbDataAdapter adapter = this.CreateDataAdapter();
                adapter.SelectCommand = command;
               
                return adapter;
            }
            catch
            {
                throw;
            }
        }

        private DbCommandBuilder CreateCommandBuilder()
        {
            return dbProviderFactory.CreateCommandBuilder();
        }

        public DbCommandBuilder CreateCommandBuilder(DbDataAdapter dbDA)
        {
            DbCommandBuilder dbCB = this.CreateCommandBuilder();
            dbCB.DataAdapter = dbDA;

            return dbCB;
        }       
    }
}

web.config
<add key="Connectionstring" value="database=local;user id=sa;pwd=sa;initial catalog=northwind"/>
<add key="SQLProvider" value="System.Data.SqlClient"/>
<add key="OledbProvider" value="System.Data.OleDb"/>
<add key="Db2Provider" value="IBM.Data.DB2"/>
<add key="OracleProvider" value="System.Data.OracleClient"/>

aspx.cs
private Database DB;
private DbDataAdapter SQLDA;
private DbCommandBuilder SQLDB;
private DbConnection Conn;
private DBCommand cmd;

//pass the connection string and provider type to create database connection
DB.Connectionstring=Configuration.ConfigurationManager.AppSettings["Connectionstring"].ToString();
DB.DataProvider=Configuration.ConfigurationManager.AppSettings["SQLProvider"].ToString();

//create database connection
Conn = DB.GetConnection();
Conn.Open();

//creating Data Adapter
SQLDA=DB.CreateDataAdapter("select * from emp",Conn);

//Create Command Builder
SQLDB=DB.CreateCommandBuilder(SQLDA);

//Create Command
cmd=DB.CreateCommand("select * from emp",Conn);