Monday, January 09, 2012
Manage multiple updates to Data using C#, TSQL and OPENXML in SQL Server
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
Subscribe to:
Post Comments (Atom)
Posts
4 comments:
I've used a similar technique before, but instead of using a temporary/variable table I've updated data directly by using XQuery instead of OPENXML or queried the XML from within a WITH statement if I need to join the data with other database tables.
If you get chance I'd love to see that in action - perhaps update/add to the example and post me back I'll post it to the blog - giving you full credit of course!
Below are some very simple examples of inserting data using XQuery. Using the WITH statement can be useful/tidier when you want to join the data from the XML to an existing table.
-- insert example
WITH data AS ( SELECT x.y.value('./id', 'int') AS identifier, x.y.value('./value', 'int') AS [value] FROM @xml.nodes('node') AS x(y))INSERT INTO ATable(ID, [Value], [AnotherValue])SELECT x.identifier, x.[Value], a.AnotherValueFROM data x JOIN AnotherTable a ON x.identifier = a.ID-- another insert exampleINSERT INTO ATable(ID, [Value])SELECT x.y.value('./id', 'int'), x.y.value('./value', 'int')FROM @xml.nodes('node') AS x(y)-- update exampleWITH data AS ( SELECT x.y.value('./id', 'int') AS identifier, x.y.value('./value', 'int') AS [Value] FROM @xml.nodes('node') AS x(y))UPDATE a SET a.[value] = x.[value]FROM Data x JOIN ATable a ON x.identifier = a.ID
Bah! I'll send you a copy of the updated script.
Post a Comment