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

最新推荐文章于 2024-02-20 01:00:00 发布

纹路猫

最新推荐文章于 2024-02-20 01:00:00 发布

阅读量427

点赞数

本文链接：https://blog.csdn.net/hougelou/article/details/70649549

版权

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;
        }