C#代碼啟用事務鎖Transaction進行一系列提交回滾操作，
一、前言
因為很多人一般進行一系列相關數據庫操作都是在存儲過程裡面，而且在存儲過程用鎖的寫法也是很簡單的，在這篇文章主要介紹一下C#後台代碼用鎖進行一系列事務操作，我建立一個簡單的winform程序，然後做一個事務：修改指定ID的用戶信息，然後新增一名用戶信息，操作成功就提交事務，程序異常和數據庫執行不成功都必須回滾事務！
二、存儲過程的事務
寫得比較簡單，我想大家都知道怎麼使用了，不懂的可以問我。
三、C#代碼的事務
1.封裝一個事務類，裡面有事務的創建、提交事務、回滾事務和銷毀事務的方法
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 Code
2.封裝簡單的數據層用到事務的方法
public class SqlHelper
{
private static readonly string constr = ConfigurationManager.ConnectionStrings["strCon"].ConnectionString;
/// <summary>
/// 有鎖的事務方法
/// </summary>
/// <param name="tran"></param>
/// <param name="sql"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(IDbTransaction tran,string sql, params SqlParameter[] 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 Code
3.winform程序
3.1程序界面
3.2C#代碼修改用戶信息並新增用戶信息用到了事務
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = GetUsersTable();
}
/// <summary>
/// 給dataGridView綁定數據源
/// </summary>
/// <returns></returns>
private DataTable GetUsersTable()
{
string sql = "select * from Users";
DataTable dt = SqlHelper.ExecuteDataTable(sql, null);
return dt;
}
/// <summary>
/// 點擊修改並新增按鈕
/// </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();
string 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();//重新綁定學生數據源
MessageBox.Show("整個事務操作成功");
}
else
{
MessageBox.Show("整個事務操作失敗");
}
}
else
{
MessageBox.Show("請填寫完整信息");
}
}
/// <summary>
/// 修改指定學生信息
/// </summary>
/// <returns></returns>
private int UpdateUserMsg(string strUserID, string strUserName, string strAge, string strAddress, string strNewUserName, string strNewAge, string strNewAddress)
{
TransactionDal dalTran = new TransactionDal(); //實例化封裝好事務類的TransactionDal
int error = 0;
try
{
dalTran.BeginTransaction(); //這裡開啟事務鎖
string sql = string.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); //傳參：事務鎖，sql, null ，執行修改操作
if (mod > 0)//執行成功
{
int mod2 = AddUser(dalTran.transaction, strNewUserName, strNewAge, strNewAddress);//如果一系列操作是相關的也要傳遞鎖過去
if (mod > 0)
{
dalTran.CommitTransaction(); //執行提交
}
else
{ //執行失敗回滾
error += 1;
dalTran.RollbackTransaction();
}
}
else //執行失敗回滾
{
error += 1;
dalTran.RollbackTransaction();
return error;
}
}
catch (Exception)
{ //執行異常回滾
error += 1;
dalTran.RollbackTransaction();
}
finally
{
dalTran.DisposeTransaction(); //釋放鎖，釋放連接實例
}
return error;
}
//新增學生,在整個事務中也需要傳遞同個事務事例
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
四、總結
C#事務鎖要try{}catch{}finally{},在一系列相關操作的時候開啟了事務鎖就只能在同一個數據庫連接實例進行鎖的操作， 代碼都寫得比較簡單，大家會用C#代碼事務，封裝合適自己使用的就好了。
