Click here to Skip to main content
Click here to Skip to main content
Go to top

Independent Data Access Layer (Ddocumentation in Progress)

, 4 Apr 2006
Rate this:
Please Sign up or sign in to vote.
The art of swap from a database to another changing a flag

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.

  • Participants and Collaborators
    • AbstractFactory
    • ConcreteFactory 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.

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

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

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

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)

Share

About the Author

Salvatore Vetro
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 PinmvpMd. Marufuzzaman20-Mar-10 20:30 
GeneralYou are very goooooooooooood! Pinmemberyin11223428-Jun-09 17:14 
Generalcmd.Parameters.AddWithValue [modified] PinmemberMember 564686715-Nov-08 1:02 
GeneralGreat job!!!! Pinmemberabyss_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?) PinmemberMember 224079615-Jul-08 23:06 
GeneralGoooood Pinmemberyousefk2-May-08 7:59 
GeneralCouldn't load into the Visual Studio PinmemberTry1013-May-07 2:04 
QuestionUnable to connect the sql server Pinmemberinrakeshworld1-Apr-07 23:50 
QuestionCan you tell me what the ER-DesignTool you use? PinmemberAdvanced27-Nov-06 3:51 
Generallittle help .. Pinmemberusafz29-Sep-06 0:44 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 4 Apr 2006
Article Copyright 2006 by Salvatore Vetro
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid