TransactionScope nested, good practices

Topics: Data Access Application Block
Jul 21, 2010 at 4:40 PM
Edited Jul 21, 2010 at 4:41 PM
Hi, I'm using vs 2008, sql server 2005, and I want use TransactionScope, nested. Any issues using EntLib and TransactionScope: 1. How can I force Rollback ?? 2. I use 2 DbCommand objects. Is right ? or better use only DbCommand object ?? 3. Another good practices, please Thanks in advanced. using (var ts1 = new System.Transactions.TransactionScope()) { dal.SelectData(); dal.InsertOrUpdateData(); File.Move(...); using (var ts = new System.Transactions.TransactionScope()) { Database db = DatabaseFactory.CreateDatabase(); var nombrePA = ConsultasUtil.SQL_GrabarFichero + infoFichero.Aplicacion + "_" + infoFichero.Listado.Value; using (var cmd = db.GetStoredProcCommand(nombrePA)) { cmd.Parameters.Clear(); db.AddInParameter(cmd, "@idVolumen", DbType.Int32, infoFichero.Volumen); db.AddInParameter(cmd, "@id_Tipo_Doc", DbType.Int32, infoFichero.TipoDocumental); db.AddInParameter(cmd, "@idFichero", DbType.Int64, idFichero); db.AddInParameter(cmd, "@nombre", DbType.String, infoFichero.Ruta); regAfectados1 = db.ExecuteNonQuery(cmd); } nombrePA = ConsultasUtil.SQL_IndexarFicheroOGF + infoFichero.Aplicacion + "_" + infoFichero.Listado.Value; using (var cmd2 = db.GetStoredProcCommand(nombrePA)) { cmd2.Parameters.Clear(); db.AddInParameter(cmd2, "@idFichero", DbType.Int64, idFichero); db.AddInParameter(cmd2, "@idVolumen", DbType.Int32, infoFichero.Volumen); db.AddInParameter(cmd2, "@NumeroOperacion", DbType.String, indiceOp); db.AddInParameter(cmd2, "@NumeroExpediente", DbType.String, indiceExp); regAfectados1 = db.ExecuteNonQuery(cmd2); } ts.Complete(); } ts1.Complete(); }
Jul 22, 2010 at 3:29 AM

"Any issues using EntLib and TransactionScope?"

I tried to look in the Issue Tracker and this is the only relevant work item I found.  But you need not worry about this as it was already fixed in v4.1

"How can I force a Rollback?"

If you don't call the Complete method on the TransactionScope, database transactions within that transactionscope will automatically be rolled back.  Another way would be to call transactionScope.Dispose();

"I use 2 DbCommand objects. Is right ? or better use only DbCommand object?"

 Either approach is ok.  Personally, I prefer using two and enclosing them in using statements and using appropriate variable names for code readability.

Regarding on the code you posted, it's fine. But keep in mind that the File operation inside the transaction won't be really part of the transaction.  It will execute whether or not you Complete or rollback the transactionscope objects.

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.