DAL BLL 模板（事务操作）（事务操作中再执行事务操作）

1、一般的DAL  BLL层

DAL层：

#region 根据编号查询食品大类是否有重复记录
        /// <summary>
        /// 根据编号查询食品大类是否有重复记录
        /// </summary>
        /// <param name="KindNo">食品大类编号</param>
        /// <param name="FShopID">分店ID</param>
        /// <returns></returns>
        public bool CheckKindNo(string KindNo, int FShopID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from POS_FoodKind");
            strSql.Append(" where KindNo=@KindNo  and FShopID=@FShopID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@KindNo", SqlDbType.Char,2),
                    new SqlParameter("@FShopID", SqlDbType.Int,4),
                        };
            parameters[0].Value = KindNo;
            parameters[1].Value = FShopID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);

        }
        #endregion

BLL层：

private readonly EatGood.DAL.FoodManage.AddFoodKind   afk = new DAL.FoodManage.AddFoodKind  ();
#region 查询是否重复编号
       /// <summary>
       /// 查询是否重复编号
       /// </summary>
       /// <param name="KindNo">食品大类编号</param>
       /// <param name="FShopID">分店ID</param>
       /// <returns></returns>
       public bool CheckKindNo(string KindNo, int FShopID)
        {
            return afk.CheckKindNo(KindNo, FShopID);

        }
        #endregion

2、事务操作

DAL层：

