problem when creating database independant access layer

Hassan Amaar

I tried to create database independant using new feature of visual .net(factories)the problem happen when passing parameters for orcale and sql server the shape of this passed parms differs that will genrate error when passing parameter or i will have to write code for diffrent diffrent database cases & that will be on the contrary of the reason of factories and exisance. thanks alot Hassan amaar.

Hassan Amaar

Paddy Boyd

I think you're maybe missing the point of the factory pattern - in what way do the passed parameters change?

Hayder Marzouk

Hi, In fact they are different : SQLServer provider : @ + name Oracle : p + name OleDB : ? (without a name) ODBC (i think) : ? (without a name) Me too i don't know why MS decided to change parameter names with providers. It's as is it and we must deal with that. There are many other differences : - Data Types - Quotes : []in sql and access , "" in Oracle,.... - And Queries are different so use only standard SQL (SQL-92 or SQL-99) to be sure that u don't need to rewrite your application when changing provider. I think u must create a class for each provider and declare diffrences as variables that u change in each class. I am sure that's the better way. HTH. Hayder Marzouk

Hassan Amaar

Thanks alot for yuor replay, it differs in the the way the prameter are passed for instance when passing aprameter to orcale prvider it will be like this :p + name for Sqlprovider it will be like this :@ + name so in this case according to what I understood I will have to create aclass for each data provider. I will do appreciate if you told if there is better solution,and please give me an example. Hassan amaar

Hassan Amaar

Hassan Amaar

Thanks you for your replay I think what you said is the only way to deal with this case Hassan Amaar

Hassan Amaar

Paddy Boyd

Well yes, this is really the idea behind the factory pattern, something like:

DalFactory fact = new DalFactory();

fact.GetDal();

then in the factory class:

GetDal()
{
IDal aDataLayerClass;

switch (source)
{
case "SqlServer":

     aDataLayerClass = new SqlServerDal();
     break;

  case "Oracle":

     aDataLayerClass = new OracleDal();
     break;

  etc...

}

return aDataLayerClass;
}

This means that whatever is calling your data layer, it doesn't matter to them what the datasource is in the background, as they are just dealing with a class like IDal, and you can write provider specific code for each datasource.