none
how to start transaction from .net . RRS feed

  • Question

  • please tel me which way i should start trnsaction from .net.

    1)

    try{ connection.begintransaction();
    .
    .
    .
    .

    transaction.commit(); }

    catch{

    Transaction.rollback(); return false;

    }

    2)

    connection.begintransaction();
    try{
    .
    .
    .
    .

    transaction.commit(); }

    catch{

    Transaction.rollback(); return false;

    }

    3)
    connection.begintransaction();

    .
    .
    .
    .

    transaction.commit();

    yours sincerley


    Friday, June 14, 2013 10:28 AM

Answers

  • Of the options you listed, I think #1 is probably best. But, there's actually a 4th option, and that's to use TransactionScope, which is preferable in my opinion.

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted })){
     try
     {
      using (SqlConnection conn = new SqlConnection(MyConnectionString))
      {
          // now you don't need to use conn.BeginTransaction or any of those
          ...
          ...
          ...
          scope.Complete();
      }
     }
     catch (Exception ex)
     {
         // No need to do anything with the TransactionScope here in the catch.
      // The Transaction is rolled back if there isn't a scope.Complete() executed
     }
    }

    The above parameters used when creating a TransactionScope are necessary, because if they are not specfied (if you simply instantiate with "new TransactionScope()"), the default IsolationLevel is Serializable, which is the most restrictive level and will cause all kinds of deadlock problems with Sql Server!!

    You can make that a little easier by writing a static method in any Utility class you might have for that sort of thing. I call mine "Utils" and it's full of static methods I use for a lot of things. Anyway, I have a blog post, http://geek-goddess-bonnie.blogspot.com/2010/12/transactionscope-and-sqlserver.html, that shows more about this.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, June 15, 2013 4:10 PM