这里的查询不一定全输入条件,可以任意选择,这就涉及到数据库的写入问题,肯定得用到循环,如果有字段则加上,没有字段则空过去。
private string dishname = string.Empty;
private string dishsort = string.Empty;
private string dishtaste = string.Empty;
private string dishprice0 = string.Empty;
private string dishprice1 = string.Empty;
Dictionary<string, string> conditions = new Dictionary<string, string>();
dishprice1 = Request.QueryString["price1"] ?? "";
dishname = Request.QueryString["name"] ?? "";
dishsort = Request.QueryString["sort"] ?? "";
dishtaste = Request.QueryString["taste"] ?? "";
dishprice0 = Request.QueryString["price0"] ?? "";
private void GetSearchParameters()
{
conditions.Clear();
if (dishname != "")
conditions.Add("DishName", Server.UrlDecode(dishname));
if (dishsort != "0" && dishsort != "")
conditions.Add("SortID", dishsort);
if (dishtaste != "0" && dishsort != "")
conditions.Add("TasteID", dishtaste);
if (dishprice0 != "" && dishprice1 != "")
{
conditions.Add("Price0",dishprice0);
conditions.Add("Price1", dishprice1);
}
}
上面这些就是将有值的信息存储起来到Dictionary<string, string>(表示键和值的集合)中去,然后开始遍历寻找,根据逻辑自己编写sql语句
public static DataTable getDishesList(Dictionary<string, string> conditions, int shopid, int appid, int page, int pagesize, out int records)
{
StringBuilder OtherCon = new StringBuilder();
foreach (string key in conditions.Keys)
{
OtherCon.Append(" and ");
if (key == "Price0")
{
OtherCon.Append("Price between @Price0 and @Price1");
}
else if (key == "DishName")
{
OtherCon.Append(" DishName=@DishName");
}
else if (key == "SortID")
{
OtherCon.Append(" SortID=@SortID");
}
else if (key == "TasteID")
{
OtherCon.Append("TasteID=@TasteID");
}
else
{
OtherCon.Append("1=1");
}
}
string query = @"
select D.*,t1.TagName as unitName,t2.TagName as tasteName,t3.DishSortName ,S.ImgSrc
From(Select Row_Number() Over(Order By OrderID asc, CreateTime desc) As Rowid,*
From Dish
Where ShopID=@ShopID and AppID=@AppID " + OtherCon.ToString() + @"
) As D
left join DishTag as t1 on D.UnitID=t1.TagID and t1.ShopID=D.ShopID and t1.AppID=D.AppID
left join DishTag as t2 on D.TasteID=t2.TagID and t2.ShopID=D.ShopID and t2.AppID=D.AppID
left join DishSort as t3 on D.SortID=t3.DishSortID and t3.ShopID=D.ShopID and t3.AppID=D.AppID
left join StoreImg S on D.DishID= S.TargetID and D.ShopID=s.ShopID and S.ImgType='菜品' Where Rowid Between @b And @e;
Select Count(1) From Dish Where ShopID=@ShopID and AppID=@AppID " + OtherCon.ToString() + @"
";
DbCommand cmd = Db_CanYin.GetSqlStringCommand(query);
Db_CanYin.AddInParameter(cmd, "@ShopID", DbType.Int32, shopid);
Db_CanYin.AddInParameter(cmd, "@AppID", DbType.Int32, appid);
foreach (string key in conditions.Keys)
{
if (key == "DishName")
{
Db_CanYin.AddInParameter(cmd, "@" + key, DbType.String, conditions[key]);
}
else if (key == "Price0" || key == "Price1")
{
Db_CanYin.AddInParameter(cmd, "@" + key, DbType.Decimal, conditions[key]);
}
else
{
Db_CanYin.AddInParameter(cmd, "@" + key, DbType.Int32, conditions[key]);
}
}
Db_CanYin.AddInParameter(cmd, "@b", DbType.Int32, (page - 1) * pagesize + 1);
Db_CanYin.AddInParameter(cmd, "@e", DbType.Int32, page * pagesize);
DataSet ds = Db_CanYin.ExecuteDataSet(cmd);
records = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return ds.Tables[0];
}