Monday, January 09, 2012

Manage multiple updates to Data using C#, TSQL and OPENXML in SQL Server

I'd like to share this little technique I've been using for quite a while, especially to receive feedback and validation on whether or not this is a good idea!

I've used this technique with great success in the past in production systems which require a a "grid" of check-boxes and a single "update" button. This kind of thing usually occurs when you have any X can be related to any Y in a many to many kind of relationship. It comes in especially handy when mapping Users to Roles or that kind of thing. Though it can easily be adapted to manage multiple updates of anything in ONE database hit.

Traditionally this kind of code ends up hitting the database either once per cell in the grid or once per row in the grid because SQL Server doesn't allow arrays of things to be sent to a stored procedure. Database hits are expensive and generally less is better.

My technique leverage's SQL Servers ability to access XML data using the OPENXML keyword. When the grid of check-boxes is submitted we gather together the form collection elements (those check-boxes that have been ticked) and create an XML snippet of the results. This is then passed to SQL Server as a single input representing a set of information. OPENXML is then used inside the stored procedure to convert the XML into a temporary table which is then interrogated and used to update the relationship information that the check box represents.

Below is a link to a visual studio 2010 project that you can download , it contains all the source code for the project please feel free to download and try it for yourself - if you find it useful or have an idea of how it could be improved please leave a comment at the bottom of this page.

I have used MVC 3 and SQL Server as a simple container for the project but the technique is equally valid for any type of project Classic ASP, ASP.NET Web-forms included, so long as the RDBMS supports XML the technique will be valid.

I will refrain from a long explanation here as the code is simply one model, controller and view and the database contains just three tables and two stored procedures. If you'd like some more detail please leave a comment at the bottom of the page and Ill update as soon as possible. If I get a lot of feedback then I will flesh out the method some more in a series of posts.

I do hope you find this useful and would love to hear from you!

Project files can be downloaded here  and unpacked using   7zip

Post a Comment