C# – Strongly Typed DataSet with a set of TableAdapters per database provider

cdatabasestrongly-typed-dataset

I want a strongly-typed DataSet along with designer TableAdapters, but Visual Studio's DataSet designer generates provider-specific (e.g. SQL Server vs. MySql) code and I don't want to commit to just one provider. An ORM would help, but:

  • Entity Framework is 3.5 only and doesn't play nice with DataSets, and
  • NHibernate doesn't support SQLite.

Here's what I've come up with:

"DataSets.Masters" contains a completely designed DataSet bound to some particular provider (e.g. SqlClient), including:

  • a CustomTableAdapter component, subclassed by each designer TableAdapter,
  • an ITableAdapterManager interface, implemented by designer's TableAdapterManager for hierarchical updates.

Everything except the DataSets.MyDataSetTableAdapters namespace is copied into the "DataSets" project, where all the TableAdapter code (along with xs:annotation) is removed.

The DataSets.MyDataSetTableAdapters namespace, along with MyDataSet.xsd etc., is copied and customized into each of "DataSets.SqlClient", "DataSets.SQLite", etc. each of which references the "DataSets" assembly.

Now I just have to choose the right assembly to load my ITableAdapterManager implementation from, based on any given connection string. When the table schema changes, I modify the Masters assembly, copy code to the production assemblies, and run a few tests.

So my question: am I making this too difficult? DataSets are so standard, and the need to support multiple database engines via the data access layer is so common, is there a method that doesn't involve copy, paste, and search & replace? What do you do?

Best Answer

It might be easier to simply ignore the autogenerated TableAdapter commands and use the ADO.Net data access factory objects when it's time for your CRUD operations. That way you can use DbProviderFactory.CreateCommandBuilder to correctly format the parameters in the CRUD operations. Note that this assumes that you aren't doing any tricky property mapping and your schema will remain consistent across data providers.

An aditional option if you use this technique is to create a class that you can enter as the BaseClass property on your TableAdapters. Add an "init"-type method that overrides the connection and the insert, delete, select, and update commands with ones from the factory (based on the auto-generated select command—which should be compatible across most providers).

Related Topic