/// <summary>
/// 表:Trade、Book (根据MID查询数据
/// </summary>
/// <param name="MID">所需MID</param>
/// <returns>查询结果集</returns>
public static List <Trade> Select_TradeAndBook_MID(int MID)
{
List <Trade> trades = new List <Trade>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from Trade a cross join Book b where a.BID=b.BID and a.MID=@MID",
new SqlParameter[] {
new SqlParameter("@MID", MID)
}))
{
while (dataReader.Read())
{
trades.Add(new Trade()
{
BID = Convert.ToInt32(dataReader["BID"]),
TID = Convert.ToInt32(dataReader["TID"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BCount = Convert.ToInt32(dataReader["BCount"]),
BName = Convert.ToString(dataReader["BName"]),
BPic = Convert.ToString(dataReader["BPic"]),
MID = Convert.ToInt32(dataReader["MID"]),
});
}
return(trades);
}
}
/// <summary>
/// 表:Book 根据BLID子查询所有信息
/// </summary>
/// <param name="BLID">BLID</param>
/// <returns>查询结果</returns>
public static List <Book> Select_BLID(int BLID)
{
List <Book> books = new List <Book>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from Book where BSID in (select BSID from BSCategory where BLID=@BLID)",
new SqlParameter[] {
new SqlParameter("@BLID", BLID)
}))
{
while (dataReader.Read())
{
books.Add(new Book()
{
BName = Convert.ToString(dataReader["BName"]),
BID = Convert.ToInt32(dataReader["BID"]),
BPic = Convert.ToString(dataReader["BPic"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BAuthor = Convert.ToString(dataReader["BAuthor"]),
BComment = Convert.ToString(dataReader["BComment"])
});
}
return(books);
}
}
/// <summary>
/// 表:Book 根据BLID分页
/// </summary>
/// <param name="PageSize">页大小</param>
/// <param name="PageIndex">当前页</param>
/// <param name="BLID">子查询所需BLID</param>
/// <returns>分页结果</returns>
public static List <Book> TopPaging_BLID(int PageSize, int PageIndex, int BLID)
{
List <Book> books = new List <Book>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select top (@PageSize) * from Book where BSID in (select BSID from BSCategory where BLID=@BLID) and bid not in(select top (@Notin) BID from Book where BSID in (select BSID from BSCategory where BLID=@BLID))",
new SqlParameter[] {
new SqlParameter("@PageSize", PageSize),
new SqlParameter("@BLID", BLID),
new SqlParameter("@Notin", (PageIndex - 1) * PageSize),
}))
{
while (dataReader.Read())
{
books.Add(new Book()
{
BID = Convert.ToInt32(dataReader["BID"]),
BName = Convert.ToString(dataReader["BName"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BPic = Convert.ToString(dataReader["BPic"]),
BAuthor = Convert.ToString(dataReader["BAuthor"]),
BComment = Convert.ToString(dataReader["BComment"]),
});
}
return(books);
}
}
/// <summary>
/// 表:Book (分页处理
/// </summary>
/// <param name="PageSize">页大小</param>
/// <param name="PageIndex">当前页</param>
/// <returns>分页结果</returns>
public static List <Book> TopPaging(int PageSize, int PageIndex)
{
List <Book> books = new List <Book>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select top (@PageSize) * from Book where BID not in(select top (@Notin) Bid from Book)",
new SqlParameter[] {
new SqlParameter("@PageSize", PageSize),
new SqlParameter("@Notin", (PageIndex - 1) * PageSize),
}))
{
while (dataReader.Read())
{
books.Add(new Book()
{
BName = Convert.ToString(dataReader["BName"]),
BID = Convert.ToInt32(dataReader["BID"]),
BPic = Convert.ToString(dataReader["BPic"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BISBN = Convert.ToString(dataReader["BISBN"]),
});
}
return(books);
}
}
/// <summary>
/// 表:Order (查询所有数据
/// </summary>
/// <returns>查询结果</returns>
public static List <Order> Select_All()
{
List <Order> orders = new List <Order>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from [order]",
null))
{
while (dataReader.Read())
{
orders.Add(new Order()
{
MID = Convert.ToInt32(dataReader["MID"]),
OAddress = Convert.ToString(dataReader["OAddress"]),
OConsignee = Convert.ToString(dataReader["OConsignee"]),
ODate = Convert.ToString(dataReader["ODate"]),
OID = Convert.ToString(dataReader["OID"]),
OState = Enum.GetName(typeof(Order.enumOState), dataReader["Ostate"]),
OSumPrice = Convert.ToDouble(dataReader["OSumPrice"]),
OTelephone = Convert.ToString(dataReader["OTelephone"]),
});
}
return(orders);
}
}
/// <summary>
/// 表:Book (根据BName进行分页
/// </summary>
/// <param name="BName">所需参数BName</param>
/// <param name="PageSize">页大小</param>
/// <param name="PageIndex">当前页</param>
/// <returns></returns>
public static List <Book> TopPaging_BName(string BName, int PageSize, int PageIndex)
{
List <Book> books = new List <Book>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select top (@PageSize) * from Book where BName like '%'+@BName+'%' and BID not in (select top (@Notin) BID from Book where BName like '%'+@BName+'%' ) order by BID",
new SqlParameter[] {
new SqlParameter("@PageSize", PageSize),
new SqlParameter("@BName", BName),
new SqlParameter("@Notin", PageSize * (PageIndex - 1)),
}))
{
while (dataReader.Read())
{
books.Add(new Book()
{
BID = Convert.ToInt32(dataReader["BID"]),
BName = Convert.ToString(dataReader["BName"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BPic = Convert.ToString(dataReader["BPic"]),
BAuthor = Convert.ToString(dataReader["BAuthor"]),
BComment = Convert.ToString(dataReader["BComment"]),
});
}
return(books);
}
}
/// <summary>
/// 表:Book (根据BName进行模糊查询
/// </summary>
/// <param name="BName"></param>
/// <returns></returns>
public static List <Book> DimSelect_BName(string BName)
{
List <Book> books = new List <Book>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from Book where BName like '%'+@BName+'%'",
new SqlParameter[] {
new SqlParameter("@BName", BName)
}))
{
while (dataReader.Read())
{
books.Add(new Book()
{
BName = Convert.ToString(dataReader["BName"]),
BID = Convert.ToInt32(dataReader["BID"]),
BPic = Convert.ToString(dataReader["BPic"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BAuthor = Convert.ToString(dataReader["BAuthor"]),
BComment = Convert.ToString(dataReader["BComment"])
});
}
return(books);
}
}
/// <summary>
/// 表:Book 根据BID查询
/// </summary>
/// <param name="BID">BSID</param>
/// <returns>查询结果</returns>
public static Book Select_BID(int BID)
{
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from Book where BID=@BID",
new SqlParameter[] {
new SqlParameter("@BID", BID)
}))
{
Book book = null;
while (dataReader.Read())
{
book = new Book()
{
BSID = Convert.ToInt32(dataReader["BSID"]),
BISBN = Convert.ToString(dataReader["BISBN"]),
BName = Convert.ToString(dataReader["BName"]),
BID = Convert.ToInt32(dataReader["BID"]),
BPic = Convert.ToString(dataReader["BPic"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BAuthor = Convert.ToString(dataReader["BAuthor"]),
BComment = Convert.ToString(dataReader["BComment"]),
BCount = Convert.ToInt32(dataReader["BCount"]),
BTOC = Convert.ToString(dataReader["BTOC"]),
BSaleCount = Convert.ToInt32(dataReader["BSaleCount"]),
};
}
return(book);
}
}
/// <summary>
/// 表:Order (分页查询
/// </summary>
/// <param name="pageindex">当前页</param>
/// <param name="pagesize">页大小</param>
/// <returns>查询结果</returns>
public static List <Order> Paging(int pageindex, int pagesize)
{
List <Order> orders = new List <Order>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from (select ROW_NUMBER() over(order by oid) 'row' ,* from [Order]) a where a.row between @start and @end",
new SqlParameter[] {
new SqlParameter("@start", (pageindex - 1) * pagesize + 1),
new SqlParameter("@end", pageindex * pagesize)
}))
{
while (dataReader.Read())
{
orders.Add(new Order()
{
MID = Convert.ToInt32(dataReader["MID"]),
OAddress = Convert.ToString(dataReader["OAddress"]),
OConsignee = Convert.ToString(dataReader["OConsignee"]),
ODate = Convert.ToString(dataReader["ODate"]),
OID = Convert.ToString(dataReader["OID"]),
OState = Enum.GetName(typeof(Order.enumOState), dataReader["Ostate"]),
OSumPrice = Convert.ToDouble(dataReader["OSumPrice"]),
OTelephone = Convert.ToString(dataReader["OTelephone"]),
});
}
return(orders);
}
}
/// <summary>
/// 表:Trade、Book (根据TID查询数据
/// </summary>
/// <param name="TID">所需TID</param>
/// <returns>查询结果</returns>
public static Trade Select_TradeAndBook_TID(int TID)
{
Trade trade = null;
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from Trade a cross join Book b where a.BID=b.BID and a.TID=@TID",
new SqlParameter[] {
new SqlParameter("@TID", TID)
}))
{
while (dataReader.Read())
{
trade = new Trade()
{
BID = Convert.ToInt32(dataReader["BID"]),
TID = Convert.ToInt32(dataReader["TID"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BCount = Convert.ToInt32(dataReader["BCount"]),
BName = Convert.ToString(dataReader["BName"]),
BPic = Convert.ToString(dataReader["BPic"]),
MID = Convert.ToInt32(dataReader["MID"]),
};
}
return(trade);
}
}
/// <summary>
/// 表:Order (根据OID及OConsignee查询
/// </summary>
/// <param name="OConsignee">所需OConsignee</param>
/// <param name="OID">所需OID</param>
/// <returns>查询结果</returns>
public static List <Order> Select_OConsignee_OID(string OConsignee, string OID)
{
List <Order> orders = new List <Order>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from [order] where OConsignee like '%'+@OConsignee+'%' and OID=@OID",
new SqlParameter[] {
new SqlParameter("@OConsignee", OConsignee),
new SqlParameter("@OID", OID),
}))
{
while (dataReader.Read())
{
orders.Add(new Order()
{
MID = Convert.ToInt32(dataReader["MID"]),
OAddress = Convert.ToString(dataReader["OAddress"]),
OConsignee = Convert.ToString(dataReader["OConsignee"]),
ODate = Convert.ToString(dataReader["ODate"]),
OID = Convert.ToString(dataReader["OID"]),
OState = Enum.GetName(typeof(Order.enumOState), dataReader["Ostate"]),
OSumPrice = Convert.ToDouble(dataReader["OSumPrice"]),
OTelephone = Convert.ToString(dataReader["OTelephone"]),
});
}
return(orders);
}
}
/// <summary>
/// 表:BLCategory (查询所有内容
/// </summary>
/// <returns>所有内容</returns>
public static List <BLCategory> Select_All()
{
List <BLCategory> bLCategories = new List <BLCategory>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader("Select * from BLCategory", null))
{
while (dataReader.Read())
{
bLCategories.Add(new BLCategory()
{
BLID = Convert.ToInt32(dataReader["BLID"]),
BLName = Convert.ToString(dataReader["BLName"]),
});
}
return(bLCategories);
}
}
/// <summary>
/// 表:BSCategory (根据
/// </summary>
/// <returns>所有内容</returns>
public static List <BSCategory> Select_BLID(int BLID)
{
List <BSCategory> bSCategories = new List <BSCategory>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader("select * from BSCategory where BLID=@BLID", new SqlParameter[] { new SqlParameter("@BLID", BLID) }))
{
while (dataReader.Read())
{
bSCategories.Add(new BSCategory()
{
BLID = Convert.ToInt32(dataReader["BLID"]),
BLName = Convert.ToString(dataReader["BLName"]),
BSID = Convert.ToInt32(dataReader["BSID"])
});
}
return(bSCategories);
}
}
/// <summary>
/// 表:Book 查询(倒序 BSaleCount 前 5)
/// </summary>
/// <returns>查询结果</returns>
public static List <Book> DOrderBy_BSaleCount()
{
List <Book> books = new List <Book>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader("select top 5 * from book order by BSaleCount desc", null))
{
while (dataReader.Read())
{
books.Add(new Book()
{
BID = Convert.ToInt32(dataReader["BID"]),
BPic = Convert.ToString(dataReader["BPic"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BName = Convert.ToString(dataReader["BName"]),
});
}
return(books);
}
}
/// <summary>
/// 表:BLCategory (根据BLID查询
/// </summary>
/// <param name="BLID">所需BLID</param>
/// <returns>查询结果</returns>
public static BLCategory Select_BLID(int BLID)
{
BLCategory bLCategory = null;
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from BLCategory where BLID=@BLID",
new SqlParameter[] {
new SqlParameter("@BLID", BLID)
}))
{
while (dataReader.Read())
{
bLCategory = new BLCategory()
{
BLID = Convert.ToInt32(dataReader["BLID"]),
BLName = Convert.ToString(dataReader["BLName"]),
};
}
return(bLCategory);
}
}
/// <summary>
/// 表:BSCategory (联合查询BSCategory与BLCategory的BLName,BLID,BSID
/// </summary>
/// <returns>所有内容</returns>
public static List <BSCategory> Select_All()
{
List <BSCategory> bSCategories = new List <BSCategory>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select a.BLID,a.BSID,a.BLName as 'BLName',b.BLName as 'BLName1' from BSCategory a cross join BLCategory b where a.BLID=b.BLID",
null))
{
while (dataReader.Read())
{
bSCategories.Add(new BSCategory()
{
BLID = Convert.ToInt32(dataReader["BLID"]),
BLName = Convert.ToString(dataReader["BLName"]),
BSID = Convert.ToInt32(dataReader["BSID"]),
BLName1 = Convert.ToString(dataReader["BLName1"])
});
}
return(bSCategories);
}
}
/// <summary>
/// 表:OrderDetails (根据OID查询
/// </summary>
/// <param name="OID">所需OID</param>
/// <returns>查询结果</returns>
public static List <OrderDetails> Select_OID(string OID)
{
List <OrderDetails> orderDetails = new List <OrderDetails>();
using (SqlDataReader dataReader = DBHelp.ExecuteSqlDataReader(
"select * from OrderDetails where OID=@OID",
new SqlParameter[] {
new SqlParameter("@OID", OID)
}))
{
while (dataReader.Read())
{
orderDetails.Add(new OrderDetails()
{
OID = Convert.ToString(dataReader["OID"]),
BPrice = Convert.ToDecimal(dataReader["BPrice"]),
BID = Convert.ToInt32(dataReader["BID"]),
BCount = Convert.ToInt32(dataReader["BCount"]),
});
}
return(orderDetails);
}
}
