public static string SqlCmdText(string queryConditionStrings)
{
Book queryBook = new Book();//用户查询的书籍
var queryConditonsUnitString=SplitQueryConditionString(queryConditionStrings);//分割书籍查询条件
int length = queryConditonsUnitString.Length / 2;//获取查询条件的个数
//按照查询条件（0:condition 1:value）构造查询书籍
for(int i1=0;i1<length;i1++){
Protocols(queryConditonsUnitString[i1,0], queryConditonsUnitString[i1,1],queryBook);//组装查询书籍
}
StringBuilder sb = new StringBuilder();
sb.Append("select * from Book where");
int i2 = 1;
if (queryBook.bookID !=null)
{
sb.Append(" BookID='" + queryBook.bookID + "'" + AddAndOrNot(i2, length));
i2++;
}
if (queryBook.bookName !=null)
{
sb.Append(" BookName='" + queryBook.bookName + "'" + AddAndOrNot(i2, length));
i2++;
}
if (queryBook.bookAuthor !=null)
{
sb.Append(" BookAuthor='" + queryBook.bookAuthor + "'" + AddAndOrNot(i2, length));
i2++;
}
if (queryBook.bookPress !=null)
{
sb.Append(" BookPress='" + queryBook.bookPress + "'" + AddAndOrNot(i2, length));
i2++;
}
if (queryBook.bookCategory !=null)
{
sb.Append(" BookCategory='" + queryBook.bookCategory + "'" + AddAndOrNot(i2, length));
i2++;
}
if (queryBook.bookPrice != 0)
{
sb.Append(" BookPrice='" + queryBook.bookPrice + "'" + AddAndOrNot(i2, length));
i2++;
}
return sb.ToString();
}
/// <summary>
/// 将书籍添加到数据库
/// 成功返回0
/// 失败返回1
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int AddBookToDB(Book book)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "insert into Book values('" + book.bookID + "','" + book.bookName + "','" + book.bookAuthor +
"','" + book.bookPrice + "','" + book.bookPress + "','" + book.bookDetail + "','" +
book.bookCategory + "','" + book.bookImageURL + "','0','" + GetCurrentTime() + "','" +
book.Inventory + "')";
int state = DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
sql.Close();
if (state == 1)
{
return 0;
}
return 1;
}
/// <summary>
/// 更新图书信息
/// </summary>
/// <param name="prbook"></param>
/// <param name="curbook"></param>
/// <returns></returns>
public int UpdateBook( Book curbook)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "update Book set BookName='" + curbook.bookName +
"',BookAuthor='" + curbook.bookAuthor + "',BookPrice='" + curbook.bookPrice +
"',BookPress='" + curbook.bookPress + "',BookDetail='" + curbook.bookDetail +
"',BookCategory='" + curbook.bookCategory + "',BookImageURL='" + curbook.bookImageURL +
"',Rating='" + curbook.Rating + "',ShelveDate='" + GetCurrentTime() + "',Inventory='" +
curbook.Inventory + "' where BookID='"+curbook.bookID+"'" ;
int state = DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
sql.Close();
if (state == 1)
{
return 0;
}
return 1;
}
public static List<Book> NewBook()
{
List<Book> newBook = new List<Book>();//推荐书籍List
SqlConnection sql = DataBaseOperations.CreateConnection();//sql连接符
string cmdText = "select * from Book order by ShelveDate desc ";//按照销量进行选择
DataSet data = DataBaseOperations.GetDataSet(cmdText, sql);//获取数据
sql.Close();//关闭连接符
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
{
Book tempBook = new Book(data.Tables[0].Rows[i]["BookID"].ToString(),
data.Tables[0].Rows[i]["BookName"].ToString(), data.Tables[0].Rows[i]["BookAuthor"].ToString(),
Convert.ToDecimal(data.Tables[0].Rows[i]["BookPrice"]),
data.Tables[0].Rows[i]["BookPress"].ToString(), data.Tables[0].Rows[i]["BookDetail"].ToString(),
data.Tables[0].Rows[i]["BookCategory"].ToString(), data.Tables[0].Rows[i]["BookImageURL"].ToString(),
Convert.ToInt32(data.Tables[0].Rows[i]["Inventory"].ToString()),
Convert.ToInt32(data.Tables[0].Rows[i]["Rating"]), data.Tables[0].Rows[i]["ShelveDate"].ToString());
newBook.Add(tempBook);
}
return newBook;
}
/// <summary>
/// 根据用户输入的条件查询书籍
/// </summary>
/// <param name="queryConditions"></param>
/// <returns>List-Book对象</returns>
public static List<Book> QueryBookFromDb(string queryConditions)
{
List<Book> queryBooks = new List<Book>();
string cmdText = null;
SqlConnection sql = DataBaseOperations.CreateConnection();//连接数据库
//判定queryConditions查询条件是否为空
if (queryConditions != null)
{
cmdText = QueryBook.SqlCmdText(queryConditions);
}
DataSet data = DataBaseOperations.GetDataSet(cmdText, sql);//获取DataSet
//遍历dataset，将图书添加到queryBooks列表中
if (data != null)
{
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
{
Book book = new Book();
book.bookID = data.Tables[0].Rows[i][0].ToString();
book.bookName = data.Tables[0].Rows[i][1].ToString();
book.bookAuthor = data.Tables[0].Rows[i][2].ToString();
book.bookPrice = Convert.ToDecimal(data.Tables[0].Rows[i][3]);
book.bookPress = data.Tables[0].Rows[i][4].ToString();
book.bookDetail = data.Tables[0].Rows[i][5].ToString();
book.bookCategory = data.Tables[0].Rows[i][6].ToString();
book.bookImageURL = data.Tables[0].Rows[i][7].ToString();
book.Rating = Convert.ToInt32(data.Tables[0].Rows[i][8]);
book.Inventory = Convert.ToInt32(data.Tables[0].Rows[i][10]);
queryBooks.Add(book);//将图书添加到queryBooks列表中
}
}
sql.Close();
return queryBooks;
}
/// <summary>
/// 查询订单信息（所有订单
/// orderList->Order->bookItemList->Book ①
/// 思路：
/// 1.对比当前订单与之前订单的订单号
/// 2.如果订单号相同，说明图书属于一个订单，获取订单列表中的最后一个订单，然后删除（删除是为了步骤5插入订单列表时方便操作）
/// 3.如果订单号不同，说明图书不属于同一订单，则新建一个订单
/// 4.获取到订单后，根据①创建订单对象
/// 5.将订单插入到订单列表尾部
/// </summary>
/// <returns></returns>
public List<Order> QueryOrderInformation()
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string preOrderId = null;//上一个订单号
string curOrderId;//当前订单号
List<Order> orderList = new List<Order>();//订单列表
Order order;//订单
//根据当前用户名查询订单
string cmdText =
"select a.OrderID,a.UserName,a.Address,a.Remark,a.Price,a.isValid,a.OrderDate,b.BookID,b.Amount,c.BookName,c.BookAuthor,c.BookPrice,c.BookPress,c.BookDetail,c.BookCategory,c.BookImageURL,c.ShelveDate,c.Inventory,c.Rating from [Order] a inner join [OrderDetail] b on a.OrderID=b.OrderID inner join [Book] c on b.BookID=c.BookID where a.UserName='******' order by a.OrderID desc";
DataSet dataSet = DataBaseOperations.GetDataSet(cmdText, sql);
if (dataSet != null)
{
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
curOrderId = dataSet.Tables[0].Rows[i][0].ToString();//获取到当前订单id
ShippingAddress shippingAddress = new ShippingAddress(dataSet.Tables[0].Rows[i][2].ToString(), dataSet.Tables[0].Rows[i][3].ToString());//创建收货对象
//创建book对象
Book aBook = new Book(dataSet.Tables[0].Rows[i][7].ToString(),
dataSet.Tables[0].Rows[i][9].ToString(), dataSet.Tables[0].Rows[i][10].ToString(),
Convert.ToDecimal(dataSet.Tables[0].Rows[i][11]), dataSet.Tables[0].Rows[i][12].ToString(),
dataSet.Tables[0].Rows[i][13].ToString(), dataSet.Tables[0].Rows[i][14].ToString(),
dataSet.Tables[0].Rows[i][15].ToString(),
Convert.ToInt32(dataSet.Tables[0].Rows[i][17]), Convert.ToInt32(dataSet.Tables[0].Rows[i][18]),dataSet.Tables[0].Rows[i][16].ToString());
//创建bookItem对象（book对象，book的数量）
BookItem bookItem = new BookItem(aBook, Convert.ToInt32(dataSet.Tables[0].Rows[i][8]));
//图书来自不同的订单
if (!isFromSameOrder(preOrderId, curOrderId))
{
order = new Order();//新建订单，初始化bookItemList
//不同的订单需要设置 OrerId/userName/orderPrice/isValid/oderDate/shippingAddress
//相同订单的信息已经设置过了，不需要重新设置
order.SetOrderInformation(dataSet.Tables[0].Rows[i][0].ToString(),
dataSet.Tables[0].Rows[i][1].ToString(), Convert.ToDecimal(dataSet.Tables[0].Rows[i][4]),
Convert.ToInt32(dataSet.Tables[0].Rows[i][5]), dataSet.Tables[0].Rows[i][6].ToString(),
shippingAddress);
}
else
{
order = orderList[orderList.Count-1];//获取到订单列表的最后一个订单
orderList.RemoveAt(orderList.Count-1);//删除这个订单信息
}
order.bookItemList.Add(bookItem);//将bookItem添加到bookItemList中
orderList.Add(order);//将order添加到orderList中
//设置订单的OrderID/UserName/价格/状态/OrderDate/ShippingAddres
preOrderId = curOrderId;//处理完一个订单后，将curOrderId赋值给preOrderId
}
}
sql.Close();//关闭sql连接
return orderList;
}
/// <summary>
/// 根据 查询条件:value构造查询的书籍
/// </summary>
/// <param name="condition">查询条件</param>
/// <param name="value">条件值</param>
private static void Protocols(string condition, string value, Book queryBook)
{
switch (condition)
{
case "bookID"://书号
queryBook.bookID = value;
break;
case "bookName"://书名
queryBook.bookName = value;
break;
case "bookAuthor"://作者
queryBook.bookAuthor = value;
break;
case "bookPress"://出版社
queryBook.bookPress = value;
break;
case "bookCategory"://种类
queryBook.bookCategory = value;
break;
case "bookPrice"://价格
queryBook.bookPrice = Convert.ToDecimal(value);
break;
}
}
/// <summary>
/// 从数据库中删除某本书籍
/// 成功返回0
/// 失败返回1
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int DeleteBookFromDB(Book book)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "delete from Book where BookID='" + book.bookID + "'";
int state = DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
sql.Close();
if (state == 1)
{
return 0;
}
return 1;
}
/// <summary>
/// 更新图书信息
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int ReviseBookInformation(Book book)
{
return AddBookToDB(book);
}
