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