bookinfo.cs

数据库课程设计

using System;
using System.Collections.Generic;
using System.Text;
using Common;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
    public class BookInfo
    {
        CommonOperator ComOP = new CommonOperator();
        SqlConnection con = new SqlConnection("server=(local);uid=sa;pwd=sa;database=libbook");
        SqlCommand objCMD = new SqlCommand();
        CommonOperator objCommonOperator = new CommonOperator();
        /// <summary>
        /// 此方法调用通用层的Search方法得到BookInfo表的所有数据
        /// </summary>
        /// <returns>返回BookInfo表的所有数据</returns>
        public DataTable GridViewBookInfo()
        {
            DataTable BookInfoTable = null;
            string sql = "select BooksID as '书籍编号',BooksName as '书籍名称', BooksGenre as '类型',BooksAuthor as '作者',BookConcernID as '出版商ID',"
                +"BooksPrice as '价格',BooksborrowPrice as '借阅价格',BooksExistent as '现存数量',BooksSum as '总数量',BooksEnterTime as '入库时间',"
+"BooksAdmin as '操作员',BooksBriefIntroduction as '简介',BooksBorrowNum as '借出次数',BooksState as '是否注销' from  Books";
            string sqlTable = "BookInfo";
            try
            {
                ComOP.ConnectDb();
                BookInfoTable = ComOP.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
            return BookInfoTable;
        }
        /// <summary>
        /// 此方法根据图书名称进行模糊查询，调用公用层的Search方法返回一个DataTable
        /// </summary>
        /// <param name="name">图书名称</param>
        /// <returns>返回根据图书名称查找到的DataTable</returns>
        public DataTable SelectLikeBookName(string name)
        {
            DataTable objBookInfoTable = new DataTable();
            string sql = "select BooksID as '书籍编号',BooksName as '书籍名称', BooksGenre as '类型',BooksAuthor as '作者',BookConcernID as '出版商ID',"
                +"BooksPrice as '价格',BooksborrowPrice as '借阅价格',BooksExistent as '现存数量',BooksSum as '总数量',BooksEnterTime as '入库时间',"
+ "BooksAdmin as '操作员',BooksBriefIntroduction as '简介',BooksBorrowNum as '借出次数',BooksState as '是否注销' from  Books where BooksName like '%" + name + "%'";
            string sqlTable = "BookInfo";
            try
            {
                ComOP.ConnectDb();
                objBookInfoTable = ComOP.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
            return objBookInfoTable;
        }
        /// <summary>
        /// 此方法根据图书编号进行模糊查询，调用共用层的Search方法返回一个DataTable
        /// </summary>
        /// <param name="ID">图书编号</param>
        /// <returns>返回根据编号查找到的DataTable</returns>
        public DataTable SelectLikeBookID(string ID)
        {
            DataTable objBookInfoTable = new DataTable();
            string sql = "select BooksID as '书籍编号',BooksName as '书籍名称', BooksGenre as '类型',BooksAuthor as '作者',BookConcernID as '出版商ID',"
                + "BooksPrice as '价格',BooksborrowPrice as '借阅价格',BooksExistent as '现存数量',BooksSum as '总数量',BooksEnterTime as '入库时间',"
+ "BooksAdmin as '操作员',BooksBriefIntroduction as '简介',BooksBorrowNum as '借出次数',BooksState as '是否注销' from  Books where"
                             + " BooksID like '%" + ID + "%'";
            string sqlTable = "BookInfo";
            try
            {
                ComOP.ConnectDb();
                objBookInfoTable = ComOP.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
            return objBookInfoTable;
        }
        /// <summary>
        /// 查询书籍的借阅价格
        ///
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataTable SelectBooksBookID(string ID)
        {
            DataTable objBookInfoTable = new DataTable();
            string sql = "select BooksPrice,BooksborrowPrice from Books where BooksID='" + ID + "'";
            string sqlTable = "BookInfo";
            try
            {
                ComOP.ConnectDb();
                objBookInfoTable = ComOP.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
            return objBookInfoTable;
        }
        /// <summary>
        /// 插入一条新的
        /// </summary>
        /// <param name="BooksID"></param>
        /// <param name="BooksName"></param>
        /// <param name="BooksGenre"></param>
        /// <param name="BooksAuthor"></param>
        /// <param name="BookConcernID"></param>
        /// <param name="BooksPrice"></param>
        /// <param name="BooksborrowPrice"></param>
        /// <param name="BooksExistent"></param>
        /// <param name="BooksSum"></param>
        /// <param name="BooksEnterTime"></param>
        /// <param name="BooksAdmin"></param>
        /// <param name="BooksBriefIntroduction"></param>
        /// <param name="BooksBorrowNum"></param>
        /// <param name="BooksState"></param>
        public void InsertBooks(string BooksID, string BooksName, string BooksGenre, string BooksAuthor, string BookConcernID, double BooksPrice, double BooksborrowPrice, int BooksExistent, int BooksSum, string BooksEnterTime, string BooksAdmin, string BooksBriefIntroduction, int BooksBorrowNum,int BooksState)
        {
            try
            {
                string sql = "insert into Books values('" + BooksID + "','" + BooksName + "','" + BooksGenre + "','" + BooksAuthor + "',"+
                    "'" + BookConcernID + "'," + BooksPrice + "," + BooksborrowPrice + "," + BooksExistent + "," + BooksSum + ",'" + BooksEnterTime + "','" + BooksAdmin + "','" + BooksBriefIntroduction + "'," + BooksBorrowNum +"," + BooksState + ")";
                ComOP.Execute(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
        }
        /// <summary>
        /// 该书存在就更新该书的数量
        /// </summary>
        /// <param name="BooksID"></param>
        /// <param name="BooksName"></param>
        /// <param name="BooksExistent"></param>
        /// <param name="BooksSum"></param>
        public void UpdateBooks(string BooksID, string BooksName, int BooksExistent, int BooksSum)
        {
            try
            {
                string sql = "update Books"+
                             "    set  BooksExistent=BooksExistent+" + BooksExistent + ",BooksSum=BooksSum+" + BooksSum + ""+
                             "where BooksID='" + BooksID + "' and BooksName='" + BooksName + "'";
                ComOP.Execute(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
        }
        /// <summary>
        /// 查该书是否已经存在
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="BooksName"></param>
        /// <returns></returns>

        public DataTable SelectBooks(string ID,string BooksName)
        {
            DataTable objBookInfoTable = new DataTable();
            string sql = "select BooksborrowPrice from Books where BooksID='" + ID + "'and BooksName ='" + BooksName + "'";
            string sqlTable = "BookInfo";
            try
            {
                ComOP.ConnectDb();
                objBookInfoTable = ComOP.Search(sql, sqlTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
            return objBookInfoTable;
        }
        /// <summary>
        /// 更新书籍信息
        /// </summary>
        /// <param name="BooksID"></param>
        /// <param name="BooksName"></param>
        /// <param name="BooksGenre"></param>
        /// <param name="BooksAuthor"></param>
        /// <param name="BookConcernID"></param>
        /// <param name="BooksPrice"></param>
        /// <param name="BooksborrowPrice"></param>
        /// <param name="BooksExistent"></param>
        /// <param name="BooksSum"></param>
        /// <param name="BooksEnterTime"></param>
        /// <param name="BooksAdmin"></param>
        /// <param name="BooksBriefIntroduction"></param>
        /// <param name="BooksBorrowNum"></param>
        /// <param name="BooksState"></param>
        public void UpdateBooks(string BooksID, string BooksName, string BooksGenre, string BooksAuthor, string BookConcernID, double BooksPrice, double BooksborrowPrice, int BooksExistent, int BooksSum, string BooksEnterTime, string BooksAdmin, string BooksBriefIntroduction, int BooksBorrowNum, int BooksState)
        {
            try
            {
                string sql = "update Books "+
                    "set BooksName='" + BooksName + "',BooksGenre='" + BooksGenre + "',BooksAuthor='" + BooksAuthor + "'," +
                    "BookConcernID='" + BookConcernID + "',BooksPrice=" + BooksPrice + ",BooksborrowPrice=" + BooksborrowPrice + ",BooksExistent=" + BooksExistent + ",BooksSum=" + BooksSum + ",BooksEnterTime='" + BooksEnterTime + "',BooksAdmin='" + BooksAdmin + "',BooksBriefIntroduction='" + BooksBriefIntroduction + "',BooksBorrowNum=" + BooksBorrowNum + ",BooksState=" + BooksState + ""+
                    "where BooksID='" + BooksID + "'";
                ComOP.Execute(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ComOP.CloseDb();
            }
        }
        private void procDeleteBooks(string BooksID)
        {
            SqlParameter booksID = null;
  
            try
            {
                objCMD.Parameters.Clear();
                objCMD.CommandText = "DELETE_Books";
                objCMD.CommandType = CommandType.StoredProcedure;

                booksID = new SqlParameter();
                booksID.ParameterName = "@BooksID";
                booksID.Value = BooksID;
                objCMD.Parameters.Add(booksID);
                objCMD.Connection = con;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void procDelBooks(string BooksID)
        {
            try
            {
                con.Open();
                procDeleteBooks(BooksID);
                objCMD.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 删除出版社时调用
        /// </summary>
        /// <param name="BookConcernID"></param>
        public void BookConcernIDDelete(string BookConcernID)
        {
            string str = "delete from Books where BookConcernID='" + BookConcernID + "'";
            try
            {
                objCommonOperator.Execute(str);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objCommonOperator.CloseDb();
            }
        }
    }
}