locked
Using transaction while saving data to database in .net RRS feed

  • Question

  • User-73514677 posted

    Hi,

    How to use transaction and rollback and provide an error if some exception happened.

    I have tried this:

    string conn = string.Empty;
    conn = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
    
     SqlTransaction tn;
     SqlConnection sConn;          
     SqlCommand cmd;
     sConn = new SqlConnection(conn);
    
     try
    {
     sConn.Open();
     tn = sConn.BeginTransaction();
     cmd = new SqlCommand("StoredProc", sConn, tn);
      try
       {
          SqlParameter sp1 = new SqlParameter();
    cmd.Parameters.Add("@p_TestId", SqlDbType.BigInt).Direction = ParameterDirection.Input;
    cmd.Parameters[0].Value = testID;
                       
    cmd.CommandType = CommandType.StoredProcedure;
                                        
    cmd.ExecuteNonQuery();
    tn.Commit();
    
    }
      catch (SqlException ex)
      {
           tn.Rollback();
      }
       
     catch (Exception ex)
      {
         throw ex;
      }
      finally
     {
        cmd.Dispose();
        tn.Dispose();
      }

    Is this correct? Is there any other way which can be used.

    How to rollback and throw it.

    Thank you

    Saturday, October 1, 2011 8:55 AM

Answers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 1, 2011 9:02 AM
  • User3866881 posted

    Is this correct?

    Hello, I think what you did is right.

    Is there any other way which can be used.

    You can also simplify your application coding by using TranscationScope in the using…… block

    1) Please refer the namespace by using this: using System.Transactions.

    2) Then use this:

     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOption))
    {
       //Do what you want here...
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 2, 2011 8:04 PM

All replies

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 1, 2011 9:02 AM
  • User3866881 posted

    Is this correct?

    Hello, I think what you did is right.

    Is there any other way which can be used.

    You can also simplify your application coding by using TranscationScope in the using…… block

    1) Please refer the namespace by using this: using System.Transactions.

    2) Then use this:

     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOption))
    {
       //Do what you want here...
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 2, 2011 8:04 PM