magazineinfo.cs

数据库课程设计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Common;
using System.Data.SqlClient;
namespace DataAccess
{
    public class MagazineInfo
    {
        //初始化公用层里的CommonOperator类
        Common.CommonOperator objCommonOpera = new CommonOperator();
        SqlConnection con = new SqlConnection("server=(local);uid=sa;pwd=sa;database=libbook");
        SqlCommand objCMD = new SqlCommand();
         /// <summary>
        /// 此方法查询期刊表的所有数据
        /// </summary>
        /// <returns>返回objTable</returns>
        public DataTable SelectMagazineInfo()
        {
            DataTable objTable = null;
            string sql = "select MagazineID as '期刊编号',MagazineName as '期刊名字', MagazineGenre as '类型',MagazineMan as '出版商',"
+"MagazineTime as '出版日期',MagazineMark as '期刊号',MagazineSumMark as '总期刊号',MagazinePrice as '价格',MagazineborrowPrice as '借阅价格',"
+"MagazineExistent as '现存数量',MagazineSum as '总数量',MagazineEnterTime as '入库时间',MagazineAdmin as '操作员',MagazineBriefIntroduction as '简介',"
+"MagazineBorrowNum as '借出次数',MagazineState as '是否注销' from  Magazine";

            string sqlTable = "MagazineInfo";
            try
            {
                objCommonOpera.ConnectDb();
                objTable = objCommonOpera.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOpera.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 此方法根据期刊名称模糊查询
        /// </summary>
        /// <param name="MagazineName">期刊名称</param>
        /// <returns>返回objTable</returns>
        public DataTable SelectMagazineName(string MagazineName)
        {
            DataTable objTable = null;
            string sql = "select MagazineID as '期刊编号',MagazineName as '期刊名字', MagazineGenre as '类型',MagazineMan as '出版商',"
+"MagazineTime as '出版日期',MagazineMark as '期刊号',MagazineSumMark as '总期刊号',MagazinePrice as '价格',MagazineborrowPrice as '借阅价格',"
+"MagazineExistent as '现存数量',MagazineSum as '总数量',MagazineEnterTime as '入库时间',MagazineAdmin as '操作员',MagazineBriefIntroduction as '简介',"
+ "MagazineBorrowNum as '借出次数',MagazineState as '是否注销' from  Magazine where MagazineName like '%" + MagazineName + "%'";
            string sqlTable = "MagazineInfo";
            try
            {
                objCommonOpera.ConnectDb();
                objTable = objCommonOpera.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOpera.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 此方法根据期刊ID模糊查询
        /// </summary>
        /// <param name="ID">期刊编号</param>
        /// <returns>返回objTable</returns>
        public DataTable SelectMagazineID(string ID)
        {
            DataTable objTable = null;
            string sql = "select MagazineID as '期刊编号',MagazineName as '期刊名字', MagazineGenre as '类型',MagazineMan as '出版商',"
+ "MagazineTime as '出版日期',MagazineMark as '期刊号',MagazineSumMark as '总期刊号',MagazinePrice as '价格',MagazineborrowPrice as '借阅价格',"
+ "MagazineExistent as '现存数量',MagazineSum as '总数量',MagazineEnterTime as '入库时间',MagazineAdmin as '操作员',MagazineBriefIntroduction as '简介',"
+ "MagazineBorrowNum as '借出次数',MagazineState as '是否注销' from  Magazine where MagazineID like '%" + ID + "%'";
            string sqlTable = "MagazineInfo";
            try
            {
                objCommonOpera.ConnectDb();
                objTable = objCommonOpera.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOpera.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 子查询
        /// 根据读者编号＋状态查询期刊编号，在根据期刊编号查询５个指定的字段
        /// </summary>
        /// <param name="ReaderID">读者编号</param>
        /// <param name="Lose">状态</param>
        /// <returns>返回objTable</returns>
        public DataTable SelectMagazineBorrowInfo(string ReaderID, string Lose)
        {
            DataTable objTable = null;
            string sql = "select MagazineID as '期刊编号',MagazineName as '期刊名称',MagazineGenre as '类型',MagazineExistent as ' 现存数量',MagazineState as '是否注销'  from Magazine where MagazineID  in "
                + "(select MagazineID from MagazineBorrow where ReaderID = '" + ReaderID + "' and BorrowBookState = '" + Lose + "')";
            string sqlTable = "MagazineInfo";
            try
            {
                objCommonOpera.ConnectDb();
                objTable = objCommonOpera.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOpera.ConnectDb();
            }
            return objTable;
        }
         //插入期刊信息
        public void AddNewMagazine(string _MagazineID, string _MagazineName,
        string _MagazineGenre, string _MagazineMan, string _MagazineTime,
        int _MagazineMark, int _MagazineSumMark, double _MagazinePrice,
        double _MagazineborrowPrice, int _MagazineExistent, int _MagazineSum, string _MagazineEnterTime,
         string _MagazineAdmin, string _MagazineBriefIntroduction,
          int _BorrowNum, int _MagazineBorrowNum)
        {
            objCommonOpera = new CommonOperator();
            objCommonOpera.ConnectDb();
            string sql;
            try
            {
                sql = "insert into Magazine values" +
                "('" + _MagazineID + "','" + _MagazineName + "','" + _MagazineGenre + "'," +
                "'" + _MagazineMan + "','" + _MagazineTime + "'," + _MagazineMark + "," +
                "" + _MagazineSumMark + "," + _MagazinePrice + "," + _MagazineborrowPrice + "," +
                 "" + _MagazineExistent + "," + _MagazineSum + ",'" + _MagazineEnterTime + "'," +
                "'" + _MagazineAdmin + "','" + _MagazineBriefIntroduction + "'," + _BorrowNum + "," + _MagazineBorrowNum + ")";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            SqlCommand sc = new SqlCommand(sql, CommonOperator.sqlConnection);
            sc.ExecuteNonQuery();
        }
        /// <summary>
        /// 此方法根据期刊名字进行查询
        /// </summary>
        /// <param name="MagazineName">读者编号</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable MagazineSelect(string MagazineName)
        {
            DataTable objTable = null;
            string sql = "select * from Magazine where MagazineName  = '" + MagazineName + "'";
            string sqlTable = "MagazineInfo";
            try
            {
                objCommonOpera.ConnectDb();
                objTable = objCommonOpera.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOpera.CloseDb();
            }
            return objTable;
        }
        /// <summary>
        /// 添加期刊如果存在则更新期刊的数量
        /// </summary>
        /// <param name="MagazineName">期刊名</param>
        /// <param name="MagazineExistent">期刊现存数量</param>
        /// <param name="MagazineSum">期刊总数量</param>
        public void MagazineUpdate(string MagazineName, int MagazineExistent, int MagazineSum)
        {
                  objCommonOpera = new CommonOperator();
            objCommonOpera.ConnectDb();
            string sql; 
    
            try
            {
             sql = "update Magazine "+
                           "   set MagazineExistent=MagazineExistent+" + MagazineExistent + ", MagazineSum=MagazineSum+" + MagazineSum + ""
                            + "where  MagazineName= '" + MagazineName + "'";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            SqlCommand sc = new SqlCommand(sql, CommonOperator.sqlConnection);
            sc.ExecuteNonQuery();
      
        }
        /// <summary>
        /// 查询期刊的借阅价格
        ///
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataTable SelectMagazineborrowPrice(string ID)
        {
            DataTable objBookInfoTable = new DataTable();
            string sql = "select MagazinePrice,MagazineborrowPrice  from Magazine ";
            string sqlTable = "MagazineborrowPrice ";
            try
            {
                objCommonOpera.ConnectDb();
                objBookInfoTable = objCommonOpera.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOpera.CloseDb();
            }
            return objBookInfoTable;
        }
        /// <summary>
        /// 删除期刊表信息存储过程
        /// </summary>
        /// <param name="MagazineID"></param>
        private void procDeleteMagazine(string MagazineID)
        {
            SqlParameter magazineID = null;
  
            try
            {
                objCMD.Parameters.Clear();
                objCMD.CommandText = "DELETE_Magazine";
                objCMD.CommandType = CommandType.StoredProcedure;

                magazineID = new SqlParameter();
                magazineID.ParameterName = "@MagazineID";
                magazineID.Value = MagazineID;
                objCMD.Parameters.Add(magazineID);
                objCMD.Connection = con;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除期刊表信息存储过程
        /// </summary>
        /// <param name="MagazineID"></param>
        public void procDelMagazine(string MagazineID)
        {
            try
            {
                con.Open();
                procDeleteMagazine(MagazineID);
                objCMD.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        public void UpdateMagazine(string _MagazineID, string _MagazineName,
string _MagazineGenre, string _MagazineMan, string _MagazineTime,
int _MagazineMark, int _MagazineSumMark, double _MagazinePrice,
double _MagazineborrowPrice, int _MagazineExistent, int _MagazineSum, string _MagazineEnterTime,
 string _MagazineAdmin, string _MagazineBriefIntroduction,
  int _BorrowNum, int _MagazineBorrowNum)
        {
            objCommonOpera = new CommonOperator();
            objCommonOpera.ConnectDb();
            string sql;
            try
            {
                sql = "update Magazine " +
                "set MagazineName ='" + _MagazineName + "',MagazineGenre ='" + _MagazineGenre + "',MagazineMan=" +
                "'" + _MagazineMan + "',MagazineTime ='" + _MagazineTime + "',MagazineMark  =" + _MagazineMark + "," +
                "MagazineSumMark =" + _MagazineSumMark + ",MagazinePrice  =" + _MagazinePrice + ",MagazineborrowPrice =" + _MagazineborrowPrice + ",MagazineExistent =" +
                 "" + _MagazineExistent + ",MagazineSum = " + _MagazineSum + ",MagazineEnterTime ='" + _MagazineEnterTime + "',MagazineAdmin =" +
                "'" + _MagazineAdmin + "',MagazineBriefIntroduction ='" + _MagazineBriefIntroduction + "',MagazineBorrowNum =" + _BorrowNum + ",MagazineState =" + _MagazineBorrowNum + "where MagazineID ='" + _MagazineID + "'";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            SqlCommand sc = new SqlCommand(sql, CommonOperator.sqlConnection);
            sc.ExecuteNonQuery();
        }
    }
}