/**
* 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);
}
