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