/// <summary> /// 根据名字查询用户信息 /// </summary> /// <param name="name"></param> /// <returns></returns> public ManagerInfo GetByName(string name) { //定义一个对象 ManagerInfo mi = null; //构造要查询的sql语句 string sql = "SELECT MID,MName,MPwd,MType FROM ManagerInfo where MName=@MName"; MySqlParameter p = new MySqlParameter("@MName", name); //使用helper进行查询,得到结果 DataTable dt = MysqlHelper.GetDataTable(sql, p); //判断是否查找到了 if (dt.Rows.Count > 0) { //用户名是存在的 mi = new ManagerInfo() { MId = int.Parse(dt.Rows[0]["MId"].ToString()), //mysql下,不能用 MName=row["MName"].ToString(), MName = name, MPwd = dt.Rows[0]["MPwd"].ToString(), MType = int.Parse(dt.Rows[0]["MType"].ToString()) }; } return(mi); }
/// <summary> /// 列表查询 /// </summary> /// <param name="dic"></param> /// <returns></returns> public List <MemberInfo> GetList(Dictionary <string, string> dic) { //构造查询sql语句,连接查询得到会员类型的名称 string sql = "SELECT mi.MId,mi.MName,mti.MTitle AS MTypeTitle,mi.MPhone,mi.MMoney,mi.MTypeId,mti.MDiscount " + " FROM MemberInfo AS mi " + " INNER JOIN MemberTypeInfo AS mti " + " ON mi.MTypeId=mti.MId " + " WHERE mi.MIsDelete=0"; //拼接查询条件 List <MySqlParameter> listP = new List <MySqlParameter>(); if (dic.Count > 0) { foreach (var pair in dic) { //sql+=" AND "+pair.Key+" LIKE '%"+pair.Value+"%'"; //写成参数化,防注入 sql += " AND mi." + pair.Key + " LIKE @" + pair.Key; listP.Add(new MySqlParameter("@" + pair.Key, "%" + pair.Value + "%")); } } //查询排序 sql += " ORDER BY mi.MId"; //执行查询 DataTable dt = MysqlHelper.GetDataTable(sql, listP.ToArray()); //定义list,完成转存 List <MemberInfo> list = new List <MemberInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new MemberInfo() { MId = Convert.ToInt32(row["MId"]), MName = row["MName"].ToString(), MPhone = row["MPhone"].ToString(), MMoney = Convert.ToDecimal(row["MMoney"]), MTypeId = Convert.ToInt32(row["MTypeId"]), MTypeTitle = row["MTypeTitle"].ToString(), MDiscount = Convert.ToDecimal(row["MDiscount"]) }); } return(list); }
/// <summary> /// 获取列表 /// </summary> /// <returns></returns> public List <DishTypeInfo> GetList() { //构造查询sql语句 string sql = "SELECT DId, DTitle, DIsDelete FROM DishTypeInfo WHERE DIsDelete=0"; //执行查询,取得表格 DataTable dt = MysqlHelper.GetDataTable(sql); //定义列表,遍历表格,填充列表并返回 List <DishTypeInfo> list = new List <DishTypeInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new DishTypeInfo() { DId = Convert.ToInt32(row["DId"]), DTitle = row["DTitle"].ToString() }); } return(list); }
/// <summary> /// 查询未删除数据 /// </summary> /// <returns></returns> public List <MemberTypeInfo> GetList() { //构造查询sql语句 string sql = "SELECT MID,MTitle,MDiscount,MIsDelete FROM MemberTypeInfo WHERE MIsDelete=0"; //执行查询,返回表格 DataTable dt = MysqlHelper.GetDataTable(sql); //遍历表格,填充列表并返回 List <MemberTypeInfo> list = new List <MemberTypeInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new MemberTypeInfo() { MId = Convert.ToInt32(row["MId"]), MTitle = row["MTitle"].ToString(), MDiscount = Convert.ToDecimal(row["MDiscount"]) }); } return(list); }
/// <summary> /// 获取列表 /// </summary> /// <returns></returns> public List <HallInfo> GetList() { //构造sql语句 string sql = "SELECT HId, HTitle, HIsDelete FROM HallInfo WHERE HIsDelete=0"; //执行,并得到表格 DataTable dt = MysqlHelper.GetDataTable(sql); //定义列表对象,遍历表格,填充列表并返回 List <HallInfo> list = new List <HallInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new HallInfo() { HId = Convert.ToInt32(row["HId"]), HTitle = row["HTitle"].ToString() }); } return(list); }
/// <summary> /// 获取列表 /// </summary> /// <param name="dic"></param> /// <returns></returns> public List <DishInfo> GetList(Dictionary <string, string> dic) { //构造sql语句 string sql = "SELECT di.DId, di.DTitle, di.DTypeId, di.DPrice, di.DChar,dti.DTitle AS DTypeTitle, di.DIsDelete " + " FROM DishInfo AS di " + " INNER JOIN DishTypeInfo AS dti " + " ON di.DTypeId=dti.DId " + " WHERE di.DIsDelete=0 AND dti.DIsDelete=0 "; //拼接筛选条件 List <MySqlParameter> psList = new List <MySqlParameter>(); if (dic.Count > 0) { foreach (var pair in dic) { sql += " AND " + pair.Key + " LIKE @" + pair.Key; psList.Add(new MySqlParameter("@" + pair.Key, "%" + pair.Value + "%")); } } //查询排序 sql += " ORDER BY di.DId"; //执行并等到表格 DataTable dt = MysqlHelper.GetDataTable(sql, psList.ToArray()); //定义列表,遍历表格,填充列表并返回 List <DishInfo> list = new List <DishInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new DishInfo() { DId = Convert.ToInt32(row["DId"]), DTitle = row["DTitle"].ToString(), DChar = row["DChar"].ToString(), DPrice = Convert.ToDecimal(row["DPrice"]), DTypeId = Convert.ToInt32(row["DTypeId"]), DTypeTitle = row["DTypeTitle"].ToString() }); } return(list); }
/// <summary> /// 查询获取结果集 /// </summary> /// <returns></returns> public List <ManagerInfo> GetList() { //构造要查询的sql语句 string sql = "SELECT MID,MName,MPwd,MType FROM ManagerInfo"; //使用helper进行查询,得到结果 DataTable dt = MysqlHelper.GetDataTable(sql); //将dt中的数据转存到list中 List <ManagerInfo> list = new List <ManagerInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new ManagerInfo() { MId = int.Parse(row["MId"].ToString()), MName = row["MName"].ToString(), MPwd = row["MPwd"].ToString(), MType = int.Parse(row["MType"].ToString()) }); } //将集合list返回 return(list); }
/// <summary> /// 获取列表 /// </summary> /// <returns></returns> public List <TableInfo> GetList(Dictionary <string, string> dic) { //构造sql语句 string sql = "SELECT ti.TId, ti.TTitle, ti.THallId, ti.TIsFree, ti.TIsDelete, hi.HTitle AS HallTitle " + "FROM TableInfo AS ti " + "INNER JOIN HallInfo AS hi " + "ON ti.THallId=hi.HId " + "WHERE ti.TIsDelete=0 AND hi.HIsDelete=0"; //拼接筛选条件 List <MySqlParameter> listP = new List <MySqlParameter>(); if (dic.Count > 0) { foreach (var pair in dic) { sql += " AND " + pair.Key + "=@" + pair.Key; listP.Add(new MySqlParameter("@" + pair.Key, pair.Value)); } } //查询排序 sql += " ORDER BY ti.TId"; //执行,得到表格 DataTable dt = MysqlHelper.GetDataTable(sql, listP.ToArray()); //定义列表,遍历表格,填充列表并返回 List <TableInfo> list = new List <TableInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new TableInfo() { TId = Convert.ToInt32(row["TId"]), TTitle = row["TTitle"].ToString(), HallTitle = row["HallTitle"].ToString(), TIsFree = Convert.ToInt32(row["TIsFree"]) }); } return(list); }
/// <summary> /// 获取订单的菜单列表 /// </summary> /// <param name="orderId"></param> /// <returns></returns> public List <OrderDetailInfo> GetDetailList(int orderId) { string sql = "SELECT odi.OId, di.DTitle, di.DPrice, odi.Count FROM OrderDetailInfo AS odi " + " INNER JOIN DishInfo AS di " + " ON odi.DishId=di.DId " + " WHERE odi.OrderId=@OrderId"; MySqlParameter p = new MySqlParameter("@OrderId", orderId); DataTable dt = MysqlHelper.GetDataTable(sql, p); List <OrderDetailInfo> list = new List <OrderDetailInfo>(); foreach (DataRow row in dt.Rows) { list.Add(new OrderDetailInfo() { OId = Convert.ToInt32(row["OId"]), DTitle = row["DTitle"].ToString(), DPrice = Convert.ToDecimal(row["DPrice"]), Count = Convert.ToInt32(row["Count"]) }); } return(list); }