C # sqltransaction--Transaction detailed _c# tutorial

Fundamentals of transaction Processing

A transaction executes a series of operations as a unit, either successfully or unsuccessfully, rolling back to the original state. In transactional terminology, a transaction is either committed or aborted. To commit a transaction, all participants must ensure that any changes to the data are permanent. Changes must be persistent regardless of system crashes or other unforeseen events. As long as one participant is unable to make this guarantee, the entire transaction fails. All data changes within the transaction scope are rolled back to a specific set point.

Transactions tie together multiple operations so that the consistency of the two connected operations and the integrity of the data are guaranteed. For a simple example: The company's employee information management system, now to input data, employee information systems assume only department, employee information two tables, including the Employee Information table has identified the Department of the field. When you enter information, first you have to input department information, and then input employee information. Specific implementation code:

 private static void Executesqltransaction (string connectionString) {using

      (SqlConnection connection = new SqlConnection (connectionString)) {connection.

        Open (); SqlCommand Command = connection.

        CreateCommand (); 

        SqlTransaction transaction; Transaction = connection.

        BeginTransaction ("Sampletransaction"); Command.

        Connection = Connection; Command.

        Transaction = Transaction; try {command.

          CommandText = "Insert into Department (ID, Name) VALUES (1, ' engineering Department ')"; Command.

          ExecuteNonQuery (); Command.

          CommandText = "Insert into Users (ID, Name,departmentid) VALUES (1, ' xyz ', 1)"; Command.

          ExecuteNonQuery (); Transaction.

        Commit (); The catch (Exception ex) {transaction.

        Rollback (); }

      }

    }

The misunderstanding of the affairs

Business has a lot of advantages "principle has been elaborated," because of its high requirements, so pay attention to business can not be abused, if the use of bad will cause a lot of trouble.

A transaction has a beginning and an end, and they specify the bounds of the transaction, which can span processes and computers within its bounds. All resources within the transaction boundary are involved in the same transaction. To maintain consistency among resources within transaction boundaries, transactions must have ACID properties, i.e. atomicity, consistency, isolation, and persistence. This is the authoritative note for MSDN.

Perhaps for the general small logic, small data transaction application is very efficient and reliable. But if the volume of data is large, the operations that are set up in a single transaction are numerous and complex, and the fatal wound of the transaction is exposed. When a transaction is in progress, the atomicity, consistency, isolation and persistence of the boundary resources must be ensured.

I have designed a test case to test the utilization of resources in the execution of a transaction. The test results are surprising: When transactions are executed, the data tables involved in the exclusive transaction result in the function of the other operation Thesaurus, which jumping the "Get Data connection Timeout" warning when the wait time is too long.

Specific test Cases:
Transaction

public class Testtransaction {///<summary>///Insert New user///</summary>///<param name= "t 
    Ran "></param>///<returns></returns> private static bool Insertintouser (SqlTransaction Tran)
                      {String strSQL = @ "INSERT into [T_user] ([f_name]) VALUES
      (@F_Name) ";
      Sqlparameter[] Params ={new SqlParameter ("@F_Name", SqlDbType.VarChar, 20)}; Params[0].
      Value= "Test1001";
      int count= sqlhelper.executenonquery (Strsql,params,tran);
      if (Count > 0) {return true;
      else {return false; }///<summary>///insert title///</summary>///<returns></returns> Priva
                    Te static bool Insertintotitle (SqlTransaction tran) {string strSQL = @ "INSERT into [T_user_title] ([F_titlename], [F_remark], [F_status], [F_edittime]) VALUES (@F_TitleName,
      @F_Remark, @F_Status, @F_EditTime) "; Sqlparameter[] Params = {new SqlParameter ("@F_TitleName", SqlDbType.VarChar,), New SqlParameter ("@F_R Emark ", SqlDbType.VarChar, New SqlParameter (" @F_Status ", SqlDbType.Int, 1), New SqlParameter (" @F_E
      Dittime ", Sqldbtype.datetime, 8)}; Params[0].
      Value = "TestUser1001"; PARAMS[1].
      Value = "This is the first Test"; PARAMS[2].
      Value = 1; PARAMS[3].
      Value = DateTime.Now;
      int count = Sqlhelper.executenonquery (Strsql,params,tran);
      if (Count > 0) {return true;
      else {return false;
    }///<summary>///detection transaction///</summary>///<returns></returns>
      public static bool Insertwithtran () {bool success = false; STring connectionstring=system.configuration.configurationsettings.appsettings["Sqlconstr"].
      ToString (); using (SqlConnection con = new SqlConnection (connectionString)) {con.
        Open (); SqlTransaction Tran = con.
        BeginTransaction ();
          try {if (Tran = = null) {throw new Exception ("Transaction is null"); } if (Insertintouser (Tran)) {if (Insertintotitle (Tran)) {Tran.
              Commit ();
            Success = true; Catch {Tran}}.
          Rollback ();
        Success = false; finally {Tran.
          Dispose (); Con.
        Close ();

    } return success;

 }
  }

Button

 protected void Button1_Click (object sender, EventArgs e)
  {
    bool success = Testtransaction.insertwithtran ();
    if (success)
    {
      Bmc.CLUtility.ShowMessage (this. Page, "Insert succeeded");      
    }
    else
    {
      Bmc.CLUtility.ShowMessage (this. Page, insert failed);
    }
  

Test steps

<1> Running Programs

<2> will run the address, sent to colleagues in the same network segment, through appropriate modifications can also see the program you run

<3> Both click on the button and query the database to see if the transaction executes correctly.

<4> creates a breakpoint in the middle of a transaction, the host clicks the button, and interrupts execution for some time at the breakpoint

<5> then you connect to the database, query the table data separately, and find that the query operation cannot be performed.

<6> Click the button on the colleague machine, query the Windows log, and find some warnings. This proves that, during the execution of a transaction, exclusive resources

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.