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( );

    MessageBox.Show("Update completed.",
        "Multiple Row Update/Insert Stored Procedure",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

Discussion

OpenXML provides a result set view of an XML document allowing you to use the XML document in a T-SQL statement in the same way a result set provider such as a table or view is used.

The simple form of the OpenXML command is:

OPENXML(int iDoc, nvarchar rowPattern)
WITH (SchemaDeclaration)

The two input arguments are:

iDoc

The document handle of the internal representation of an XML document created by using the system stored procedure sp_xml_preparedocument

rowPattern

The XPath query used to select the nodes in the XML document to be processed

The argument for the WITH clause is:

SchemaDeclaration

The format of the result set. If not supplied, the results are returned in an edge table format representing the XML document structure in a single table.

The system stored procedure sp_xml_preparedocument reads XML as input text using the MSXML parser and returns a handle that you can use to access the internal representation of the XML document. The handle is valid for the duration of the connection to the SQL Server or until it is reset. The handle can be invalidated and the associated memory freed by calling the system stored procedure sp_xml_removedocument. The syntax of the stored procedure is:

sp_xml_preparedocument hDoc OUTPUT, [xmlText], [xpathNamespaces]

The arguments are:

hDoc

An integer parameter that returns a handle to the internal representation of the XML document.

xmlText

A text parameter that specifies the original XML document. The default value is null which results in the return of a handle to an internal representation to an empty XML document.

xpathNamespaces

A text parameter that specifies the namespace declarations used in row and column XPath expressions in OpenXML. The default value is:

<root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop">

The system stored procedure sp_xml_removedocument removes the internal representation of an XML document specified by a document handle obtained from the system stored procedure sp_xml_preparedocument and invalidates the handle. The syntax of the stored procedure is:

sp_xml_removedocument hDoc

The argument is:

hDoc

An integer parameter that returns a handle to the internal representation of the XML document.

For more information about the OpenXML command and the system stored procedures sp_xml_preparedocument and sp_xml_removedocuemnt, see Microsoft SQL Server Books Online.