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
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
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
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.
