/// <summary> /// 书籍精品查询 /// </summary> /// <returns></returns> public static List <Book> select() { List <Book> list = new List <Book>(); string sql = "select top 5 * from dbo.Book Order by BSaleCount desc"; using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null)) { while (sdr.Read()) { Book b = new Book(); b.BID = int.Parse(sdr["BID"].ToString()); b.BSID = int.Parse(sdr["BSID"].ToString()); b.BName = sdr["BName"].ToString(); if (b.BName.Length > 10) { b.BName = b.BName.Substring(0, 11); } b.BAuthor = sdr["BAuthor"].ToString(); b.BISBN = sdr["BISBN"].ToString(); b.BTOC = sdr["BTOC"].ToString(); b.BComment = sdr["BComment"].ToString(); if (b.BComment.Length > 10) { b.BComment = b.BComment.Substring(0, 10); } b.BPic = sdr["BPic"].ToString(); b.BPrice = Double.Parse(sdr["BPrice"].ToString()); b.BCount = int.Parse(sdr["BCount"].ToString()); b.BDate = DateTime.Parse(sdr["BDate"].ToString()); b.BSaleCount = int.Parse(sdr["BSaleCount"].ToString()); list.Add(b); } } return(list); }
/// <summary> /// 根据id显示购物车 /// </summary> /// <param name="MID"></param> /// <returns></returns> public static List <Trade> getTradeByMID(int MID) { List <Trade> list = new List <Trade>(); string sql = "select * from Trade inner join Book on Trade.BID=Book.BID where MID=@MID"; SqlParameter[] pare = new SqlParameter[] { new SqlParameter("@MID", MID) }; using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, pare)) { while (sdr.Read()) { Trade t = new Trade(); t.TID = Convert.ToInt32(sdr["TID"]); t.BID = Convert.ToInt32(sdr["BID"]); t.MID = Convert.ToInt32(sdr["MID"]); t.BCount = Convert.ToInt32(sdr["BCount"]); t.BName = sdr["BName"].ToString(); t.BPic = sdr["BPic"].ToString(); t.BPrice = Convert.ToInt32(sdr["BPrice"]); list.Add(t); } } return(list); }
/// <summary> /// 根据图书类型查询 /// </summary> /// <param name="BLID">大类型id</param> /// <param name="BSID">小类型id</param> /// <returns></returns> public static List <Book> selectBookByType(string BLID, string BSID, int PageSize, int PageIndex, string BName) { string sql = string.Format("select top(4) * from Book where BID not in (select top({0}*({1}-1)) BID from Book where BName like '%{2}%' )", PageSize, PageIndex, BName); if (BSID != null) { sql = string.Format(@"select top(4) * from Book where BSID={0} and BID not in (select top({1}*({2}-1)) BID from Book where BSID={3})", Convert.ToInt32(BSID), PageSize, PageIndex, Convert.ToInt32(BSID)); } if (BLID != null) { sql = string.Format(@"select top(4) * from dbo.Book b where BSID in (select BSID from BLCategory bl inner join BSCategory bs on bs.BLID=bl.BLID where bl.BLID={0}) and BID not in(select top({1}*({2}-1)) BID from Book where BSID in (select BSID from BLCategory bl inner join BSCategory bs on bs.BLID=bl.BLID where bl.BLID={3}))", Convert.ToInt32(BLID), PageSize, PageIndex, Convert.ToInt32(BLID)); } List <Book> list = new List <Book>(); using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null)) { while (sdr.Read()) { Book b = new Book(); b.BID = int.Parse(sdr["BID"].ToString()); b.BSID = int.Parse(sdr["BSID"].ToString()); b.BName = sdr["BName"].ToString(); if (b.BName.Length > 10) { b.BName = b.BName.Substring(0, 11); } b.BAuthor = sdr["BAuthor"].ToString(); b.BISBN = sdr["BISBN"].ToString(); b.BTOC = sdr["BTOC"].ToString(); b.BComment = sdr["BComment"].ToString(); if (b.BComment.Length > 10) { b.BComment = b.BComment.Substring(0, 10); } b.BPic = sdr["BPic"].ToString(); b.BPrice = Double.Parse(sdr["BPrice"].ToString()); b.BCount = int.Parse(sdr["BCount"].ToString()); b.BDate = DateTime.Parse(sdr["BDate"].ToString()); b.BSaleCount = int.Parse(sdr["BSaleCount"].ToString()); list.Add(b); } } return(list); }
/// <summary> /// 查询大类节点 /// </summary> /// <returns></returns> public static List <BLCategory> getBLCategoryList() { List <BLCategory> list = new List <BLCategory>(); String sql = string.Format("select * from BLCategory"); using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null)) { while (sdr.Read()) { BLCategory bl = new BLCategory(); bl.BLID = Convert.ToInt32(sdr["BLID"]); bl.BLName = sdr["BLName"].ToString(); list.Add(bl); } } return(list); }
/// <summary> /// 查询大,小类节点 /// </summary> /// <returns></returns> public static List <BSCategory> getBSCategoryList() { List <BSCategory> list = new List <BSCategory>(); String sql = string.Format("select * from dbo.BLCategory l inner join dbo.BSCategory s on l.BLID=s.BLID"); using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null)) { while (sdr.Read()) { BSCategory bs = new BSCategory(); bs.BLID = Convert.ToInt32(sdr["BLID"]); bs.BSID = Convert.ToInt32(sdr["BSID"]); bs.BLName = sdr["BLName"].ToString(); bs.BSName = sdr[4].ToString(); list.Add(bs); } } return(list); }
/// <summary> /// 根据书籍ID查询书籍详细信息 /// </summary> /// <returns></returns> public static List <Book> selectByID(int id) { List <Book> list = new List <Book>(); string sql = "select * from Book where BID=@id"; SqlParameter[] pare = new SqlParameter[] { new SqlParameter("@id", id) }; using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, pare)) { while (sdr.Read()) { Book b = new Book(); b.BID = int.Parse(sdr["BID"].ToString()); b.BSID = int.Parse(sdr["BSID"].ToString()); b.BName = sdr["BName"].ToString(); if (b.BName.Length > 10) { b.BName = b.BName.Substring(0, 11); } b.BAuthor = sdr["BAuthor"].ToString(); b.BISBN = sdr["BISBN"].ToString(); b.BTOC = sdr["BTOC"].ToString(); if (b.BTOC.Length > 100) { b.BTOC = b.BTOC.Substring(0, 100); } b.BComment = sdr["BComment"].ToString(); if (b.BComment.Length > 10) { b.BComment = b.BComment.Substring(0, 10); } b.BPic = sdr["BPic"].ToString(); b.BPrice = Double.Parse(sdr["BPrice"].ToString()); b.BCount = int.Parse(sdr["BCount"].ToString()); b.BDate = DateTime.Parse(sdr["BDate"].ToString()); b.BSaleCount = int.Parse(sdr["BSaleCount"].ToString()); list.Add(b); } } return(list); }
/// <summary> /// 查询小类节点 /// </summary> /// <returns></returns> public static List <BSCategory> getBSCategoryList(int BLID) { List <BSCategory> list = new List <BSCategory>(); String sql = string.Format("select * from BSCategory where BLID=@BLID"); SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@BLID", BLID) }; using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, sp)) { while (sdr.Read()) { BSCategory bs = new BSCategory(); bs.BLID = Convert.ToInt32(sdr["BLID"]); bs.BSID = Convert.ToInt32(sdr["BSID"]); bs.BSName = sdr["BSName"].ToString(); list.Add(bs); } } return(list); }
/// <summary> /// 根据订单号查询订单表 /// </summary> /// <returns></returns> public static List <Order> getOrdersByOID(string OID) { List <Order> list = new List <Order>(); string sql = string.Format("select * from dbo.Orders where OID='{0}'", OID); SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null); while (sdr.Read()) { Order o = new Order(); o.MID = Convert.ToInt32(sdr["MID"]); o.OAddress = sdr["OAddress"].ToString(); o.OConsignee = sdr["OConsignee"].ToString(); o.ODate = Convert.ToDateTime(sdr["ODate"]); o.OID = sdr["OID"].ToString(); o.OState = Convert.ToInt32(sdr["OState"]); o.state = "新订单"; switch (o.OState) { case 1: o.state = "新订单"; break; case 2: o.state = "确认订单"; break; case 3: o.state = "发货订单"; break; default: o.state = "成交订单"; break; } o.OSumPrice = Convert.ToInt32(sdr["OSumPrice"]); o.OTelephone = sdr["OTelephone"].ToString(); list.Add(o); } return(list); }
/// <summary> /// 根据订单号查询订单详细表 /// </summary> /// <param name="OID"></param> /// <returns></returns> public static List <OrderDetails> getOrderDetailByOID(string OID) { List <OrderDetails> list = new List <OrderDetails>(); String sql = string.Format("select * from OrderDetails where OID=@OID"); SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@OID", OID) }; using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, sp)) { while (sdr.Read()) { OrderDetails o = new OrderDetails(); o.OID = sdr["OID"].ToString(); o.BID = Convert.ToInt32(sdr["BID"]); o.BPrice = Convert.ToInt32(sdr["BPrice"]); o.BCount = Convert.ToInt32(sdr["BCount"]); list.Add(o); } } return(list); }
/// <summary> /// 登录 /// </summary> /// <returns></returns> public static Member select(string name, string pwd) { Member m = new Member(); string sql = string.Format("select * from dbo.Member where MName = @name and MPassword = @pwd"); SqlParameter[] pare = new SqlParameter[] { new SqlParameter("@name", name), new SqlParameter("@pwd", pwd) }; using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, pare)) { while (sdr.Read()) { m.MID = int.Parse(sdr["MID"].ToString()); m.MName = sdr["MName"].ToString(); m.MEmail = sdr["MEmail"].ToString(); m.MPassword = sdr["MPassword"].ToString(); } } return(m); }
public static List <Book> selByName(String Bname) { String sql = string.Format("select * from [dbo].[Book] where BName like '%@Bname%'"); SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Bname", Bname) }; List <Book> list = new List <Book>(); using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, sp)) { while (sdr.Read()) { Book b = new Book(); b.BID = int.Parse(sdr["BID"].ToString()); b.BSID = int.Parse(sdr["BSID"].ToString()); b.BName = sdr["BName"].ToString(); if (b.BName.Length > 10) { b.BName = b.BName.Substring(0, 11); } b.BAuthor = sdr["BAuthor"].ToString(); b.BISBN = sdr["BISBN"].ToString(); b.BTOC = sdr["BTOC"].ToString(); b.BComment = sdr["BComment"].ToString(); if (b.BComment.Length > 10) { b.BComment = b.BComment.Substring(0, 10); } b.BPic = sdr["BPic"].ToString(); b.BPrice = Double.Parse(sdr["BPrice"].ToString()); b.BCount = int.Parse(sdr["BCount"].ToString()); b.BDate = DateTime.Parse(sdr["BDate"].ToString()); b.BSaleCount = int.Parse(sdr["BSaleCount"].ToString()); list.Add(b); } } return(list); }
/// <summary> /// 查询订单表 /// </summary> /// <returns></returns> public static List <Order> getOrders(string ID, string name, int PageSize, int PageIndex) { List <Order> list = new List <Order>(); string sql = string.Format("select top(5) * from dbo.Orders where OID not in(select top({0}*({1}-1)) OID from Orders where OID like '%{2}%' and OConsignee like '%{3}%') and OID like '%{4}%' and OConsignee like '%{5}%'", PageSize, PageIndex, ID, name, ID, name); using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null)) { while (sdr.Read()) { Order o = new Order(); o.MID = Convert.ToInt32(sdr["MID"]); o.OAddress = sdr["OAddress"].ToString(); o.OConsignee = sdr["OConsignee"].ToString(); o.ODate = Convert.ToDateTime(sdr["ODate"]); o.OID = sdr["OID"].ToString(); o.OState = Convert.ToInt32(sdr["OState"]); o.OSumPrice = Convert.ToInt32(sdr["OSumPrice"]); o.OTelephone = sdr["OTelephone"].ToString(); list.Add(o); } } return(list); }
/// <summary> /// 根据用户id和多个书籍id显示结账数据 /// </summary> /// <param name="MID"></param> /// <returns></returns> public static List <Trade> getTradeByMIDAndBIDS(int MID, string ids) { List <Trade> list = new List <Trade>(); string sql = string.Format("select * from Trade inner join Book on Trade.BID=Book.BID where MID={0} and Trade.TID in ({1})", MID, ids); using (SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null)) { while (sdr.Read()) { Trade t = new Trade(); t.TID = Convert.ToInt32(sdr["TID"]); t.BID = Convert.ToInt32(sdr["BID"]); t.MID = Convert.ToInt32(sdr["MID"]); t.BCount = Convert.ToInt32(sdr["BCount"]); t.BName = sdr["BName"].ToString(); t.BPic = sdr["BPic"].ToString(); t.BPrice = Convert.ToInt32(sdr["BPrice"]); list.Add(t); } } return(list); }