Implementing a Generic Data Access Layer in ADO.NET Part 2

In the first part of this series of articles on Data Access Layer, we have had a look at what the strategies are, for designing and implementing a Generic Data Access Layer. We have had a look at the enumerators and the factory classes that we will be using. In this part of this article of three part series, we will discuss how we can implement the DatabaseHelper class, one that would be responsible for performing the actual database operations.

The DatabaseHelper class encapsulates the various calls to the database to perform the CRUD operations. The DBManager class that we will discuss later acts as a wrapper on top of this class. You have various methods in the DatabaseHelper class to add parameters, to execute queries, stored procedures, etc.

Here is the code that illustrates how the connection to the database is established based on the provider type chosen and the command object created.

 public DatabaseHelper(string connectionstring, ProviderType provider)
{
this.strConnectionString = connectionstring;
objFactory = DBFactory.GetProvider(provider);
objConnection = objFactory.CreateConnection();
objCommand = objFactory.CreateCommand();
objConnection.ConnectionString = this.strConnectionString;
objCommand.Connection = objConnection;
}

In ADO.NET, you have the following data providers.

  • SQL Server Data Provider
  • Oracle Data Provider
  • Odbc Data Provider
  • OleDB Data Provider

Note: Depending on the data provider used, you need to use the command object that is specific to that provider. Your data reader should also be specific to the data provider used. The use of the DBFactory class as shown in the code snippet above. Note that you use the command objects to execute the database commands that contain the SQL statements. Added to this, we will have overloaded versions of AddParameter method to add parameters to the command objects so that we can pass parameters to the database stored procedures or SQL statements. Here is the simplest version of the AddParameter method.

internal int AddParameter(string name, object value)

{
DbParameter dbParameter = objFactory.CreateParameter();
dbParameter.ParameterName = name;
dbParameter.Value = value;
return objCommand.Parameters.Add(dbParameter);
}

While the ParameterName identifies the unique name of the parameter to be passed, the Value implies the value of the parameter passed. Hence, if the ParameterName comprises of “@EmpName”, the Parameter’s value might be “Joydip Kanjilal”.
Continues…

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>