Limitations of Typed DataSets in Visual Studio 2005

The teams responsible for the new features in ADO.NET 2.0 and the typed DataSet designer included in Visual Studio 2005 deserve a good vacation. They have put a big effort on making typed DataSets a powerful foundation for our data access layers.

If you choose to go the typed DataSet way now, you will be able to create most of your data access layer without writing a single line of code. You just visually pick the tables or stored procedures, define relations, create SQL queries with parameters and all the code for your DataTables and DataTableAdapers is created behind the scenes. It all gets stored in a XML schema defition file, and a tool automatically generate a set of classes from it. The generated code itself is very simple, but in my opition is efficient, and it works. Moreover, if your data schema changes, you don't have to touch a single line of code.

Coupling typed DataSets with binding objects like ObjectDataSource and bindable controls like the new GridView, makes up a solutions that reminds me of Microsoft Access for its simplicity. I think this is the first time I have used a RAD tool that supports multi-tier development.

I have to say, I would love to use typed DataSets for all my future development.

Unfortunatelly, while normal DataSets are database engine agnostic, they forgot to teach the typed DataSet code generator (MSDataSetGenerator) about the new DBProviderFactory included in ADO.NET 2.0.

So, once you have choosen your data source in design time, the code generator will produce a DataSet that is database engine specific. You can still choose the OLEDB provider, but DBProviderFactory makes a much better model for ADO.NET, because it allows you to use a native managed provider in runtime, if it is available.

This is all a pity, because everything else in the DataSet is so well though. For instance, table columns and query parameters are all of database independent types. They even use Nullable for variables that can be null. The new ConnectionString setting stores a ProviderName attribute anyway. And it only takes a couple of lines of code to create a connection or command from a DBProviderFactory. So why did they do it this way?

I don't see technical reasons (maybe I am blind). So, I am suspicious that someone though: "Ok, let's make this database specific so we sell more copies of SQL Server 2005". OK, maybe Microsoft.NET makes SQL Server 2005 a more attractive SQL Server, but this doesn't work the other way.

For instance, in the software development company I work for, we do database engine independent software, and we are good at it. We have had the same factory pattern for some time.

Even if we love SQL Server, database independece has been always one of our top requirements. If a new customer comes to us, we will offer SQL Server to them, but in many cases, they have already a big investment in a databaes engine, and this can be any engine they like.

Since source code for MSDataSetGenerator is not available, and since it is not practical to touch ourselves the generated code, we need Microsoft to tweak this for good.

It is very late for this, but if you care, please, vote for this suggestion on MSDN Feedback.