/// <summary>
/// 冻结/解冻用户账号
/// </summary>
/// <param name="customer">顾客</param>
/// <param name="state">1：冻结 </param>
/// <returns></returns>
public int RestrictOrPermitCustomerLogin(string userName, int state)
{
string cmdText1, cmdText2;
if (state == 1)
{
cmdText1 = "update Users set LegalityState=1 where Name='" + userName + "'";
cmdText2 = "update Customer set LegalityState=1 where Name='" + userName + "'";
}
else
{
cmdText1 = "update Users set LegalityState=0 where Name='" + userName + "'";
cmdText2 = "update Customer set LegalityState=0 where Name='" + userName + "'";
}
SqlConnection sql = DataBaseOperations.CreateConnection();
int state1 = DataBaseOperations.ReviseDataToDataBase(cmdText1, sql);
int state2 = DataBaseOperations.ReviseDataToDataBase(cmdText2, sql);
sql.Close();
if (state1 == 1 && state2 == 1)
{
return(0);
}
return(1);
}
/// <summary>
/// 提交订单信息
/// </summary>
/// <param name="shippingAddress">收货地址</param>
/// <param name="bookItems">用户购买的图书</param>
/// <returns>0：成功 1：失败</returns>
public int SubmitOrder(Order order)
{
//1.获取购物车中的图书 bookItems
//2.生成订单号 orderid
//3.将订单信息写入数据库
if (order != null)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "insert into [Order] values('" + order.orderId + "','" + order.userName + "','" +
order.shippingAddress.Address + "','" + order.shippingAddress.Remark + "','" + order.Price +
"','" + order.isValid + "','" + order.orderDate + "')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);//将订单信息插入OrderID表中
foreach (var bookitem in order.bookItemList)
{
cmdText = "insert into [OrderDetail] values('" + order.orderId + "','" + bookitem.Book.bookID + "','" + bookitem.bookAmount + "')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);//将订单信息插入OrderDetail表中
int bookRating = bookitem.Book.Rating + 1;
cmdText = "update [Book] set Rating='" + bookRating + "'where BookID='" + bookitem.Book.bookID + "'";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);//更新图书销量
}
sql.Close();
return(0);
}
return(1);
}
/// <summary>
/// 用户注册
/// 0：成功注册
/// 1：用户已存在
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <param name="customersex"></param>
/// <param name="securityquestion"></param>
/// <param name="securityquestionanswer"></param>
/// <returns></returns>
public int Register()
{
SqlConnection sql = DataBaseOperations.CreateConnection(); //建立数据库连接
string cmdText = "select * from Users where Name='" + Name + "'";
SqlDataReader sqlDataReader = DataBaseOperations.GetSqlDataReader(cmdText, sql); //查询数据库中是否有该用户
if (sqlDataReader.HasRows) //搜索结果中有数据，说明已经注册
{
sqlDataReader.Close(); //关闭sqlDataReader(必要操作！！)
sql.Close(); //关闭sql连接
return(1); //用户名已经存在，返回1，共前台使用
}
//将用户信息写入数据库
else
{
sqlDataReader.Close();//关闭sqlDataReader(必要操作！！)
//将用户信息写入User表
cmdText = "insert into Users (Name,Password,Power,LegalityState) values ('" + Name + "','" + Password +
"','" + Powers + "','" + legalityState + "')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
//将用户信息写入Customer表
cmdText =
"insert into Customer (Name,Password,CustomerSex,SecurityQuestion,SecurityQuestionAnswer,LegalityState) values('" +
Name + "','" + Password + "','" + customerSex + "','" +
securityQuestion + "','" + securityQuestionAnswer + "','" + legalityState +
"')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql); //执行插入操作
sql.Close(); //关闭sql连接
return(0); //操作成功返回0，供前台使用
}
}
/// <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>
/// 将书籍添加到数据库
/// 成功返回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);
}