By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

Typed dataset

P: n/a
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
{
}

Nov 20 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a


"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
{
}
I would suggest looking into the Enterprise Library written by Microsoft
(Patterns and Practices) for your connection questions.
http://link.toolbot.com/gotdotnet.com/23723

For your Typed DataSets...you can make a single typed dataset for both. All
the DataSet does is store the information received from the database.

HTH,
Mythran
Nov 20 '06 #2

P: n/a

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
{
}

Nov 20 '06 #3

P: n/a


"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
Nov 21 '06 #4

P: n/a
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


Nov 21 '06 #5

P: n/a


"sloan" <sl***@ipass.netwrote in message
news:en**************@TK2MSFTNGP04.phx.gbl...
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


If the developer uses the app settings (web.config) to store the stored
procedure names and connection info (which is what the EL uses for it's
connection string information), then you won't have to worry about
recompiling or using custom interfaces if/when you change DBMS's...just a
thought ;)

HTH,
Mythran
Nov 21 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.