ADO.NET Cookbook

8.11. Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Database

Problem

You need to update a SQL Server 2000 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.

The schema of table TBL0811 used in this solution is shown in Table 8-10.

Table 8-10. TBL0811 schema

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

Example 8-16 uses a single stored procedure:

SP0811_Update

Used to update the table TBL0811 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 records and all deleted records, respectively. These parameters are parsed using the system stored procedure sp_xml_preparedocument that returns a handle that is subsequently used to access the parsed XML document. OpenXML is used to update, insert, and delete the DataSet changes made to TBL0811. Finally, the system stored procedure sp_xml_removedocument is used to free the memory used by the parsed XML documents.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataSet containing the contents of the table TBL0811. The ColumnMapping for each column is set to MappingType.Attribute. The default view of the table is bound to the data grid on the form. ...