473,327 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Typed dataset

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
5 3094


"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

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


"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
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


"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Job Lot | last post by:
I am confused how strongly typed dataset is different from un-typed dataset. Is there any good link explaining pros and cons of both? Which one should be used preferably?
1
by: Nedu N | last post by:
Hi All, I am facing problem in copying content of table from a untyped dataset into to a table inside the typed dataset. I wanted to copy the data into typed dataset in order to ease the further...
2
by: BeanTownBizTalkGuru | last post by:
Trying to get some feedback. Here's the scenerio. We are processing XML data which we generated a typed dataset to represent the XML document being processed. After procesing is complete we...
1
by: HardBap | last post by:
I've created a strongly typed DataSet (Customers.xsd) using the xsd.exe tool. I want to be able to access fields using ds.Customer.CompanyName. The problem is when I return this DataSet from a...
1
by: Trond | last post by:
I have a class MessageController that has a method GetMessagesDataset that connects to a database SPROC. When done it returns a dataset. Then in my ASP.NET for i do this: msgController = new...
3
by: Freeon | last post by:
Hi, I am looking for a way to sort a strong typed dataset. It would seem the most straightforward way is to use a dataview. The only problem is when I use the dataview I seem to loose the strong...
1
by: Optimus | last post by:
Hi everyone, I currently develop an application in vs.net 2005 with vb.net. I was trying to use typed dataset and I've got in trouble for converting untyped dataset into Typed DataSet. I don't...
4
by: Ronald S. Cook | last post by:
I've always used untyped datasets. In a Microsoft course, it walks through creating typed datasets and harps on the benefits. It has you drag all these things around ..wizard, wizard, wizard......
21
by: Peter Bradley | last post by:
Hi all, This post is sort of tangentially related to my earlier posts on configuration files for DLLs. Does anyone know how to create typed DataSets using VS2005's new DataSet designer, but...
4
by: Rachana | last post by:
Hi, I have understood Data Sets but what is meant by typed/untyped/ strongly typed datasets. Can any one explain me or suggest any site/ article, to get these concepts (and their ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.