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