We all know that if you need to insert/update data in several database tables that have relationship with each other, then you will need to make sure the insert/update process is completed in a transaction to ensure the data integrity. With stored procedure, it is not hard to perform transaction, but what if you are using DataTable?
In this post, I will show how to use TransactionScope to implement transaction for DataTable update.
Suppose we have two tables: Customers and Orders, it is very easy to use TransactionScope (I will skip the portion of populating the DataTables) in your code (you need System.Transactions namespace):
- using (TransactionScope scope = new TransactionScope())
- {
- dtCustomer.Update();
- dtOrders.Update();
- scope.Complete();
- }
If everything is fine, then scope.Complete() will update the two tables, and if any exception occurred, the transaction will rollback.
However, if your SQL Server is on a different server from the one hosting your application, then you may get this error when using TransactionScope:
Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.
It is because MSDTC is disabled by default. Your server admin can easily enable MSDTC by following the steps below:
1. Go to Administrative Tools from Control Panel
2. Click Component Services, then expand Component Services, and right-click My Computer and choose Properties
3. Go to MSDTC tab, click Security Configuration, check Network DTC Service and check the following options:
- Network DTC Access
- Allow Remote Clients
- Allow Inbound
- Allow Outbound
- Enable TIP Transactions
4. If prompted, reboot the server
If your application runs on the same sever as SQL Server does, you will not see the error above, but you may encounter a different error if it is a Windows Server 2003:
error 0x8004D025 (XACT_E_PARTNER_NETWORK_TX_DISABLED)
It is caused by the fact that by default DTC access is disabled on the Windows Server 2003 products. To enable DTC access on Windows Server 2003, follow the steps described in this Microsoft Support article: http://support.microsoft.com/kb/817064
It is clear that it is easy to use TransactionScope to protect data integrity while updating database tables, but it involves server security changes. Your company may not allow DTC access on server, if it is the case, you cannot use TransactionScope and will have to pursue other options to implement transaction for DataTable update.
In my next post, I will show how to implement DataTable transaction with XML. So stay tuned.
Great, thanks for this