The Monday Dialectics of the ADO.NET 2.0 Strongly-Typed DataSet

Good Stuff

• Research this Google™ search: http://www.google.com/search?q=DataSet+%22MSDN+Magazine%22. It looks like there are over 50,000 reasons to like the DataSet object in Visual Studio. But now that my Google™ honeymoon is over, let’s say 15,000 reasons.

• Check out “The Baker’s Dozen: 13 Productivity Tips for ADO.NET 2.0.”

• The DataSet-based data layer means (to me) that ‘back-end’ stored procedures that I have been writing for the last few years can be moved to strongly-typed DataSet definitions. The remaining stored procedures in the DBMS are forward-facing, production-speed things. This should make leaner and meaner code for the production side and more maintainable (but fatter) code on the administration side.

• Performance and more security should not be an issue for back-end maintenance. Maintainability over a large time should dominate here. This means that a bunch of SQL statements sitting in a DLL file instead of inside the DBMS should not be of too much concern. What is more cared for are those moments dragging of and dropping, cutting and pasting at DatSet design time.

• What is interesting to me is that there is a quasi-transactional thing going on with DataSet-based procedures: you can “party” on a DataSet and when you are done save it back to the database. By rejecting all DataSet changes or not calling the Update() method on the associated Table Adapter when any exception is thrown, something transactional is going on…### Bad Stuff

• Connection and SQL strings persist inline in the code. There should be the option to place these resources in Resource files. Another option is to make a separate code file that contains a single Dictionary<string,string> definition for all strings used by the strongly-typed DataSet (but I am sure that Steve Lasker, Program Manager of Visual Studio Data Designtime, would suggest that generics was not available for his team to take “full advantage”). Storing these strings in assemblies could be interpreted as a security risk—so I am surprised that security-panicked Microsoft has not addressed this for 2005. It is possible to conceal the code-generated data access code, what Steve Lasker calls “database intimates” in his Blog post “Splitting Typed DataSets from TableAdapters—Sharing Validation Code across the tiers.” His Blog post is 10 printed pages. My thinking demands that this option should come for free.

• No exploitation of the DbProviderFactory. I’m sure this will be in the next version of DataSet code generation. This is really a restatement of the previous gripe.

• Code-generated SQL strings contain ‘fully qualified’ references to database names ([mydatabase].dbo.[mytable]). This stands in the way of making strongly-typed DataSet objects that can be used on multiple databases of the same schema.

• The type of the connection used for the DataSet depends on the type used at design time. Visual Studio 2005 users will be influenced by whatever is in the Data Connections tree in the Server Explorer. For me this is usually .NET Framework Data Provider for OLE DB (because of legacy from Visual Studio .NET 2003) or .NET Framework Data Provider for SQL Server (the preferred connection type for Visual Studio 2005 connecting to SQL Server).

• Using the DataSet for DBMS ‘get’ and ‘set’ procedures generates code that features a protected CommandCollection member. By default all connection string information is buried ‘deeply’ in the DataSet. This means that the connection cannot be changed on the fly at runtime by default. It better for me to write my own data command code for this case.

• When you add a DataSet to a Visual Studio 2005 Project as a linked item, the namespace changes to the default namespace of the hosting project through the benefits of code generation. This is a slight but persistent annoyance. At least a dialog should pop up to ask us about making this change.

• For more check out “.NET 2.0—Strongly Typed Datasets—Things that suck!!” by Sahil Malik (June 2005).

rasx()