SqlDataSource Issues...

Url: http://wilderminds.blob.core.windows.net/downloads/datasourcesgone…

I’ve blogged before about issues with the SqlDataSource.  I’ve crufted up an example of the problems that can be downloaded here (with usual caveat of changing the connection string in the web.config to point to a DB with the Northwind database).

There are three basic issues:

• The problem is that if a database allows nulls (and a specific row has an NULL) then the automagically created queries in the SqlDataSource will affect zero rows. So data loss can happen without alerting the user.
• The other related issue is that if an Update fires and affects zero rows it is swallowed unless the user handles the DataSource.Selected event.
• Lastly, if your query includes SQL Server Timestamp fields, the SqlDataSource designer doesn’t know how to handle it correctly (either by inserting it into the Keys field of the control, or that it isn’t a varbinary field).

I want the ASP.NET team to use the SqlCommandBuilder so that we have a common SQL Generationh platform so problems fixed in one place don’t re-rear their heads.
 
In general you can get around these issues by hand-crafting your own SQL (or preferrably using Stored Procs) and handing the Updated and Deleted events in your DataSources.  The problem with this is that since SqlDataSources seem to be a good RAD-type tool, then they should work in most databases ‘out of the box’ and they don’t.

Any comments on the code or this issue are certainly welcomed!