Click here to Skip to main content
15,867,686 members
Articles / Web Development / IIS
Article

Independent Data Access Layer (Ddocumentation in Progress)

Rate me:
Please Sign up or sign in to vote.
4.83/5 (124 votes)
4 Apr 2006CPOL2 min read 163.1K   1.4K   104   45
The art of swap from a database to another changing a flag
Image 1

Introduction

Developers often write applications that use databases. Because it is so common, they need to simplify tasks regarding data access functionality. The applications quite often require to be adapted to suit the programming model of each database.

Background

The System.Data namespace consists mostly of the classes that constitute the ADO.NET architecture. This namespace also defines a number of data access interfaces that could be used from different providers. Having a data access layer component that you could use against an Access 2000 database in development, and against an SQL Server or Oracle database in production should be a dream. Should not it?

How Have I Thought Of Doing It?

The Liskov Substitution Principle comes to help us. It states that derived classes must be usable through the base class interface without the need for the user to know the difference. In simple words, it means that an object of a class that implements an interface can be upcast to the interface type. I have introduced that because it could help us to understand a very useful patter called an Abstract Factory. What is the intent of the Abstract Factory patter?

Abstract Factory class diagram

An Abstract Factory Design Pattern provides a contract for creating families of related or dependent objects without having to specify their concrete classes.

Image 2

  • Participants and Collaborators
    • AbstractFactory
    • ConcreteFactory <code>ConcreteProduct
    • AbstractProduct
    • Client
  • Consequences
    • Isolates concrete classes
    • Makes exchanging product families easy
    • Promotes consistency among products
    • Supporting new kinds of products can be difficult
  • Implementation
    • Abstract Factories are often Singletons
    • Often, a Factory Method is used to create the product

Data Access Layer Interface

We need to create Data Access Layer objects, but we also need the system to be independent of how every single DAL is created. The following code block shows the IDal interface representing the abstract product.

C#
public interface IDal
{
    IDbCommand CreateCommand();
    IDbCommand CreateCommand( string cmdText );
    IDbCommand CreateCommand( string cmdText, IDbConnection cn );
    IDbCommand CreateCommand( string cmdText, IDbConnection cn, IDbTransaction trans ); 
    IDbConnection CreateConnection();
    IDbConnection CreateConnection( string cnString );
    IDbDataAdapter CreateDataAdapter();
    IDbDataAdapter CreateDataAdapter( IDbCommand selectCmd );
    IDbDataAdapter CreateDataAdapter( string selectCmdText, string selectCnString );
    IDbDataAdapter CreateDataAdapter( string selectCmdText, IDbConnection selectCn );
    IDataReader CreateDataReader( IDbCommand dbCmd );
    IDataReader CreateDataReader( IDbCommand dbCmd, CommandBehavior dbCmdBehavior );
}

Examples Of Implementation

To make my in/out parameters independent of the implementation, I used common interfaces (System.Data) as parameter types. It is time to implement the real product extending the IDAL interface. To do that, we have to create a new class I am going to name an OracleDal. Why do I call it OracleDal? Because I want to build an Oracle access point. Of course, you could see it in the complete source code I have enclosed and clicking the other icons, you could create different objects using many others providers. The example below shows a reduced OracleDal implementation.

Oracle

Oracle

C#
public class OracleDal: IDal
{
    public IDbCommand CreateCommand
        ( string cmdText, IDbConnection cn, IDbTransaction trans )
    {
        IDbCommand oracleCmd = null;

        try
        {
    
            oracleCmd = new OracleCommand( cmdText, 
                (OracleConnection)cn, (OracleTransaction)trans );
        }
        catch( OracleException oracleExc )
        {
            if(oracleCmd != null)
                oracleCmd.Dispose();
            throw new Exception( oracleExc.Message );
        }
        return oracleCmd;
    }
    public IDataReader CreateDataReader(IDbCommand dbCmd, 
                    CommandBehavior dbCmddBehavior )
    {
        IDataReader dr = null;
        try
        {
            dr = dbCmd.ExecuteReader( dbCmdBehavior );
        }
        catch( OracleException oracleExc )
        {
            if( dr != null)
            {
                if(!dr.IsClosed)
                dr.Close();
                dr.Dispose();
            }
            throw new Exception( oracleExc.Message );
        }
        return dr;
    }
    public IDbConnection CreateConnection( string cnString )
    {
        IDbConnection oracleCn = null;
        try
        {
            oracleCn = new OracleConnection( cnString );
        }
        catch( OracleException oracleExc )
        {
            if( oracleCn != null)
            oracleCn.Dispose();
            throw new Exception( oracleExc.Message );
        }
        return oracleCn; 
    }
    public IDbDataAdapter CreateDataAdapter
            ( string selectCmdText, IDbConnection selectCn )
    {
        IDbDataAdapter oracleDataAdapter = null;
        try
        {
            oracleDataAdapter = new OracleDataAdapter
                    ( selectCmdText, (OracleConnection)selectCn );
        }
        catch( OracleException oracleExc )
        {
            throw new Exception( oracleExc.Message );
        }
        return oracleDataAdapter;
    }
}

