多条件查询(Sql拼接)

最新推荐文章于 2023-08-10 14:04:58 发布

飞翔ing

最新推荐文章于 2023-08-10 14:04:58 发布

阅读量1k

点赞数

分类专栏： C#—WinForm

本文链接：https://blog.csdn.net/Yanweijie1/article/details/8473487

版权

C#—WinForm 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

多条件查询(Sql拼接)

关于日期区间的查询

    #region 日期
    string abcd = string.Empty;
    string yy = this.datime2.Value.Year.ToString();
    string mm = this.datime2.Value.Month.ToString();
    string day = this.datime2.Value.Day.ToString();


    if (mm.Equals("2") && day.Equals("28") || mm.Equals("2") && day.Equals("29") || mm.Equals("2") && day.Equals("30"))
    {
        abcd = yy + "/" + 3 + "/" + 1;
    }
    else if (mm.Equals("1") && day.Equals("31") || mm.Equals("3") && day.Equals("31") || mm.Equals("5") && day.Equals("31") || mm.Equals("7") && day.Equals("31") || mm.Equals("8") && day.Equals("31") || mm.Equals("10") && day.Equals("31"))
    {
        abcd = yy + "/" + (Convert.ToInt32(mm) + 1).ToString() + "/" + 1;
    }
    else if (mm.Equals("12") && day.Equals("31"))
    {
        abcd = (Convert.ToInt32(yy) + 1) + "/" + 1 + "/" + 1;
    }
    else if (mm.Equals("4") && day.Equals("30") || mm.Equals("6") && day.Equals("30") || mm.Equals("9") && day.Equals("30") || mm.Equals("11") && day.Equals("30"))
    {
        abcd = yy + "/" + (Convert.ToInt32(mm) + 1).ToString() + "/" + 1;
    }
    else
    {
        string days = (Convert.ToInt32(day) + 1).ToString();
        abcd = yy + "/" + mm + "/" + days;
    }
    #endregion 日期

    smhandOverLineModel.lastModifiedS = Convert.ToDateTime(abcd);

DAL层拼接查询

        /// <summary>
        /// 根据多条件进行高级查询
        /// </summary>
        /// <param name="smhandOverModel"></param>
        /// <returns></returns>
        public IList<SmhandOverLineModel> SelectSmhandOverSerach(SmhandOverLineModel smhandOverLineModel)
        {
            string sql = "select productcode,productname,sum(realqty) realqty,state,lastmodified from SmhandOverLine where 1 = 1 ";
            if (smhandOverLineModel != null)
            {
                if (smhandOverLineModel.productCode != null && !smhandOverLineModel.productCode.Trim().Equals(""))
                {
                    sql += string.Format(" and productCode = '{0}'", smhandOverLineModel.productCode);
                }
                if (smhandOverLineModel.lastModified != null && smhandOverLineModel.lastModified.ToString() != "")
                {
                    sql += string.Format("and lastModified between '{0}' and '{1}'", smhandOverLineModel.lastModified, smhandOverLineModel.lastModifiedS);
                }
                if (smhandOverLineModel.state != null && !smhandOverLineModel.state.Trim().Equals(""))
                {
                    sql += string.Format(" and state = '{0}'", smhandOverLineModel.state);
                }
            }
            sql += "group by  productcode,productname,state,lastmodified order by productcode";
            return base.SelectIn<SmhandOverLineModelPurchase>(sql);
        }