关于信息查询和相关的数据库操作语句的写法

这里的查询不一定全输入条件，可以任意选择，这就涉及到数据库的写入问题，肯定得用到循环，如果有字段则加上，没有字段则空过去。

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];
        }