OOP Question, hope im in the right place

Well, I'll try to arrange my questions...

I took the Employees table of northwind and tried to work on it - just for the example
I managed to seperate my test app into Components, I hope I did well

1) I'v created an abstract class called BusinessObject which have for now 3 methods like
Load,Save,Delete.
2) created a class Employee which inherits from my BusinessClass and implements the methods
this class catches SQLException and wrap it with my own Exceptions, so my app won't
mind of which DB Provider I will use.

3) I have created a class which inherits from ApplicationException and from that class
I inherited 2 classed of my own Exceptions.
4) I have a DAL class which interacts with the Database , retrieves data , executenonqueries and
so on
so: ASPX page instaciates the Employee Class and catches My custom Exceptions.
So far... its seems ok... but

What if I want now to use the OLEDB or ORACLE providers , do I need to copy the Employee Class and make it fit with the right Connection,Adapter,Exceptions,Commnds syntaxes ?

I understand that the Application : ASPX+CodeBehind should not care cause I wrap my Excpetions with customs ones.
So I understand that for each Provider I have to Clone My Employee Class and the DAL class.

Hope someone could help me think if im thinking right

Thanks In Advance

How have you defined your DAL class?

Well for now my Dal class is using the System.Data.Sqlclient - for MSSQL

I guess if I would like to use OLEDB I have to make another dal using OLEDB

if its true, I ask again what if once I use OLEDB, or MSSQL or another provider should i also have to copy each Employee class ?

thx

Ok, I should have been clearer. Can you post your [skeleton] code for the DAL Class, i.e. field, property and method definitions? (So I can suggest an appropriate course of action)

Here is part of my dal

using System;
using System.Data;
using System.Data.SqlClient;

namespace Crud
{
public class DAL
{
private SqlConnection conn;
private SqlDataAdapter adapter;
private SqlCommandBuilder builder;
private SqlCommand selCommand = new SqlCommand();

public DAL()
{
conn = new SqlConnection(" .................. Connection String");

adapter = new SqlDataAdapter();
builder = new SqlCommandBuilder(adapter);
selCommand.Connection = conn;

adapter.SelectCommand = new SqlCommand();
adapter.SelectCommand.Connection = conn;
adapter.UpdateCommand = new SqlCommand();
adapter.UpdateCommand.Connection = conn;
adapter.DeleteCommand = new SqlCommand();
adapter.DeleteCommand.Connection = conn;
}
public void Open()
{
conn.Open();
}

public void Close()
{
conn.Close();
}

public DataSet GetTables(SqlCommand cmd)
{
cmd.Connection = conn;
adapter.SelectCommand = cmd;

DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
public DataSet GetTables(string sql)
{
adapter.SelectCommand.CommandText = sql;
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
public DataSet GetTables(DataSet ds, string sql, string tableName)
{
adapter.SelectCommand.CommandText = sql;
adapter.Fill(ds,tableName);
return ds;
}

public void Update(DataSet ds)
{
ds.AcceptChanges();
ds.Tables[0].AcceptChanges();
adapter.Update(ds);
}

Public SqlDataReader SelectReader(string sql)
{
selCommand.CommandText = sql;
return selCommand.ExecuteReader();
}

public int ExecuteCommand(SqlCommand cmd)
{
cmd.Connection = conn;
int rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected;
}

and so on, As you can see im using the SQLClient here , what about OLEDB or Other providers ?

If you define your Connection, DataAdapter and Command objects as their base Interfaces and then assigning the appropriate Odbc, OleDb, Oracle or SqlClient instantiation, you can call the appropriate method without worring about which type you are using. For example, defining your connection as IDBConnection you can still call Open, without having to cast the object, even if it is SqlClient or any other type:

IDBConnection conn;

Then in your Constructor:

conn = new SqlConnection();

conn.ConnectionString =connectionString;

conn.Open();

or

conn = new OleDbConnection();

conn.ConnectionString =connectionString;

conn.Open();

Ok I understand what you saying, SO this way i can make my Dal universal to all providers.

thats for the DAL..

SO now ->>>

ASPX - with code behind instantiates Employee Class which inherited from BusinessObject abstarct class.

The Employee Class uses the universal DAL ... but ... if I want to use SQLClient I would make Employee class using that base type ,
and if I want OLEDB I still need to copy Employee Class and make change there to use OLEDB base types ?... am I right ?

Thnks for patiance

 ziros wrote:
The Employee Class uses the universal DAL ... but ... if I want to use SQLClient I would make Employee class using that base type ,
and if I want OLEDB I still need to copy Employee Class and make change there to use OLEDB base types ?... am I right ?

Perhaps I didn't explain myself particularly well.

You need to implement an Enumeration to determine which Data Provider to instantiate. For example (each Provider is defined in the .Net Class Library):

public Enumeration DataProviderType {

   Odbc,

   OleDb,

   OracleClient,

   SqlClient,

   SqlServerCe

}

And then in the DAL class Constructor pass in a DataTypeProvider parameter and then use that to instantiate the appropriate objects:

DAL dal = new DAL(DataTypeProvider.SqlClient);

public DAL (DataTypeProvider dataTypeProvider) {

   switch (dataTypeProvider) {

      case DataTypeProvider.SqlClient :

         //instantiate SqlClient objects to the IDB fields

         break;

      case DataTypeProvider.OleDb :

         //instantiate OleDb objects to the IDB fields

         break;

      //etc.

   }

}

Unfortunately, with this solution, you can't use the SqlCommandBuilder, as there isn't a CommandBuilder Interface