Update DataTable in transaction with XML and Stored Procedure

In my previous post, I discussed how to use TransactionScope to update DataTable objects in a transaction. The biggest shortcoming of using TransactionScope is that several security settings on the server need to be modified in order to make it work, and this may not be feasible for many companies because of the security concern.

Today, I will discuss how to use XML and stored procedure to update DataTable objects in transaction.

The basic idea is this:

Convert each DataTable object and its content into an XML string
Pass XML strings to a stored procedure on SQL Server
Use transaction in SQL to update the corresponding database tables

Now let’s start.

1. Get XML string from a DataTable object

Code Snippet
System.IO.StringWriter sr = new System.IO.StringWriter();
dt.WriteXml(sr, false);
string sXML = sr.ToString();

Use the same snippet to convert the rest DataTable objects into XML strings.

2. Create a stored procedure to implement SQL transaction

First, for each database table update, I created a store procedure with the corresponding XML string as parameter. The stored procedure will query the XML string, then insert/update the database table.

For SQL 2000, the stored procedure looks like this:

Code Snippet
CREATE PROCEDURE [dbo].[usp_InsertCustomer] 
    @sXML NTEXT
AS
    
BEGIN
 
    SET NOCOUNT ON;
    DECLARE @docHandle int
 
    exec sp_xml_preparedocument @docHandle OUTPUT, @sXML
    
    INSERT INTO Customers(CustomerId, FirstName, LastName, EmailAddress, Telephone)
        SELECT *
        FROM OPENXML (@docHandle, 'DocumentElement/Customers', 2)
        WITH (CustomerId uniqueidentifier, FirstName VARCHAR(200), LastName VARCHAR(200), 
            EmailAddress VARCHAR(200), Telephone VARCHAR(15))
    
    exec sp_xml_removedocument @docHandle
END

In SQL 2000, you need to use sp_xml_preparedocument and sp_xml_removedocument system stored procedures to query XML string. But in SQL 2005 and above, you don’t need them because they support XML data type, and you can directly query an XML object.

For SQL 2005 and above:

Code Snippet
CREATE PROCEDURE [dbo].[usp_InsertCustomer] 
    @sXML XML
AS
    
BEGIN
 
    SET NOCOUNT ON;
    
    INSERT INTO Customers(CustomerId, FirstName, LastName, EmailAddress, Telephone)
        SELECT 
            x.d.value('CustomerId', 'uniqueidentifier') As CustomerId,
            x.d.value('FirstName', 'VARCHAR(200)') AS FirstName,
            x.d.value('LastName', 'VARCHAR(200)') AS LastName,
            x.d.value('EmailAddress', 'VARCHAR(200)') AS EmailAddress,
            x.d.value('Telephone', 'VARCHAR(15)') AS Telephone
        FROM @sXML.nodes('//DocumentElement/Customers') x(d)    
END

Likewise, you can create a stored procedure for updating CustomerOrder database table with the XML string as its input parameter.

3. A main stored procedure with transaction

Code Snippet
CREATE PROCEDURE [dbo].[usp_InsertCustomerAndOrder] 
    @sCustomerXML NTEXT, 
    @sCustomerOrderXML NTEXT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @err int
    SET @err = 0
    
    BEGIN TRAN
        — 1. Insert Customer
        exec usp_InsertCustomer @sCustomerXML
        SET @err = @@ERROR
        
        IF @err <> 0
        BEGIN
            GOTO RollbackTransaction
        END
        
        — 2. Insert CustomerOrder
        exec usp_InsertCustomerOrder @sCustomerOrderXML
        SET @err = @@ERROR
        IF @err <> 0
        BEGIN
            GOTO RollbackTransaction
        END        
        
    COMMIT TRAN
    
    RollbackTransaction:
    IF @err <> 0
    BEGIN
        ROLLBACK TRAN
    END
    
END

If any of the two stored procedure inside fails, then @err will not be 0, and then transaction will roll back. If you are using SQL 2005 or above, you can change “NTEXT” data type to “XML” data type.

4. Put all together

In you application, you just need to pass the two XML strings (in this example) generated in step 1 to the store procedure “usp_InsertCustomerAndOrder”, and you are done.

Note, you can throw detailed error message for each execution step if fails so your application can catch the error and handle it accordingly.

Please let me know if you find this post helpful.

0 0 votes

Article Rating