C# WhereSqlClauseBuilder.Clear方法代码示例

 public Int32 KelownaPayMonthInfo(String yearMonth)
 {
     DateTime dt = DateTime.Parse(yearMonth.Insert(4, "-").Insert(7, "-01")).AddMonths(1);
     String yearMonthD = String.Concat(dt.Year.ToString(), dt.Month.ToString());
     using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Required))
     {
         StringBuilder sql = new StringBuilder();
         WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
         foreach (PersonBaseFeeMonthInfo PersonBaseFeeMonthInfo in PersonBaseFeeInfoAdapter.Instance.GetPersonBaseFeeInfoList(PersonBaseFeeTarget.PersonBaseFeeMonth, yearMonth))
         {
             PersonBaseFeeMonthInfo.YearMonth = yearMonthD;
             sql.Append(ORMapping.GetInsertSql(PersonBaseFeeMonthInfo, BuilderEx.TSqlBuilderInstance));
             sql.Append(";");
         }
         //工资月表
         builder.AppendItem(PayMonthInfoDBConst.YearMonth, yearMonth);
         foreach (PayMonthInfo PayMonthInfo in PayMonthInfoAdapter.Instance.GetPayMonthInfoList(builder))
         {
             PayMonthInfo.YearMonth = yearMonthD;
             sql.Append(ORMapping.GetInsertSql(PayMonthInfo, BuilderEx.TSqlBuilderInstance));
             sql.Append(";");
         }
         //人员基本工资项目部门分摊月表
         builder.Clear();
         builder.AppendItem(PersonBaseFeeDepartmentProjectMonthInfoDBConst.YearMonth, yearMonth);
         foreach (PersonBaseFeeDepartmentProjectMonthInfo personFeeDepartmentProjectMonthInfo in PersonBaseFeeDepartmentProjectInfoAdapter.Instance.GetPersonBaseFeeDepartmentProjectInfoList(yearMonth, builder))
         {
             personFeeDepartmentProjectMonthInfo.YearMonth = yearMonthD;
             sql.Append(ORMapping.GetInsertSql(personFeeDepartmentProjectMonthInfo, BuilderEx.TSqlBuilderInstance));
             sql.Append(";");
         }
         //工资项目月表
         builder.Clear();
         builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth);
         foreach (FeeMonthInfo feeMonthInfo in FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder))
         {
             feeMonthInfo.YearMonth = yearMonthD;
             sql.Append(ORMapping.GetInsertSql(feeMonthInfo, BuilderEx.TSqlBuilderInstance));
             sql.Append(";");
         }
         //税率月表
         builder.Clear();
         builder.AppendItem(TaxMonthInfoDBConst.YearMonth, yearMonth);
         foreach (TaxMonthInfo taxMonthInfo in TaxInfoAdapter.Instance.GetTaxInfoList(yearMonth, builder))
         {
             taxMonthInfo.YearMonth = yearMonthD;
             sql.Append(ORMapping.GetInsertSql(taxMonthInfo, BuilderEx.TSqlBuilderInstance));
             sql.Append(";");
         }
         int result = _DataHelper.ExecuteSql(sql.ToString());
         scope.Complete();
         return result;
     }
 }

    private void InitializeCalculateExpFeeListBox()
    {
        List<FeeInfo> feeList = new List<FeeInfo>(), feeListNew = new List<FeeInfo>();
        WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
        if (action != ActionType.Add)
        {//if (this.ddlFee.SelectedIndex > 0){} //为完成：如果打开后又选了重新选择了父项目或改了项目编码怎么办？
            builder.AppendItem(FeeInfoDBConst.FeeID, feeID, "<>");
            builder.AppendItem(FeeInfoDBConst.ParentID, feeID, "<>");
            builder.AppendItem(FeeInfoDBConst.CalculateExp, String.Concat(@"%\[", feeID, @"\]%##ESCAPE##"), " NOT LIKE ");
        }
        builder.AppendItem(FeeInfoDBConst.FeeType, FeeType.Sum.ToString("D"));//计算型
        feeList = this.ReCreateFeeID(FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder).Where(fee => !fee.UseFlag == bool.Parse(Status.True.ToString())).ToList());
        Tools.ListBoxDataBind(this.litbCalculateFee, feeList, FeeInfoConst.FeeName, FeeInfoConst.FeeID);
        builder.Clear();
        builder.AppendItem(FeeInfoDBConst.FeeType, FeeType.Common.ToString("D"));//组成型
        feeList = this.ReCreateFeeID(FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder).Where(fee => !fee.UseFlag == bool.Parse(Status.True.ToString())).ToList());
        Tools.ListBoxDataBind(this.litbCommonFee, feeList, FeeInfoConst.FeeName, FeeInfoConst.FeeID);
        builder.Clear();
        builder.AppendItem(FeeInfoDBConst.FeeType, FeeType.Parameter.ToString("D"));//参数型
        feeList = this.ReCreateFeeID(FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder).Where(parent => (!parent.UseFlag == bool.Parse(Status.True.ToString())) && (parent.ParentID == "" || parent.ParentID == null)).ToList());
        Tools.ListBoxDataBind(this.litbParameterFee, feeList, FeeInfoConst.FeeName, FeeInfoConst.FeeID);
        builder.Clear();
        builder.AppendItem(FeeInfoDBConst.FeeType, FeeType.Tax.ToString("D"));//个税型
        feeList = this.ReCreateFeeID(FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder).Where(parent => (!parent.UseFlag == bool.Parse(Status.True.ToString())) && (parent.ParentID == "" || parent.ParentID == null)).ToList());
        Tools.ListBoxDataBind(this.litbTaxFee, feeList, FeeInfoConst.FeeName, FeeInfoConst.FeeID);

        Tools.ListBoxDataBindByEnum(this.litbFunction, typeof(FeeFunction), false);//函数
    }

 public Int32 InsertPayMonthInfo(String yearMonth, bool rewrite)
 {
     using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Required))
     {
         StringBuilder sql = new StringBuilder();
         WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
         if (rewrite)
         {
             builder.AppendItem(PayMonthInfoDBConst.YearMonth, yearMonth);
             this.DeletePayMonthInfo(builder);
             //工资月表
             foreach (PayMonthInfo payMonthInfo in PersonBaseFeeInfoAdapter.Instance.GetPersonBaseFeeInfoList(PersonBaseFeeTarget.PayMonth, yearMonth))
             {
                 sql.Append(ORMapping.GetInsertSql(payMonthInfo, BuilderEx.TSqlBuilderInstance));
                 sql.Append(";");
             }
         }
         else
         {
             builder.AppendItem(String.Concat(PersonInfoDBConst.EntryDate, "<= DATEADD(Day,-1,CONVERT(CHAR(8),DATEADD(Month,1,'", yearMonth, "01'),120)+'1')"));
             builder.AppendItem(String.Concat("ISNULL(", PersonInfoDBConst.LeftDate, ",DATEADD(Month,1,GETDATE()))"), yearMonth + "01", ">=");
             List<PersonInfo> personList = PersonInfoAdapter.Instance.GetPersonInfoList(builder);
             //工资项目月表
             foreach (FeeInfo feeInfo in FeeInfoAdapter.Instance.GetFeeInfoList(null, null))
             {
                 FeeMonthInfo feeMonthInfo = Tools.ClassDataCopy<FeeInfo, FeeMonthInfo>(feeInfo);
                 feeMonthInfo.YearMonth = yearMonth;
                 sql.Append(ORMapping.GetInsertSql(feeMonthInfo, BuilderEx.TSqlBuilderInstance));
                 sql.Append(";");
             }//人员基本工资月表
             foreach (PersonBaseFeeMonthInfo personBaseFeeMonthInfo in PersonBaseFeeInfoAdapter.Instance.GetPersonBaseFeeInfoList(PersonBaseFeeTarget.PersonBaseFeeMonth, null).Join(personList, baseFee => baseFee.PersonID, person => person.PersonID, (baseFeeDepart, person) => baseFeeDepart).ToList())
             {
                 personBaseFeeMonthInfo.YearMonth = yearMonth;
                 sql.Append(ORMapping.GetInsertSql(personBaseFeeMonthInfo, BuilderEx.TSqlBuilderInstance));
                 sql.Append(";");
                 //工资月表
                 FeeInfo feeInfo = FeeInfoAdapter.Instance.LoadFeeInfo(null, personBaseFeeMonthInfo.FeeID);
                 if (String.IsNullOrEmpty(feeInfo.CalculateExp))
                 {
                     PayMonthInfo PayMonthInfo = Tools.ClassDataCopy<PersonBaseFeeMonthInfo, PayMonthInfo>(personBaseFeeMonthInfo);
                     PayMonthInfo.YearMonth = yearMonth;
                     PayMonthInfo.PayMoney = PayMonthInfo.FeeValue;
                     sql.Append(ORMapping.GetInsertSql(PayMonthInfo, BuilderEx.TSqlBuilderInstance));
                     sql.Append(";");
                 }
             }
             //人员基本工资项目部门分摊月表
             WhereSqlClauseBuilder builderDepartment = new WhereSqlClauseBuilder();
             foreach (PersonBaseFeeDepartmentProjectInfo personFeeDepartmentProjectInfo in PersonBaseFeeDepartmentProjectInfoAdapter.Instance.GetPersonBaseFeeDepartmentProjectInfoList(null, null).Join(personList, baseFeeDepart => baseFeeDepart.PersonID, person => person.PersonID, (baseFeeDepart, person) => baseFeeDepart).ToList())
             {
                 PersonBaseFeeDepartmentProjectMonthInfo personFeeDepartmentProjectMonthInfo = Tools.ClassDataCopy<PersonBaseFeeDepartmentProjectInfo, PersonBaseFeeDepartmentProjectMonthInfo>(personFeeDepartmentProjectInfo);
                 personFeeDepartmentProjectMonthInfo.YearMonth = yearMonth;
                 sql.Append(ORMapping.GetInsertSql(personFeeDepartmentProjectMonthInfo, BuilderEx.TSqlBuilderInstance));
                 sql.Append(";");
             }
             //税率月表
             builder.Clear();
             foreach (TaxInfo taxInfo in TaxInfoAdapter.Instance.GetTaxInfoList(null, builder))
             {
                 TaxMonthInfo taxMonthInfo = Tools.ClassDataCopy<TaxInfo, TaxMonthInfo>(taxInfo);
                 taxMonthInfo.YearMonth = yearMonth;
                 sql.Append(ORMapping.GetInsertSql(taxMonthInfo, BuilderEx.TSqlBuilderInstance));
                 sql.Append(";");
             }
         }
         int result = _DataHelper.ExecuteSql(sql.ToString());
         scope.Complete();
         return result;
     }
 }

 protected void gvBaseList_DataBound(object sender, EventArgs e)
 {
     GridView gvList = (GridView)sender;
     if (!(gvList.Rows.Count == 1 && (gvList.Rows[0].Cells[0].Text == SalaryConst.EmptyText || gvList.DataKeys[0].Value == null)))
     {
         ImageButton ibtnEdit;
         Label lblFeeName;
         WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
         String strAddSpace = String.Empty;
         Double sumValue = 0.00;
         for (Int32 i = 0; i < gvList.Rows.Count; i++)
         {
             String strFeeCode = gvList.DataKeys[i].Values[FeeInfoConst.FeeCode].ToString();
             String strFeeID = gvList.DataKeys[i].Values[FeeInfoConst.FeeID].ToString();
             lblFeeName = gvList.Rows[i].FindControl("lblFeeName") as Label;
             //缩进
             strAddSpace = SalaryAppAdapter.Instance.AddSpaceInFrontOfFeeName(strFeeCode);
             lblFeeName.Text = strAddSpace + gvList.DataKeys[i].Values[FeeInfoConst.FeeName].ToString();
             //gvList.Rows[i].Cells[0].Text = strAddSpace + gvList.DataKeys[i].Values[FeeInfoConst.FeeName].ToString();
             builder.AppendItem(FeeInfoDBConst.ParentID, strFeeID);
             if (FeeInfoAdapter.Instance.GetFeeInfoList(this.yearMonth, builder).Any())
             {
                 //gvList.Rows[i].Cells[1].Text = "";
                 ibtnEdit = gvList.Rows[i].FindControl("btnCommonEdit") as ImageButton;
                 ibtnEdit.Visible = false;
                 //ibtnAddChild = gvList.Rows[i].FindControl("ibtnSave") as ImageButton;
                 //ibtnAddChild.Visible = false;
             }
             builder.Clear();
             if (strAddSpace.Length == 0)
             {
                 sumValue += Double.Parse(gvList.DataKeys[i].Values[PersonBaseFeeInfoConst.FeeValue].ToString());
             }
         }
         gvList.FooterRow.Cells[1].Text = sumValue.ToString("n");//"#,##0.00"
     }
 }