/// <summary> /// This method removes a university by the given name /// </summary> /// <param name="Uni_Name"></param> public void removeUniversityByName(String Uni_Name) { LD = LinkDatabase.getInstance(); string query = @"DELETE FROM " + LD.databaseName + @".UNIVERSITY WHERE UNI_NAME = '" + Uni_Name + @"';"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
public void modifyUserUniversity(string newUni, string oldUni, string userEmail) { LD = LinkDatabase.getInstance(); string query = @"UPDATE " + LD.databaseName + @".USER SET UNI_NAME = '" + newUni + @"' WHERE UNI_NAME = '" + oldUni + @"'AND Email = '" + userEmail + @"'; "; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// /// </summary> /// <param name="phone"></param> /// <param name="email"></param> /// <param name="relatedID"></param> public void insertFavourite(string phone, string email, int relatedID) { LD = LinkDatabase.getInstance(); string query = @"INSERT INTO " + LD.databaseName + ".FAVOURITES(Phone_Num, Email, ID)" + @"VALUES('" + phone + "','" + email + "','" + relatedID + "');"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// Method to add a new university to the db /// </summary> /// <param name="name"></param> /// <param name="city"></param> /// <param name="prov"></param> /// <param name="Country"></param> public void insertUniversity(string name, string city, string prov, string Country) { LD = LinkDatabase.getInstance(); string query = @"INSERT INTO " + LD.databaseName + ".UNIVERSITY(UNI_NAME, City, Prov_State, Country)" + @"VALUES('" + name + "','" + city + "','" + prov + "','" + Country + "');"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// This function removes an Admin given its email. /// </summary> /// <param name="email"></param> public void removeAdminByEmail(String email) { LD = LinkDatabase.getInstance(); string query = @"DELETE FROM " + LD.databaseName + @".ADMIN WHERE Email = '" + email + @"';"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// /// </summary> /// <param name="phone"></param> /// <param name="email"></param> /// <param name="uni"></param> /// <param name="fname"></param> /// <param name="lname"></param> /// <param name="PW"></param> public void insertUser(string phone, string email, string uni, string fname, string lname, string PW) { LD = LinkDatabase.getInstance(); string query = @"INSERT INTO " + LD.databaseName + ".USER(Phone_Num, Email, UNI_NAME, Fname, Lname, Password)" + @"VALUES('" + phone + "','" + email + "','" + uni + "','" + fname + "','" + lname + "','" + PW + "');"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// /// </summary> /// <param name="email"></param> /// <param name="role"></param> public void insertReview(string Phone_Num, string Email, string Reviewer_Email, string Description, int Rating) { LD = LinkDatabase.getInstance(); string query = @"INSERT INTO " + LD.databaseName + ".REVIEW(Phone_Num, Email, Reviewer_Email, Description, Rating)" + @"VALUES('" + Phone_Num + "','" + Email + "','" + Reviewer_Email + "','" + Description + "','" + Rating + "');"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// This function inserts an admin into the database /// </summary> /// <param name="phone"></param> /// <param name="email"></param> /// <param name="uni"></param> /// <param name="fname"></param> /// <param name="lname"></param> /// <param name="PW"></param> public void insertAdmin(string email, string role) { LD = LinkDatabase.getInstance(); string query = @"INSERT INTO " + LD.databaseName + ".ADMIN(Email, Role)" + @"VALUES('" + email + "','" + role + "');"; try { LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } }
/// <summary> /// /// </summary> public bool deletePost(string id) { LD = LinkDatabase.getInstance(); string query = @"DELETE FROM " + LD.databaseName + @".POST WHERE ID = '" + id + @"';"; try { LD.executeNonQueryGeneric(query); return(true); } catch (Exception e) { sw.Write("Failure in deletePost: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } return(false); }
public void saveFavourite(string phone, string email, string id) { if (!favouriteExists(phone, email, id)) { string query = @"INSERT INTO " + LD.databaseName + @".FAVOURITES(phone_num, email, id)VALUES('" + phone + @"', '" + email + @"', '" + id + @"');"; try { LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query); } catch (Exception e) { sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } } }
/// <summary> /// Method to insert a new post into the db /// </summary> /// <param name="post"></param> public string insertPost(Post post) { string query1 = @"INSERT INTO " + LD.databaseName + @".POST(Phone_Num, Email, UNI_NAME, Date, Book_Condition, Price, Description, Title)" + @"VALUES('" + post.Phone + @"','" + post.email + @"','" + post.Uni + @"','" + post.date + @"','" + post.condition + @"','" + post.price + @"','" + post.description + @"','" + post.Title + @"');"; if (checkTextbook(post.Title, post.edition)) { if (checkAuthor(post.author, post.Title)) { //Title and Author both in data base, just insert post (Best Case) try { if (!checkCourse(post.code, post.department, post.Uni)) { string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" + post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');"; LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query5); //Insert course } if (!checkUsedFor(post.Title, post.code, post.department)) { string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" + post.Title + @"', '" + post.code + @"', '" + post.department + @"');"; LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query4); //Insert used_for } LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query1); //Insert Post } catch (Exception e) { } } else { //Textbook exists but not author, insert post and author (highly unlikely) string query2 = @"INSERT INTO " + LD.databaseName + @".AUTHOR(name, title)VALUES('" + post.author + @"', '" + post.Title + @"');"; try { LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query2); //Insert author if (!checkCourse(post.code, post.department, post.Uni)) { string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" + post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');"; LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query5); //Insert course } if (!checkUsedFor(post.Title, post.code, post.department)) { string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" + post.Title + @"', '" + post.code + @"', '" + post.department + @"');"; LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query4); //Insert used_for } LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query1); //Insert post } catch (Exception e) { } } } else if (checkAuthor(post.author, post.Title)) { string query3 = @"INSERT INTO " + LD.databaseName + @".TEXTBOOK(title, edition)VALUES('" + post.Title + @"', '" + post.edition + @"');"; //Textbook does not exist but author does(highly unlikely) try { LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query3); //Insert textbook if (!checkCourse(post.code, post.department, post.Uni)) { string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" + post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');"; LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query5); //Insert course } if (!checkUsedFor(post.Title, post.code, post.department)) { string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" + post.Title + @"', '" + post.code + @"', '" + post.department + @"');"; LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query4); //Insert used_for } LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query1); //Insert post } catch (Exception e) { sw.Write("Failure in insertPost textbook and post: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } } else { string query2 = @"INSERT INTO " + LD.databaseName + @".AUTHOR(name, title)VALUES('" + post.author + @"', '" + post.Title + @"');"; string query3 = @"INSERT INTO " + LD.databaseName + @".TEXTBOOK(title, edition)VALUES('" + post.Title + @"', '" + post.edition + @"');"; try { //Both text book and author do not , insert all LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query3); //Insert textbook LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query2); //Insert author if (!checkCourse(post.code, post.department, post.Uni)) { LD = LinkDatabase.getInstance(); string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" + post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');"; LD.executeNonQueryGeneric(query5); //Insert course } if (!checkUsedFor(post.Title, post.code, post.department)) { LD = LinkDatabase.getInstance(); string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" + post.Title + @"', '" + post.code + @"', '" + post.department + @"');"; LD.executeNonQueryGeneric(query4); //Insert used_for } LD = LinkDatabase.getInstance(); LD.executeNonQueryGeneric(query1); //Insert post } catch (Exception e) { sw.Write("Failure in insertPost, post: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt")); } } return("You have successfully created a post"); }