示例#1
0
 //返回书籍检索结果
 public DataTable GetBookByBID(string BID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select *from tb_book where BID=@bid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@bid",BID)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     db.CloseConnection();
     return dt;
 }
示例#2
0
 //返回人员检索结果,不显示借阅信息
 public DataTable GetEmployeeByPSID(string PSID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select PSID,Name,MaxBorrow from tb_employee where PSID=@psid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@psid",PSID)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     db.CloseConnection();
     return dt;
 }
示例#3
0
 //
 public DataTable GetBookByTitle(string Title)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     //认为不存在同名情况
     string sql = "select *from tb_book where Title=@title";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@title",Title)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     db.CloseConnection();
     return dt;
 }
示例#4
0
 //不考虑同名情况
 public DataTable GetEmployeeByName(string Name)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     //认为不存在同名情况
     string sql = "select PSID from tb_employee where Name=@name";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@name",Name)
     };
     string psid = Convert.ToString(db.getTheResult(sql, pms));
     DataTable dt = GetEmployeeByPSID(psid);
     db.CloseConnection();
     return dt;
 }
示例#5
0
    /*具体实现时发现为无法使用的服务层
     * 借书的逻辑在查询书籍之后,BID可确定
    //借书
    //传入参数为ID(辅助函数将name转为PSID)
    //返回是否借阅成功
    //之后配套函数需要执行,返回employee可借数量
    //前置条件:PSID存在,Title可以不存在
    public bool Borrow(string PSID, string Title)
    {
        DBConnectionLayer db = new DBConnectionLayer();
        db.OpenConnection();

        //查Title,如果没书,return false;有书返回第一本BID(不能在tb_borrowed中)
        string sqlSearchBook = "select BID from tb_book where Title=@title except (select BID from tb_borrowed))";
        SqlCeParameter[] pmsBook = new SqlCeParameter[]{
            new SqlCeParameter("@title",Title)
        };
        DataTable dtBook = db.GetDataTable(sqlSearchBook, pmsBook);
        if (dtBook.Rows.Count == 0)
        {
            return false;
        }
        string bid = dtBook.Rows[0][1].ToString();

        //查Employee,如果达到最大借阅,return false
        if (CanBorrowNum(PSID) == 0)
        {
            return false;
        }

        //满足可借条件
        string sql = "insert into tb_borrowed(PSID,BID) values(PSID=@psid,BID=@bid)";
        SqlCeParameter[] pms = new SqlCeParameter[]{
            new SqlCeParameter("@psid",PSID),
            new SqlCeParameter("@title",Title)
        };
        db.ExecuteCommand(sql, pms);
        db.CloseConnection();
        return true;
    }
    */
    //在查询书籍逻辑之后,故书籍一定存在,不能借阅只能是达到了最大借阅量
    //BID可确定,之后search employee,PSID也可确定
    public bool Borrow(string PSID, string BID)
    {
        DBConnectionLayer db = new DBConnectionLayer();
        db.OpenConnection();

        //检查是否达到最大借阅量
        if (CanBorrowNum(PSID) == 0)
        {
            return false;
        }

        //满足可借条件
        string sql = "insert into tb_borrowed(PSID,BID) values(@psid,@bid)";
        SqlCeParameter[] pms = new SqlCeParameter[]{
            new SqlCeParameter("@psid",PSID),
            new SqlCeParameter("@bid",BID)
        };
        db.ExecuteCommand(sql, pms);
        db.CloseConnection();
        return true;
    }
示例#6
0
 //还书后显示借阅者信息的辅助函数,通过BID查询该书的借阅者
 //该书一定被借阅了
 public string GetPSIDByBID(string BID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select PSID from tb_borrowed where BID=@bid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@bid",BID)
     };
     return Convert.ToString(db.getTheResult(sql, pms));
 }
示例#7
0
 //返回未借出的书籍
 public DataTable QueryByTitleUnborrowed(string Title)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "SELECT *FROM tb_book WHERE(tb_book.BID NOT IN (SELECT   BID FROM   tb_borrowed)) AND (tb_book.Title =@title)";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@title",Title)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     return dt;
 }
示例#8
0
 //含有字符则认为是Title
 //返回已借出的书籍
 public DataTable QueryByTitleBorrowed(string Title)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "SELECT tb_book.BID, tb_book.Title, tb_employee.PSID, tb_employee.Name FROM tb_book right OUTER JOIN tb_borrowed ON tb_book.BID = tb_borrowed.BID LEFT OUTER JOIN  tb_employee ON tb_borrowed.PSID = tb_employee.PSID where tb_book.Title=@title";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@title",Title)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     return dt;
 }
示例#9
0
 /*错误的设计,Name位数可能多于PSID
 //通过employee信息查询出其借书状态
 //前置条件:输入为PSID,故需要Operation中NameToPSID辅助
 //返回DataTable, 借阅信息
 public DataTable QueryByEmployee(string PSID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select *from tb_borrowed where PSID=@psid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@psid",PSID)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     db.CloseConnection();
     return dt;
 }
 */
 public DataTable QueryByPSID(string PSID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "SELECT tb_borrowed.PSID, tb_employee.Name, tb_employee.MaxBorrow, tb_borrowed.BID, tb_book.Title FROM tb_borrowed INNER JOIN tb_book ON tb_borrowed.BID = tb_book.BID INNER JOIN tb_employee ON tb_borrowed.PSID = tb_employee.PSID WHERE (tb_borrowed.PSID = @psid)";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@psid",PSID)
     };
     DataTable dt = db.GetDataTable(sql, pms);
     db.CloseConnection();
     return dt;
 }
示例#10
0
 //查询用户借阅数量
 //可借条件的判断<5,借还书之后返回可借数量
 //前置条件:PSID必须存在
 public int CanBorrowNum(string PSID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select count(*) from tb_borrowed where PSID=@psid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@psid",PSID)
     };
     int borrowedNum = Convert.ToInt32(db.getTheResult(sql, pms));
     db.CloseConnection();
     return MaxBorrowNum(PSID)-borrowedNum;
 }
示例#11
0
 //还书
 //仅需要知道BID便可归还
 //同借书后三条
 //前置条件:无
 public void Return(string BID)
 {
     //建立打开数据库连接
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "delete from tb_borrowed where BID=@bid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@bid",BID)
     };
     db.ExecuteCommand(sql, pms);
     db.CloseConnection();
 }
示例#12
0
 //为弹出提示提供信息
 //前置条件:PSID一定存在
 public string PSIDToName(string PSID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select Name from tb_employee where PSID=@psid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@psid",PSID)
     };
     return Convert.ToString(db.getTheResult(sql, pms));
 }
示例#13
0
 //默认姓名无重复,姓名可能在不数据库中
 public string NameToPSID(string Name)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sql = "select PSID from tb_employee where Name=@name";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@name",Name)
     };
     return Convert.ToString(db.getTheResult(sql, pms));
 }
示例#14
0
 //查询用户最大借阅数量
 //服务CanBorrowNum,方便后期维护
 //前置条件:PSID必须存在
 public int MaxBorrowNum(string PSID)
 {
     DBConnectionLayer db = new DBConnectionLayer();
     db.OpenConnection();
     string sqlMaxNum = "select MaxBorrow from tb_employee where PSID=@psid";
     SqlCeParameter[] pms = new SqlCeParameter[]{
         new SqlCeParameter("@psid",PSID)
     };
     int res = Convert.ToInt32(db.getTheResult(sqlMaxNum, pms));
     db.CloseConnection();
     return res;
 }