//返回书籍检索结果
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;
}
//返回人员检索结果，不显示借阅信息
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;
}
//
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;
}
//不考虑同名情况
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;
}
/*具体实现时发现为无法使用的服务层
* 借书的逻辑在查询书籍之后，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;
}
//还书后显示借阅者信息的辅助函数，通过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));
}
//返回未借出的书籍
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;
}
//含有字符则认为是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;
}
/*错误的设计，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;
}
//查询用户借阅数量
//可借条件的判断<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;
}
//还书
//仅需要知道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();
}
//为弹出提示提供信息
//前置条件：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));
}
//默认姓名无重复，姓名可能在不数据库中
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));
}
//查询用户最大借阅数量
//服务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;
}