reader.cs

数据库课程设计

using System;
using System.Collections.Generic;
using System.Text;
using Common;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
   public  class Reader
    {
        //初始化公用层里的DBOperator类
      
        CommonOperator objCommOperator = new CommonOperator();
        SqlConnection con = new SqlConnection("server=(local);uid=sa;pwd=;database=libbook");
        SqlCommand objCMD = new SqlCommand();

        //数据库中是否存在此读者
        public DataTable ReadIDcheck(string ReaderID)
        {

            DataTable dtUserInfo = null;
            DataSet objDataSet = new DataSet();
            string search = "select ReaderID as'读者编号',ReaderName as '读者姓名',ReaderSex  '性别',ReaderBirthday  as '生日'," +
                "ReaderCertificate  as '有效证件',ReaderCertificateID  as '证件号',ReaderPhone as '读者电话',ReaderAccession  as '登记日期'," +
                "  ReaderRestrict  as '至限制日期',ReaderDistinction as '级别',ReaderDay  as '限制借阅天数',ReaderMoney  as '押金'," +
                " ReaderAdmin  as '操作员',ReaderRemark  as '备注',ReaderBookBorrowNum as '借阅书记次数',ReaderMagazineBorrowNum  as '借阅期刊次数'," +
                "ReaderState  as '是否挂失' from Reader where ReaderID='" + ReaderID + "'";
            string sqlTable = "BookInfo";
            try
            {
                objCommOperator.ConnectDb();
                dtUserInfo = objCommOperator.Search(search, sqlTable);
             
            }
            catch (Exception ex)
            {
                throw ex;
            }
                   finally
            {
                objCommOperator.CloseDb();
            }
            return dtUserInfo;
        }

        //  `插入读者信息
        public void InsertReaderInfo(string ReaderID,
           string ReaderName, string ReaderSex, double ReaderMoney, string ReaderDistinction,
            DateTime ReaderBirthday, DateTime ReaderAccession, DateTime ReaderRestrict,
            string ReaderState, int ReaderDay, string ReaderCertificate, string ReaderCertificateID,
            string ReaderPhone, string ReaderAdmin, int ReaderBookBorrowNum, int ReaderMagazineBorrowNum,
            string ReaderRemark)
        {
            try
            {
                //     SqlConnection sql = objCommOperator.Connection();
                string insert = "insert into Reader values(" + "'" + ReaderID + "','" + ReaderName + "','" +
                    ReaderSex + "','" + ReaderBirthday + "','" + ReaderCertificate + "','"
                    + ReaderCertificateID + "','" + ReaderPhone + "','" + ReaderAccession + "','" + ReaderRestrict
                    + "','" + ReaderDistinction + "'," + ReaderDay + "," + ReaderMoney + ",'" + ReaderAdmin + "','" +
                    ReaderRemark + "'," + ReaderBookBorrowNum + "," + ReaderMagazineBorrowNum + ",'" + ReaderState + "')";

                objCommOperator.Execute(insert);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommOperator.CloseDb();
            }
        }
        //更新押金ReaderMoney字段
        public void updateMoney(string ReaderID, double money)
        {
            try
            {
                //           SqlConnection sql = objCommOperator.Connection();
                string update = "update Reader set ReaderMoney=ReaderMoney+"
                    + money + "  where ReaderID=" + "'" + ReaderID + "'";
                objCommOperator.Execute(update);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommOperator.CloseDb();
            }
        }
       /// <summary>
       /// 设置读者的状态
       /// </summary>
       /// <param name="ReaderID"></param>
       /// <param name="ReaderState"></param>
        public void updateReaderState(string ReaderID, string ReaderState)
        {
            try
            {
                string update = "update Reader set ReaderState='" +
                    ReaderState + "' where ReaderID='" + ReaderID + "'";
                objCommOperator.Execute(update);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommOperator.CloseDb();
            }
        }
       /// <summary>
       /// 返回读者表所有信息
       /// </summary>
       /// <returns></returns>
        public DataTable ReaderAllShow()
        {
            DataTable objDataTable = new DataTable();
            
            string sql = "select ReaderID as'读者编号',ReaderName as '读者姓名',ReaderSex  '性别',ReaderBirthday  as '生日',"+
                "ReaderCertificate  as '有效证件',ReaderCertificateID  as '证件号',ReaderPhone as '读者电话',ReaderAccession  as '登记日期',"+
                "  ReaderRestrict  as '至限制日期',ReaderDistinction as '级别',ReaderDay  as '限制借阅天数',ReaderMoney  as '押金',"+
                " ReaderAdmin  as '操作员',ReaderRemark  as '备注',ReaderBookBorrowNum as '借阅书记次数',ReaderMagazineBorrowNum  as '借阅期刊次数',"+
                "ReaderState  as '是否挂失' from Reader";
            string sqlTable = "BookInfo";
            try
            {
                objCommOperator.ConnectDb();
                objDataTable = objCommOperator.Search(sql, sqlTable);
      

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommOperator.CloseDb();
            }
            return objDataTable;
        }
       /// <summary>
       /// 根据读者编号删除读者信息
       /// </summary>
       /// <param name="ReaderID"></param>
        public void ReaderDelete(string ReaderID)
        {
            string str = "delete from Reader where ReaderID='" + ReaderID + "'";
            try
            {
                objCommOperator.Execute(str);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommOperator.CloseDb();
            }
        }
       /// <summary>
       /// 更新读者信息
       /// </summary>
       /// <param name="ReaderID"></param>
       /// <param name="ReaderName"></param>
       /// <param name="ReaderSex"></param>
       /// <param name="ReaderMoney"></param>
       /// <param name="ReaderDistinction"></param>
       /// <param name="ReaderBirthday"></param>
       /// <param name="ReaderAccession"></param>
       /// <param name="ReaderRestrict"></param>
       /// <param name="ReaderState"></param>
       /// <param name="ReaderDay"></param>
       /// <param name="ReaderCertificate"></param>
       /// <param name="ReaderCertificateID"></param>
       /// <param name="ReaderPhone"></param>
       /// <param name="ReaderAdmin"></param>
       /// <param name="ReaderBookBorrowNum"></param>
       /// <param name="ReaderMagazineBorrowNum"></param>
       /// <param name="ReaderRemark"></param>
       public void UpdateReaderInfo(string ReaderID,
         string ReaderName, string ReaderSex, double ReaderMoney, string ReaderDistinction,
          DateTime ReaderBirthday, DateTime ReaderAccession, DateTime ReaderRestrict,
          string ReaderState, int ReaderDay, string ReaderCertificate, string ReaderCertificateID,
          string ReaderPhone, string ReaderAdmin, int ReaderBookBorrowNum, int ReaderMagazineBorrowNum,
          string ReaderRemark)
       {
           try
           {
               //     SqlConnection sql = objCommOperator.Connection();
               string insert = "update Reader " +
                   "set ReaderName='" + ReaderName + "',ReaderSex='" +
                   ReaderSex + "',ReaderBirthday='" + ReaderBirthday + "',ReaderCertificate='" + ReaderCertificate + "',ReaderCertificateID='"
                   + ReaderCertificateID + "',ReaderPhone='" + ReaderPhone + "',ReaderAccession='" + ReaderAccession + "',ReaderRestrict='" + ReaderRestrict
                   + "',ReaderDistinction='" + ReaderDistinction + "',ReaderDay=" + ReaderDay + ",ReaderMoney=" + ReaderMoney + ",ReaderAdmin='" + ReaderAdmin + "',ReaderRemark='" +
                   ReaderRemark + "',ReaderBookBorrowNum=" + ReaderBookBorrowNum + ",ReaderMagazineBorrowNum=" + ReaderMagazineBorrowNum + ",ReaderState='" + ReaderState + "' where ReaderID='" + ReaderID + "'";

               objCommOperator.Execute(insert);
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               objCommOperator.CloseDb();
           }
       }
       /// <summary>
       /// 调用DELETE_Reader存储过程删除读者信息及相关信息
       /// </summary>
       /// <param name="ReaderID"></param>
       private void procDeleteReader(string ReaderID)
       {
           SqlParameter readerID = null;

           try
           {
               objCMD.Parameters.Clear();
               objCMD.CommandText = "DELETE_Reader";
               objCMD.CommandType = CommandType.StoredProcedure;

               readerID = new SqlParameter();
               readerID.ParameterName = "@ReaderID";
               readerID.Value = ReaderID;
               objCMD.Parameters.Add(readerID);
               objCMD.Connection = con;
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }
       public void procDeleteReaderID(string ReaderID)
       {
           try
           {
               con.Open();
               procDeleteReader(ReaderID);
               objCMD.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               con.Close();
           }
       }
       /// <summary>
       /// 查询读者级别
       /// </summary>
       /// <param name="ReaderID"></param>
       /// <returns></returns>
       public DataTable SelectReaderDistinction(string ReaderID)
       {
           DataTable objDataTable = new DataTable();
         
           string sql = "select ReaderDistinction   from Reader where ReaderID='" + ReaderID + "'";
           string sqlTable = "BookInfo";
           try
           {
               objCommOperator.ConnectDb();
               objDataTable = objCommOperator.Search(sql, sqlTable);


           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               objCommOperator.CloseDb();
           }
           return objDataTable;
       }
    }
}