DB2

DB2

C#
public class DB2Dal: IDal 
{ 
    public IDbCommand CreateCommand( string cmdText, 
    IDbConnection cn, IDbTransaction trans ) 
    { 
        IDbCommand db2Cmd = null; 
        try 
        { 
            db2Cmd = new iDB2Command( cmdText, (db2Connection)cn, 
            (iDB2Transaction)trans ); 
        } 
        catch( iDB2Exception db2Exc ) 
        { 
            if(db2Cmd != null) db2Cmd.Dispose(); 
            throw new Exception( db2Exc.Message ); 
        } 
        return db2Cmd; 
    } 
    public IDataReader CreateDataReader(
        IDbCommand dbCmd, CommandBehavior dbCmddBehavior ) 
    { 
        IDataReader dr = null; 
        try 
        { 
            dr = dbCmd.ExecuteReader( dbCmdBehavior ); 
        } 
        catch( iDB2Exception db2Exc ) 
        { 
            if( dr != null) 
            { 
                if(!dr.IsClosed) dr.Close(); 
                    dr.Dispose(); 
            } 
        throw new Exception( db2Exc.Message ); 
        } 
    return dr; 
    } 
    public IDbConnection CreateConnection( string cnString ) 
    { 
        IDbConnection db2Cn = null; 
        try 
        { 
            db2Cn = new iDB2Connection( cnString ); 
        } 
        catch( iDB2Exception db2Exc ) 
        { 
            if( db2Cn != null) 
                db2Cn.Dispose(); 
        throw new Exception( db2Exc.Message ); 
        } 
        return db2Cn;  
    } 
    public IDbDataAdapter CreateDataAdapter( 
        string selectCmdText, IDbConnection selectCn ) 
    { 
        IDbDataAdapter db2DataAdapter = null; 
        try 
        { 
            db2DataAdapter = new iDB2DataAdapter
                        ( selectCmdText, (iDB2Connection)selectCn ); 
        } 
        catch( iDB2Exception db2Exc ) 
        { 
            throw new Exception( db2Exc.Message ); 
        } 
        return db2DataAdapter; 
    } 
}

Sql Server

Sql Server

C#
public class SqlDal: IDal
{
    public IDbCommand CreateCommand( string cmdText, IDbConnection cn, 
                IDbTransaction trans )
    {
        IDbCommand sqlCmd = null;
        try
        {
            sqlCmd = new SqlCommand( cmdText, (sqlConnection)cn, (SqlTransaction)trans );
        }
        catch( SqlException sqlExc )
        {
            if(sqlCmd != null)
                sqlCmd.Dispose();
        throw new Exception( sqlExc.Message );
        }
        return sqlCmd;
    }
    public IDataReader CreateDataReader
        (IDbCommand dbCmd, CommandBehavior dbCmddBehavior )
    {
        IDataReader dr = null;
        try
        {
            dr = dbCmd.ExecuteReader( dbCmdBehavior );
        }
        catch( SqlException sqlExc )
        {
            if( dr != null)
            {
                if(!dr.IsClosed)
                    dr.Close();
                    dr.Dispose();
            }
            throw new Exception( sqlExc.Message );
        }
        return dr;
    }
    public IDbConnection CreateConnection( string cnString )
    {
        IDbConnection sqlCn = null;
        try
        {
            sqlCn = new SqlConnection( cnString );
        }
        catch( SqlException sqlExc )
        {
            if( sqlCn != null)
                sqlCn.Dispose();
        throw new Exception( sqlExc.Message );
        }
        return sqlCn; 
    }
    public IDbDataAdapter CreateDataAdapter
            ( string selectCmdText, IDbConnection selectCn )
    {
        IDbDataAdapter sqlDataAdapter = null;
        try
        {
            sqlDataAdapter = 
                new SqlDataAdapter( selectCmdText, (SqlConnection)selectCn );
        }
        catch( SqlException sqlExc )
        {
            throw new Exception( sqlExc.Message );
        }
        return sqlDataAdapter;
    }
}

