private int getUserID()
{
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
string query = "select id from account where username = '******'";
int userID = -1;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
userID = reader.GetInt32(0);
}
}
}
}
mysql.closeMysqlConnection();
return userID;
}
public void deleteQuestion(Question question)
{
logger.Debug("deleteQuestion()");
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
string query = "delete from question where id = " + question.Id + ";";
string answersIDs = "";
foreach (Answer answer in question.AnswerList)
answersIDs += answer.Id.ToString() + ",";
if (!String.IsNullOrEmpty(answersIDs))
answersIDs = answersIDs.Substring(0, answersIDs.Length - 1);
mysql.initializeConnection();
mysql.openMysqlConnection();
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
query = "delete from answer where id in (" + answersIDs + ");";
if (!String.IsNullOrEmpty(answersIDs))
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
logger.Info("Question With ID " + question.Id + " Deleted");
}
public void deleteQuestionaire(int question_id)
{
String query = "select * from questionaire";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.openMysqlConnection();
MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection);
MySqlDataReader dr = cmd.ExecuteReader();
//Execute
mysql.closeMysqlConnection();
}
public void deleteAnswer(Answer answer)
{
logger.Debug("deleteAnswer()");
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
string query = "delete from answer where id = " + answer.Id + "";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
}
private bool usernameExist(string username)
{
bool usernameExist = false;
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
string query = "select username from account where username = '******'";
mysql.initializeConnection();
mysql.openMysqlConnection();
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
using (MySqlDataReader reader = cmd.ExecuteReader())
if (reader.HasRows)
usernameExist = true;
mysql.closeMysqlConnection();
return usernameExist;
}
public List<Question> findQuestion(string usernameF,Subject subject, Department department)
{
logger.Debug("findQuestion()");
List<Question> questionList = new List<Question>();
string query = "";
if(usernameF != "all")
query = "select Q.id as question_id,Q.question,Q.create_date,A.id as user_id,Q.level_range,A.username from question Q " +
"inner join account A on A.id = Q.account_id " +
"inner join subject_department SD on Q.subject_department_id = SD.id where SD.subject_id = " + subject.Id + " and SD.department_id = " + department.Id + " and A.username = '******'; ";
else
query = "select Q.id as question_id,Q.question,Q.create_date,A.id as user_id,Q.level_range,A.username from question Q " +
"inner join account A on A.id = Q.account_id " +
"inner join subject_department SD on Q.subject_department_id = SD.id where SD.subject_id = " + subject.Id + " and SD.department_id = " + department.Id + " ; ";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
//Question Fields
int question_id;
string question_descr;
int level_question;
DateTime date_question;
//Account Fields
int account_id;
string username;
//Questions
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
question_id = reader.GetInt32(0);
question_descr = reader.GetString(1);
date_question = reader.GetDateTime(2).Date;
Debug.Write("NOWWWWWWWWWW");
Debug.Write(date_question.ToString("MM/dd/yyyy"));
account_id = reader.GetInt32(3);
level_question = reader.GetInt32(4);
username = reader.GetString(5);
questionList.Add(new Question() { Id = question_id, Question_descr = question_descr, Account = new Account() { Username = username }, Subject = subject, Department = department, Level = level_question, Date = date_question });
}
}
} // reader closed and disposed up here
} // command disposed here
mysql.closeMysqlConnection();
//Answer of Above Questions
if (questionList.Any())
{
mysql.openMysqlConnection();
setAnsersForIndividualQuestion(questionList, mysql);
mysql.closeMysqlConnection();
}
//Debug
foreach (Question question in questionList)
{
Debug.WriteLine(question.Question_descr);
foreach (Answer answer in question.AnswerList)
Debug.WriteLine(answer.Answer_descr);
}
return questionList;
}
public void updateQuestion(Question question)
{
string query = "select SD.id from subject_department SD where SD.subject_id=" + question.Subject.Id + " and SD.department_id=" + question.Department.Id + " ";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
int subjectdepartment_id = -1;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
while (reader.Read())
{
subjectdepartment_id = reader.GetInt32(0);
}
}
}
string formatForMySql = question.Date.ToString("yyyy-MM-dd");
query = "update question set question = '" + question.Question_descr + "',level_range=" + question.Level + ",create_date='" + formatForMySql + "',subject_department_id = " + subjectdepartment_id + " where id = " + question.Id + "";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
}
public void saveQuestion(Question question)
{
string query = "select SD.id from subject_department SD where SD.subject_id=" + question.Subject.Id + " and SD.department_id=" + question.Department.Id + " ";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
long subjectdepartment_id = -1;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
while (reader.Read())
{
subjectdepartment_id = reader.GetInt32(0);
}
}
}
string formatForMySql = question.Date.ToString("yyyy-MM-dd");
query = "insert into question(question,level_range,create_date,account_id,subject_department_id) values('" + question.Question_descr + "'," + question.Level + ",'" + formatForMySql + "'," + question.Account.Id + "," + subjectdepartment_id + ")";
long new_questionId;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
cmd.ExecuteNonQuery();
new_questionId = cmd.LastInsertedId;
}
question.Id = (int)new_questionId;
mysql.closeMysqlConnection();
}
public void saveAnswer(Answer answer, int question_id)
{
logger.Debug("saveAnswer()");
string formatForMySql = answer.Date.ToString("yyyy-MM-dd");
string query = "insert into answer(answer,create_date,correct,account_id) values('" + answer.Answer_descr + "','" + formatForMySql + "'," + (answer.Correct ? 1 : 0) + "," + answer.Account.Id + ")";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
long new_answer_id;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
cmd.ExecuteNonQuery();
new_answer_id = cmd.LastInsertedId;
}
query = "insert into question_answer(question_id,answer_id) values(" + question_id + "," + new_answer_id + ")";
logger.Info("Answer iD " + new_answer_id);
logger.Info("Question ID " + question_id);
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
answer.Id = (int)new_answer_id;
mysql.closeMysqlConnection();
}
public List<Answer> findAnswer(int question_id)
{
logger.Debug("findAnswer()");
string query = "select id,answer,correct,create_date,account_id from answer A "
+ " inner join question_answer QA on QA.answer_id = A.id where QA.question_id = " + question_id;
List<Answer> listAnswer = new List<Answer>();
Answer tempAnswer;
int answer_id;
string answer;
bool correct;
DateTime date_answer;
int account_id;
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
using (MySqlDataReader reader = cmd.ExecuteReader())
if (reader.HasRows)
while (reader.Read())
{
answer_id = reader.GetInt32(0);
answer = reader.GetString(1);
correct = reader.GetInt32(2) == 1 ? true : false;
date_answer = reader.GetDateTime(3).Date;
account_id = reader.GetInt32(4);
tempAnswer = new Answer() { Id = answer_id, Answer_descr = answer, Correct = correct, Date = date_answer, Account = new Account() { Id = account_id } };
listAnswer.Add(tempAnswer);
}
mysql.closeMysqlConnection();
return listAnswer;
}
public void updateAnswer(Answer answer)
{
logger.Debug("updateAnswer()");
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
string formatForMySql = answer.Date.ToString("yyyy-MM-dd");
string query = "update answer set answer='" + answer.Answer_descr + "',create_date='" + formatForMySql + "',correct=" + (answer.Correct ? 1 : 0) + ",account_id=" + answer.Account.Id + " where id=" + answer.Id + "";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
}