Thursday, September 12, 2013

MDTC Transactionscope vs SqlTransaction

Microsoft distributed Transaction Coordinator is inbuilt transaction handling engine where it take care of integrity and execution of transaction scope. Now if we have same database operation then handling transaction integrity within database is the best option but if the transaction and business operation span across multiple database and data source then Transaction scope of MDTC is the best option to do so.

SQLTransaction ADO.net is for database Alternative can be done smartly in stored procedures itself with Begin Trans, Commit and rollback

TransactionScope is Operating system based.
http://codingcramp.blogspot.se/2009/06/how-to-setup-and-use-transactionscope.html

SQL Transaction Example
public void DoWorkSqlTransactions()
{
using (SqlConnection con1 = new SqlConnection("my connection string"))
using (SqlConnection con2 = new SqlConnection("my second connection string"))
{
try
{
con1.Open();
con2.Open();
SqlTransaction tran1 = con1.BeginTransaction();
SqlTransaction tran2 = con2.BeginTransaction();

try
{
SqlCommand cmd1 = new SqlCommand("update ...", con1, tran1);
cmd1.ExecuteNonQuery();

// Don't want select in transaction
cmd1 = new SqlCommand("select ...", con1);

SqlCommand cmd2 = new SqlCommand("insert ...", con2, tran2);
cmd2.ExecuteNonQuery();
tran1.Save("savepoint");
tran2.Save("savepoint");
tran1.Commit();
tran2.Commit();
}
catch (Exception)
{
tran1.Rollback();
tran2.Rollback();
}
}
catch (Exception)
{
// error handling for connection failure
}
finally
{
con1.Close();
con2.Close();
}
}
}
Transaction Scope
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
{}
Just sorted.
Post a Comment