Пример #1
0
        /// <summary>
        /// 查询用户信息
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static List <Customer> QueryUserInformation(string userName)
        {
            List <Customer> qureyUsers = new List <Customer>();
            SqlConnection   sql        = DataBaseOperations.CreateConnection();//数据库连接符
            string          cmdText    = null;

            if (userName == "null")
            {
                cmdText = "select * from [Customer]";//查询所有用户
            }
            else
            {
                cmdText = "select * from [Customer] where Name='" + userName + "'";//查询某个用户
            }

            DataSet data = DataBaseOperations.GetDataSet(cmdText, sql);

            for (int i = 0; i < data.Tables[0].Rows.Count; i++)
            {
                Customer user = new Customer(data.Tables[0].Rows[i]["Name"].ToString(),
                                             data.Tables[0].Rows[i]["Password"].ToString(), Convert.ToInt32(data.Tables[0].Rows[i]["CustomerSex"]),
                                             data.Tables[0].Rows[i]["SecurityQuestion"].ToString(),
                                             data.Tables[0].Rows[i]["SecurityQuestionAnswer"].ToString(), Convert.ToInt32(data.Tables[0].Rows[i]["LegalityState"]));

                qureyUsers.Add(user);
            }
            return(qureyUsers);
        }
Пример #2
0
        /// <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);
        }
Пример #3
0
        /// <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);
        }
Пример #4
0
        /// <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,供前台使用
            }
        }
Пример #5
0
        /// <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);
        }
Пример #6
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);
        }
Пример #7
0
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <returns>0:用户密码正确,账号未被冻结,并且提取相应的权限值</returns>
        /// <returns>1:用户密码错误</returns>
        /// <returns>2:用户尚未注册</returns>
        /// <returns>3:账号被冻结</returns>
        public int Login()
        {
            SqlConnection sql           = DataBaseOperations.CreateConnection();
            string        cmdText       = "select * from Users where Name='" + Name + "'";
            SqlDataReader sqlDataReader = DataBaseOperations.GetSqlDataReader(cmdText, sql);

            //如果查询到该用户,匹配用户名/密码
            if (sqlDataReader.HasRows) //根据查询结果里面的行数判断是否有该用户
            {
                sqlDataReader.Close(); //关闭sqlDataReader,必要操作
                SqlDataAdapter sqlDataAdapter = DataBaseOperations.GetSqlDataAdapter(cmdText, sql);
                DataSet        dataSet        = DataBaseOperations.GetDataSet(sqlDataAdapter);

                //对用户密码进行匹配
                if (dataSet.Tables[0].Rows[0]["Password"].ToString() == Password)
                {
                    string power = dataSet.Tables[0].Rows[0]["Power"].ToString();//获取数据库中用户权限(string)
                    //将string权限转换为Power类型
                    switch (power)
                    {
                    case "Customer":
                        Powers = Power.Customer;
                        break;

                    case "Manager":
                        Powers = Power.Manager;
                        break;
                    }
                    if (Convert.ToInt32(dataSet.Tables[0].Rows[0]["LegalityState"]) == 0)
                    {
                        sql.Close(); //关闭sql连接
                        return(0);   //用户密码正确,账号未被冻结,提示前台进行相应的操作
                    }
                    else
                    {
                        sql.Close(); //关闭sql连接
                        return(3);   //账号被冻结,提示前台进行相应的操作
                    }
                }
                else
                {
                    sql.Close(); //关闭sql连接
                    return(1);   //用户密码错误,提示前台进行相应的操作
                }
            }
            sql.Close(); //关闭sql连接
            return(2);   //用户名不存在,提示用户注册
        }
Пример #8
0
        /// <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);
        }
Пример #9
0
        /// <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);
        }
Пример #10
0
        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);
        }
Пример #11
0
        /// <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);
        }