C # code enables transaction lock transaction for a series of commit rollback operations

First, preface

Because a lot of people generally carry out a series of related database operations are in the stored procedure, and in the stored procedure is also very simple to write a lock, in this article mainly introduces the C # background code with a series of locks for transaction operations, I set up a simple WinForm program, Then do a transaction: Modify the user information of the specified ID, and then add a user information, the operation succeeds in committing the transaction, the program exception and the database execution is unsuccessful must roll back the transaction!

Second, the transaction of the stored procedure

Write relatively simple, I think we all know how to use, do not understand can ask me.

Iii. transactions for C # code 1. Encapsulates a transaction class that has the method of creating the transaction, committing the transaction, rolling back the transaction, and destroying the transaction

     Public classTransactiondal:sqlhelper { PublicDbConnection DbConnection =NULL;  PublicDbtransaction transaction =NULL;  Public voidBeginTransaction () {dbconnection=sqlhelper.createconnection (); DbConnection.            Open (); Transaction=DbConnection.        BeginTransaction (); }         Public voidcommittransaction () {if(NULL!=transaction)            {Transaction.commit (); }        }         Public voidRollbackTransaction () {if(NULL!=transaction) {transaction.            Rollback (); }        }         Public voiddisposetransaction () {if(DbConnection. state = =ConnectionState.Open) {dbconnection.            Close (); }            if(NULL!=transaction) {transaction.            Dispose (); }        }    }

View Code2. Encapsulating a simple data layer with a transactional approach

  Public classSqlHelper {Private Static ReadOnly stringConstr = configurationmanager.connectionstrings["Strcon"].        ConnectionString; /// <summary>        ///transactional methods with locks/// </summary>        /// <param name= "Tran" ></param>        /// <param name= "SQL" ></param>        /// <param name= "PMS" ></param>        /// <returns></returns>         Public Static intExecuteNonQuery (IDbTransaction Tran,stringSqlparamssqlparameter[] PMS) {                           using(SqlCommand cmd =NewSqlCommand (SQL, (SqlConnection) Tran. Connection, (SqlTransaction) tran)) {if(PMS! =NULL) {cmd.                    Parameters.addrange (PMS); }                                        returncmd.                ExecuteNonQuery (); }        }         Public StaticDataTable executedatatable (stringSqlparamssqlparameter[] PMS) {SqlDataAdapter adapter=NewSqlDataAdapter (SQL, CONSTR); if(PMS! =NULL) {adapter.            SelectCommand.Parameters.AddRange (PMS); } DataTable DT=NewDataTable (); Adapter.            Fill (DT); returnDT; }                protected StaticSystem.Data.Common.DbConnection createconnection () {SqlConnection con=NewSqlConnection (CONSTR); returncon; }    }

View Code3.winform Program 3.1 Program Interface

3.2c# code modifies user information and adds user information to the transaction

    Public Partial classForm1:form { PublicForm1 () {InitializeComponent (); }        Private voidForm1_Load (Objectsender, EventArgs e) {Datagridview1.datasource=getuserstable (); }        /// <summary>        ///bind a data source to DataGridView/// </summary>        /// <returns></returns>        PrivateDataTable getuserstable () {stringsql ="SELECT * from Users"; DataTable DT= sqlhelper.executedatatable (SQL,NULL); returnDT; }        /// <summary>        ///Click Edit and Add New button/// </summary>        /// <param name= "Sender" ></param>        /// <param name= "E" ></param>        Private voidButton_Click (Objectsender, EventArgs e) {            stringstrUserID =TxtID.Text.Trim (); stringstrUserName =TxtUserName.Text.Trim (); stringStrage =TxtAge.Text.Trim (); stringStraddress =TxtAddress.Text.Trim (); stringStrnewusername =TxtNewUserName.Text.Trim (); stringStrnewage =TxtNewAge.Text.Trim (); stringStrnewaddress =TxtNewAddress.Text.Trim (); if(strUserID! =""&& strUserName! =""&& Strage! =""&& straddress! =""&& Strnewusername! =""&& Strnewage! =""&& strnewaddress! ="")            {                intError =updateusermsg (strUserID, strUserName, Strage, straddress, Strnewusername, Strnewage, strnewaddress); if(Error = =0) {Datagridview1.datasource= Getuserstable ();//rebind student Data SourcesMessageBox.Show ("the entire transaction operation succeeded"); }                Else{MessageBox.Show ("the entire transaction operation failed"); }            }            Else{MessageBox.Show ("please fill in the complete information"); }        }        /// <summary>        ///Modify the specified student information/// </summary>        /// <returns></returns>        Private intUpdateusermsg (stringstrUserID,stringstrUserName,stringStrage,stringStraddress,stringStrnewusername,stringStrnewage,stringstrnewaddress) {Transactiondal Daltran=NewTransactiondal ();//instantiating a transactiondal that encapsulates a good transaction class            intError =0; Try{daltran.begintransaction (); //This opens the transaction lock                stringsql =string. Format ("Update Users set username= ' {0} ', age={1},address= ' {2} ' where userid={3}", strUserName, Strage, straddress, strUserID); intMoD = sqlhelper.executenonquery (daltran.transaction, SQL,NULL);//parameter: Transaction lock, SQL, NULL, modify Operation                if(MoD >0)//Successful Execution                {                    intMOD2 = AddUser (daltran.transaction, Strnewusername, Strnewage, strnewaddress);//If a series of operations are relevant, also pass the lock over                    if(MoD >0) {daltran.committransaction (); //Execute Commit                    }                    Else                    { //Rollback of execution failureError + =1;                    Daltran.rollbacktransaction (); }                }                Else     //Rollback of execution failure{Error+=1;                    Daltran.rollbacktransaction (); returnerror; }            }            Catch(Exception) {//performing an exception rollbackError + =1;            Daltran.rollbacktransaction (); }            finally{daltran.disposetransaction ();//Release the lock, release the connection instance            }            returnerror; }        //new students, also need to pass the same transaction case throughout the transaction        Private intAddUser (IDbTransaction Tran,stringStrnewusername,stringStrnewage,stringstrnewaddress) {            stringsql =string. Format ("INSERT into Users values (' {0} ', {1}, ' {2} ')", Strnewusername, Strnewage, strnewaddress); intMoD = sqlhelper.executenonquery (tran, SQL,NULL); returnMoD; }    }

View Code

Iv. Summary

C # Transaction lock to try{}catch{}finally{}, in a series of related operations to open a transaction lock can only be in the same database connection instance lock operation, the code is written relatively simple, we will use C # code transactions, packaging suitable for their own use of the good.

C # code enables transaction lock transaction for a series of commit rollback operations