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
  1. System.IO.StringWriter sr = new System.IO.StringWriter();
  2. dt.WriteXml(sr, false);
  3. 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
  1. CREATE PROCEDURE [dbo].[usp_InsertCustomer]
  2.     @sXML NTEXT
  3. AS
  4.     
  5. BEGIN
  6.  
  7.     SET NOCOUNT ON;
  8.     DECLARE @docHandle int
  9.  
  10.     exec sp_xml_preparedocument @docHandle OUTPUT, @sXML
  11.     
  12.     INSERT INTO Customers(CustomerId, FirstName, LastName, EmailAddress, Telephone)
  13.         SELECT *
  14.         FROM OPENXML (@docHandle, 'DocumentElement/Customers', 2)
  15.         WITH (CustomerId uniqueidentifier, FirstName VARCHAR(200), LastName VARCHAR(200),
  16.             EmailAddress VARCHAR(200), Telephone VARCHAR(15))
  17.     
  18.     exec sp_xml_removedocument @docHandle
  19. 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
  1. CREATE PROCEDURE [dbo].[usp_InsertCustomer]
  2.     @sXML XML
  3. AS
  4.     
  5. BEGIN
  6.  
  7.     SET NOCOUNT ON;
  8.     
  9.     INSERT INTO Customers(CustomerId, FirstName, LastName, EmailAddress, Telephone)
  10.         SELECT
  11.             x.d.value('CustomerId', 'uniqueidentifier') As CustomerId,
  12.             x.d.value('FirstName', 'VARCHAR(200)') AS FirstName,
  13.             x.d.value('LastName', 'VARCHAR(200)') AS LastName,
  14.             x.d.value('EmailAddress', 'VARCHAR(200)') AS EmailAddress,
  15.             x.d.value('Telephone', 'VARCHAR(15)') AS Telephone
  16.         FROM @sXML.nodes('//DocumentElement/Customers') x(d)    
  17. 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
  1. CREATE PROCEDURE [dbo].[usp_InsertCustomerAndOrder]
  2.     @sCustomerXML NTEXT,
  3.     @sCustomerOrderXML NTEXT
  4. AS
  5. BEGIN
  6.     SET NOCOUNT ON;
  7.     
  8.     DECLARE @err int
  9.     SET @err = 0
  10.     
  11.     BEGIN TRAN
  12.         — 1. Insert Customer
  13.         exec usp_InsertCustomer @sCustomerXML
  14.         SET @err = @@ERROR
  15.         
  16.         IF @err <> 0
  17.         BEGIN
  18.             GOTO RollbackTransaction
  19.         END
  20.         
  21.         — 2. Insert CustomerOrder
  22.         exec usp_InsertCustomerOrder @sCustomerOrderXML
  23.         SET @err = @@ERROR
  24.         IF @err <> 0
  25.         BEGIN
  26.             GOTO RollbackTransaction
  27.         END        
  28.         
  29.     COMMIT TRAN
  30.     
  31.     RollbackTransaction:
  32.     IF @err <> 0
  33.     BEGIN
  34.         ROLLBACK TRAN
  35.     END
  36.     
  37. 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.

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Mahesh Nagar Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Mahesh Nagar
Guest
Mahesh Nagar

Very Very fine article . i am using this method since 2 years . but its very good presentation and so easy to understand
keep it up
thanks
Mahesh Nagar