C#操作SqlServer数据库事务

•  操作事务

  • // 1.连接数据库
    string connstring = @"Server = 李昊轩-212\MSSQLSERVER11;Database = Student;uid = sa;Pwd= 123456";
    SqlConnection conn = new SqlConnection(connstring);
    conn.Open();
    // 2.创建多个sql语句
    List<string> list = new List<string>() {
        "delete from XueSheng where StudentId = 1004",
        "delete from XueSheng where StudentId = 100"
    };
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    try
    {
        // 3.开启事务
        cmd.Transaction = conn.BeginTransaction();
        foreach (string s in list)
        {
            // 4.设置执行的sql
            cmd.CommandText = s;
            cmd.ExecuteNonQuery();
        }
        // 5. 如果没有出错 提交事务
        cmd.Transaction.Commit();
    }
    // 6. 如果执行错误 跳转到catch里面 在catch执行回滚
    catch (Exception e)
    {
        if (cmd.Transaction != null)
        {
            cmd.Transaction.Rollback(); // 执行sql语句有错误的情况 执行回滚操作
        }
        throw new Exception("执行删除sql事务出错:" + e.Message);
    }
    finally
    {
        // 7.不管是否执行有误 把事务设置为null 清除事务
        if(cmd.Transaction!= null)
        {
            cmd.Transaction = null;
        }
        conn.Close();
    }