//不考虑同名情况
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查询该书的借阅者
//该书一定被借阅了
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));
}
//查询用户借阅数量
//可借条件的判断<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;
}
//为弹出提示提供信息
//前置条件：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;
}