Strongly Typed DataSet with a set of TableAdapters per database provider?

Question

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?

Answer 1

NHibernate does support SQLite http://www.hibernate.org/361.html.

I recommend using NHibernate combined with Fluent NHibernate. Fluent NHibernate is a library that allows you to use NHibernate without needing to deal with any xml yourself which in my opinion is NHibernate's greatest drawback.

Also Fluent NHibernate supports an auto persistence model that if your domain objects are close to your database schema you can automap your entire business domain without writing mapping code for every single object. The further your business objects differ from your database the more complex it becomes to use the automapping features of Fluent NHibernate and it's worth using static mapping.