C#: Supporting multiple types of databases with Table Adapters

Question

Here's the scenario:

I want to be able to support both SQL Server CE and SQL Server 200x databases using a strongly-typed dataset.

The problem I'm getting is that it's compiling using a specific type of connection (either a SqlConnection or a SqlCeConnection) so I can't change the type at runtime to any IDbConnection object.

Am I asking too much from the Visual Studio-generated datasets? If not, what's the trick to getting it to work?

Answer 1

I gave up using designer-generated TableAdapters a long time ago. There's not easy way to control what they do, no easy way to change the connection string for all of them... and of course, no way at all of make them work with different ADO.NET provider, since the provider is hard-coded in the generated code. TableAdapters don't even inherit a common base class so you can write generic code (well, they do inherit from component, but that's pretty useless...)

Instead, I use my own homemade "TableAdapter" generator. It builds a DbDataAdapter from a SELECT query, using a DbCommandBuilder (actually, the SELECT query is not even necessary since it can be inferred from the DataTable's structure). For more complex DataTables which need specific logic, I make them implement a custom IDataAdapterProvider so that they can provide their own DbCommands.