关于 c# 中SQL 事务的回滚

用事务的方式执行 无返回的sql 语句或存储过程，使用了 cmd.ExecuteNonQuery(); 后

如果没有提交事务，该方法执行并未成功，并且此时该方法涉及的表，其他事务都无法操作。

必须将该事务提交或回滚，才能继续。

 

用于DPD网路版bug的修复：ftp没有上传成功，但服务器却修改了数据库记录，可以通过事务先执行存储过程，不提交事务，如果客户端连接断开，就回滚，如果没断开，就提交。当然这会造成该事务对所涉及的表的独占。

 

代码如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Runtime.InteropServices;


namespace 制图统一平台
{
    public class M_DBSQL : M_DBInterface
    {
        private string m_dbConnectStr = string.Empty;

        private SqlTransaction sqlTrans ; //如果事务没有执行完成，那么事务中操作的表将被锁定，其他连接无法操作
        private SqlConnection sqlConn;

        #region 属性
        /// <summary>
        /// 查看数据库连接字符串
        /// </summary>
        public string StrCon
        {
            get { return m_dbConnectStr; }
            set{ m_dbConnectStr = value;}
        }

        /// <summary>
        /// 当前实例的事务
        /// </summary>
        public SqlTransaction SqlTrans
        {
            get { return sqlTrans; }
            set { sqlTrans = value; }
        }
        /// <summary>
        /// 当前实例的连接
        /// </summary>
        public SqlConnection SqlConn
        {
            get { return sqlConn; }
            set { sqlConn = value; }
        }

        #endregion

   /// <summary>
        /// 执行无返回的语句，如果失败，回滚
        /// </summary>
        /// <param name="cmdStr"></param>
        public bool excuteSql(string cmdStr)
        {
            bool re = false;
            try
            {
                sqlConn = new SqlConnection(m_dbConnectStr);

                SqlCommand cmd = sqlConn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cmdStr;

                if (sqlConn.State == ConnectionState.Closed)
                    sqlConn.Open();

                sqlTrans = sqlConn.BeginTransaction();
                cmd.Transaction = sqlTrans;
                try
                {
                    cmd.ExecuteNonQuery();
                  //  sqlTrans.Commit();
                    re = true;
                }
                catch (Exception e)
                {
                    try
                    {
                        sqlTrans.Rollback();//事务提交以后不能回滚
                    }
                    catch (SqlException ex)
                    {
                        if (sqlTrans.Connection != null)
                        {
                            throw new Exception("回滚失败! 异常类型: " + ex.GetType());
                        }
                    }
                }
                finally
                {
                    //sqlConn.Close();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return re;
        }   

}
}

//form 中 3个按钮代码如下：

  M_DBSQL sql = new M_DBSQL();

        private void buttonexcuteSql_Click(object sender, EventArgs e)
        {

            sql.excuteSql("insert table1(num,val) values('4','f')");//delete table1

        }

        private void buttonRollBack_Click(object sender, EventArgs e)
        {
            try
            {
                if (sql.SqlTrans.Connection != null)
                {
                    sql.SqlTrans.Rollback();
                    // sql.SqlTrans.Commit();
                    sql.SqlConn.Close();
                }
             
            }
            catch(System.Exception ex)
            {
              
            }
        }

        private void buttonCommit_Click(object sender, EventArgs e)
        {
            try
            {
                if (sql.SqlTrans.Connection != null)
                {
                
                    sql.SqlTrans.Commit();
                 
                }
                if (sql.SqlConn != null)
                {
                    sql.SqlConn.Close();
                }

            }
            catch (System.Exception ex)
            {

            }
        }