事务中使用存储过程解决数据库表锁定达到报错回滚效果

红颜色的为重要代码 

SqlConnection con = DBCommon.GetSqlConnection();
//SqlCommand cmd = con.CreateCommand();
 //创建存储过程
 SqlCommand cmd = new SqlCommand("Sp_drugStockRecord", con);
//创建事务
SqlTransaction tran = con.BeginTransaction("tranM");

cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;

try
            {

               SqlParameter[] sp = new SqlParameter[7];
                sp[0] = new SqlParameter("@drugID", SqlDbType.Int);
                sp[1] = new SqlParameter("@department", SqlDbType.VarChar);
                sp[2] = new SqlParameter("@batchNum", SqlDbType.VarChar);
                sp[3] = new SqlParameter("@amount", SqlDbType.Float);
                sp[4] = new SqlParameter("@stockTime", SqlDbType.DateTime);
                sp[5] = new SqlParameter("@produceDate", SqlDbType.DateTime);
                sp[6] = new SqlParameter("@period", SqlDbType.DateTime);
                //药品ID
                for (int i = 0; i < dDepotIn.LstDepotInBillDrugList.Count; i++)
                {
                    cmd.Parameters.Add(sp[0]);
                    cmd.Parameters["@drugID"].Value = dDepotIn.LstDepotInBillDrugList[i].drugID;
                    //部门
                    cmd.Parameters.Add(sp[1]);
                    cmd.Parameters["@department"].Value = dDepotIn.storeroom;
                    //批次
                    cmd.Parameters.Add(sp[2]);
                    cmd.Parameters["@batchNum"].Value = dDepotIn.LstDepotInBillDrugList[i].batchNum;
                    //大单位库存
                    cmd.Parameters.Add(sp[3]);
                    cmd.Parameters["@amount"].Value = dDepotIn.LstDepotInBillDrugList[i].drugAmount;
                    //入库时间
                    cmd.Parameters.Add(sp[4]);
                    cmd.Parameters["@stockTime"].Value = dDepotIn.data.ToString("yyyy-MM-dd HH:mm:ss");
                    //生产日期
                    cmd.Parameters.Add(sp[5]);
                    cmd.Parameters["@produceDate"].Value = dDepotIn.LstDepotInBillDrugList[i].ProduceDate.ToString("yyyy-MM-dd");
                    //有效期
                    cmd.Parameters.Add(sp[6]);
                    cmd.Parameters["@period"].Value = dDepotIn.LstDepotInBillDrugList[i].DrugPeriod.ToString("yyyy-MM-dd");
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                cmd.CommandType = CommandType.Text;//清除存储过程

               switch (dDepotIn.AboutBillType)
                {
                    case "采购单":
                        string sql1 = "update t_drugPurchasing set state = '已采购' where purchasingNumber = '" + dDepotIn.AboutBillNum + "'";
                        //DBCommon.ExecuteNonQuery(sql1);
                        cmd.CommandText = sql1;
                        cmd.ExecuteNonQuery();
                        break;
                    case "科室退药单":
                        string sql2 = "update t_returnDepot set state = '已入库' where retunId = " + dDepotIn.AboutBillNum + "";
                        //DBCommon.ExecuteNonQuery(sql2);
                        cmd.CommandText = sql2;
                        cmd.ExecuteNonQuery();
                        break;
                }

               //更新为已入库
                string sql3 = "update t_drugDepotIn set state = '已入库' where number = " + dDepotIn.number + "";
                //DBCommon.ExecuteNonQuery(sql3);
                cmd.CommandText = sql3;
                cmd.ExecuteNonQuery();
                tran.Commit();//执行
                return true;
            }
            catch (Exception er)
            {
                tran.Rollback();//回滚
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }

 

 

存储过程：

 

//首先查询，如果有则更新，如果没有则新增。

create proc Sp_drugStockRecord
@drugID int,
@department varchar(500),
@batchNum varchar(500),
@amount float,
@stockTime datetime,
@produceDate datetime,
@period datetime

as

begin
  if exists(select drugID from t_drugStockRecord where drugID =@drugID and department =@department  and batchNum = @batchNum)
    begin
        update t_drugStockRecord set amount=(@amount+@Amount)
        where drugID = @drugID and department=@department and batchNum=@batchNum
    end
  else
    begin
        insert into  t_drugStockRecord(drugID, department, batchNum, amount, stockTime, produceDate, period) values
        (@drugID, @department, @batchNum, @amount, @stockTime, @produceDate, @period)
    end
end
--drop proc Sp_drugStockRecord