[ 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.
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.
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_Update
ALTER PROC SP0811_Update
@data ntext = null,
@datadelete ntext = null
AS
DECLARE @hDoc int
-- updated and inserted records
if @data is not null
begin
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 @hDoc
end
-- deleted records
if @datadelete is not null
begin
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 @hDoc
end
The C# code is shown in Example 8-17.
Example 8-17. File: StoredProcedureMultipleRowsForm.cs
// Namespaces, variables, and constants
using 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 procedure.
SqlConnection conn = new SqlConnection(
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand( );
cmd.Connection = conn;
cmd.CommandText = STOREDPROCEDURE_NAME;
cmd.CommandType = CommandType.StoredProcedure;
// Inserted and updated records
if (ds.HasChanges(DataRowState.Added | DataRowState.Modified))
{
sb = new StringBuilder( );
sw = new StringWriter(sb);
ds.GetChanges(
DataRowState.Added | DataRowState.Modified).WriteXml(sw,
XmlWriteMode.WriteSchema);
cmd.Parameters.Add("@data", SqlDbType.NText);
cmd.Parameters["@data"].Value = sb.ToString( );
sw.Close( );
}
// Deleted records
if (ds.HasChanges(DataRowState.Deleted))
{
sb = new StringBuilder( );
sw = new StringWriter(sb);
// Get the DataSet containing the records deleted and call
// RejectChanges( ) so that the original version of those rows
// are available so that WriteXml( ) works.
DataSet dsChange = ds.GetChanges(DataRowState.Deleted);
dsChange.RejectChanges( );
dsChange.WriteXml(sw, XmlWriteMode.WriteSchema);
cmd.Parameters.Add("@datadelete", SqlDbType.NText);
cmd.Parameters["@datadelete"].Value = sb.ToString( );
sw.Close( );
}
// Execute the stored procedure.
conn.Open( );
cmd.ExecuteNonQuery( );
conn.Close( );
ds.AcceptChanges( );