SQL中运用事务实现多表更新操作

  using (SqlConnection conn = new SqlConnection(SQLHelper.constr))//SQLHelper.constr为数据库连接字符串
            {
                conn.Open();//连接数据库
                //使用事务处理多SQL语句
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {               

            var strSql = new StringBuilder();
            SqlParameter[] parameters = {
                                new SqlParameter("@rodNO", SqlDbType.VarChar, 50),
                                new SqlParameter("@brokenrodNO", SqlDbType.VarChar, 50),
                                new SqlParameter("@Operator", SqlDbType.VarChar, 50),
                                new SqlParameter("@machineNO", SqlDbType.VarChar, 50),
                                new SqlParameter("@starttime", SqlDbType.DateTime),
                                new SqlParameter("@Remark", SqlDbType.VarChar, 200),
                                new SqlParameter("@RecordTime", SqlDbType.DateTime),
                                new SqlParameter("@State", SqlDbType.VarChar, 50)
                                                    };
            parameters[0].Value = model.newrodNO; //下面几个表中的字段顺序都是一致的,所以才可以公用一个赋值语句
            parameters[1].Value = model.rodNO; 
            parameters[2].Value = model.Operator;
            parameters[3].Value = model.machineNO;
            parameters[4].Value = model.starttime;
            parameters[5].Value = model.Remark;
            parameters[6].Value = model.RecordTime;
            parameters[7].Value = model.State;         

            //插入到断棒表
            strSql.Append("insert into PRO_Precisionstretch_BreakRod(");
            strSql.Append("rodNO,brokenrodNO,Operator,machineNO,starttime,Remark,RecordTime,State)");
            strSql.Append(" values (");
            strSql.Append("@rodNO,@brokenrodNO,@Operator,@machineNO,@starttime,@Remark,@RecordTime,@State) ");
            //更新本延伸开始表
            strSql.Append("update PRO_Precisionstretch_Begin set ");
            strSql.Append("rodNO=@rodNO ");
            strSql.Append("where rodNO like @brokenrodNO + '%' ");
            //更新本延伸结束表
            strSql.Append("update PRO_Precisionstretch_end set ");
            strSql.Append("rodNO=@rodNO ");
            strSql.Append("where rodNO like @brokenrodNO + '%' ");
            SQLHelper.RunSql(strSql.ToString(), parameters);

                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

 

转载于:https://www.cnblogs.com/QiuJL/archive/2011/08/05/4524345.html