This article takes a look at the strategies that can be adopted for implementing a generic data access layer using ADO.NET. It discusses how efficiently you can make use of generics to design and implement a data access layer that can work with almost any database. It also discusses the pros and cons of using the Data Access Application Block from Microsoft.
Generally an application can be divided into:
The User Interface Layer is concerned with interacting with the user and display data. The Business Logic Layer is concerned with executing the business logic operations of the application based on certain business rules.
A Data Access Layer comprises of a collection of classes, interfaces and their methods and properties that are used to perform CRUD (Create, Read, Update and Delete) operations in the application. A Data Access Layer encapsulates the code that is used to connect to the database and perform these operations and it actually works as a link between the business entities in your application and the actual data storage layer. You typically use the Data Access Layer to create and populate business entities with data from the database and for updating and storing business entities in the database.
Features of a Data Access Layer
A Data Access Layer should provide the following features:
The ADO.NET library provides you some major classes and interfaces that you can use to design and implement your data access layer.
The ADO.NET library supports the following providers:
In order to make the data access layer provider independent, you can use the factory pattern. We will now see how we can design a factory class that can be used to return a specific data provider, connection, command, data adapter or data reader, all based on the database type you are using. Here is the code:
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
public enum DataProviderType
{
Oracle, SqlServer, OleDb, Odbc
}
internal class DBFactory
{
private static DbProviderFactory objFactory = null;
public static DbProviderFactory GetDataProvider(DataProviderType provider)
{
switch (provider)
{
case DataProviderType.SqlServer:
objFactory = SqlClientFactory.Instance;
break;
case DataProviderType.OleDb:
objFactory = OleDbFactory.Instance;
break;
case DataProviderType.Oracle:
objFactory = OracleClientFactory.Instance;
break;
case DataProviderType.Odbc:
objFactory = OdbcFactory.Instance;
break;
}
return objFactory;
}
public static DbConnection GetConnection(DataProviderType providerType)
{
switch (providerType)
{
case DataProviderType.SqlServer:
return new SqlConnection();
case DataProviderType.OleDb:
return new OleDbConnection();
case DataProviderType.Odbc:
return new OdbcConnection();
case ProviderType.Oracle:
return new OracleConnection();
default:
return null;
}
}
public static DbCommand GetCommand(DataProviderType providerType)
{
switch (providerType)
{
case DataProviderType.SqlServer:
return new SqlCommand();
case DataProviderType.OleDb:
return new OleDbCommand();
case DataProviderType.Odbc:
return new OdbcCommand();
case DataProviderType.Oracle:
return new OracleCommand();
default:
return null;
}
}
public static DbDataAdapter GetDataAdapter(DataProviderType providerType)
{
switch (providerType)
{
case DataProviderType.SqlServer:
return new SqlDataAdapter();
case DataProviderType.OleDb:
return new OleDbDataAdapter();
case DataProviderType.Odbc:
return new OdbcDataAdapter();
case DataProviderType.Oracle:
return new OracleDataAdapter();
default:
return null;
}
}
}
Now we will create a wrapper on top of this class called DBManager. Here is the code:
using System.Data.Common;
using System;
using System.Data;
public sealed class DataManager
{
private DbConnection dbConnection;
private String strConnectionString;
private DataProviderType dataProviderType;
public DBManager(DataProviderType providerType, string
connectionString)
{
this.strConnectionString = connectionString;
this.dataProviderType = providerType;
dbConnection = DBFactory.GetConnection(providerType);
dbConnection.ConnectionString = connectionString;
}
public void Open()
{
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
}
public void Close()
{
if (dbConnection.State != ConnectionState.Closed)
dbConnection.Close();
}
public DbConnection Connection
{
get
{
return dbConnection;
}
}
public String ConnectionString
{
get
{
return strConnectionString;
}
}
public DataProviderType DBProvider
{
get
{
return dataProviderType;
}
}
public DataSet GetDataSet(String sqlString)
{
using (DbDataAdapter dbDataAdapter = DBFactory.GetDataAdapter(this.DBProvider))
{
try
{
dbDataAdapter.SelectCommand = DBFactory.GetCommand(this.DBProvider);
dbDataAdapter.SelectCommand.CommandText = sqlString;
dbDataAdapter.SelectCommand.Connection = this.Connection;
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable();
dataTable.BeginLoadData();
dbDataAdapter.Fill(dataTable);
dataTable.EndLoadData();
dataSet.EnforceConstraints = false;
dataSet.Tables.Add(dataTable);
return dataSet;
}
catch (Exception ex)
{
return null;
}
}
}
}
You can now use this class to perform CRUD (Create, Read, Update, and Delete) operations in your database. I leave it to my readers to incorporate more and more methods as per the requirements in this class.
You can also use the DataManager class to bind data to the data controls. To bind data to a repeater control, use this code:
DBManager dbManager = new DBManager(DataProvider.SqlServer);
dbManager.ConnectionString =
ConfigurationSettings.AppSettings["ConnectionString"].ToString();
try
{
dbManager.Open();
Repeater1.DataSource =
dbManager.GetDataSet("Select employeeCode, employeeName,
employeeAdress from employee");
Repeater1.DataBind();
}
catch (Exception ex)
{
throw;
}
finally
{
dbManager.Close();
}
Conclusion
This article has presented the best practices and strategies for implementing a data access layer. It should be noted that you should open connections as late as possible and close them as early as possible to optimize the data access performance.