Suggestion for ADO.NET 2.0: better SqlCommandBuilder support
I've long been a fan of the concept of the .NET Framework's SqlCommandBuilder class in theory, if not fully in practice. I appreciate the foundation upon which it was designed - to automatically generate potentially complex SQL code for CRUD operations, and when used with the SqlDataAdapter.Update() method, making for very simple modification of database-born content - being well-conceived, although (not unexpectedly) many in the community aren't as liberal in their support of it.
A project I worked on today had several database tables use several JOINs, which instantly led me to discount the opportunity to use a SqlCommandBuilder, opting for manually building a dynamic SQL string and use SqlParameters. I tried to trick the Framework into supporting an UPDATE operation by creating a View of data originating in 5 different tables on the DB. No dice - I still got the "dynamic SQL generated cannot come from multiple base tables" error. Darn.
I honestly think the concept is cool, but in my own development is rarely implemented, as the chance by which I actually query a single table with "SELECT * FROM Table" is rare-to-nil.
That having been said, I think a couple of changes would be great to make for the next version of ADO.NET (I admittedly haven't kept up as well as I should with such features):
- More SqlCommandBuilder constructor overloads - supporting selectable parameters so that the SQL generated by the object itself can contain a selectable number of fields, rather than having the long, drawnout UPDATE statements that test the nullability state of each and every field in a recordset, improving performance. Perhaps implementing a signature like SqlCommandBuilder(SqlParameter[] sql).
- Support JOINed tablular data - I know this is a big one, but I think it would be cool to support even simple multi-table data. As evident above, quite even a simple JOIN kills any use for a SqlCommandBuilder for me. And I know I'm not alone.
- Be able to support JOINed tabular data derived from a database-side View - if the Framework is smart enough to recognize that a View utilizes 2 or more base tables, then why not be able to bake-in a feature that could provide UPDATE capability to those tables. At least for the more simple of JOIN operations.
In all, I'd like to see the SqlCommandBuilder be a much more reliable part of .NET than just the obligatory lesson learned in what can be done - and then back it up.