//返回人员检索结果，不显示借阅信息
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 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 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 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;
}