If you have multiple tableadapter method calls that you want to run within a transaction, the following is the simplest way to do it.
Key points:
- Use TransactionScope from System.Transactions namespace
- Expose your connection property for the TableAdapter (if it is in a referenced project then you will need to change a the connection modifier in the DataSet designer to make it Public rather than Internal)
- To avoid having the transaction promoted to DTC (and thus degrading performance and requiring the DTC service is running on the Sql Server), make sure you use only one connection, and ensure that you have opened it manually before you call the tableadapter methods.
ProcessStageTableAdapter adp1 = new ProcessStageTableAdapter();
JobCategoryTableAdapter adp2 = new JobCategoryTableAdapter();
using (TransactionScope tsc = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
{
conn.Open();
adp1.Connection = conn;
adp2.Connection = conn;
adp1.Insert(
"New Data");
adp2.Insert("
New Data");
tsc.Complete();
}
}
This will work great with SQL Server 2005. For other options see Sahil Malik's post and for general info on transactions visit Florin Lazar's blog and the Msdn Transactions forum
HTH
Ian