/** * Method to update the member connected * pass in arguments all informations necessary to update a member */ public static void UpdateMemberConnected(string username, string password, int age, string email, string gender, string nationality, string categoryOfKnowledge, string typeOfMember, string newsletter) { if (!DBConnection.IsOpen) { // opens the connection DBConnection.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = DBConnection.Connection; string strSQL = @"UPDATE MEMBER SET USERNAME='******',MEMBERPASSWORD='******',AGE ='" + age + "',EMAIL ='" + email + "',GENDER ='" + gender + "',NATIONALITY='" + nationality + "',CATEGORY_OF_KNOWLEDGE='" + categoryOfKnowledge + "',TYPE_OF_MEMBER='" + typeOfMember + "',NEWSLETTER='" + newsletter + "'WHERE MEMBER_ID='" + SessionUser.WhoIsLoggedIn().MemberID + "'"; cmd.CommandText = strSQL; try { cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } DBConnection.Close(); }
/* * Method to create question by calling procedure * pass in arguments the ID of category and the text of the question * */ public static void CreateQuestion(int categoryID, string questionText) { // checks to see if the database connection is not open if (!DBConnection.IsOpen) { // opens the connection DBConnection.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = DBConnection.Connection; cmd.CommandType = System.Data.CommandType.StoredProcedure; string strSQL = "CREATEQUESTION"; cmd.Parameters.Add("member_id", OracleDbType.Int32).Value = SessionUser.WhoIsLoggedIn().MemberID; cmd.Parameters.Add("category_id", OracleDbType.Int32).Value = categoryID; cmd.Parameters.Add("question_text", OracleDbType.Varchar2).Value = questionText; cmd.CommandText = strSQL; cmd.ExecuteNonQuery(); DBConnection.Close(); }
/* * Method to create answer by calling procedure * pass in arguments the id of question and the answer text * */ public static void CreateAnswer(int questionID, string answerText) { // checks to see if the database connection is not open if (!DBConnection.IsOpen) { // opens the connection DBConnection.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = DBConnection.Connection; //Indicate that is a procedure stored cmd.CommandType = System.Data.CommandType.StoredProcedure; //Indicate the name of procedure string strSQL = "CREATEANSWER"; //Pass attributes required to run the procedure cmd.Parameters.Add("question_id", OracleDbType.Int32).Value = questionID; cmd.Parameters.Add("member_id", OracleDbType.Int32).Value = SessionUser.WhoIsLoggedIn().MemberID; cmd.Parameters.Add("question_text", OracleDbType.Varchar2).Value = answerText; cmd.CommandText = strSQL; cmd.ExecuteNonQuery(); DBConnection.Close(); }
/** * Method to select the member who is connected thank's to his session * return the member */ public static Member SelectMemberConnected() { Member member = new Member(); if (!DBConnection.IsOpen) { // opens the connection DBConnection.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = DBConnection.Connection; //Query to select username of the member connected string cmdText = @"SELECT * FROM MEMBER WHERE USERNAME ='******'"; cmd.CommandText = cmdText; OracleDataReader reader = null; try { reader = cmd.ExecuteReader(); while (reader != null && reader.Read()) { member.MemberID = reader.GetInt32(0); member.Username = reader.GetString(1); member.MemberPassword = reader.GetString(2); member.Age = reader.GetInt32(3); member.Email = reader.GetString(4); member.Gender = reader.GetString(5); member.Nationality = reader.GetString(6); member.CategoryOfKnowledge = reader.GetString(7); member.Newsletter = reader.GetString(8); member.TypeOfMember = reader.GetString(9); } } catch { throw; } return(member); }
/** * Method to login a member * pass in arguments the username and the password enter by the user * return boolean to said if the user is login or not */ public static bool Login(string username, string password) { Member member = new Member(); bool foundMember = false; if (!DBConnection.IsOpen) { // opens the connection DBConnection.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = DBConnection.Connection; string cmdText = @"SELECT * FROM MEMBER WHERE USERNAME ='******'AND MEMBERPASSWORD ='******'"; cmd.CommandText = cmdText; OracleDataReader reader = null; try { reader = cmd.ExecuteReader(); if (reader.HasRows) { foundMember = true; //for each row, read the value of column in the table member while (reader != null && reader.Read()) { member.MemberID = reader.GetInt32(0); member.Username = reader.GetString(1); member.MemberPassword = reader.GetString(2); member.Age = reader.GetInt32(3); member.Email = reader.GetString(4); member.Gender = reader.GetString(5); member.Nationality = reader.GetString(6); member.CategoryOfKnowledge = reader.GetString(7); member.Newsletter = reader.GetString(8); member.TypeOfMember = reader.GetString(9); } //Create session for the member SessionUser.LogOn(member); } else { MessageBox.Show("Your username or your password is not correct"); } } catch { throw; } return(foundMember); }