//Get the price according to BookId
public double GetPriceById(string bookId)
{
string sql = "select BookPrice from Book Where BookId=@BookId";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@BookId", bookId),
};
try
{
object obj = SQLHelper.GetOneResult(sql, para);
if (obj == null)
{
return(0.00);
}
else
{
return(Convert.ToDouble(obj));
}
}
catch (Exception ex)
{
throw ex;
}
}
//Determine if the category name exists
public bool IsExistTypeName(string typeName)
{
//Preparing SQL
string sql = "select TypeId from BookType where TypeName=@TypeName";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@TypeName", typeName),
};
//Perform
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Determine if an ISBN exists
public bool IsExistISBN(string isbn)
{
//Preparing SQL
string sql = "Select BookId from Book where ISBN=@ISBN";
//Populate parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@ISBN", isbn),
};
//Execute and return results
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Determine if a member has borrowed a book
public bool IsBorrowedBook(string memberId)
{
//Preparing SQL statements
string sql = "Select BorrowId from BorrowBook Where MemberId=@MemberId";
//Preparing parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@MemberId", memberId),
};
//Start execution and return value
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Write a log
public int WirteLoginLog(LoginLogs objLoginLogs)
{
//Preparing SQL statements
string sql = "Insert into LoginLogs (LoginId, UserName, LoginComputer, LoginTime) ";
sql += "Values (@LoginId, @UserName, @LoginComputer, @LoginTime); Select @@Identity";
//Preparing parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LoginId", objLoginLogs.LogInId),
new SqlParameter("@UserName", objLoginLogs.UserName),
new SqlParameter("@LoginComputer", objLoginLogs.LoginComputer),
new SqlParameter("@LoginTime", objLoginLogs.LoginTime),
};
//Execute and return
try
{
return(Convert.ToInt32(SQLHelper.GetOneResult(sql, para)));
}
catch (Exception ex)
{
throw ex;
}
}
//Determine if a category number has a subclass
public bool IsExistSubType(int typeId)
{
//Preparing SQL statements
string sql = "select TypeName from BookType Where ParentTypeId=@TypeId ";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@TypeId", typeId),
};
//Submit
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Verify that the membership category name exists
public bool IsExistLevelName(string levelName)
{
//Preparing SQL statements
string sql = "Select LevelId from MemberLevel where LevelName=@LevelName ";
//Preparing parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LevelName", levelName),
};
//Submit
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Determine if the login account exists
public bool IsExistLoginId(int loginId)
{
//Preparing SQL statements
string sql = "Select LoginPwd from SysAdmins Where LoginId=@Loginid ";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LoginId", loginId),
};
//Execute and return
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Generate a membership number
public string BuildMemberId(int levelId)
{
//Preparing SQL queries
string sql = "Select top 1 MemberId from member where MemberLevel = @LevelId order by MemberId desc ";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LevelId", levelId),
};
//execution
try
{
//Receive execution results
object obj = SQLHelper.GetOneResult(sql, para);
//judgment
if (obj == null)
{
return(levelId.ToString("00") + "00000001");
}
else
{
return(levelId.ToString("00") + (Convert.ToInt32(obj.ToString().Substring(2)) + 1).ToString("00000000"));
}
}
catch (Exception ex)
{
throw ex;
}
}
//Generate a node number
public string BuildNewTypeId(int typeId)
{
//Preparing SQL statements
string sql = "Select top 1 TypeId, TypeName, ParentTypeId, TypeDESC from BookType Where ParentTypeId = @TypeId order by TypeId DESC";
//Prepare parameters for SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@TypeId", typeId),
};
//Execute and return
try
{
object obj = SQLHelper.GetOneResult(sql, para);
if (obj == null)
{
return(typeId.ToString() + "01");
}
else
{
return((Convert.ToInt32(obj) + 1).ToString());
}
}
catch (Exception ex)
{
throw ex;
}
}
//Determine if the Publisher name exists
public bool IsExistPressName(string pressName)
{
//Preparing SQL statements
string sql = "Select PressId from BookPress Where PressName=@PressName";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@PressName", pressName),
};
//Execute and return results
try
{
if (SQLHelper.GetOneResult(sql, para) == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
throw e;
}
}
//Get borrowNum based on memeberId
public int GetBorrowedNumByMemberId(string memberId)
{
//Preparing SQL statements
string sql = "select BorrowedNum from BorrowBook Where MemberId=@MemberId";
//Preparing parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@MemberId", memberId),
};
//Execute and return
try
{
return(Convert.ToInt32(SQLHelper.GetOneResult(sql, para)));
}
catch (Exception ex)
{
throw ex;
}
}
//Get a certain ISBN inventory
public int GetInventoryNumByISBN(string isbn)
{
//Preparing SQL
string sql = "Select InventoryNum from Book where ISBN=@ISBN";
//Populate parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@ISBN", isbn),
};
//Execute and return results
try
{
return(Convert.ToInt32(SQLHelper.GetOneResult(sql, para)));
}
catch (Exception ex)
{
throw ex;
}
}
//Get a name by Id
public string GetUserName(int loginId)
{
//Preparing SQL
string sql = "Select UserName from SysAdmins where LoginId=@LoginId";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LoginId", loginId),
};
//Execute and return
try
{
return(SQLHelper.GetOneResult(sql, para).ToString());
}
catch (Exception ex)
{
throw ex;
}
}
//Count the amount of expired books
public int GetBorrowBookOverdue(string borrowId)
{
//Preparing SQL statements: Total
string sql = "Select count(*) from BorrowBookDetail where BorrowId=@BorrowId And IsReturn=0 And IsHandleOverdueorLost=0 And IsOverdue=1";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@BorrowId", borrowId),
};
//Execute and return
try
{
return(Convert.ToInt32(SQLHelper.GetOneResult(sql, para)));
}
catch (Exception ex)
{
throw ex;
}
}
//Get Publisher name
public string GetPressNameById(int pressId)
{
// Preparing SQL statements
string sql = "Select PressName from BookPress Where PressId=@PressId ";
//Preparing parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@PressId", pressId),
};
//Execute and return results
try
{
return(SQLHelper.GetOneResult(sql, para).ToString());
}
catch (Exception ex)
{
throw ex;
}
}
//Obtain membership name based on membership number
public string GetNameById(int levelId)
{
//Preparing SQL statements
string sql = "Select LevelName from MemberLevel where LevelId=@LevelId";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LevelId", levelId),
};
//execution
try
{
return(SQLHelper.GetOneResult(sql, para).ToString());
}
catch (Exception ex)
{
throw ex;
}
}
//Get a breakdown of the appropriate category
public string GetTypeDESC(int typeId)
{
//Preparing SQL queries
string sql = "Select TypeDESC from BookType Where TypeId=@TypeId";
//Preparing parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@TypeId", typeId),
};
//Perform
try
{
return(SQLHelper.GetOneResult(sql, para).ToString());
}
catch (Exception ex)
{
throw ex;
}
}
//Get the biggest book to borrow books through Levelid
public int GetMaxBorrowNumById(int levelId)
{
//Preparing SQL statements
string sql = "Select MaxBorrowNum from MemberLevel where LevelId=@LevelId";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LevelId", levelId),
};
//execution
try
{
return(Convert.ToInt32(SQLHelper.GetOneResult(sql, para)));
}
catch (Exception ex)
{
throw ex;
}
}
//Get DetailId
public int GetDetailId(string borrowId, string bookId)
{
//Preparing SQL statements
string sql = "select DetailId from BorrowBookDetail Where BorrowId=@BorrowId And BookId=@BookId Order by DetailId DESC";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@BookId", bookId),
new SqlParameter("@BorrowId", borrowId),
};
//Execute and return results
try
{
return(Convert.ToInt32(SQLHelper.GetOneResult(sql, para)));
}
catch (Exception ex)
{
throw ex;
}
}
//Generate a level number
public string BuildNewLevelId()
{
//SQL Statement to prepare query
string sql = "Select Top 1 LevelId from MemberLevel Order By LevelId DESC ";
//Execution and return values
try
{
object obj = SQLHelper.GetOneResult(sql);
if (obj == null)
{
return("1");
}
else
{
return((Convert.ToInt32(obj) + 1).ToString());
}
}
catch (Exception ex)
{
throw ex;
}
}
//Generate Administrator number
public string BuildLoginId()
{
//Preparing SQL statements
string sql = "Select Top 1 LoginId from SysAdmins Order by LoginId DESC ";
//Execute and return
try
{
object obj = SQLHelper.GetOneResult(sql);
if (obj == null)
{
return("1001");
}
else
{
return((Convert.ToInt32(obj) + 1).ToString());
}
}
catch (Exception ex)
{
throw ex;
}
}
//Generate a press number and return
public string BuildNewPressId()
{
//Prepare SQL: Get the largest one PressId
string sql = "Select TOP 1 PressId From BookPress Order by PressId DESC";
//Execute and return results
try
{
object obj = SQLHelper.GetOneResult(sql);
//Judgment
if (obj == null)
{
return("1000");
}
else
{
return((Convert.ToInt32(obj) + 1).ToString());
}
}
catch (Exception ex)
{
throw ex;
}
}
//Generate a book Number
public string BuildNewBookId(int typeId)
{
if (typeId.ToString().Length == 3)
{
//Preparing SQL
string sql = "Select top 1 BookId from Book where BookType=@TypeId order by BookId DESC ";
//Populate parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@TypeId", typeId),
};
//Execute and return results
try
{
object obj = SQLHelper.GetOneResult(sql, para);
//If the value is empty
if (obj == null)
{
return(typeId.ToString() + "0000001");
}
else //If it's not empty
{
return(typeId.ToString() + "00" + (Convert.ToInt32(obj.ToString().Substring(5)) + 1).ToString("00000"));
}
}
catch (Exception ex)
{
throw ex;
}
}
else if (typeId.ToString().Length == 5)
{
//Preparing SQL
string sql = "Select top 1 BookId from Book where BookType=@TypeId order by BookId DESC ";
//Populate parameters in SQL statements
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@TypeId", typeId),
};
//Execute and return results
try
{
object obj = SQLHelper.GetOneResult(sql, para);
//If the value is empty
if (obj == null)
{
return(typeId.ToString("00000") + "00001");
}
else //If it's not empty
{
return(typeId.ToString("00000") + (Convert.ToInt32(obj.ToString().Substring(5)) + 1).ToString("00000"));
}
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return("");
}
}
