public String addInfo()
{
String query1 = String.Format("INSERT INTO Manager (f_name, m_name, l_name, prs_email, company, cmp_add_no, cmp_add_street, cmp_add_town, cmp_email, u_id) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', {9})",
fName, mName, lName, personalEmail, company, number, street, town, companyEmail, userID);
String query2 = String.Format("INSERT INTO BusPhone (u_id, phone) VALUES ({0}, '{1}')", userID, companyPhone);
String query3 = String.Format("INSERT INTO PrsPhone (u_id, phone) VALUES ({0}, '{1}')", userID, personalPhone);
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
String msg = myConnection.executeNonReturningQuery(query1);
String msg2 = myConnection.executeNonReturningQuery(query2);
String msg3 = myConnection.executeNonReturningQuery(query3);
return msg + msg2 + msg3;
}
public String createCV()
{
String query1 = String.Format("INSERT INTO [Candidate] ([u_id],[f_name],[m_name],[l_name],[dob],[gender],[maritual_status],[add_no],[add_street],[add_town],[email])" +
"VALUES({0}, '{1}', '{2}', '{3}', '{4}', {5}, {6}, '{7}', '{8}', '{9}', '{10}')",
userID, fName, mName, lName, dob, gender, mStatus, number, street, town, email);
String query2 = String.Format("INSERT INTO [PrsPhone]([u_id],[phone]) VALUES ({0}, '{1}')", userID, phone);
String query3 = String.Format("INSERT INTO [CanSchool]([u_id],[school]) VALUES ({0}, '{1}')", userID, school);
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
String msg = myConnection.executeNonReturningQuery(query1);
String msg2 = myConnection.executeNonReturningQuery(query2);
String msg3 = myConnection.executeNonReturningQuery(query3);
return msg + msg2 + msg3;
}
public String createForum(String userID, String forumTitle)
{
DateTime time = new DateTime();
time = DateTime.Now;
String timeString = time.ToString("yyyy-MM-dd HH:MM:ss");
String query = String.Format("INSERT INTO Forum( u_id, f_time, f_title) VALUES ( {0}, '{1}', '{2}')", userID, timeString, forumTitle);
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
String msg = myConnection.executeNonReturningQuery(query);
return msg;
}
public String UpdateCV()
{
String query1 = String.Format( "UPDATE [Candidate] SET [f_name] = '{0}', [m_name] = '{1}', [l_name] = '{2}', [dob] = '{3}', [gender] = {4}, [maritual_status] = {5}, [add_no] = '{6}', [add_street] = '{7}', [add_town] = '{8}', [email] = '{9}' WHERE u_id = {10}",
fName, mName, lName, dob, gender, mStatus, number, street, town, email, userID);
String query2 = String.Format("UPDATE [CanSchool] SET [school] = '{0}' WHERE u_id = {1}", school, userID);
String query3 = String.Format("UPDATE [PrsPhone] SET [phone] = '{0}' WHERE u_id = {1}", phone, userID);
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
String msg1 = myConnection.executeNonReturningQuery(query1);
String msg2 = myConnection.executeNonReturningQuery(query2);
String msg3 = myConnection.executeNonReturningQuery(query3);
return msg1 + msg2 + msg3;
}
public String insertComputerLanguage(String language, String proficiency)
{
String languageLowerCase = (language.Replace(" ", String.Empty)).ToLower();
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
String query = String.Format("INSERT INTO ComputerLanguage(u_id, languageLowerCase, language, proficiency) VALUES({0}, '{1}', '{2}', {3})", userID, languageLowerCase, language, proficiency);
return myConnection.executeNonReturningQuery(query);
}
public String UpdateInfo()
{
String query1 = String.Format("UPDATE [Manager] SET [f_name] = '{0}', [m_name] = '{1}', [l_name] = '{2}', [prs_email] = '{3}', [company] = '{4}', [cmp_add_no] = '{5}', [cmp_add_street] = '{6}', [cmp_add_town] = '{7}', [cmp_email] = '{8}' WHERE u_id = {9}",
fName, mName, lName, personalEmail, company, number, street, town, companyEmail, userID);
String query2 = String.Format("UPDATE [PrsPhone] SET [phone] = '{0}' WHERE u_id = {1}", personalPhone, userID);
String query3 = String.Format("UPDATE [BusPhone] SET [phone] = '{0}' WHERE u_id = {1}", companyPhone, userID);
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
String msg1 = myConnection.executeNonReturningQuery(query1);
String msg2 = myConnection.executeNonReturningQuery(query2);
String msg3 = myConnection.executeNonReturningQuery(query3);
return msg1 + msg2 + msg3;
}
// function to create a new user account
public String createAccount(String username, String password, String email, int role)
{
DBConnection myConnection = new DBConnection();
myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
if (this.checkAvailability(username, email) >= 1)
{
String query1 = String.Format("INSERT INTO UserAcc(u_name, passd, role) VALUES ('{0}','{1}', {2})", username, password, role);
String msg1 = myConnection.executeNonReturningQuery(query1);
return msg1;
}
else
{
return "Username is already taken.";
}
}