bookborrowread.cs

数据库课程设计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace DataAccess
{
   public class BookBorrowRead
    {
       SqlConnection con = new SqlConnection("server=(local);uid=sa;pwd=sa;database=libbook");
       Common.CommonOperator objCommonOperator = new CommonOperator();
       SqlCommand objCMD = new SqlCommand();
        /// <summary>
        /// 此方法根据图书编号和读者编号查询是否被借阅
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="BookID">图书编号</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable BookBorrowReadLose(string ReaderID, string BookID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select * from BookBorrow where BooksID  = '" + BookID + "'and ReaderID  = '" + ReaderID + "'and BorrowBookState    = '" + Lose + "'";
            string sqlTable = "BookBorrowRead";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 此方法调用Borrow_Book存储过程
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="BookID">图书编号</param>
       public void ProcBorrow(string ReaderID, string BookID, double BookMoney)
        {
            try
            {
                con.Open();
                BorrowParameter(ReaderID, BookID, BookMoney);
                objCMD.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 此方法对Borrow_Book存储过程的设置
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="BookID">图书编号</param>
       private void BorrowParameter(string ReaderID, string BookID, double BookMoney)
        {
            SqlParameter bookID = null;
            SqlParameter readerID = null;
            SqlParameter bookMoney = null;
         
            try
            {
                objCMD.Parameters.Clear();
                objCMD.CommandText = "Borrow_Book";
                objCMD.CommandType = CommandType.StoredProcedure;

                readerID = new SqlParameter();
        
                readerID.ParameterName = "@readerID";
                readerID.Value = ReaderID;
                objCMD.Parameters.Add(readerID);

                bookID = new SqlParameter();
               
                bookID.ParameterName = "@bookID";
                bookID.Value = BookID;
                objCMD.Parameters.Add(bookID);
                bookMoney = new SqlParameter();
                bookMoney.SqlDbType = SqlDbType.Money;
                bookMoney.ParameterName = "@price";
                bookMoney.Value = BookMoney;
                objCMD.Parameters.Add(bookMoney);

        
                objCMD.Connection = con;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据读者编号查询需要的5个字段信息
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <returns>返回objTable</returns>
        public DataTable selectReaderInfo(string memberID)
        {
            DataTable objTable = null;
            string sql = "select ReaderID as '读者编号', ReaderName as '读者姓名' ,ReaderDistinction as '级别',ReaderMoney as '押金' ,ReaderState  as '状态' from Reader where ReaderID  = '" + memberID + "'";
            string sqlTable = "ReaderInfo";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 子查询
        /// 根据读者编号+状态 查询图书编号，在根据图书编号查询图书的5个字段的信息
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="Lose">状态</param>
        /// <returns>返回objTable</returns>
        public DataTable SelectReaderBorrowBook(string ReaderID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select BooksID as '书籍编号' ,BooksName as '书籍名称'  ,BooksGenre  as '类型' ,BooksExistent as '现存数量',BooksState '是否注销'  from Books where BooksID in"
                + "(select BooksID from BookBorrow where ReaderID  = '" + ReaderID + "' and BorrowBookState    = '" + Lose + "')";
            string sqlTable = "BookInfo";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 根据图书编号查询指定的５个子段
        /// </summary>
        /// <param name="BookID">图书编号</param>
        /// <returns>返回objTable</returns>
        public DataTable selectBookInfo(string BarCode)
        {
            DataTable objTable = null;
            string sql = "select BooksID as '书籍编号',BooksName as '书籍名称',BooksAuthor as '书籍作者',BooksExistent as '现存数量',BooksState as '是否注销'    from Books where BooksID = '" + BarCode + "'";
            string sqlTable = "BookInfo";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 子查询
        /// 根据图书编号＋状态查询没有还书的读者，在根据读者查询指定的５个字段
        /// </summary>
        /// <param name="BookID">图书编号</param>
        /// <param name="Lose">状态</param>
        /// <returns>返回objTable</returns>
        public DataTable SelectBookBorrowInfo(string BookID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select ReaderID as '读者编号' ,ReaderName as '读者姓名',ReaderDistinction as '级别',ReaderMoney as '押金' ,ReaderState as '状态'    from Reader where ReaderID  in  "+
                         " (select ReaderID  from BookBorrow where BooksID  = '" + BookID + "' and BorrowBookState='" + Lose + "')";
            string sqlTable = "ReaderInfo";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
       /// <summary>
       /// 删除书籍借阅表的信息
       /// </summary>
       /// <param name="BookID">书籍ID</param>
       /// <param name="ReaderID">读者ID</param>
       public void delBorrowBook(string BookID, string ReaderID)
       {
           try
           {
               string sql = "DELETE FROM BookBorrow  "
                         +"WHERE BooksID ='"+ BookID + "' and  ReaderID ='" + ReaderID + "'";
               objCommonOperator.Execute(sql);
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               objCommonOperator.CloseDb();
           }
       }
       /// <summary>
       /// 此方法对Return_book存储过程的设置
       /// </summary>
       /// <param name="BorrowNum">借书数量</param>
       /// <param name="ReaderID">读者ID</param>
       /// <param name="BookID">图书ID</param>
       private void ReturnParameter(string ReaderID, string BookID, string Lose, double Price)
       {
           SqlParameter bookID = null;
           SqlParameter readerID = null;
           SqlParameter lose = null;
           SqlParameter price = null;
           try
           {
               objCMD.Parameters.Clear();
               objCMD.CommandText = "Return_book";
               objCMD.CommandType = CommandType.StoredProcedure;

               bookID = new SqlParameter();
            
               bookID.ParameterName = "@bookID";
               bookID.Value = BookID;
               objCMD.Parameters.Add(bookID);

               readerID = new SqlParameter();
               readerID.ParameterName = "@readerID";
               readerID.Value = ReaderID;
               objCMD.Parameters.Add(readerID);

               lose = new SqlParameter();
               lose.SqlDbType = SqlDbType.VarChar;
               lose.ParameterName = "@lose";
               lose.Value = Lose;
               objCMD.Parameters.Add(lose);

               price = new SqlParameter();
               price.SqlDbType = SqlDbType.Money;
               price.ParameterName = "@price";
               price.Value = Price;
               objCMD.Parameters.Add(price);
               objCMD.Connection = con;
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }
       /// <summary>
       /// 此方法调用Return_book存储过程
       /// </summary>
       /// <param name="BorrowNum">借书的数量</param>
       /// <param name="ReaderID">读者ID</param>
       /// <param name="BookID">图书ID</param>
       public void ProcReturn(string ReaderID, string BookID, string Lose, double Price)
       {
           try
           {
               con.Open();
               ReturnParameter(ReaderID, BookID, Lose, Price);
               objCMD.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               con.Close();
           }
       }
       /// <summary>
       /// （根据读者编号+图书编号+状态，因为这3者是唯一的）
       /// 察看当前读者借阅当前的这本书是否超过应还时间
       /// </summary>
       /// <param name="BookID"></param>
       /// <param name="ReaderID"></param>
       /// <param name="Lose"></param>
       /// <returns></returns>
       public DataTable SelectOverdoeReaderID(string BookID, string ReaderID, string Lose)
       {
           DataTable objTable = null;
           string sql = "select * from BookBorrow where AnswerTime  < '" + DateTime.Now.ToShortDateString() + "'and BooksID  = '" + BookID + "'and ReaderID  = '" + ReaderID + "'and BorrowBookState ='" + Lose + "'";
           string sqlTable = "BookBorrowRead";
           try
           {
               objCommonOperator.ConnectDb();
               objTable = objCommonOperator.Search(sql, sqlTable);
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               objCommonOperator.CloseDb();
           }
           return objTable;
       }
       /// <summary>
       /// 返回借阅的时间进行计算
       /// </summary>
       /// <param name="BookID"></param>
       /// <param name="ReaderID"></param>
       /// <param name="Lose"></param>
       /// <returns></returns>
       public DataTable SelectBookBorrowTime (string BookID, string ReaderID, string Lose)
       {
           DataTable objTable = null;
           string sql = "select BorrowTime  from BookBorrow where BooksID  = '" + BookID + "'and ReaderID  = '" + ReaderID + "'and BorrowBookState ='" + Lose + "'";
           string sqlTable = "BookBorrowRead";
           try
           {
               objCommonOperator.ConnectDb();
               objTable = objCommonOperator.Search(sql, sqlTable);
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               objCommonOperator.CloseDb();
           }
           return objTable;
       }
    }
}