ADO.Net存储过程transaction的使用事项

来源：互联网发布：prezi for mac 中文版编辑：程序博客网时间：2024/05/31 19:27

public bool InsertInvitation(DataTable dt)        {            SqlTransaction transaction = null;            try            {                using (SqlConnection conn = new SqlConnection(sqlHelper.SQLConString))                {                    conn.Open();                    transaction = conn.BeginTransaction();                    SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction);                    //服务器上目标表的名称                    sbc.DestinationTableName = dt.TableName;                    sbc.BatchSize = dt.Rows.Count;                    sbc.BulkCopyTimeout = 180;                    sbc.WriteToServer(dt);                    SqlCommand cmd = new SqlCommand();                    cmd.CommandType = CommandType.Text;                    cmd.Connection = conn;                    cmd.Transaction = transaction;                    cmd.CommandText = "insert AABB(ID)values(1)";                    cmd.ExecuteNonQuery();                    Convert.ToInt32("sdfs");                    transaction.Rollback();                }            }            catch (Exception ex)            {                transaction.Rollback();                LogMsg.WriteLogToApplicationFolderByMonth(ex.ToString());                return false;            }            return true;        }

当报异常的时候，transaction会引发“此 SqlTransaction 已完成；它再也无法使用。”

改成下面的样子则无碍。另外，使用事务的时候，要注意将SqlTransaction赋给执行命令的SqlTransaction，如

SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction);、cmd.Transaction = transaction;

public bool InsertInvitation(DataTable dt)        {            using (SqlConnection conn = new SqlConnection(sqlHelper.SQLConString))            {                SqlTransaction transaction = null;                try                {                    conn.Open();                    transaction = conn.BeginTransaction();                    SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction);                    //服务器上目标表的名称                    sbc.DestinationTableName = dt.TableName;                    sbc.BatchSize = dt.Rows.Count;                    sbc.BulkCopyTimeout = 180;                    sbc.WriteToServer(dt);                    SqlCommand cmd = new SqlCommand();                    cmd.CommandType = CommandType.Text;                    cmd.Connection = conn;                    cmd.Transaction = transaction;                    cmd.CommandText = "insert AABB(ID)values(1)";                    object i=cmd.ExecuteNonQuery();                    if(Convert.toInt32(i)<=0){                      transaction.Rollback();                    }                                        transaction.Commit();                }                catch (Exception ex)                {                    transaction.Rollback();                    LogMsg.WriteLogToApplicationFolderByMonth(ex.ToString());                    return false;                }            }            return true;        }

0 0