magazineborrowread.cs

数据库课程设计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Common;
using System.Data.SqlClient;
namespace DataAccess
{
    public class MagazineBorrowRead
    {
        Common.CommonOperator objCommonOperator = new CommonOperator();
        SqlConnection con = new SqlConnection("server=(local);uid=sa;pwd=sa;database=libbook");
        SqlCommand objCMD = new SqlCommand();
        /// <summary>
        /// 根据期刊编号查询指定的5个字段信息
        /// </summary>
        /// <param name="MagazineID">期刊编号</param>
        /// <returns>返回objTable</returns>
        public DataTable selectMagazineInfo(string BarCode)
        {
            DataTable objTable = null;
            string sql = "select MagazineID as '期刊编号',MagazineName as '期刊名称',MagazineGenre as '期刊类型',MagazineTime as '出版日期',MagazineExistent as '现存数量'  from Magazine where MagazineID  = '" + BarCode + "'";
            string sqlTable = "MagazineInfo";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql,sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 子查询
        /// 根据期刊编号+状态 查询借阅的读者编号，在根据读者编号查询指定的5个字段信息
        /// </summary>
        /// <param name="MagazineID">期刊编号</param>
        /// <param name="Lose">状态</param>
        /// <returns>返回objTable</returns>
        public DataTable SelectBorrowMagazineReader(string MagazineID, 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 MagazineBorrow where MagazineID  = '" + MagazineID + "' 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="MagazineID"></param>
        /// <param name="ReaderID"></param>
        public void delMagazineLose(string MagazineID, string ReaderID)
        {
            try
            {
                string sql = "DELETE FROM MagazineBorrow  "
                          + "WHERE MagazineID  ='" + MagazineID + "' and  ReaderID ='" + ReaderID + "'";
                objCommonOperator.Execute(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
        }
        /// <summary>
        /// 期刊借越信息
        /// </summary>
        /// <param name="MagazineID"></param>
        /// <param name="ReaderID"></param>
        /// <param name="Price"></param>
        /// <param name="Day"></param>
        private void BorrowParameter(string MagazineID, string ReaderID,double MagazineMoney)
        {
            SqlParameter magazineID = null;
            SqlParameter readerID = null;
            SqlParameter magazineMoney = null;
            try
            {
                objCMD.Parameters.Clear();
                objCMD.CommandText = "Borrow_Magazine";
                objCMD.CommandType = CommandType.StoredProcedure;

                magazineID = new SqlParameter();
       
                magazineID.ParameterName = "@magazineID";
                magazineID.Value = MagazineID;
                objCMD.Parameters.Add(magazineID);

                readerID = new SqlParameter();
              
                readerID.ParameterName = "@readerID";
                readerID.Value = ReaderID;
                objCMD.Parameters.Add(readerID);
                magazineMoney = new SqlParameter();
                magazineMoney.SqlDbType = SqlDbType.Money;
                magazineMoney.ParameterName = "@price";
                magazineMoney.Value = MagazineMoney;
                objCMD.Parameters.Add(magazineMoney);

                objCMD.Connection = con;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 调用Borrow_Magazine存储过程
        /// </summary>
        /// <param name="MagazineID"></param>
        /// <param name="ReaderID"></param>
        public void ProcBorrowMagazine(string MagazineID, string ReaderID, double MagazineMoney)
        {
            try
            {
                con.Open();
                BorrowParameter(MagazineID, ReaderID, MagazineMoney);
                objCMD.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 此方法根据期刊编号和读者编号查询是否被借阅
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="BookID">图书编号</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable MagazineBorrowLose(string ReaderID, string MagazineID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select * from MagazineBorrow where MagazineID   = '" + MagazineID + "'and ReaderID   = '" + ReaderID + "'and BorrowBookState    = '" + Lose + "'";
            string sqlTable = "MagazineBorrow";
            try
            {
                objCommonOperator.ConnectDb();
                objTable = objCommonOperator.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 根据期刊编号+读者编号+状态查询期刊借阅过期没有?
        /// </summary>
        /// <param name="MagazineID">期刊编号</param>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="Lose">状态</param>
        /// <returns>返回objTable</returns>
        public DataTable MagazineOverdoeReaderID(string MagazineID, string ReaderID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select * from MagazineBorrow where AnswerTime  < '" + DateTime.Now.ToShortDateString() + "'and ReaderID   = '" + MagazineID + "'and ReaderID = '" + ReaderID + "'and BorrowBookState   ='" + Lose + "'";
            string sqlTable = "MagazineBorrowRead";
            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 SelectMagazineBorrowTime(string MagazineID, string ReaderID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select BorrowTime   from MagazineBorrow where MagazineID   = '" + MagazineID + "'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>
        /// 对Return_Magazine存储过程进行设置
        /// </summary>
        /// <param name="MagazineID">期刊编号</param>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="Price">价钱</param>
        /// <param name="Lose">状态</param>
        private void ReturnParameter(string MagazineID, string ReaderID, double Price, string Lose)
        {
            SqlParameter magazineID = null;
            SqlParameter readerID = null;
            SqlParameter price = null;
            SqlParameter lose = null;
            try
            {
                objCMD.Parameters.Clear();
                objCMD.CommandText = "Return_Magazine";
                objCMD.CommandType = CommandType.StoredProcedure;

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

                magazineID = new SqlParameter();

                magazineID.ParameterName = "@magazineID";
                magazineID.Value = MagazineID;
                objCMD.Parameters.Add(magazineID);

                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_Magazine存储过程
        /// </summary>
        /// <param name="MagazineID">期刊编号</param>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="Price">价钱</param>
        /// <param name="Lose">状态</param>
        public void ProcReturnMagazine(string MagazineID, string ReaderID, double Price, string Lose)
        {
            try
            {
                con.Open();
                ReturnParameter(MagazineID, ReaderID, Price, Lose);
                objCMD.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
    }
}