Friday, October 14, 2011

How to create common Data access layer for all database?(DB 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.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
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);

No comments:

Post a Comment