If you are able to have a perfect mirroring of names for things like
stored
procedures, then true, you don't have to use the IDatabaseObject .....
(as in , using the IDatabaseObject ~and the EnterpriseLibrary.Data.
For example, if you had a stored procedure:
uspEmployeesGetAll
and you wrote the same stored procedure for sql server and also for
Oracle,
then you can just use the Enterprise Library.
So the datalayer object (using the EnterpriseLibrary.Data object) would
look
somehting like this:
private readonly string USP_EMPLOYEES_GET_ALL =
"uspEmployeesGetAll";
private Microsoft.Practices.EnterpriseLibrary.Data.Database
GetDatabase()
{
// Create the Database object, using the default database
service. The
// default database service is determined through
configuration.
Database db = DatabaseFactory.CreateDatabase();
return db;
}
public MyStrongDataSet GetAllEmployesDS()
{
Database db = this.GetDatabase();
string sqlCommand = this.USP_EMPLOYEES_GET_ALL ;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
DataSets.RealEstateMasterDS returnDS = new MyStrongDataSet ();
db.LoadDataSet(dbCommand, returnDS, new string[] {
returnDS.Employee.TableName });
return returnDS;
}
Again, the code above will work with either sql server OR Oracle (based on
what you put in the dataConfiguration.config file)
PROVIDED YOU HAVE A STORED PROCEDURE in either rdbms named
"uspEmployeesGetAll"
Which is what you're getting at:
However........................
If the name of your stored procedure was
uspEmployeesGetAll (in sql server)
and
prcAllEmployeesGet (in oracle) (I don't know what naming conventions in
oracle are)
~~then you'd want to use the IDatabaseObject idea that I described.
Because using the Interface method will ~~avoid~~ this situation:
public MyStrongDataSet GetAllEmployesDS()
{
Database db = this.GetDatabase();
string sqlCommand = string.Empty:
if (m_usingOracle == true) //some flag saying youre using oracle
{
sqlCommand = "prcAllEmployeesGet";
}
else
{
sqlCommand ="uspEmployeesGetAll " ;
}
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
DataSets.RealEstateMasterDS returnDS = new MyStrongDataSet ();
db.LoadDataSet(dbCommand, returnDS, new string[] {
returnDS.Employee.TableName });
return returnDS;
}
the above is "hacky" in that your adding if statements based on a db
(because of naming conventions)
the IDatabaseObject ....... with 2 concrete implementations avoids this.
because each concrete class can call which stored procedure is germane to
that database .. .without doing a bunch of if'ing.
I'm actually working on a project where I did this using Access and Sql
Server.
The IDatabaseObject method works great, because I use inline sql with
Access, and stored procedures with Sql Server
Here is roughly how I do it.
interface IDatabaseObject
{
MyStrongDataSet GetAllEmployesDS();
}
class EmployeeDataViaSqlServer : IDatabaseObject
{
public MyStrongDataSet GetAllEmployesDS()
{
Database db = this.GetDatabase();
string sqlCommand = "uspEmployeesGetAll" ;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
MyStrongDataSet returnDS = new MyStrongDataSet ();
db.LoadDataSet(dbCommand, returnDS, new string[] {
returnDS.Employee.TableName });
return returnDS;
}
}
class EmployeeDataViaAccess : IDatabaseObject
{
public MyStrongDataSet GetAllEmployesDS()
{
Database db = this.GetDatabase();
string sqlCommand = "Select EmpID, LastName, FirstName,
MiddleName from Employees" ;
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
MyStrongDataSet returnDS = new MyStrongDataSet ();
db.LoadDataSet(dbCommand, returnDS, new string[] {
returnDS.Employee.TableName });
return returnDS;
}
}
class MyDatabaseFactory
{
public static IDatabaseObject GetADatabaseObject(string keyValue)
{
IDatabaseObject returnObject = null;
if(keyValue=="Access")
{
returnObject = new EmployeeDataViaAccess();
}
else
{
returnObject = new EmployeeDataViaSqlServer();
}
return returnObject;
}
}
Then you'd get a database like this:
string keyValue = ""; // get the value from .config file or something like
that
IDatabaseObject dbo = MyDatabaseFactory.GetADatabaseObject(keyValue);
MyStrongDataSet ds = dbo.GetAllEmployeesDS();
Console.WriteLine (ds.GetXml());
Again, I think if you know (going into the project) you're going to be
supporting N number of rdbms' , then I'd put the effort in to go with the
IDatabaseObject (with a corresponding factory object) .........
Eventually, you''re going to hit a case where you need to name something
different (stored procedure, view, etc) in sql server then you would in
oracle.
It makes maintenance so much easier. And you'll never fall victim to
if (key=="oracle")
{
//do something
}
{
//do something else
}
You put all decision making in the Factory, making it cleaner. And which
is
what the Factory (Simple) Pattern is about.
I hope that explanation works.
But if you think you can pull off the "same names for every table, view,
stored procedure", then you are correct, you can pull it off with just the
EnterpriseLibrary.Data.
If it were me (and like I said, this is what I did), I'd go ahead and code
up the extra Interface and Factory right from the start.
Mythran said:
We replied just minutes apart
Anywho, I don't understand why you would recommend implementing a
SQLDatabase as well as OraDatabase object and Enterprise Library as well?
Unless you meant if the OP did not want to use Enterprise Library,
Enterprise Library has both classes already available (or set of
classes...IE: SqlDatabase and OracleDatabase IIRC).
HTH,
Mythran
