ADO.NET 3.5 Cookbook, 2nd Edition

9.12. Using OpenXML to Update Multiple Changes to SQL Server

Problem

You need to update a SQL Server database with changes to multiple rows in a DataSet by executing a single stored procedure.

Solution

Use OpenXML with an XMLdocument representing a DataSet of the changes made.

Example 9-20 shows the T-SQL statement that creates the table UpdateUsingOpenXml used in this solution.

Example 9-20. T-SQL statement: Create table UpdateUsingOpenXml

USE AdoDotNet35Cookbook
GO
CREATE TABLE UpdateUsingOpenXml(
    Id int NOT NULL PRIMARY KEY,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL)

The SQL statement creates the table UpdateUsingOpenXml with the schema shown in Table 9-11.

Table 9-11. UpdateUsingOpenXml schema

Column name	Data type	Length	Allow nulls?
Id	int	4	No
Field1	nvarchar	50	Yes
Field2	nvarchar	50	Yes

The T-SQL batch in Example 9-21 creates records in the table UpdateUsingOpenXml required by the solution.

Example 9-21. T-SQL batch to create records in table UpdateUsingOpenXml

USE AdoDotNet35Cookbook
GO

INSERT INTO UpdateUsingOpenXml VALUES (1, 'Field1.1', 'Field2.1');
INSERT INTO UpdateUsingOpenXml VALUES (2, 'Field1.2', 'Field2.2');
INSERT INTO UpdateUsingOpenXml VALUES (3, 'Field1.3', 'Field2.3');

The solution uses a single stored procedure named SycnUpdateUsingOpenXml that updates the table UpdateUsingOpenXml with the changes made to the DataSet passed in as an NText input parameter @data. The parameters @data and @datadeleted contain an XML representation of a DataSet containing all updated and added ...