C # The Code enables the Transaction lock Transaction to perform a series of commit rollback operations,

C # The Code enables the Transaction lock Transaction to perform a series of commit rollback operations,
I. Preface

Because many people generally perform a series of related database operations in the stored procedure, and the writing of locks in the stored procedure is also very simple, this article mainly introduces C # background code using locks for a series of transaction operations. I established a simple winform program and then made a transaction: modifying the user information of the specified ID, add a new user information and commit the transaction after the operation is successful. The transaction must be rolled back if the program is abnormal or the database is not successful!

Ii. Stored Procedure transactions

I think everyone knows how to use it. If you don't understand it, ask me.

III. C # code transactions 1. encapsulate a transaction class with the public class TransactionDal: SqlHelper {public DbConnection dbconnection = null; public DbTransaction transaction = null; public void BeginTransaction () {dbconnection = SqlHelper. createConnection (); dbconnection. open (); transaction = dbconnection. beginTransaction ();} public void CommitTransaction () {if (null! = Transaction) {transaction. Commit () ;}} public void RollbackTransaction () {if (null! = Transaction) {transaction. rollback () ;}} public void DisposeTransaction () {if (dbconnection. state = ConnectionState. open) {dbconnection. close ();} if (null! = Transaction) {transaction. Dispose ();}}}View Code2. encapsulate the simple data layer using the transaction method public class SqlHelper {private static readonly string constr = ConfigurationManager. connectionStrings ["strCon"]. connectionString; /// <summary> /// the transaction method with a lock /// </summary> /// <param name = "tran"> </param> // <param name = "SQL"> </param> // <param name = "pms"> </param> // <returns> </returns> public static int ExecuteNonQuery (IDbTransaction tran, string SQL, params SqlParamet Er [] pms) {using (SqlCommand cmd = new SqlCommand (SQL, (SqlConnection) tran. Connection, (SqlTransaction) tran) {if (pms! = Null) {cmd. parameters. addRange (pms);} return cmd. executeNonQuery () ;}} public static DataTable ExecuteDataTable (string SQL, params SqlParameter [] pms) {SqlDataAdapter adapter = new SqlDataAdapter (SQL, constr); if (pms! = Null) {adapter. selectCommand. parameters. addRange (pms);} DataTable dt = new DataTable (); adapter. fill (dt); return dt;} protected static System. data. common. dbConnection CreateConnection () {SqlConnection con = new SqlConnection (constr); return con ;}}View Code3.winform program 3.1 Program Interface

3.2C # The Code modifies user information and adds user information. The transaction public partial class Form1: Form {public Form1 () {InitializeComponent ();} private void Form1_Load (object sender, EventArgs e) is used) {dataGridView1.DataSource = GetUsersTable ();} /// <summary> /// bind a data source to the dataGridView /// </summary> /// <returns> </returns> private DataTable GetUsersTable () {string SQL = "select * from Users"; DataTable dt = SqlHelper. executeDataTable (SQL, nu Ll); return dt ;} /// <summary> /// click Modify and add a button /// </summary> /// <param name = "sender"> </param> // <param name = "e"> </param> private void button_Click (object sender, eventArgs e) {string strUserID = txtID. text. trim (); string strUserName = txtUserName. text. trim (); string strAge = txtAge. text. trim (); string strAddress = txtAddress. text. trim (); string strNewUserName = txtNewUserName. text. trim (); stri Ng strNewAge = txtNewAge. Text. Trim (); string strNewAddress = txtNewAddress. Text. Trim (); if (strUserID! = "" & StrUserName! = "" & StrAge! = "" & StrAddress! = "" & StrNewUserName! = "" & StrNewAge! = "" & StrNewAddress! = "") {Int error = UpdateUserMsg (strUserID, strUserName, strAge, strAddress, strNewUserName, strNewAge, strNewAddress); if (error = 0) {dataGridView1.DataSource = GetUsersTable (); // rebind the student data source MessageBox. show ("the entire transaction succeeded");} else {MessageBox. show ("failed transaction operation") ;}} else {MessageBox. show ("please fill in the complete information ");}} /// <summary> /// modify the specified student information /// </summary> /// <returns> </returns> private int UpdateUserMsg (string strUserID, string strUserName, string strAge, string strAddress, string strNewUserName, string strNewAge, string strNewAddress) {TransactionDal dalTran = new TransactionDal (); // instantiate TransactionDal int error that encapsulates the transaction class = 0; try {dalTran. beginTransaction (); // here the transaction lock string SQL = string is enabled. format ("update Users set UserName = '{0}', Age = {1}, Address = '{2}' where UserID = {3}", strUserName, strAge, strAddress, strUserID); int mod = SqlHelper. executeNonQuery (dalTran. transaction, SQL, null); // pass the parameter: transaction lock, SQL, null, execute the modification operation if (mod> 0) // execution successful {int mod2 = AddUser (dalTran. transaction, strNewUserName, strNewAge, strNewAddress); // if a series of operations are related, pass the lock in the past if (mod> 0) {dalTran. commitTransaction (); // execution commit} else {// execution failure rollback error + = 1; dalTran. rollbackTransaction () ;}} else // execution failed rollback {error + = 1; dalTran. rollbackTransaction (); return error ;}} catch (Exception) {// execute an Exception rollback error + = 1; dalTran. rollbackTransaction ();} finally {dalTran. disposeTransaction (); // release the lock and release the connection instance} return error;} // new student. The same transaction case private int AddUser (IDbTransaction tran, string strNewUserName, string strNewAge, string strNewAddress) {string SQL = string. format ("insert into Users values ('{0}', {1}, '{2}')", strNewUserName, strNewAge, strNewAddress); int mod = SqlHelper. executeNonQuery (tran, SQL, null); return mod ;}}View Code

 

Iv. Summary

C # The transaction lock should be try {} catch {} finally {}. When a transaction lock is enabled in a series of related operations, the transaction lock can only be performed in the same database connection instance, the code is easy to write. You can use C # code transactions to encapsulate suitable ones for your use.