C# Help Home » Articles » Data Access » Data Base Independent Data Access Layer

Data Base Independent Data Access Layer

28. Sep, 2007 by admin 0 Comments

Introduction

In this Article I want to describe you how we can approach a data base free data layer.

It doesn't mean that we will not use anydatabase, but we will try to incorporate any data source with our datalayer. This kind of problem happen when we are not sure about eitherdata source/Database will be Oracle, SQLServer, Access, etc.

An advantage having with this approach is when data source changes there is no single line change in our database layer.

Scope

C#, VB.NET, Visual Basic, Java (Both Desktop and web)

Implementation of Data Access Layer

Let me describe whatever approaches I havetried to solve this problem. First I thought I have to use Interfacelevel communication rather than implementation as we were doing inpast.

Rather than using Sqlconnection, Oledbconnection, SqlCommand, OledbCommand etc.

For connection
- DbConnection.
For Command
- IDbCommand.

One problem solves of specific connection andcommand objects. Another problem is how I should tell my data accesslayer to retrieve parameter for stored procedure. Because if I write inthe code then our problem is remain we have to modify the data accesslayer after any data source changes.

Then I have to introduce a file which can be modified on runtime and then ready to another data source.

I introduced an Xml file for this.

Class Diagram

image002 Data Base Independent Data Access Layer

Class Code

For Generic connection and command I use factory method to get the generic connection irrespective of any data source.

private static IDbConnection GetConnectionFromFactory(string strDBProvider, string strDBConnectionString, IDbConnection objIdbConnection)
{
switch (strDBProvider)
{
case _SQLSERVER:
{
objIdbConnection = new SqlConnection(strDBConnectionString);
break;
}
case _ORACLE:
{
objIdbConnection = new OleDbConnection(strDBConnectionString);
break;
}
case _OTHER:
{
objIdbConnection = new OleDbConnection(strDBConnectionString);
break;
}
}
return objIdbConnection;
}

Similarly for I have to apply this generic theme to all of my ADO.net objects.

public static IDataParameter[] GetParameter(int parmaterCount)
{
IDataParameter[] idbParamters = null;
if (parmaterCount > 0)
{

switch (/*ConfigurationSettings.AppSettings["providerName"]*/"sqlserver")
{
case _SQLSERVER:
{
idbParamters = new SqlParameter[parmaterCount];
break;
}
case _ORACLE:
{
idbParamters = new OleDbParameter[parmaterCount];
break;
}

case _OTHER:
{
idbParamters = new OleDbParameter[parmaterCount];
break;
}
}
}
return idbParamters;
}

And same operation with DataCommand Objects.There are some comments on ConfigurationSettings.AppSettings.Because Iwas using this in dll and I cann.t access theConfigurationSettings.AppSettings object. But we can use any way aroundon it. Anyhow this is not our point of discussion here.

public static IDbCommand GetIDBCommand()
{
IDbCommand objIdbComand = null;

switch (/*ConfigurationSettings.AppSettings["providerName"]*/"sqlserver")
{
case _SQLSERVER:
{
objIdbComand = new SqlCommand();
break;
}
case _ORACLE:
{
objIdbComand = new OleDbCommand();
break;
}

case _OTHER:
{
objIdbComand = new OleDbCommand();
break;
}
}

return objIdbComand;
}

We can pass our stored procedure code andobject value array. We have a utility method through which we canaccess the stored procedure name and it.s collection of parameters.

There is one thing very strictly follow thatyou have to pass object value array in same sequence of the parameteras you have defined in the stored procedure in the database. Thisstored procedure parameters sequence is also reflecting in theconfigXml file.

Retrieving stored procedure information form Configuration file

XmlNodeList xnlstParamtercollection = xnode.SelectNodes("parameterMappings/parameter");

if (xnlstParamtercollection != null && xnlstParamtercollection.Count > 0)
{
IDBParameter = DataProvider.GetParameter(parameterValues.Length);
int paramcounter = 0;
foreach (XmlNode name in xnlstParamtercollection)
{
IDBParameter[paramcounter] = DataProvider.GetParameterInstance();
IDBParameter[paramcounter].ParameterName = name.SelectNodes("@name").Item(0).InnerXml;
IDBParameter[paramcounter].Value = parameterValues[paramcounter];

if (name.SelectNodes("@direction").Item(0).InnerXml == DEFAULT_PARATMER_DIRECTION)
IDBParameter[paramcounter].Direction = ParameterDirection.Input;

else
IDBParameter[paramcounter].Direction = ParameterDirection.Output;
objIdbCommand.Parameters.Add(IDBParameter[paramcounter]);

paramcounter++;
}
}

There is also possibility that you canretrieve the parameter collection from the SQLServer directly .Butanyway this is working fine. Because here is our main intention isdatabase free DAL.

Specialized parameter retrieving mechanism for SQLServer

But now consider the scenario when we havespecially data source SQLServer. Then we have to use this functionwhich is not including in my code but you can consider it.

private static SqlParameter[]

DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;

connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();

if (!includeReturnValueParameter)

{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

// Init the parameters with a DBNull value
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}

I will try my best to improve this article innext version because truly speaking this is an idea how we canimplement this Database frees DataAccessLayer. But I am sure when youpeople read it then you can also incorporate your ideas in it or youcan give me suggestion which is very helpful for me.

Future Development

This bit is pretty much up to you guys, if anyone makes any valid suggestions I'd be more than happy to implement them.

History

Version 1.0 (02 September 2007) – Initial Release

About Nazish Ali Rizvi

Hi, I am Nazish Ali I did my Master of Computer Science from Karachi University.Primary Programming Language is C# .My other hobbies Reading philosophy, poetry.