public static DataTable GetLectuer(MyMySql sql) { string sqlstr = $@" SELECT * FROM lecture"; return(sql.executeQuery(sqlstr)); }
public static bool ChangePrivillege(MyMySql sql, string userid, string privillege) { string SQLstr = $@" UPDATE users SET userprivillege = {privillege} WHERE userid = {userid}"; return(sql.executeNonQuery(SQLstr) == 1); }
public static DataTable GetBooks(MyMySql sql) { string SQLstr = $@" SELECT * FROM books;"; return(sql.executeQuery(SQLstr)); }
public static bool AddTag(MyMySql sql, string bookid, string tagid) { string sqlstr = $@" INSERT INTO book_tag(bookid, tagid) VALUES ({bookid}, {tagid})"; return(sql.executeNonQuery(sqlstr) == 1); }
public static bool AddBook(MyMySql sql, string bookname, string bookauthor, string bookcount) { string SQLstr = $@" INSERT INTO books (bookname, bookauthor, bookcount) VALUES ('{bookname.Trim()}','{bookauthor.Trim()}',{bookcount})"; return(sql.executeNonQuery(SQLstr) == 1); }
public static bool RemoveTag(MyMySql sql, string bookid, string tagid) { string sqlstr = $@" DELETE FROM book_tag WHERE bookid = {bookid} AND tagid = {tagid}"; return(sql.executeNonQuery(sqlstr) != 0); }
public static UserDescription GetUserDescription(MyMySql sql, string userid) { string sqlstr = $@" SELECT username, password, sex, contact FROM users WHERE userid = {userid}"; DataRow row = sql.executeQueryFirst(sqlstr); return(new UserDescription(row["username"].ToString(), row["password"].ToString(), int.Parse(row["sex"].ToString()), row["contact"].ToString())); }
public static bool EditBook(MyMySql sql, string bookid, string bookname, string bookauthor, string bookcount) { string SQLstr = $@" UPDATE books SET bookname = '{bookname}', bookauthor = '{bookauthor}', bookcount = {bookcount} WHERE bookid = {bookid}"; return(sql.executeNonQuery(SQLstr) == 1); }
public static DataTable getUnAviliableSeats(MyMySql sql, string roomid) { string sqlstr = $@" SELECT deskid, seatid FROM seats WHERE roomid = {roomid} AND backdatetime IS NULL"; return(sql.executeQuery(sqlstr)); }
public static bool Leave(MyMySql sql, string roomid, string deskid, string seatid) { string sqlstr = $@" UPDATE seats SET backdatetime = NOW() WHERE roomid = {roomid} AND deskid = {deskid} AND seatid = {seatid}"; return(sql.executeNonQuery(sqlstr) == 1); }
static public int BorrowBooks(MyMySql sql, string userid, List <string> bookids) { int result = 0; string booklist = ""; foreach (var bookid in bookids) { booklist += $"\n({userid}, {bookid}, NOW()),"; } booklist = booklist.Substring(0, booklist.Length - 1); string SQLstr = "INSERT INTO borrowlog(`userid`, `bookid`, `borrowdatetime`) VALUES" + booklist; result = sql.executeNonQuery(SQLstr); return(result); }
public static string[] getBooksByTag(MyMySql sql, string tagid) { string sqlstr = $@" SELECT bookid FROM book_tag WHERE tagid = {tagid} GROUP BY bookid"; DataTable table = null; if ((table = sql.executeQuery(sqlstr)) != null) { string[] result = table.AsEnumerable().Select(d => d.Field <string>("bookid")).ToArray <string>(); return(result); } return(null); }
public static SeatInfo GetSeatByUser(MyMySql sql_seats, string userid) { string sqlstr = $@" SELECT roomid, deskid, seatid FROM seats WHERE userid = {userid} AND backdatetime IS NULL"; DataRow row = sql_seats.executeQueryFirst(sqlstr); if (row != null) { return(new SeatInfo(row["roomid"].ToString(), row["deskid"].ToString(), row["seatid"].ToString())); } else { return(new SeatInfo(null, null, null)); } }
static public bool changeUserInfo(MyMySql mysql, string userid, string contact, int sex, string username = "", string password = "") { string passwd = EncryptionUtils.getMD5(password).ToUpper(); string SQLstr = "UPDATE users SET " + (username.Equals("") ? "" : $"username = '******',") + (password.Equals("") ? "" : $"password = '******',") + $"contact = '{contact}', " + $"sex = {sex} " + $"WHERE userid = {userid}"; int result = mysql.executeNonQuery(SQLstr); if (result != 1) { return(false); } return(true); }
static public UserInfo ConfirmAlreadyMD5(string username, string password, MyMySql sql) { string passwd = password.ToUpper(); MySqlDataReader reader = sql.executeReader($"SELECT userid,userprivillege FROM users WHERE username = '******' AND password = '******'"); if (reader != null) { UserInfo info = null; if (reader.Read()) { info = new UserInfo(reader["userid"].ToString(), (USER_TYPE)int.Parse(reader["userprivillege"].ToString())); } reader.Close(); sql.TryClose(); if (info != null) { return(info); } } return(new UserInfo("", USER_TYPE.INVAILD)); }
static public void ReturnBook(MyMySql sql, string logid) { string SQLstr = $"UPDATE borrowlog SET returndatetime = NOW() WHERE logid = {logid}"; sql.executeNonQuery(SQLstr); }
public static DataTable GetAllUsers(MyMySql sql_users) { return(sql_users.executeQuery(@"SELECT * FROM users")); }
public static bool Order(MyMySql sql, string roomid, string deskid, string seatid, string userid) { string sqlstr = $"INSERT INTO seats (roomid, userid, deskid, seatid, orderdatetime) VALUES ({roomid},{userid}, {deskid},{seatid}, NOW())"; return(sql.executeNonQuery(sqlstr) == 1); }