TransactionScope with TableAdapters promoting to DTC transaction - why?
Why does the following promote to DTC when using SQL Server 2005? All tableadapters are using the same connection so surely the transaction can stay lightweight?
using (SqlConnection conn =newSqlConnection(ConfigurationManager.ConnectionStrings["DACL.Properties.Settings.CatalogueConnectionString"].ConnectionString))
{
attributesTA.Connection = assetTA.Connection = attributeValueTA.Connection = tag2AssetTA.Connection = conn;
using (TransactionScope tc =newTransactionScope())
{
assetTA.Update(mainDataSet1.Asset);
attributesTA.Update(mainDataSet1.Attributes);
attributeValueTA.Update(mainDataSet1.AttributeValue);
tag2AssetTA.Update(mainDataSet1.Tag2Asset);
tc.Complete();
}
}
[2573 byte] By [
IanBla] at [2008-2-7]
Ian,
I'm not familiar with TableAdapters but I just did a quick research. It looks that TableAdapters will Open and Close the connection every time you make a method call on them, if the connection is not already opened. In this case, the lightweight TM will see more than one connection being opened and thus it will promote the transaction.
If that is true, I recommend changing your code to:
using (TransactionScope tc = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DACL.Properties.Settings.CatalogueConnectionString"].ConnectionString))
{
conn.Open();
attributesTA.Connection = assetTA.Connection = attributeValueTA.Connection = tag2AssetTA.Connection = conn;
assetTA.Update(mainDataSet1.Asset);
attributesTA.Update(mainDataSet1.Attributes);
attributeValueTA.Update(mainDataSet1.AttributeValue);
tag2AssetTA.Update(mainDataSet1.Tag2Asset);
tc.Complete();
}
Let me know if this works.
