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
- 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:
- 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:
- 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
- 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.