/// <summary> /// Method to get all Posts by title /// </summary> /// <param name="title"></param> /// <returns></returns> public List <Result> getFavourites(string email) { LD = LinkDatabase.getInstance(); string query = @"SELECT P.ID, P.TITLE FROM " + LD.databaseName + @".POST AS P, " + LD.databaseName + @".FAVOURITES AS F WHERE F.EMAIL = '" + email + @"' AND F.ID = P.ID;"; //string query = @"SELECT ID, TITLE FROM " + LD.databaseName + @".POST AS P NATURAL JOIN " + LD.databaseName + //@".FAVOURITES AS F WHERE F.EMAIL = '" + email + @"' AND F.ID = P.ID;"; List <Result> Favs = new List <Result>(); try { Result temp; MySqlDataReader reader = LD.executeGenericSQL(query); while (reader.Read()) { temp = new Result(); temp.ID = reader.GetInt32("ID").ToString(); temp.title = reader.GetString("Title"); Favs.Add(temp); } } catch (Exception e) { sw.Write(e.Message); } finally { LD.doClose(); } return(Favs); }
/// <summary> /// This retrieves an admin by its email address /// </summary> /// <param name="Phone_Num"></param> /// <returns></returns> public List <Admin> getAdminByEmail(string Email) { LD = LinkDatabase.getInstance(); List <Admin> users = new List <Admin>(); Admin temp; string customQuerry = @"SELECT * FROM " + LD.databaseName + @".ADMIN WHERE Email = '" + Email + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(customQuerry); if (reader != null) { while (reader.Read()) { temp = new Admin(); temp.email = reader.GetString("Email"); temp.role = reader.GetString("Role"); users.Add(temp); } } } catch (Exception e) { sw.Write("Failure in getUsers: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } LD.doClose(); return(users); }
public bool checkAuthor(string author, string title) { bool result = false; LD = LinkDatabase.getInstance(); string query = @"SELECT NAME, TITLE FROM " + LD.databaseName + @".AUTHOR WHERE NAME = '" + author + "' AND TITLE = '" + title + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { result = true; } } catch (Exception e) { sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(result); }
public List <User> getUserListByPhone(string Phone_Num) { LD = LinkDatabase.getInstance(); List <User> users = new List <User>(); User temp; string customQuerry = @"SELECT * FROM " + LD.databaseName + @".USER WHERE Phone_Num = '" + Phone_Num + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(customQuerry); if (reader != null) { while (reader.Read()) { temp = new User(); temp.phone = reader.GetString("Phone_Num"); temp.email = reader.GetString("Email"); temp.Uni = reader.GetString("UNI_NAME"); temp.fname = reader.GetString("Fname"); temp.lname = reader.GetString("Lname"); users.Add(temp); } } } catch (Exception e) { sw.Write("Failure in getUsers: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } LD.doClose(); return(users); }
public bool checkUsedFor(string title, string course, string department) { bool result = false; LD = LinkDatabase.getInstance(); string query = @"SELECT TITLE, COURSENUM, DEPARTMENT FROM " + LD.databaseName + @".USED_FOR WHERE TITLE = '" + title + @"' AND COURSENUM = '" + course + @"' AND DEPARTMENT = '" + department + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { result = true; } } catch (Exception e) { sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(result); }
public bool checkTextbook(string textbook, int edition) { bool result = false; LD = LinkDatabase.getInstance(); string query = @"SELECT TITLE, EDITION FROM " + LD.databaseName + @".TEXTBOOK WHERE TITLE = '" + textbook + @"' AND EDITION = '" + edition + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { result = true; } } catch (Exception e) { sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(result); }
public List <Result> getUserPosts(string username) { LD = LinkDatabase.getInstance(); string query = @"SELECT TITLE, ID FROM " + LD.databaseName + @".POST WHERE EMAIL = '" + username + @"';"; List <Result> search = new List <Result>(); try { Result temp; MySqlDataReader reader = LD.executeGenericSQL(query); while (reader.Read()) { temp = new Result(); temp.ID = reader.GetInt32("ID").ToString(); temp.title = reader.GetString("Title"); search.Add(temp); } } catch (MySqlException d) { sw.Write("Sql Error:" + d.Message); } catch (Exception e) { sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(search); }
public List <Result> getSearchTitles(string title, string department, string code, string university) { LD = LinkDatabase.getInstance(); //string query = @"SELECT TITLE FROM POST WHERE TITLE = '" + title + @"';"; string query = @"SELECT P.TITLE, P.ID FROM " + LD.databaseName + @".POST AS P, " + LD.databaseName + @".USED_FOR AS U" + @" WHERE P.Title = U.Title AND P.Title LIKE '%" + title + @"%' AND U.Department = '" + department + @"' AND U.CourseNum = '" + code + @"' AND P.UNI_Name = '" + university + @"';"; List <Result> search = new List <Result>(); try { Result temp; MySqlDataReader reader = LD.executeGenericSQL(query); while (reader.Read()) { temp = new Result(); temp.ID = reader.GetInt32("ID").ToString(); temp.title = reader.GetString("Title"); search.Add(temp); } } catch (MySqlException d) { sw.Write("Sql Error:" + d.Message); } catch (Exception e) { sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } LD.doClose(); return(search); }
public UserInfo getUserInfo(string username) { LD = LinkDatabase.getInstance(); string query = @"SELECT UNI_NAME, PHONE_NUM FROM " + LD.databaseName + @".USER WHERE EMAIL = '" + username + @"';"; UserInfo info = new UserInfo(); try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { info.phone = reader.GetString("PHONE_NUM"); info.university = reader.GetString("UNI_NAME"); } } catch (Exception e) { sw.Write("Failure in getPost: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } LD.doClose(); return(info); }
/// <summary> /// Method to get all the universites in a list /// </summary> /// <returns></returns> public List <UniData> getUniversitiesByName(string UNI_NAME) { LD = LinkDatabase.getInstance(); string query = @"SELECT * FROM " + LD.databaseName + @".UNIVERSITY WHERE UNI_NAME = '" + UNI_NAME + @"';"; List <UniData> output = new List <UniData>(); try { MySqlDataReader reader = LD.executeGenericSQL(query); UniData temp; while (reader.Read()) { temp = new UniData(); temp.name = reader.GetString("UNI_NAME"); temp.city = reader.GetString("City"); temp.prov_state = reader.GetString("Prov_State"); temp.country = reader.GetString("Country"); output.Add(temp); } } catch (Exception e) { sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(output); }
/// <summary> /// /// </summary> /// <param name="Email"></param> /// <returns></returns> public List <ReviewResults> getReviewByEmail(string Email) { LD = LinkDatabase.getInstance(); List <ReviewResults> reviews = new List <ReviewResults>(); ReviewResults temp; string customQuerry = @"SELECT * FROM " + LD.databaseName + @".REVIEW WHERE Email = '" + Email + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(customQuerry); if (reader != null) { while (reader.Read()) { temp = new ReviewResults(); temp.Reviewer_Email = reader.GetString("Reviewer_Email"); temp.Description = reader.GetString("Description"); temp.Rating = reader.GetInt32("Rating"); reviews.Add(temp); } } } catch (Exception e) { sw.Write("Failure in getUsers: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } LD.doClose(); return(reviews); }
public string getUserPhone(string email) { LD = LinkDatabase.getInstance(); string query = @"SELECT PHONE_NUM FROM " + LD.databaseName + @".USER WHERE EMAIL = '" + email + @"';"; string result = ""; try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { result = reader.GetString("Phone_num"); } } catch (Exception e) { sw.Write("Failure in getPhone: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(result); }
/// <summary> /// Main method used to get posts from front page search /// </summary> /// <param name="courseName"></param> /// <param name="title"></param> /// <param name="courseCode"></param> /// <param name="UniName"></param> //public List<Post> getPost(string department, string title, string courseCode, string UniName) public Post getPost(string id) { LD = LinkDatabase.getInstance(); string query = @"SELECT * FROM " + LD.databaseName + @".POST NATURAL JOIN " + LD.databaseName + @".USED_FOR WHERE " + LD.databaseName + @".POST.ID = '" + id + @"';"; Post temp = new Post(); try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { temp.ID = reader.GetInt32("ID"); temp.Phone = reader.GetString("Phone_Num"); temp.email = reader.GetString("Email"); temp.Uni = reader.GetString("UNI_NAME"); temp.date = reader.GetString("Date"); // temp.bookType = reader.GetString("BookType"); temp.condition = reader.GetString("Book_Condition"); temp.price = reader.GetDouble("Price"); temp.description = reader.GetString("Description"); temp.Title = reader.GetString("Title"); temp.department = reader.GetString("Department"); temp.code = reader.GetString("CourseNum"); // outPost.Add(temp); } } catch (Exception e) { sw.Write("Failure in getPost: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } LD.doClose(); return(temp); }
public bool checkCourse(string code, string department, string university) { bool result = false; LD = LinkDatabase.getInstance(); string query = @"SELECT COURSENUM, DEPARTMENT, UNI_NAME FROM " + LD.databaseName + @".COURSE WHERE COURSENUM = '" + code + @"' AND DEPARTMENT = '" + department + @"' AND UNI_NAME = '" + university + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { result = true; } } catch (Exception e) { sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } finally { LD.doClose(); } return(result); }
public bool favouriteExists(string phone, string email, string id) { bool result = false; LD = LinkDatabase.getInstance(); string query = @"SELECT * FROM " + LD.databaseName + @".FAVOURITES WHERE PHONE_NUM = '" + phone + @"' AND EMAIL = '" + email + @"' AND ID = '" + id + @"';"; try { MySqlDataReader reader = LD.executeGenericSQL(query); if (reader.Read()) { result = true; } } catch (Exception e) { sw.Write(e.Message); } finally { LD.doClose(); } return(result); }