Linq to Sql 多条件查询

Linq To Sql 多条件查询

string proName = this.txtName.Text.Trim();
string lowPrice = this.txtLowPrice.Text.Trim();
string highPrice = this.txtHighPrice.Text.Trim();

decimal? lowPrice1 = null, highPrice1 = null;
if (!string.IsNullOrEmpty(lowPrice))
{
        lowPrice1 = decimal.Parse(lowPrice);
}
if (!string.IsNullOrEmpty(highPrice))
{
        highPrice1 = decimal.Parse(highPrice);
}

var data = from p in dataContext.TblCate
                join g in dataContext.TblCategory on p.intCategoryId equals g.intCategoryId
                join b in dataContext.TblBrand on p.intBrandId equals b.intBrandId
                where string.IsNullOrEmpty(proName) || p.chvCateName.Contains(proName)
                where lowPrice1 == null || p.mnyCatePrice >= lowPrice1
                where highPrice1 == null || p.mnyCatePrice < highPrice1
                select new
                 {
                          intCateId = p.intCateId,
                          chvCateName = p.chvCateName,
                          mnyCatePrice = p.mnyCatePrice,
                          chvCateDescript = p.chvCateDescript,
                          chvCategoryName = g.chvCategoryName,
                          chvBrandName = b.chvBrandName
                  };

多条件查询SQL语句

string sql = @"select pro.intCateId, pro.chvCateName, pro.mnyCatePrice, pro.chvCateDescript, cate.chvCategoryName, brand.chvBrandName
                     from TblCate as pro
                     inner join TblCategory as cate on pro.intCategoryId = cate.intCategoryId
                     inner join TblBrand as brand on pro.intBrandId=brand.intBrandId
                     where (@proName='' or pro.chvCateName like '%' + @proName + '%')
                     and (@lowPrice='' or pro.mnyCatePrice>=@lowPrice)
                     and (@highPrice='' or pro.mnyCatePrice between @lowPrice and @highPrice)";

data = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, sql,
           new SqlParameter("@proName", SqlDbType.NVarChar) { Value = proName },
           new SqlParameter("@lowPrice", lowPrice),
           new SqlParameter("@highPrice", highPrice));