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.Databas e
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" <ki********@hotmail.comwrote in message
news:OT**************@TK2MSFTNGP03.phx.gbl...
>
"sloan" <sl***@ipass.netwrote in message
news:#v**************@TK2MSFTNGP03.phx.gbl...
1 typed dataset: definately. That's the cool part about a typed
dataset.
It doesn't matter whehter its populated by access, sql server, excel ,
oracle, anythingUnderTheSun.
The way you want to architect is like this:
IDatabaseObject
public MyTypedDS GetAllEmployees()
Then you can have 2 concrete implementations.
OracleDatabaseObject : IDatabaseObject
SqlServerDatabaseObject : IDatabaseObject
Then you create a factory class, which decided which of the 2 concretes
to
return
DatabaseFactory
public IDatabaseObject ( string mykey )
{
if (key=="oracle")
return new OracleDatabaseObject
else
return new SqlServerDatabaseObject
}
You would DEFINATELY look at the EnterpriseLibrary(.Data) object.
Not only could you use sql server or oracle, you could use the ~same
syntax
to talk to the database.
I would still do IDatabaseObject thing.. .I think its worth the effort.
Because something you'll find some situation where oracle and sql server
are
different enough to justify 2 concrete classes.
http://sholliday.spaces.live.com/blog/
go here, and find the article on "Factory Method" using the key,
environment
and reflection methods.
"John" <Jo**@discussions.microsoft.comwrote in message
news:BF**********************************@microsof t.com...
Hi,
I am developing a windows app using C# 2005. This app uses SQL Server
or
Oracle database depending on the what the user is using.
Can I create one typed dataset and use it for SQL Server and Oracle?
Probably not.
How can I structure my data access layer so the app will be switching
easily
between those two databases with a locally stored db connection
string?
How
about the following? Thanks for your suggestions.
public class Database
{
}
public class SQLDatabase : Database
{
}
public class OraDatabase : Database
{
}
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