tailieunhanh - Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Database

[ Team LiB ] Recipe 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. | Team LiB Recipe 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 Fieldl 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 Sets up the sample by creating a DataSet containing the contents of the table TBL0811. The ColumnMapping for each column is set to . The default view of the table is bound to the data grid on the form. Update Writes the XML representation of the added and changed records in the DataSet to the stored procedure NText parameter @data and the XML representation of deleted records in the DataSet to the stored procedure NText parameter @datadelete. The stored procedure SP0811_Update is called to update the database with the batched changes. Example 8-16. Stored procedure SP0811_Update ALTER PROC SP0811_Update @data ntext null @datadelete ntext null AS DECLARE @hDoc int -- updated