C#代码启用事务锁Transaction 规范操作

阅读原文时间：2021年04月20日阅读：1

一、前言

因为很多人一般进行一系列相关数据库操作都是在存储过程里面，而且在存储过程用锁的写法也是很简单的，在这篇文章主要介绍一下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