/// <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);
}