I have just read an article Managing Transactions using TransactionScope that provides an example of using TableAdapter and TransactionScope together:
public static int AddDepartmentWithEmployees(Department dept)The code demos using TableAdapter and TransactionScope to insert a Department record and a collection of Employee records in that department into database, presuming SQL Server 2005 in this case to take advantage of lightweight transaction.
{
int res = 0;
DepartmentAdapter deptAdapter = new DepartmentAdapter();
EmployeeAdapter empAdapter = new EmployeeAdapter();
using (TransactionScope txScope = new TransactionScope())
{
res += deptAdapter.Insert(dept.DepartmentName);
//Custom method made to return Department ID after inserting the department "Identity Column"
dept.DepartmentID = deptAdapter.GetInsertReturnValue();
foreach(Employee emp in dept.Employees)
{
emp.EmployeeDeptID = dept.DepartmentID;
res += empAdapter.Insert(emp.EmployeeName, emp.EmployeeDeptID);
}
txScope.Complete();
}
return res;
}
Looks really great and simple. But there's an issue of such implementation: Distributed Transaction Coordinator (DTC) is promoted at run time to complete this transaction. A run time error page will show up if DTC is not properly setup in the server; even with DTC configured, overhead of DTC would introduce big performance issues.
The reason is that each TableAdapter maintains its own database connection, and multiple operations with the same TableAdapter would lead to multiple database connections. A lightweight transaction is used in TransactionScope by default with SQL Server 2005, but DTC is promoted if multiple connections exist inside the same TransactionScope, which is the case in the above example.
We all know the cost of DTC is too expensive. Thus it's recommended to avoid using TableAdapters with TransactionScope, or avoid letting TableAdapters to manage the connections. You should manually and explicitly maintain all connections that are involved in a transaction scope if you have to use them together.
