Link to home
Start Free TrialLog in
Avatar of gjok
gjokFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Switching between database providers in web.config / Data Access Layer

Hi.
I've been looking at a basic example of a web application that allows you to specify between MS Access or SQL Server in the web.config. (see snippet part 1 below)

Now, as both these providers use ifferent sql syntax, I understand that I will need to parse my sql statements accordingly as shown in snippet part.3.

However, how do I handle things like "getdate()" which is not supported by Access? Am I to have separate code blocks for handling Access and sql server? If so isnt the following line kind of pointless?:

param.ParameterName = DalHelpers.ReturnCommandParamName("startDate")

-or- should I just NOT use "getdate()" and build the date manually?

Also, am I correct in assuming that the code in snippet part.2 will handle the dates (sql uses a quote and access uses a '#' symbol)

Part.1 - Web.config:
 
  <appSettings>
    <add key="DefaultConnectionString" value="SqlServerConString" />
  </appSettings>
 
  <connectionStrings>
    <add name="SqlServerConString" connectionString="server=(local)\SqlExpress etc, etc, " providerName="System.Data.SqlClient" />
    <add name="AccessConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwrootI etc, etc ;" providerName="System.Data.OleDb" />
  </connectionStrings>
 
 
 
Part.2 - Code
      param = myCommand.CreateParameter()
      param.ParameterName = DalHelpers.ReturnCommandParamName("startDate")
      param.DbType = DbType.DateTime
      param.Value = startDate
      myCommand.Parameters.Add(param)
 
 
 
Part.3 - Class:
 
  Public Shared Function ReturnCommandParamName( _
      ByVal paramName As String) As String
    Dim returnValue As String = String.Empty
    Select Case AppConfiguration.ConnectionStringSettings.ProviderName.ToLower()
      Case "system.data.sqlclient"
        returnValue = "@" & paramName
      Case "system.data.oledb"
        returnValue = "?"
      Case Else
        Throw New NotSupportedException("The provider " & _
            AppConfiguration.ConnectionStringSettings.ProviderName & " is not supported")
    End Select
    Return returnValue
  End Function

Open in new window

Avatar of nsanga
nsanga

I am not a user of SQL Server. But for ur requirement, I would suggest you to define a interface and as many classes as type of data bases you want to support like

public interface IDataBase
{

   public MyData GetData();
public SaveData(MyData data1);

}

and define classes like

class SQLServer: IDataBase {}

class OracleDataBase: IDataBase {}

Once you have these classes, you can handle sql executions seperately for each type.

This is a common generic way of handling things in software world. Hope this will help you.
Avatar of gjok

ASKER

That sounds good, but I have never used interfaces before, and I am not sure how and where I would implement it
Could you post a small example?
It's way better to use System.Data.Common. And then you can e.g. have the Enterprise Library DataAccess block provide the factory for you. It will create the appropriate instances of database classes based on the provider type in your config file.
saves you a lot of hand crafting already existing stuff.
Avatar of gjok

ASKER

Is the Data Access Application Block an actual DAL?
The Enterprise Library needs to be installed . What if the webhost doesnt have it installed?
Avatar of gjok

ASKER

Also I dont think it supports 'MS Access' (or at least not completely)
Since you're having issue only with date may be you should check for the kind of Data access your currently connected (SQL Server or Access) and should build or access the date accordingly.

switch( databaseModel )
{
case SQLServer:
// Logic goes here
break;
case MSAccess:
// Logic goes here
break;
}
it supports ms access via oledb.

the Enterprise Library is not a DAL, but it abstracts your database calls in a way.
ASKER CERTIFIED SOLUTION
Avatar of nsanga
nsanga

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gjok

ASKER

nsanga,
This is looking interesting and I could learn what interfaces are at the same time.
If this interface is in my DAL, how would I call it from my UI?

Like this ?
MyData.SQLServer.Connect()
and for Access:
MyData.MsAccess.Connect()
if so, doesnt this mean that I will need to write duplicate/similar code for each provider?
yes.....

to use it...

IDataBase mysql = new SQLServer(@"Data Source=SWI-XSSAYAM\XSSAYAM; Initial Catalog=SPF_Catalog; User ID=scott; Password=tiger" );
mysql.Connect();
MyData d1= mysql.GetData("select * from table1");


//similary for oracle

IDataBase myoracle = new Oracledatabase(@"Provider=MSDAORA;Data Source=ORCL; User ID=user1;  Password=oracle;" );
myoracle.Connect();
MyData d1= myoracle.GetData("select * from table1");


but one thing to say, if you use oledb as below,

 class Oledbdatabase //: IDataBase
    {
        private string mstrConnString;
        private System.Data.OleDb.OleDbConnection  conn;
        private System.Data.OleDb.OleDbCommand cmd;

        Oledbdatabase(string pstrConnection)
        {
            mstrConnString = pstrConnection;
        }


        // any other SQLServer specific member variables

        public void Connect()
        {
            // connection to database. may be you need to have connection and command objects instantiated here
            conn = new System.Data.OleDb.OleDbConnection(mstrConnString);
            conn.Open();
            cmd = conn.CreateCommand();
        }

        public Hashtable GetData(string SQL)
        {
            // here
            //execute the sql against command and create your object and return it
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = SQL;
            System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();

            Hashtable mcolitems = new Hashtable();

            while (dr.Read())
            {
                MyData d1 = new MyData();

                // now populate d1 properties by reading from dr


                //then all it to return collection
                mcolitems.Add(d1.id, d1);
            }

            return mcolitems;
        }
    }


you can send connection string for any provider which support oledb ( eg oracle)

also please include following line in  in Connect() function ( as in above code)
 conn.Open();  
Avatar of gjok

ASKER

Thanks nsanga, this looks faily easy and I dont need to worry about installing anything. The Enterprise Library sounds good, but I have too many questions...

PockyMaster, what are the drawbacks with the Enterprise Library?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gjok

ASKER

I hope its OK with you both that I split the points in half.
I think you both offer equally viable solutions to this problem, and I plan on experimenting with both approaches.
Thanks