Information

The description in this article is not complete. I am sorry about that. I am going to insert the other part as soon as possible. If you want, you can use the complete source code I have attached. Please contact me if you have any suggestions.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Italy Italy
Salvo is a software architect working in Milan, Italy.

He enjoys design infrastructures based on object oriented paradigm.
At the moment he is managing the integration of “ERP Business Processes” with other applications developing designing business services that are service-oriented architecture compliant.

Comments and Discussions

 
GeneralThis is simply outstanding Pin
Md. Marufuzzaman20-Mar-10 20:30
professionalMd. Marufuzzaman20-Mar-10 20:30 
GeneralYou are very goooooooooooood! Pin
yin11223428-Jun-09 17:14
yin11223428-Jun-09 17:14 
Generalcmd.Parameters.AddWithValue [modified] Pin
stormydaniels15-Nov-08 1:02
stormydaniels15-Nov-08 1:02 
GeneralGreat job!!!! Pin
abyss_won27-Aug-08 23:11
abyss_won27-Aug-08 23:11 
GeneralThe type or namespace name 'IBM' could not be found (are you missing a using directive or an assembly reference?) Pin
Member 224079615-Jul-08 23:06
Member 224079615-Jul-08 23:06 
GeneralGoooood Pin
yousefk2-May-08 7:59
yousefk2-May-08 7:59 
GeneralCouldn't load into the Visual Studio Pin
Try1013-May-07 2:04
Try1013-May-07 2:04 
QuestionUnable to connect the sql server Pin
inrakeshworld1-Apr-07 23:50
inrakeshworld1-Apr-07 23:50 
QuestionCan you tell me what the ER-DesignTool you use? Pin
Advanced27-Nov-06 3:51
Advanced27-Nov-06 3:51 
Can you tell me what the ER-DesignTool you use? Thanks.
Generallittle help .. Pin
usafz29-Sep-06 0:44
usafz29-Sep-06 0:44 
AnswerRe: little help .. Pin
Salvatore Vetro2-Oct-06 22:57
Salvatore Vetro2-Oct-06 22:57 
GeneralRe: little help .. Pin
usafz3-Oct-06 5:05
usafz3-Oct-06 5:05 
Questionsupporting MySql Pin
mgonz17-Aug-06 18:46
mgonz17-Aug-06 18:46 
AnswerRe: supporting MySql Pin
Salvatore Vetro2-Oct-06 23:00
Salvatore Vetro2-Oct-06 23:00 
Questionhow can i maintain buffer in my DAL Pin
asmysee7-May-06 22:47
asmysee7-May-06 22:47 
AnswerRe: how can i maintain buffer in my DAL Pin
Salvatore Vetro7-May-06 22:58
Salvatore Vetro7-May-06 22:58 
GeneralRe: how can i maintain buffer in my DAL Pin
asmysee8-May-06 18:52
asmysee8-May-06 18:52 
Generalgreat idea but...... Pin
Jocks775-May-06 10:48
Jocks775-May-06 10:48 
GeneralI want to create the IDbDataParameter array object. Pin
pareshguj18-Apr-06 21:40
pareshguj18-Apr-06 21:40 
AnswerRe: I want to create the IDbDataParameter array object. Pin
Salvatore Vetro18-Apr-06 22:05
Salvatore Vetro18-Apr-06 22:05 
GeneralThanks for yous Artical Pin
asmysee13-Apr-06 2:11
asmysee13-Apr-06 2:11 
AnswerRe: Thanks for yous Artical Pin
Salvatore Vetro17-Apr-06 22:28
Salvatore Vetro17-Apr-06 22:28 
GeneralVery good Pin
Only the best10-Apr-06 22:58
Only the best10-Apr-06 22:58 
AnswerRe: Very good Pin
Salvatore Vetro17-Apr-06 22:29
Salvatore Vetro17-Apr-06 22:29 
GeneralNice article. One question Pin
BigJim616-Apr-06 3:14
BigJim616-Apr-06 3:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.