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

[ Team LiB ] Recipe 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.
Nội dung trích xuất từ tài liệu:
Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Database[ Team LiB ]Recipe 8.11 Using a Single Stored Procedure to Update Multiple Changes to a SQLServer DatabaseProblemYou need to update a SQL Server 2000 database with changes to multiple rows in aDataSet by executing a single stored procedure.SolutionUse 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 NoField1 nvarchar 50 YesField2 nvarchar 50 YesExample 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.Update Button.Click 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_UpdateALTER PROC SP0811_Update @data ntext = null, @datadelete ntext = nullASDECLARE @hDoc int-- updated and inserted recordsif @data is not nullbegin EXEC sp_xml_preparedocument @hDoc OUTPUT, @data UPDATE TBL0811 SET TBL0811.Field1 = XmlTBL0811.Field1, TBL0811.Field2 = XmlTBL0811.Field2 FROM OPENXML(@hDoc, NewDataSet/TBL0811) WITH ( Id Integer, Field1 nvarchar(50), Field2 nvarchar(50) ) XmlTBL0811 WHERE TBL0811.Id = XmlTBL0811.Id INSERT INTO TBL0811 SELECT Id, Field1, Field2 FROM OPENXML(@hdoc, NewDataSet/TBL0811) WITH ( Id Integer, Field1 nvarchar(50), Field2 nvarchar(50) ) XmlTBL0811 WHERE XmlTBL0811.Id NOT IN (SELECT Id from TBL0811) EXEC sp_xml_removedocument @hDocend-- deleted recordsif @datadelete is not nullbegin EXEC sp_xml_preparedocument @hDoc OUTPUT, @datadelete DELETE TBL0811 FROM TBL0811 INNER JOIN OPENXML(@hDoc, NewDataSet/TBL0811) WITH ( Id Integer, Field1 nvarchar(50), Field2 nvarchar(50) ) XmlTBL0811 ON TBL0811.Id = XmlTBL0811.Id EXEC sp_xml_removedocument @hDocendThe C# code is shown in Example 8-17.Example 8-17. File: StoredProcedureMultipleRowsForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Windows.Forms;using System.Text;using System.IO;using System.Data;using System.Data.SqlClient;private DataSet ds;private const String TABLENAME = TBL0811;private const String STOREDPROCEDURE_NAME = SP0811_Update;// . . .private void StoredProcedureMultipleRowsForm_Load(object sender, System.EventArgs e){ ds = new DataSet( ); // Create the DataAdapter. SqlDataAdapter da = new SqlDataAdapter(SELECT * FROM + TABLENAME, ConfigurationSettings.AppSettings[Sql_ConnectString]); // Load the schema and data for the table. da.FillSchema(ds, SchemaType.Source, TABLENAME); da.Fill(ds, TABLENAME); // Columns in XML representation of data as attributes foreach(DataColumn col in ds.Tables[TABLENAME].Columns) col.ColumnMapping = MappingType.Attribute; // This technique supports only update and insert; turn off delete // records in the default view. ds.Tables[TABLENAME].DefaultView.AllowDelete = false; // Bind the default view of the table to the grid. dataGrid.DataSource = ds.Tables[TABLENAME].DefaultView;}private void updateButton_Click(object sender, System.EventArgs e){ StringBuilder sb; StringWriter sw; // Create a connection and command for the update stored procedur ...