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!

 



Shawn
Shawn Wildermuth
Author, Teacher, and Coach



My Courses

pluralsight
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular (updated for 2.0)
Using Visual Studio Code for ASP.NET Core Projects (new)
Implementing and Securing an API with ASP.NET Core
Building a Web App with ASP.NET Core, MVC6, EF Core and AngularJS
Building a Web App with ASP.NET5, MVC6, EF7, and AngularJS (Retired)
Best Practices in ASP.NET: Entities, Validation, and View Models
Webstorm Fundamentals
Front-End Web Development Quick Start
Lessons from Real World .NET Code Reviews
Node.js for .NET Developers

Application Name WilderBlog Environment Name Production
Application Ver 2.0.0.0 Runtime Framework .NETCoreApp,Version=v2.0
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 4.6.26020.03
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86