public DataSet queryBorrowlistDao(string tablename, string userId)
{
SqlParams p = new SqlParams("userId", Convert.ToInt32(userId), (int)SqlParamsType.IntType);
ArrayList arr = new ArrayList();
arr.Add(p);
string sql = "select Users.UserId,Books.BookName,Books.Author,Books.Summary,Books.IsBorrow,Books.BookId,Books.Cover from Users join BorrowInfo on Users.UserId = BorrowInfo.UserId join Books on BorrowInfo.BookId = Books.BookId where Users.UserId = @" + p.key;
DataSet data = conn.ExecuteSqlForDataSet(arr, sql, tablename);
return(data);
}
public DataSet QueryIsBorrow(string bookId, string tablename)
{
SqlParams bookIdParam = new SqlParams("bookId", Convert.ToInt32(bookId), (int)SqlParamsType.IntType);
ArrayList arr = new ArrayList();
arr.Add(bookIdParam);
string sql = "select IsBorrow from Books where BookId = @" + bookIdParam.key;
DataSet data = conn.ExecuteSqlForDataSet(arr, sql, tablename);
return(data);
}
public int UpdateBooksStatus(string bookId)
{
SqlParams bookIdParam = new SqlParams("bookId", Convert.ToInt32(bookId), (int)SqlParamsType.IntType);
ArrayList arr = new ArrayList();
arr.Add(bookIdParam);
string sql = "update Books set IsBorrow = 0 where BookId = @" + bookIdParam.key;
int num = conn.ExecuteSqlForNon(arr, sql);
return(num);
}
public DataSet SelectIsRepeat(string account, string tablename)
{
SqlParams p = new SqlParams("account", account, (int)SqlParamsType.StringType);
ArrayList arr = new ArrayList();
arr.Add(p);
string sql = "select count(*) as Count from Users where Account = @" + p.key;
DataSet data = conn.ExecuteSqlForDataSet(arr, sql, tablename);
return(data);
}
public int ChangeLoginStauts(int userId, string tablename)
{
SqlParams p = new SqlParams("userId", userId, (int)SqlParamsType.IntType);
string sql = "update Users set IsLogin = 1 where UserId = @" + p.key;
ArrayList arr = new ArrayList();
arr.Add(p);
//DataSet data = conn.ExecuteSqlForDataSet(arr,sql,tablename);
int num = conn.ExecuteSqlForNon(arr, sql);
return(num);
}
public int UpdateIcon(string iconUrl, string userId, string tablename)
{
SqlParams userIdParam = new SqlParams("userId", userId, (int)SqlParamsType.IntType);
SqlParams iconUrlParam = new SqlParams("iconUrl", iconUrl, (int)SqlParamsType.StringType);
ArrayList arr = new ArrayList();
arr.Add(iconUrlParam);
arr.Add(userIdParam);
string sql = "update Users set Icon = @" + iconUrlParam.key + " where UserId = @" + userIdParam.key;
int num = conn.ExecuteSqlForNon(arr, sql);
return(num);
}
public int DeleteBorrowInfoList(string bookId, string userId)
{
SqlParams pBookId = new SqlParams("bookId", Convert.ToInt32(bookId), (int)SqlParamsType.IntType);
SqlParams pUserId = new SqlParams("userId", Convert.ToInt32(userId), (int)SqlParamsType.IntType);
ArrayList arr = new ArrayList();
arr.Add(pBookId);
arr.Add(pUserId);
string sql = "delete from BorrowInfo where BookId = @" + pBookId.key + " and UserId = @" + pUserId.key;
int num = conn.ExecuteSqlForNon(arr, sql);
return(num);
}
public int InsertBorrowList(string userId, string bookId)
{
SqlParams userIdParam = new SqlParams("userId", Convert.ToInt32(userId), (int)SqlParamsType.IntType);
SqlParams bookIdParam = new SqlParams("bookId", Convert.ToInt32(bookId), (int)SqlParamsType.IntType);
ArrayList arr = new ArrayList();
arr.Add(userIdParam);
arr.Add(bookIdParam);
string sql = "insert into BorrowInfo(UserId,BookId,BorrowDate) values(@" + userIdParam.key + ",@" + bookIdParam.key + ",(select now()))";
int num = conn.ExecuteSqlForNon(arr, sql);
return(num);
}
public DataSet VerifyLoginDao(string account, string password, string tablename)
{
SqlParams accountParam = new SqlParams("account", account, (int)SqlParamsType.StringType);
SqlParams passwordParam = new SqlParams("password", password, (int)SqlParamsType.StringType);
string sql = "select count(*) as Count,IsLogin,UserId from Users where account = @" + accountParam.key + " and " + "Password = @" + passwordParam.key;
ArrayList arr = new ArrayList();
arr.Add(accountParam);
arr.Add(passwordParam);
DataSet data = conn.ExecuteSqlForDataSet(arr, sql, tablename);
return(data);
}
public int RegisterUser(string account, string password, string username, int isAdmin, string tablename)
{
SqlParams accountParam = new SqlParams("account", account, (int)SqlParamsType.StringType);
SqlParams passwordParam = new SqlParams("password", password, (int)SqlParamsType.StringType);
SqlParams usernameParam = new SqlParams("username", username, (int)SqlParamsType.StringType);
SqlParams isAdminParam = new SqlParams("isAdmin", isAdmin, (int)SqlParamsType.IntType);
ArrayList arr = new ArrayList();
arr.Add(accountParam);
arr.Add(passwordParam);
arr.Add(usernameParam);
arr.Add(isAdminParam);
string sql = "insert into Users(Account,Password,Username,IsAdmin,IsLogin,CreateTime) values(@" + accountParam.key + ",@" + passwordParam.key + ",@" + usernameParam.key + ",@" + isAdminParam.key + ",0," + "(select now())" + ")";
int num = conn.ExecuteSqlForNon(arr, sql);
return(num);
}
public DataSet queryBookList(string tablename, string isBorrow)
{
ArrayList arr = new ArrayList();
string sql = "";
if (isBorrow == null)
{
sql = "select * from Books";
}
else
{
SqlParams p = new SqlParams("isBorrow", Convert.ToInt32(isBorrow), (int)SqlParamsType.IntType);
arr.Add(p);
sql = "select * from Books where IsBorrow = @" + p.key;
}
DataSet data = conn.ExecuteSqlForDataSet(arr, sql, tablename);
return(data);
}
public DataSet QueryUsersInfoDao(int userId, string tablename)
{
//string sql = "select * from Users where UserId = " + userId;
string sql = "";
ArrayList arr = new ArrayList();
if (userId == 0)
{
sql = "select * from Users order by CreateTime DESC";
}
else
{
SqlParams p = new SqlParams("userId", userId, (int)SqlParamsType.IntType);
sql = "select * from Users where UserId = @userId";
arr.Add(p);
}
//DataSet data = conn.QuerySqlstr(sql, tablename);
object data = conn.ExecuteSqlForDataSet(arr, sql, tablename);
return((DataSet)data);
}
