public List <DishInfo> Getlist(Dictionary <string, string> dic)
{
string sql = "SELECT DishInfo.*,DishTypeInfo.DTitle AS DTypeTitle FROM DishInfo INNER JOIN DishTypeInfo ON DishInfo.DTypeId=DishTypeInfo.DId WHERE DishInfo.DIsDelete = 0";
if (dic.Count > 0)
{
foreach (var item in dic)
{
sql = sql + " AND " + item.Key + " LIKE '%" + item.Value + "%'";
}
}
List <DishInfo> list = new List <DishInfo>();
DataTable dt = SqliteHelper.GetDataTable(sql);
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"]),
DIsDelete = Convert.ToBoolean(row["DIsDelete"]),
DTypeId = Convert.ToInt32(row["DTypeId"]),
DTypeTitle = row["DTypeTitle"].ToString()
});
}
return(list);
}
public ManagerInfo Login(string name)
{
ManagerInfo manager = null;
string sql = "select * from ManagerInfo where MName=@name";
SQLiteParameter[] param =
{
new SQLiteParameter("@name", name)
};
DataTable dt = SqliteHelper.GetDataTable(sql, param);
if (dt.Rows.Count > 0)
{
manager = new ManagerInfo()
{
MId = Convert.ToInt32(dt.Rows[0][0]),
MName = name,
MPwd = dt.Rows[0][2].ToString(),
MType = Convert.ToInt32(dt.Rows[0][3])
};
}
else
{
}
return(manager);
}
/// <summary>
/// 刷新并返回查询结果
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public List <VIPInfo> GetList(Dictionary <string, string> dic)
{
string sql = "select mi.*,mti.mTitle " +
"from MemberInfo as mi " +
"inner join MemberTypeInfo as mti " +
"on mi.mTypeId=mti.mid " +
"where mi.mIsDelete=0 ";
List <SQLiteParameter> sp = new List <SQLiteParameter>();
if (dic != null)
{
foreach (var pair in dic)
{
sql += "and " + pair.Key + " like @" + pair.Key;
sp.Add(new SQLiteParameter("@" + pair.Key, "%" + pair.Value + "%"));
}
}
DataTable table = SqliteHelper.GetDataTable(sql, sp.ToArray());
var rows = table.Rows;
List <VIPInfo> list = new List <VIPInfo>();
foreach (DataRow row in rows)
{
list.Add(new VIPInfo()
{
MId = Convert.ToInt32(row["mid"]),
MTypeId = Convert.ToInt32(row["mtypeid"]),
MName = row["mname"].ToString(),
MPhone = row["mphone"].ToString(),
MCount = Convert.ToDecimal(row["mmoney"]),
MTypeTitle = row["mtitle"].ToString()
});
}
return(list);
}
/// <summary>
/// 根据用户名查找对象
/// </summary>
/// <param name="name">string name</param>
/// <returns>ManagerInfo 返回null为未找到对象</returns>
public ManagerInfo GetByName(string name)
{
//定义一个对象
ManagerInfo mi = null;
//构造sql语句和参数
string sql = "SELECT * FROM ManagerInfo WHERE MName = @name";
SQLiteParameter p = new SQLiteParameter("@name", name);
//执行查询得到结果
DataTable dt = SqliteHelper.GetDataTable(sql, p);
//判断是否根据用户名查找到了对象
if (dt.Rows.Count > 0)
{
//用户名存在
mi = new ManagerInfo()
{
MId = Convert.ToInt32(dt.Rows[0][0]),
MName = name,
MPwd = dt.Rows[0][2].ToString(),
MType = Convert.ToInt32(dt.Rows[0][3])
};
}
else
{
//用户名不存在
}
return(mi);
}
/// <summary>
/// Retrieve TableInfo DataGridView
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public List <TableInfo> GetList(Dictionary <string, string> dic)
{
string sql = "SELECT ti.TId, ti.TTitle, ti.THallId, ti.TIsFree ,ti.TIsDelete, hi.HTitle FROM TableInfo AS ti INNER JOIN HallInfo AS hi ON ti.THallId = hi.HId WHERE ti.TIsDelete = 1 AND hi.HIsDelete = 1";
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " AND " + pair.Key + " = '" + pair.Value + "' ";
}
}
DataTable dt = SqliteHelper.GetDataTable(sql);
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(),
THallId = Convert.ToInt32(row["THallId"]),
TIsFree = Convert.ToBoolean(row["TIsFree"]),
HallTitle = row["HTitle"].ToString(),
TIsDelete = Convert.ToBoolean(row["TIsDelete"])
});
}
return(list);
}
public List <OrderDetailInfo> GetDetailList(int orderId)
{
//throw new NotImplementedException();
string sql = @"select odi.oid,di.dTitle,di.dPrice,odi.count from dishinfo as di
inner join orderDetailInfo as odi
on di.did=odi.dishid
where odi.orderId=@orderid";
SQLiteParameter p = new SQLiteParameter("@orderid", orderId);
DataTable dt = SqliteHelper.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);
}
public List<MemberInfo> SelectAll(Dictionary<string, string> dic)
{
string sql = "select m.*,t.MTitle from MemberInfo m left join memberTypeInfo t on m.Mtypeid=t.mid where m.misdelete=0";
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " and " + pair.Key + " like '%" + pair.Value + "%'";
}
}
List<MemberInfo> list = new List<MemberInfo>();
DataTable table = SqliteHelper.GetDataTable(sql);
foreach (DataRow row in table.Rows)
{
list.Add(new MemberInfo()
{
MId = Convert.ToInt32(row["MId"]),
MName = row["MName"].ToString(),
MPhone = row["MPhone"].ToString(),
MMoney = Convert.ToDecimal(row["MMoney"]),
MType = row["MTitle"].ToString()
});
}
return list;
}
/// <summary>
/// 根据用户名查找对象
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public ManagerInfo GetByName(string name)
{
//定义一个对象
ManagerInfo mi = null;
//构造语句
string sql = "select * from ManagerInfo where mname=@name";
//为语句构造参数
SQLiteParameter p = new SQLiteParameter("@name", name);
//执行查询得到结果
DataTable dt = SqliteHelper.GetDataTable(sql, p);
//判断是否根据用户名查找到了对象
if (dt.Rows.Count > 0)
{
//用户名存在
mi = new ManagerInfo()
{
MId = Convert.ToInt32(dt.Rows[0][0]),
MName = name,
Mpwd = dt.Rows[0][2].ToString(),
Mtype = Convert.ToInt32(dt.Rows[0][3])
};
}
else
{
//用户名不存在
}
return(mi);
}
public List <MemberInfo> GetInfo(Dictionary <string, string> dic)
{
string sql = "SELECT MemberInfo.*,MemberTypeInfo.MTitle as MTypeTitle FROM MemberInfo inner join MemberTypeInfo on MemberInfo.MTypeID=MemberTypeInfo.MId WHERE MemberInfo.MIsDelete = 0";
if (dic.Count > 0)
{
foreach (var item in dic)
{
sql = sql + " and " + item.Key + " like '%" + item.Value + "%'";
}
}
DataTable dt = SqliteHelper.GetDataTable(sql);
List <MemberInfo> list = new List <MemberInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new MemberInfo()
{
MId = Convert.ToInt32(row["MId"]),
MTypeId = Convert.ToInt32(row["MTypeId"]),
MName = row["MName"].ToString(),
MPhone = row["MPhone"].ToString(),
MMoney = Convert.ToDecimal(row["MMoney"]),
MIsdelete = Convert.ToBoolean(row["MIsdelete"]),
MTypeTitle = row["MTypeTitle"].ToString()
});
}
return(list);
}
//查询，搜索
public List <TableInfo> GetList(Dictionary <string, string> dic)
{
string sql = @"select ti.*, hi.HTitle from tableinfo as ti
inner join hallinfo as hi
on ti.THallId = hi.HId
where ti.TIsDelete = 0 and hi.HIsDelete = 0";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " and " + pair.Key + "=@" + pair.Key;
listP.Add(new SQLiteParameter("@" + pair.Key, pair.Value));
}
}
DataTable dt = SqliteHelper.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["htitle"].ToString(),//datagridview显示厅包项
THallId = Convert.ToInt32(row["thallId"]),
TIsFree = Convert.ToBoolean(row["tIsFree"])
});
}
return(list);
}
/// <summary>
/// 初始化数据
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public List <DishInfo> GetList(Dictionary <string, string> dic)
{
string sql = @"select di.*,dti.dtitle as dtypetitle from dishinfo as di inner join dishtypeinfo as dti on di.dtypeid=dti.did where di.disdelete=0 and dti.disdelete=0";
List <SQLiteParameter> sp = new List <SQLiteParameter>();
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " and di." + pair.Key + " like @" + pair.Key;
sp.Add(new SQLiteParameter("@" + pair.Key, "%" + pair.Value + "%"));
}
}
DataTable table = SqliteHelper.GetDataTable(sql, sp.ToArray());
List <DishInfo> list = new List <DishInfo>();
foreach (DataRow row in table.Rows)
{
list.Add(new DishInfo()
{
DId = Convert.ToInt32(row["did"]),
DTitle = row["dtitle"].ToString(),
DTypeTitle = row["dtypetitle"].ToString(),
DPrice = Convert.ToDecimal(row["dprice"]),
DChar = row["dchar"].ToString()
});
}
return(list);
}
public List <DishInfo> GetList(Dictionary <string, string> dict)
{
string sql = "select di.*,dti.Dtitle as DTypeTitle from DishInfo di inner join DishTypeInfo dti on di.DTypeId=dti.DId " +
" where di.DIsDelete=0 and dti.DIsDelete=0";
if (dict.Count > 0)
{
foreach (var parm in dict)
{
if (parm.Key == "di.Dtitle")
{
sql += " and " + parm.Key + " like '%" + parm.Value + "%' ";
}
if (parm.Key == "di.DTypeId")
{
sql += " and " + parm.Key + "=" + parm.Value;
}
}
}
DataTable dt = SqliteHelper.GetDataTable(sql);
List <DishInfo> list = new List <DishInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new DishInfo()
{
DId = Convert.ToInt32(row["did"]),
DChar = row["dchar"].ToString(),
DPrice = Convert.ToDecimal(row["DPrice"]),
DTypeTitle = row["DTypeTitle"].ToString(),
DTitle = row["DTitle"].ToString()
});
}
return(list);
}
//CRUD操作 Create(增加)/Retrieve(取回)/Update(修改)/Delete(删除)
//retrieve
public List <DishInfo> GetList(Dictionary <string, string> dic)
{
string sql = "SELECT " +
"di.DId, di.DTypeId, di.DTitle, dti.DTitle AS DTypeTitle, di.DChar, di.DPrice, di.DIsDelete " +
"FROM DishInfo AS di INNER JOIN DishTypeInfo AS dti ON di.DTypeId = dti.DId " +
"WHERE di.DIsDelete = 1 AND dti.DIsDelete =1";
if (dic.Count != 0)
{
foreach (var pair in dic)
{
sql += " AND di." + pair.Key + " LIKE '%" + pair.Value + "%'";
}
}
DataTable dt = SqliteHelper.GetDataTable(sql);
List <DishInfo> list = new List <DishInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new DishInfo()
{
DId = Convert.ToInt32(row["DId"]),
DTypeId = Convert.ToInt32(row["DTypeId"]),
DTitle = row["DTitle"].ToString(),
DTypeTitle = row["DTypeTitle"].ToString(),
DChar = row["DChar"].ToString(),
DPrice = Convert.ToDecimal(row["DPrice"])
});
}
return(list);
}
/// <summary>
/// 初始化查询数据
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public List <TableInfo> GetList(Dictionary <string, string> dic)
{
string sql = "select ti.*,hi.htitle from tableinfo as ti inner join hallinfo as hi on ti.thallid=hi.hid where ti.tisdelete=0 and hi.hisdelete=0";
List <SQLiteParameter> sp = new List <SQLiteParameter>();
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " and " + pair.Key + "=@" + pair.Key;
sp.Add(new SQLiteParameter("@" + pair.Key, pair.Value));
}
}
DataTable table = SqliteHelper.GetDataTable(sql, sp.ToArray());
List <TableInfo> list = new List <TableInfo>();
foreach (DataRow row in table.Rows)
{
list.Add(new TableInfo()
{
TId = Convert.ToInt32(row["tid"]),
TTitle = row["ttitle"].ToString(),
THallId = Convert.ToInt32(row["thallid"]),
TIsFree = Convert.ToBoolean(row["tisfree"]),
THalltitle = row["htitle"].ToString()
});
}
return(list);
}
public List <TableInfo> GetList(Dictionary <string, string> dic)
{
string sql = "select T.* ,H.HTitle TType from TableInfo T left join HallInfo H on T.THallId==H.Hid where T.TIsDelete=0 and H.HIsDelete=0";
if (dic.Count > 0)
{
foreach (var key in dic)
{
sql += " and " + key.Key + "=" + key.Value;
}
}
List <TableInfo> list = new List <TableInfo>();
DataTable table = SqliteHelper.GetDataTable(sql);
foreach (DataRow row in table.Rows)
{
list.Add(new TableInfo()
{
TId = Convert.ToInt32(row["TId"]),
TTitle = row["TTitle"].ToString(),
TType = row["TType"].ToString(),
TIsFree = Convert.ToBoolean(row["TIsFree"])
});
}
return(list);
}
public List <MemberInfo> GetList(Dictionary <string, string> dic)
{
//连接查询，得到会员的名字
string sql = "select mi.*, mti.mTitle as MTypeTitle from MemberInfo as mi " +
"inner join MemberTypeInfo as mti on mi.mTypeId=mti.mid " +
"where mi.mIsDelete=0";
//拼接条件
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " and " + pair.Key + " like '%" + pair.Value + "%'";
}
}
//执行得到结果集
DataTable dt = SqliteHelper.GetDataTable(sql);
//定义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()
});
}
return(list);
}
public List <MemberInfo> GetList(Dictionary <string, string> dic)
{
//连接查询,得到会员类型的名字
string sql = "SELECT mi.MId, mi.MName, mi.MPhone, mi.MMoney, mi.MTypeId, mti.MDiscount, mi.MIsDelete, mti.MTitle AS MTypeTitle FROM MemberInfo AS mi INNER JOIN MemberTypeInfo AS mti ON mi.MTypeId = mti.MId WHERE mi.MIsDelete = 1";
//拼接条件
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " AND " + pair.Key + " Like '%" + pair.Value + "%'";
}
}
//执行得到结果集
DataTable dt = SqliteHelper.GetDataTable(sql);
//定义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);
}
public List <DishInfo> GetList(Dictionary <string, string> dic)
{
List <DishInfo> list = new List <DishInfo>();
string sql = "select di.*,type.DTitle as DType from DishInfo as di left join DishTypeInfo as type on di.dtypeid=type.did where di.disdelete=0 and type.disdelete=0";
if (dic != null)
{
if (dic.Count > 0)
{
foreach (var pair in dic)
{
sql += " and " + pair.Key + " like '%" + pair.Value + "%'";
}
}
}
DataTable table = SqliteHelper.GetDataTable(sql);
foreach (DataRow row in table.Rows)
{
list.Add(new DishInfo()
{
DId = Convert.ToInt32(row["DId"]),
DTitle = row["DTitle"].ToString(),
DType = row["DType"].ToString(),
DPrice = Convert.ToDecimal(row["DPrice"]),
DChar = row["DChar"].ToString()
});
}
return(list);
}
/// <summary>
/// 查询会员信息
/// </summary>
/// <param name="dictionary">根据用户名和手机号对会员进行搜索</param>
/// <returns></returns>
public List <MemberInfo> GetList(Dictionary <string, string> dictionary)
{
string sql = "select mi.*,mti.MTitle as MTypeTitle " +
"from MemberInfo as mi " +
"inner join MemberTypeInfo as mti " +
"on mi.MTypeId = mti.MId " +
"where mi.mIsDelete=0 ";
if (dictionary.Count > 0)
{
foreach (var pair in dictionary)
{
sql += " and " + pair.Key + " like'%" + pair.Value + "%'";
}
}
DataTable table = SqliteHelper.GetDataTable(sql);
List <MemberInfo> list = new List <MemberInfo>();
foreach (DataRow row in table.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()
});
}
return(list);
}
/// <summary>
/// Get an user object by a name
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public ManagerInfo GetByName(string name)
{
ManagerInfo mi = null;
//Construct the sql query statement
string sql = "select * from managerInfo where mname=@name";
//Set the parameter for the query
SQLiteParameter p = new SQLiteParameter("@name", name);
//Execute the query
DataTable dt = SqliteHelper.GetDataTable(sql, p);
if (dt.Rows.Count > 0)
{
mi = new ManagerInfo()
{
MId = Convert.ToInt32(dt.Rows[0][0]),
MName = name,
MPwd = dt.Rows[0][2].ToString(),
MType = Convert.ToInt32(dt.Rows[0][3])
};
//type = mi.MType;
}
else
{
Console.WriteLine("The user does not exist");
//type = 0;
}
return(mi);
}
//查询所有会员
public List <MemberInfo> GetList(Dictionary <string, string> dict)
{
string sql = "select mi.*,mti.MTitle as mTypeTitle from memberinfo mi" +
" inner join membertypeinfo mti where mi.MTypeId=mti.MId and mi.MIsDelete=0";
if (dict.Count > 0)
{
foreach (KeyValuePair <string, string> pari in dict)
{
sql += " and " + pari.Key + " like '%" + pari.Value + "%'";
}
}
List <MemberInfo> list = new List <MemberInfo>();
DataTable dt = SqliteHelper.GetDataTable(sql);
foreach (DataRow row in dt.Rows)
{
MemberInfo mi = new MemberInfo()
{
MId = Convert.ToInt32(row["mid"]),
MMoney = Convert.ToDecimal(row["mmoney"]),
MName = row["mname"].ToString(),
MPhone = row["mphone"].ToString(),
MTypeTitle = row["mTypeTitle"].ToString()
};
list.Add(mi);
}
return(list);
}
public List <DishTypeInfo> GetList()
{
string sql = "select * from DishTypeInfo where dIsDelete=0";
DataTable table = SqliteHelper.GetDataTable(sql);
List <DishTypeInfo> list = new List <DishTypeInfo>();
foreach (DataRow row in table.Rows)
{
list.Add(new DishTypeInfo()
{
DId = Convert.ToInt32(row["did"]),
DTitle = row["dtitle"].ToString()
});
}
return(list);
}
//查询
public List <HallInfo> GetList()
{
string sql = "select * from HallInfo where HisDelete=0";
DataTable dt = SqliteHelper.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);
}
//获取用户信息
public List <ManagerInfo> selectAllManagerInfo()
{
string sql = "select * from ManagerInfo";
DataTable dt = SqliteHelper.GetDataTable(sql);
List <ManagerInfo> list = new List <ManagerInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new ManagerInfo {
MId = Convert.ToInt32(row["mid"]),
MName = row["mname"].ToString(),
MPwd = row["mpwd"].ToString(),
MType = Convert.ToInt32(row["mtype"])
});
}
return(list);
}
public List <RemoveInfo> GetList()
{
List <RemoveInfo> list = new List <RemoveInfo>();
string sql = "select * from RemoveInfo";
DataTable table = SqliteHelper.GetDataTable(sql);
foreach (DataRow row in table.Rows)
{
list.Add(new RemoveInfo()
{
RId = Convert.ToInt32(row["RId"]),
RName = row["RName"].ToString(),
RTable = row["RTable"].ToString()
});
}
return(list);
}
public List <DishTypeInfo> GetList()
{
string sql = "SELECT DId, DTitle, DIsDelete FROM DishTypeInfo WHERE DIsDelete = 1";
DataTable dt = SqliteHelper.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);
}
public List <MemberTypeInfo> GetList()
{
string sql = "select * from MemberTypeInfo where MIsDelete=0";
List <MemberTypeInfo> list = new List <MemberTypeInfo>();
DataTable table = SqliteHelper.GetDataTable(sql);
foreach (DataRow row in table.Rows)
{
list.Add(new MemberTypeInfo()
{
MId = Convert.ToInt32(row[0]),
MTitle = row[1].ToString(),
MDiscount = Convert.ToDecimal(row[2])
});
}
return(list);
}
//GetList
public List <MemberTypeInfo> GetList()
{
string sql = "select * from membertypeinfo where mIsDelete= 0";
DataTable dt = SqliteHelper.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);
}
public List <MemberInfo> GetList(Dictionary <string, string> dic)
{
//连接查询，得到会员类型的名字
string sql = "select mi.*,mti.mTitle as MTypeTitle,mti.mDiscount " +
"from MemberInfo as mi " +
"inner join MemberTypeInfo as mti " +
"on mi.mTypeId=mti.mid " +
"where mi.mIsDelete=0 and mti.mIsDelete=0";
// +"and mname like '%sadf%'";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
//拼接条件
if (dic.Count > 0)
{
foreach (var pair in dic)
{
//" and mname like @mname"
sql += " and mi." + pair.Key + " like @" + pair.Key;
//@mname,'%abc%'
listP.Add(new SQLiteParameter("@" + pair.Key, "%" + pair.Value + "%"));
}
}
//执行得到结果集
DataTable dt = SqliteHelper.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);
}
//GetList
public List <ManagerInfo> GetList()
{
string sql = "select * from ManagerInfo";
DataTable dt = SqliteHelper.GetDataTable(sql);
List <ManagerInfo> list = new List <ManagerInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new ManagerInfo()
{
Mid = Convert.ToInt32(row["Mid"]),
MName = (row["MName"]).ToString(),
MPwd = (row["MPwd"]).ToString(),
MType = Convert.ToInt32(row["MType"]),
});
}
return(list);
}