By Nazish Ali Rizvi
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 any database, but we will try to incorporate any data source with our data layer. This kind of problem happen when we are
not sure about either data 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 have tried to solve this problem. First I thought I have to use Interface level communication rather than implementation
as we were doing in past.
Rather than using Sqlconnection, Oledbconnection, SqlCommand, OledbCommand etc.
For connection
- DbConnection.
For Command
- IDbCommand.
One problem solves of specific connection and command objects. Another problem is how I should tell my data access layer to retrieve parameter for stored
procedure. Because if I write in the code then our problem is remain we have to modify the data access layer 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.
<objectMapping type="ForTestAuthentication">
<!--table name-->
<operation name="DbInsert" fname="tested">
<!--operation name-->
<command name="LoginCheck" spnumber="0">
<!--stored procedure name-->
<parameterMappings>
<parameter name="@username" member="aspnet_Users" type="varchar" length="50" direction="input"/>
</parameterMappings>
</objectMapping>
Class Diagram
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 I was using this in dll and I cann.t access
the ConfigurationSettings.AppSettings object. But we can use any way around on 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 and object value array. We have a utility method through which we can access the stored procedure name and it.s collection
of parameters.
There is one thing very strictly follow that you have to pass object value array in same sequence of the parameter as you have defined in the stored procedure
in the database. This stored procedure parameters sequence is also reflecting in the configXml 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 can retrieve the parameter collection from the SQLServer directly .But anyway this is working fine. Because here is our main
intention is database free DAL.
Specialized parameter retrieving mechanism for SQLServer
But now consider the scenario when we have specially data source SQLServer. Then we have to use this function which 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 in next version because truly speaking this is an idea how we can implement this Database frees DataAccessLayer. But
I am sure when you people read it then you can also incorporate your ideas in it or you can 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.
