CODEWITHSUNDEEP
c#databasestrongly-typed-dataset
Joshua Tacoma
Joshua Tacoma

Reputation: 407

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

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:

Here's what I've come up with:

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

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?

Upvotes: 2

Views: 3773

Answers (2)

Jacob Proffitt
Jacob Proffitt

Reputation: 12768

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).

Upvotes: 1

Chris Marisic
Chris Marisic

Reputation: 33108

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.

Upvotes: 1

Related Questions