.Net: how to create vendor independent Dataset, Tableadapters, bindings (DB decided at runtime)

Question

I have a C# Windows Forms application, whose prototype was created on SQL Server (strongly-typed dataset). In its final version, the application must be able to work over SQL Server, MySQL or Oracle.

Now I am wondering which parts (if any) can be reused from the prototype. 1. Dataset (typed) ? 2. TableAdapters? (probably not, they contain SQL Server-specific syntax) 3. Bindings to DataGridViews

Most importantly, if we need to re-implement all this, is there a way to do this at design-time? Or, 1. do we need to programmatically create untyped-dataset? 2. do we need to programmatically create its data adapters (or table adapters)? If yes, which of the two? 3. do we need to programmatically create its bindings to the datagridviews of the interface?

Perhaps irrelevant: if we create a entity model (AFAIK it provides db independence) from the existing db schema, could we use this somehow to create bindings to our datagridviews?

Thank you!

Answer 1

Many questions, here some answers:

• Datasets are DB independent. Typed Datasets probably as well.
• DataAdapters are in so far DB dependent as they contain SQL to talk to the DB
• There are abstractions and possibilities to work vendor independent with the basic ADO.NET concepts (IDbConnection, IDbCommand, etc.)
• You may also bind plain old c# objects to BindingSource & friends. If you go down this route prepare to throw pretty much everything away you have prototyped. You will require a framework that can translate between "entities" and the DB. It will depend on that framework what the limitations on your entities are and how your DB independence looks like.

Answer 2

So, in order to keep our Bindings and dataGridViews, as well as some additional logic we have implemented, should we throw away all the generated TableAdapters and write them manually? If we do throw them away, should we use DataAdapters instead?

Is this a "by-the-book" approach? Has anyone done something like this?

More generally, if you need to create a Forms application to work in multiple dbs, would you do it: A. with untyped dataset, dataadapters/tableadapters and bindings created by hand B. somehow generate a vendor-independent dataset and dataadapters/tableadapters (how?) and bind them at design time through the VS gui C. some other way???

Answer 3

1. The typed dataset/table is database independent. (however, if you add adapters in the designer they get DB-specific.. Don't use adapters from the designer
   
2. The adapters ARE NOT database independent.
   
3. Databinding is database independent. But beware of drag-drop databinding automatically adding an adapter
   
   
   

My advice:

• Remove the adpaters from the dataset designer
• Rewrite your own repositories/adapters using a simple class with methods that get/fill tables. So you use them instead of the generated adapters. These classes can be DB-specific. So for instance a PersonRepositorySqlServer,PersonRepositoryMySql. Or perhaps you give the db-type with the constructor to reuse the SQL as much as possible..
  
• If you used adapter on your forms, remove them to. Hand-code the filling of the dataset

What I always do to answer the rest of the questions

• I use typed datasets but I just make the tables and not the adapters
• I Usualy code the databindings since sometimes the designer messes up but this is not necessary to be db independent
• I write my own repositories that use adapters to fill/get/update the datatables. However I code them by hand. Given a typed datatable it's rather easy to auomatically generate update/insert/delete/fill statements by the way..

Rewriting the adapters looks hard but is actually pretty doable.