#region  插入食品大类事务操作
        /// <summary>
        /// 插入食品大类事务操作
        /// </summary>
        /// <param name="modelPOS_FoodKind">食品大类model</param>
        /// <param name="modelSYS_Logs">日志model</param>
        /// <returns></returns>
        public bool AddFoodKindTran(EatGood.Model.FoodManage.POS_FoodKind modelPOS_FoodKind, EatGood.Model.FoodManage.SYS_Logs modelSYS_Logs)
        {

            List<CommandInfo> cmdlist = new List<CommandInfo>();

            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("insert into POS_FoodKind(");
            strSql1.Append("KindNo,DeptID,PrntNo,KindName,NID_KindName,CanTouch,MaxDiscPercent,CanChangeQty,CanChangeAmt,IsFloorSum,IsSaleSort,CanDiscAll,IsRevoked,BillGroup,CanCheck1,IsBonus,BonusPercent,IsSpltOrder,IsOnePrnt,FoodKindGrp,BonusAmt,IsProduct,IsUnitQty,IsNonprofit,AffixationRate,IsCalcTime,IsPrint,IsSumPrnt,IsCanUseCoupon,PenKID,HaveItemNote,JHARate,JHBRate,JHBName,CanUseCoupon,K3CODE,IsRoomAmtCalcTime,RoomAmtDefTime,RoomAmtCalcTime,IsShowInKit,IsEffect,K3CodeName,FShopID,FSwitchFlag,FZBID)");
            strSql1.Append(" values (");
            strSql1.Append("@KindNo,@DeptID,@PrntNo,@KindName,@NID_KindName,@CanTouch,@MaxDiscPercent,@CanChangeQty,@CanChangeAmt,@IsFloorSum,@IsSaleSort,@CanDiscAll,@IsRevoked,@BillGroup,@CanCheck1,@IsBonus,@BonusPercent,@IsSpltOrder,@IsOnePrnt,@FoodKindGrp,@BonusAmt,@IsProduct,@IsUnitQty,@IsNonprofit,@AffixationRate,@IsCalcTime,@IsPrint,@IsSumPrnt,@IsCanUseCoupon,@PenKID,@HaveItemNote,@JHARate,@JHBRate,@JHBName,@CanUseCoupon,@K3CODE,@IsRoomAmtCalcTime,@RoomAmtDefTime,@RoomAmtCalcTime,@IsShowInKit,@IsEffect,@K3CodeName,@FShopID,@FSwitchFlag,@FZBID)");
            SqlParameter[] parameters1 = {
                    new SqlParameter("@KindNo", SqlDbType.Char,2),
                    new SqlParameter("@DeptID", SqlDbType.Int,4),
                    new SqlParameter("@PrntNo", SqlDbType.Char,1),
                    new SqlParameter("@KindName", SqlDbType.VarChar,16),
                    new SqlParameter("@NID_KindName", SqlDbType.Int,4),
                    new SqlParameter("@CanTouch", SqlDbType.Bit,1),
                    new SqlParameter("@MaxDiscPercent", SqlDbType.TinyInt,1),
                    new SqlParameter("@CanChangeQty", SqlDbType.Bit,1),
                    new SqlParameter("@CanChangeAmt", SqlDbType.Bit,1),
                    new SqlParameter("@IsFloorSum", SqlDbType.Bit,1),
                    new SqlParameter("@IsSaleSort", SqlDbType.Bit,1),
                    new SqlParameter("@CanDiscAll", SqlDbType.Bit,1),
                    new SqlParameter("@IsRevoked", SqlDbType.Bit,1),
                    new SqlParameter("@BillGroup", SqlDbType.TinyInt,1),
                    new SqlParameter("@CanCheck1", SqlDbType.Bit,1),
                    new SqlParameter("@IsBonus", SqlDbType.Bit,1),
                    new SqlParameter("@BonusPercent", SqlDbType.TinyInt,1),
                    new SqlParameter("@IsSpltOrder", SqlDbType.Bit,1),
                    new SqlParameter("@IsOnePrnt", SqlDbType.Bit,1),
                    new SqlParameter("@FoodKindGrp", SqlDbType.TinyInt,1),
                    new SqlParameter("@BonusAmt", SqlDbType.Float,8),
                    new SqlParameter("@IsProduct", SqlDbType.Bit,1),
                    new SqlParameter("@IsUnitQty", SqlDbType.Bit,1),
                    new SqlParameter("@IsNonprofit", SqlDbType.Bit,1),
                    new SqlParameter("@AffixationRate", SqlDbType.Float,8),
                    new SqlParameter("@IsCalcTime", SqlDbType.Bit,1),
                    new SqlParameter("@IsPrint", SqlDbType.Bit,1),
                    new SqlParameter("@IsSumPrnt", SqlDbType.Bit,1),
                    new SqlParameter("@IsCanUseCoupon", SqlDbType.Bit,1),
                    new SqlParameter("@PenKID", SqlDbType.Int,4),
                    new SqlParameter("@HaveItemNote", SqlDbType.Bit,1),
                    new SqlParameter("@JHARate", SqlDbType.Float,8),
                    new SqlParameter("@JHBRate", SqlDbType.Float,8),
                    new SqlParameter("@JHBName", SqlDbType.VarChar,30),
                    new SqlParameter("@CanUseCoupon", SqlDbType.Bit,1),
                    new SqlParameter("@K3CODE", SqlDbType.VarChar,20),
                    new SqlParameter("@IsRoomAmtCalcTime", SqlDbType.Bit,1),
                    new SqlParameter("@RoomAmtDefTime", SqlDbType.Int,4),
                    new SqlParameter("@RoomAmtCalcTime", SqlDbType.Int,4),
                    new SqlParameter("@IsShowInKit", SqlDbType.Bit,1),
                    new SqlParameter("@IsEffect", SqlDbType.Bit,1),
                    new SqlParameter("@K3CodeName", SqlDbType.VarChar,50),
                    new SqlParameter("@FShopID", SqlDbType.Int,4),
                    
                    new SqlParameter("@FSwitchFlag", SqlDbType.Int,4),
                    new SqlParameter("@FZBID", SqlDbType.VarChar,30)};
            parameters1[0].Value = modelPOS_FoodKind.KindNo;
            parameters1[1].Value = modelPOS_FoodKind.DeptID;
            parameters1[2].Value = modelPOS_FoodKind.PrntNo;
            parameters1[3].Value = modelPOS_FoodKind.KindName;
            parameters1[4].Value = modelPOS_FoodKind.NID_KindName;
            parameters1[5].Value = modelPOS_FoodKind.CanTouch;
            parameters1[6].Value = modelPOS_FoodKind.MaxDiscPercent;
            parameters1[7].Value = modelPOS_FoodKind.CanChangeQty;
            parameters1[8].Value = modelPOS_FoodKind.CanChangeAmt;
            parameters1[9].Value = modelPOS_FoodKind.IsFloorSum;
            parameters1[10].Value = modelPOS_FoodKind.IsSaleSort;
            parameters1[11].Value = modelPOS_FoodKind.CanDiscAll;
            parameters1[12].Value = modelPOS_FoodKind.IsRevoked;
            parameters1[13].Value = modelPOS_FoodKind.BillGroup;
            parameters1[14].Value = modelPOS_FoodKind.CanCheck1;
            parameters1[15].Value = modelPOS_FoodKind.IsBonus;
            parameters1[16].Value = modelPOS_FoodKind.BonusPercent;
            parameters1[17].Value = modelPOS_FoodKind.IsSpltOrder;
            parameters1[18].Value = modelPOS_FoodKind.IsOnePrnt;
            parameters1[19].Value = modelPOS_FoodKind.FoodKindGrp;
            parameters1[20].Value = modelPOS_FoodKind.BonusAmt;
            parameters1[21].Value = modelPOS_FoodKind.IsProduct;
            parameters1[22].Value = modelPOS_FoodKind.IsUnitQty;
            parameters1[23].Value = modelPOS_FoodKind.IsNonprofit;
            parameters1[24].Value = modelPOS_FoodKind.AffixationRate;
            parameters1[25].Value = modelPOS_FoodKind.IsCalcTime;
            parameters1[26].Value = modelPOS_FoodKind.IsPrint;
            parameters1[27].Value = modelPOS_FoodKind.IsSumPrnt;
            parameters1[28].Value = modelPOS_FoodKind.IsCanUseCoupon;
            parameters1[29].Value = modelPOS_FoodKind.PenKID;
            parameters1[30].Value = modelPOS_FoodKind.HaveItemNote;
            parameters1[31].Value = modelPOS_FoodKind.JHARate;
            parameters1[32].Value = modelPOS_FoodKind.JHBRate;
            parameters1[33].Value = modelPOS_FoodKind.JHBName;
            parameters1[34].Value = modelPOS_FoodKind.CanUseCoupon;
            parameters1[35].Value = modelPOS_FoodKind.K3CODE;
            parameters1[36].Value = modelPOS_FoodKind.IsRoomAmtCalcTime;
            parameters1[37].Value = modelPOS_FoodKind.RoomAmtDefTime;
            parameters1[38].Value = modelPOS_FoodKind.RoomAmtCalcTime;
            parameters1[39].Value = modelPOS_FoodKind.IsShowInKit;
            parameters1[40].Value = modelPOS_FoodKind.IsEffect;
            parameters1[41].Value = modelPOS_FoodKind.K3CodeName;
            parameters1[42].Value = modelPOS_FoodKind.FShopID;
            
            parameters1[43].Value = modelPOS_FoodKind.FSwitchFlag;
            parameters1[44].Value = modelPOS_FoodKind.FZBID;

            CommandInfo cmd1 = new CommandInfo(strSql1.ToString(), parameters1);
            cmdlist.Add(cmd1);
          

            StringBuilder strSql4 = new StringBuilder();
            strSql4.Append("insert into SYS_Logs(");
            strSql4.Append("LogType,ComNo,OperatorID,LogTime,HostIP,HostName,LogText,KeyField,KeyID,KeyWord,BillID,FShopID,FSwitchFlag,KeyMainField,KeyMainValue,EmpID)");
            strSql4.Append(" values (");
            strSql4.Append("@LogType,@ComNo,@OperatorID,@LogTime,@HostIP,@HostName,@LogText,@KeyField,@KeyID,@KeyWord,@BillID,@FShopID,@FSwitchFlag,@KeyMainField,@KeyMainValue,@EmpID)");
            SqlParameter[] parameters4 = {
                    new SqlParameter("@LogType", SqlDbType.Int,4),
                    new SqlParameter("@ComNo", SqlDbType.VarChar,4),
                    new SqlParameter("@OperatorID", SqlDbType.Int,4),
                    new SqlParameter("@LogTime", SqlDbType.DateTime),
                    new SqlParameter("@HostIP", SqlDbType.VarChar,15),
                    new SqlParameter("@HostName", SqlDbType.VarChar,30),
                    new SqlParameter("@LogText", SqlDbType.VarChar,1000),
                    new SqlParameter("@KeyField", SqlDbType.VarChar,20),
                    new SqlParameter("@KeyID", SqlDbType.Int,4),
                    new SqlParameter("@KeyWord", SqlDbType.VarChar,20),
                    new SqlParameter("@BillID", SqlDbType.BigInt,8),
                    new SqlParameter("@FShopID", SqlDbType.Int,4),
                    new SqlParameter("@FSwitchFlag", SqlDbType.Int,4),
                    new SqlParameter("@KeyMainField", SqlDbType.VarChar,20),
                    new SqlParameter("@KeyMainValue", SqlDbType.VarChar,20),
                    new SqlParameter("@EmpID", SqlDbType.Int,4)};
            parameters4[0].Value = modelSYS_Logs.LogType;
            parameters4[1].Value = modelSYS_Logs.ComNo;
            parameters4[2].Value = modelSYS_Logs.OperatorID;
            parameters4[3].Value = modelSYS_Logs.LogTime;
            parameters4[4].Value = modelSYS_Logs.HostIP;
            parameters4[5].Value = modelSYS_Logs.HostName;
            parameters4[6].Value = modelSYS_Logs.LogText;
            parameters4[7].Value = modelSYS_Logs.KeyField;
            parameters4[8].Value = modelSYS_Logs.KeyID;
            parameters4[9].Value = modelSYS_Logs.KeyWord;
            parameters4[10].Value = modelSYS_Logs.BillID;
            parameters4[11].Value = modelSYS_Logs.FShopID;
            parameters4[12].Value = modelSYS_Logs.FSwitchFlag;
            parameters4[13].Value = modelSYS_Logs.KeyMainField;
            parameters4[14].Value = modelSYS_Logs.KeyMainValue;
            parameters4[15].Value = modelSYS_Logs.EmpID;

            CommandInfo cmd4 = new CommandInfo(strSql4.ToString(), parameters4);
            cmdlist.Add(cmd4);

            int Flag = DbHelperSQL.ExecuteSqlTran(cmdlist);
            if (Flag > 0)
                return true;
            else
                return false;
        }

        #endregion

