O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required