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

Wilder Minds Training
Vue.js by Example
Bootstrap 4 by Example
Intro to Font Awesome 5 (Free Course)
Pluralsight
Designing RESTful Web APIs (new)
Building an API with ASP.NET Web API
Building an API with ASP.NET Core
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular
Less: Getting Started

Application Name WilderBlog Environment Name Production
Application Ver v4.0.30319 Runtime Framework x86
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 3.0.0
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86