BLL层：

#region  插入食品大类事务操作
       /// <summary>
       /// 插入食品大类事务操作
       /// </summary>
       /// <param name="modelPOS_FoodKind">食品大类model</param>
       /// <param name="modelSYS_Logs">日志model</param>
       /// <returns></returns>
       public bool AddFoodKindTran(EatGood.Model.FoodManage.POS_FoodKind modelPOS_FoodKind, EatGood.Model.FoodManage.SYS_Logs modelSYS_Logs)
       {
           return afk.AddFoodKindTran(modelPOS_FoodKind, modelSYS_Logs);
       }

       #endregion

3、事务操作中包含事务操作

#region 设置角色权限事务操作函数

        public bool SetEmployeeRoleFunctionTrans(EatGood.Model.GuestManage.Sys_RoleFunction[] modelSys_RoleFunction, EatGood.Model.SYS_Logs modelSYS_Logs, int Count)
        {
            List<CommandInfo> cmdlist = new List<CommandInfo>();

            StringBuilder[] strSql1 = new StringBuilder[Count];
            CommandInfo[] cmd1 = new CommandInfo[Count];
            EatGood.Model.GuestManage.Sys_RoleFunction[] model = new EatGood.Model.GuestManage.Sys_RoleFunction[Count];
            model = modelSys_RoleFunction;
            for (int i = 0; i < Count; i++)
            {
                strSql1[i] = new StringBuilder();
                cmd1[i] = new CommandInfo();

                strSql1[i].Append("   declare @p1 int ");
                strSql1[i].Append("  set @p1=NULL ");
                strSql1[i].Append("  declare @p3 varchar(255) ");
                strSql1[i].Append("  set @p3=NULL ");
                strSql1[i].Append("  exec sp_prepexecrpc @p1 output,N'P_SYS_RoleFunction_set',@p3 output,@head_version,@head_operid,@head_Language,@action_in,@FID,@FRoleID,@FFunctionID,@FPermission,@FShopID,@FSwitchFlag,@FIsRevoked    ");
                strSql1[i].Append("  select @p1, @p3 ");


                SqlParameter[] parameters1 = new   SqlParameter [] {
                     new SqlParameter("@head_version", SqlDbType.VarChar,10) ,
                    new SqlParameter("@head_operid",SqlDbType.Int,20),
                    new SqlParameter("@head_Language",SqlDbType.Int,1),
                    new SqlParameter("@action_in",SqlDbType.Int,1),

                    new SqlParameter("@FID", SqlDbType.BigInt,8),
                    new SqlParameter("@FRoleID", SqlDbType.Int,4),
                    new SqlParameter("@FFunctionID", SqlDbType.Int,4),
                    new SqlParameter("@FPermission", SqlDbType.Bit,1),
                    new SqlParameter("@FShopID", SqlDbType.Int,4),
                    
                    new SqlParameter("@FSwitchFlag", SqlDbType.Int,4),
                    new SqlParameter("@FIsRevoked", SqlDbType.Bit,1)};
                parameters1[0].Value = model[i].headversion;
                parameters1[1].Value = model[i].headoperid;
                parameters1[2].Value = model[i].headLanguage;
                parameters1[3].Value = model[i].actionin;
                parameters1[4].Value = model[i].FID;
                parameters1[5].Value = model[i].FRoleID;
                parameters1[6].Value = model[i].FFunctionID;
                parameters1[7].Value = model[i].FPermission;
                parameters1[8].Value = model[i].FShopID;
                parameters1[9].Value = model[i].FSwitchFlag;
                parameters1[10].Value = model[i].FIsRevoked;


                cmd1[i] = new CommandInfo(strSql1[i].ToString(), parameters1);
                cmdlist.Add(cmd1[i]);
            }





            StringBuilder strSql5 = new StringBuilder();
            strSql5.Append("insert into SYS_Logs(");
            strSql5.Append("LogType,ComNo,OperatorID,LogTime,HostIP,HostName,LogText,KeyField,KeyID,KeyWord,BillID,FShopID,FSwitchFlag,KeyMainField,KeyMainValue,EmpID)");
            strSql5.Append(" values (");
            strSql5.Append("@LogType,@ComNo,@OperatorID,@LogTime,@HostIP,@HostName,@LogText,@KeyField,@KeyID,@KeyWord,@BillID,@FShopID,@FSwitchFlag,@KeyMainField,@KeyMainValue,@EmpID)   alter table [POS_FoodDef] check constraint [FK_POS_FOODDEF_2_POS_FOGNOS]  ");
            SqlParameter[] parameters5 = {
                    new SqlParameter("@LogType", SqlDbType.Int,4),
                    new SqlParameter("@ComNo", SqlDbType.VarChar,4),
                    new SqlParameter("@OperatorID", SqlDbType.Int,4),
                    new SqlParameter("@LogTime", SqlDbType.DateTime),
                    new SqlParameter("@HostIP", SqlDbType.VarChar,15),
                    new SqlParameter("@HostName", SqlDbType.VarChar,30),
                    new SqlParameter("@LogText", SqlDbType.VarChar,1000),
                    new SqlParameter("@KeyField", SqlDbType.VarChar,20),
                    new SqlParameter("@KeyID", SqlDbType.Int,4),
                    new SqlParameter("@KeyWord", SqlDbType.VarChar,20),
                    new SqlParameter("@BillID", SqlDbType.BigInt,8),
                    new SqlParameter("@FShopID", SqlDbType.Int,4),
                    new SqlParameter("@FSwitchFlag", SqlDbType.Int,4),
                    new SqlParameter("@KeyMainField", SqlDbType.VarChar,20),
                    new SqlParameter("@KeyMainValue", SqlDbType.VarChar,20),
                    new SqlParameter("@EmpID", SqlDbType.Int,4)};
            parameters5[0].Value = modelSYS_Logs.LogType;
            parameters5[1].Value = modelSYS_Logs.ComNo;
            parameters5[2].Value = modelSYS_Logs.OperatorID;
            parameters5[3].Value = modelSYS_Logs.LogTime;
            parameters5[4].Value = modelSYS_Logs.HostIP;
            parameters5[5].Value = modelSYS_Logs.HostName;
            parameters5[6].Value = modelSYS_Logs.LogText;
            parameters5[7].Value = modelSYS_Logs.KeyField;
            parameters5[8].Value = modelSYS_Logs.KeyID;
            parameters5[9].Value = modelSYS_Logs.KeyWord;
            parameters5[10].Value = modelSYS_Logs.BillID;
            parameters5[11].Value = modelSYS_Logs.FShopID;
            parameters5[12].Value = modelSYS_Logs.FSwitchFlag;
            parameters5[13].Value = modelSYS_Logs.KeyMainField;
            parameters5[14].Value = modelSYS_Logs.KeyMainValue;
            parameters5[15].Value = modelSYS_Logs.EmpID;

            CommandInfo cmd5 = new CommandInfo(strSql5.ToString(), parameters5);
            cmdlist.Add(cmd5);

            int Flag = DbHelperSQL.ExecuteSqlTran(cmdlist);

            return true;  
            if (Flag > 0)
                return true;
            else
                return false;
        }


        #endregion