Help! Typed Dataset across multiple databases (e.g. IDBDataAdapte

[Guest]

Is there a way the strongly typed dataset wizard will create classes that are
usable across multiple databases?

We are writing an application that can use Oracle, PostgreSQL, or Sql Server
as the database. We have setup a DAL (using IDBConnection, IDBDataAdapter,
etc.) to facilitate database portability. We can now use the DAL for all our
database functions, which is great.

However, we can't figure out how we to utilize the auto-generated strongly
typed dataset classes across multiple databases. Is it possible? Our app
has 1000's of columns, so this would be a MASSIVE time saver vs. creating all
the classes by hand or hand-writing all the SQL.

Thanks,
Josh

 

[Sahil Malik [MVP]]

Datasets are disconnected from the database - there is no reason why a
dataset generated from one source won;t work with another.

But I think you will have to give up drag drop and get down dirty with code
to make that work in practise.

What exactly are you trying to acheive here?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Guest]

Our problem isn't with datasets, but rather with the database specific code
that is created when generating a strongly typed dataset. Strongly typed
datasets seem like a perfect solution to our system requirements: we have
over 100 tables with 1000-1500 columns that must be accessed within the code,
via Windows Forms, or transmitted to web forms. Strongly typed datasets give
us this UI and platform flexibility, while facilitating N-tier communications
via XML.

However, this application must be database independent (SQL Server,
PostgreSQL, Access, or Oracle) at the same time, which is where we're lost
.... how can we use the Strongly Typed DataSet classes with ADO.NET's database
interface classes?

Using the auto-generated strongly typed datasets would greatly reduce our
development time and future support costs, so we're looking for any way that
we could use them ... e.g.:
- can we extend the strongly typed dataset classes to leverage the
interface classes instead?
- can we modify some configuration in the class wizard so that
IDBDataAdapter and other IDB* classes are used instead of SqlDataAdapter,
OracleDataAdapter, etc.?
- is there any other slick way to perform this?

I imagine that many companies run into this issue, particularly since
Microsoft advocates strongly typed datasets and building DAL factories to
achieve n-tier database independant applciations.

Your help is greatly appreciated,
Thanks,
josh

 

[Sahil Malik [MVP]]

First of all - again - you have to consider the strongly typed datasets as
completely disconnected from the database. What sits in the middle of the
dataset - is the data adapter.

So your data adapter is database specific, and it has database specific
commands. If those are auto generated for you, they can be auto generated
for any other database using the relevant "Command Builder" object.

Database specific objects are certainly interface based, so yes you can use
strongly typed datasets to leverage interface based classes (i.e. strongly
typed instances casted to interfaces) - and yes that approach will work.

But anytime I am faced with such complexity, I throw drag and drop out the
window, and get down on the floor and write code. I just don't think drag
drop is meant for this.

I must point out - with drag drop - the command builder is generating
hyper-inefficient queries. With 1000-1500 columns - just look at the where
clause of the auto generated queries - every single column is in the where
clause of the update query.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/