private void populateTopic() { StringBuilder sql = new StringBuilder("SELECT TopicDescription FROM Topics "); sql.Append("WHERE TopicID IN (SELECT TopicID FROM CourseTopics WHERE "); sql.Append("CourseTopicRefID IN (SELECT CourseTopicRefID FROM Questions WHERE QuestionTitle IN ("); for (int i = question.Items.Count - 1; i >= 0; i--) { if (i == 0) { sql.Append("'" + question.Items[i] + "')))"); } else { sql.Append("'" + question.Items[i] + "', "); } } DataTable dt = SqlManager.query(sql.ToString()); populateComboBox(topic, dt); }
private void populateDepartment() { StringBuilder sql = new StringBuilder("SELECT DepartmentName FROM Departments "); sql.Append("WHERE DepartmentID IN (SELECT DepartmentID FROM CourseTopics WHERE "); //sql.Append("LocationID=" + locationID + " AND "); sql.Append("CourseTopicRefID IN (SELECT CourseTopicRefID FROM Questions WHERE QuestionTitle IN ("); for (int i = question.Items.Count - 1; i >= 0; i--) { if (i == 0) { sql.Append("'" + question.Items[i] + "')))"); } else { sql.Append("'" + question.Items[i] + "', "); } } DataTable dt = SqlManager.query(sql.ToString()); populateComboBox(department, dt); }
private void login_Click(object sender, EventArgs e) { var Username = username.Text; // Password should be hashed, I don't have time to work on this. var Password = password.Text; var sql = "SELECT UserID FROM Users " + "WHERE UserName='******' AND UserPassword='******' " + "AND NOT EXISTS (SELECT NonActiveID FROM NonActive WHERE UserID = NonActiveID)"; DataTable datatable = SqlManager.query(sql); // Test SQL query if (datatable.Rows.Count == 0) { MessageBox.Show("User could not be authenticated.", "User Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { var UserID = Convert.ToInt32(datatable.Rows[0][0]); // Check if user is admin, if so redirect to admin form, otherwise redirect to user form sql = "Select * FROM Admins WHERE AdminID=" + UserID; datatable = SqlManager.query(sql); this.Hide(); if (datatable.Rows.Count == 0) { // Open user controls new UserForm(UserID).ShowDialog(); } else { // Open admin controls new AdminForm().ShowDialog(); } this.Close(); } }
private void populateQuestions() { // Populate Questions dropdown StringBuilder sql = new StringBuilder("SELECT QuestionTitle FROM Questions"); if (locationID != 0 || departmentID != 0 || courseID != 0 || topicID != 0 || difficultyID != 0 || typeID != 0 || formatID != 0) { sql.Append(" WHERE "); } // First use topicID to narrow things down var idmap = new Dictionary <string, int>(); if (locationID != 0) { idmap.Add("LocationID", locationID); } if (departmentID != 0) { idmap.Add("DepartmentID", departmentID); } if (courseID != 0) { idmap.Add("CourseID", courseID); } if (topicID != 0) { idmap.Add("TopicID", topicID); } var first = true; var last = idmap.Count; foreach (var key in idmap.Keys) { last--; if (first) { sql.Append("CourseTopicRefID IN (SELECT CourseTopicRefID FROM CourseTopics WHERE "); sql.Append(key + "=" + idmap[key]); first = false; } else { sql.Append(" AND " + key + "=" + idmap[key]); } if (last == 0) { sql.Append(")"); } } idmap.Clear(); if (difficultyID != 0) { idmap.Add("DifficultyID", difficultyID); } if (typeID != 0) { idmap.Add("TypeID", typeID); } if (formatID != 0) { idmap.Add("FormatID", formatID); } foreach (var key in idmap.Keys) { if (first) { sql.Append(key + "=" + idmap[key]); first = false; } else { sql.Append(" AND " + key + "=" + idmap[key]); } } DataTable dt = SqlManager.query(sql.ToString()); question.Items.Clear(); if (dt.Rows.Count == 0) { noquestions.Visible = true; } else { noquestions.Visible = false; } for (int i = 0; i < dt.Rows.Count; i++) { question.Items.Add(dt.Rows[i][0]); } editquestion.Enabled = history.Enabled = false; populateLocation(); populateDepartment(); populateCourse(); populateTopic(); populateDifficulty(); populateType(); populateFormat(); }
private void format_SelectedIndexChanged(object sender, EventArgs e) { formatID = Convert.ToInt32(SqlManager.query("SELECT FormatID FROM OutputFormats " + "WHERE FormatDescription='" + format.Text + "'").Rows[0][0]); populateQuestions(); }
private void type_SelectedIndexChanged(object sender, EventArgs e) { typeID = Convert.ToInt32(SqlManager.query("SELECT TypeID FROM QuestionTypes " + "WHERE TypeDescription='" + type.Text + "'").Rows[0][0]); populateQuestions(); }
private void difficulty_SelectedIndexChanged(object sender, EventArgs e) { difficultyID = Convert.ToInt32(SqlManager.query("SELECT DifficultyID FROM Difficulty " + "WHERE DifficultyDescription='" + difficulty.Text + "'").Rows[0][0]); populateQuestions(); }
private void topic_SelectedIndexChanged(object sender, EventArgs e) { topicID = Convert.ToInt32(SqlManager.query("SELECT TopicID FROM Topics WHERE " + "TopicDescription='" + topic.Text + "'").Rows[0][0]); populateQuestions(); }
private void course_SelectedIndexChanged(object sender, EventArgs e) { courseID = Convert.ToInt32(SqlManager.query("SELECT CourseID FROM Courses " + "WHERE CourseName='" + course.Text + "' AND LocationID=" + locationID + " AND DepartmentID=" + departmentID).Rows[0][0]); populateQuestions(); }
public AddQuestion(int aid, int qid) { // Author ID will be tracked for later userID = aid; // Question ID will be used if valid ID is passed questionID = qid; InitializeComponent(); // Label columns for easier access subquestionDt.Columns.Add("SubQuestionText", typeof(String)); subquestionDt.Columns.Add("SubQuestionImage", typeof(String)); answerDt.Columns.Add("AnswerText", typeof(String)); answerDt.Columns.Add("AnswerImage", typeof(String)); // populate dropdown boxes populateDropDown(location, "SELECT LocationName FROM Locations"); populateDropDown(topic, "SELECT TopicDescription FROM Topics"); populateDropDown(difficulty, "SELECT DifficultyDescription FROM Difficulty"); populateDropDown(type, "SELECT TypeDescription FROM QuestionTypes"); populateDropDown(format, "SELECT FormatDescription FROM OutputFormats"); if (questionID != 0) { // Populate all fields with question data if a question id was passed to the question var sql = "SELECT QuestionTitle, QuestionBody, QuestionImage, " + "DifficultyID, TypeID, FormatID, LocationID, DepartmentID, CourseID, TopicID " + "FROM Questions Q, CourseTopics C " + "WHERE Q.CourseTopicRefID=C.CourseTopicRefID AND QuestionID=" + questionID; DataTable dt = SqlManager.query(sql); title.Text = dt.Rows[0][0].ToString(); body.Text = dt.Rows[0][1].ToString(); image.Text = dt.Rows[0][2].ToString(); var ret = SqlManager.query("SELECT DifficultyDescription FROM Difficulty WHERE DifficultyID=" + dt.Rows[0][3].ToString()).Rows[0][0].ToString(); difficulty.SelectedIndex = difficulty.FindStringExact(ret); ret = SqlManager.query("SELECT TypeDescription FROM QuestionTypes WHERE TypeID=" + dt.Rows[0][4].ToString()).Rows[0][0].ToString(); type.SelectedIndex = type.FindStringExact(ret); ret = SqlManager.query("SELECT FormatDescription FROM OutputFormats WHERE FormatID=" + dt.Rows[0][5].ToString()).Rows[0][0].ToString(); format.SelectedIndex = format.FindStringExact(ret); ret = SqlManager.query("SELECT LocationName FROM Locations WHERE LocationID=" + dt.Rows[0][6].ToString()).Rows[0][0].ToString(); location.SelectedIndex = location.FindStringExact(ret); ret = SqlManager.query("SELECT DepartmentName FROM Departments WHERE DepartmentID=" + dt.Rows[0][7].ToString()).Rows[0][0].ToString(); department.SelectedIndex = department.FindStringExact(ret); ret = SqlManager.query("SELECT CourseName FROM Courses WHERE CourseID=" + dt.Rows[0][8].ToString()).Rows[0][0].ToString(); course.SelectedIndex = course.FindStringExact(ret); ret = SqlManager.query("SELECT TopicDescription FROM Topics WHERE TopicID=" + dt.Rows[0][9].ToString()).Rows[0][0].ToString(); topic.SelectedIndex = topic.FindStringExact(ret); // Populate subquestions sql = "SELECT SubQuestionID, SubQuestionText, SubQuestionImage FROM SubQuestions " + "WHERE QuestionID=" + questionID + " ORDER BY SubQuestionID"; dt = SqlManager.query(sql); if (dt.Rows.Count > 0) { subquestion.Enabled = true; } for (int i = 0; i < dt.Rows.Count; i++) { subquestion.Items.Add("SubQuestion " + i); var row = subquestionDt.NewRow(); row["SubQuestionText"] = dt.Rows[i][1]; row["SubQuestionImage"] = dt.Rows[i][2]; subquestionDt.Rows.Add(row); } // Populate answers sql = "SELECT AnswerID, AnswerText, AnswerImage FROM Answers " + "WHERE QuestionID=" + questionID + " ORDER BY AnswerID"; dt = SqlManager.query(sql); if (dt.Rows.Count > 0) { answer.Enabled = true; } for (int i = 0; i < dt.Rows.Count; i++) { answer.Items.Add("Answer " + i); var row = answerDt.NewRow(); row["AnswerText"] = dt.Rows[i][1]; row["AnswerImage"] = dt.Rows[i][2]; answerDt.Rows.Add(row); } } }
private void submit_Click(object sender, EventArgs e) { // Check question title, this should be unique to make searching easier if (questionID == 0 && SqlManager.query("SELECT 1 from Questions WHERE QuestionTitle='" + title.Text + "'").Rows.Count == 1) { MessageBox.Show("Question title already exists, update title and resubmit.", "Title not unique", MessageBoxButtons.OK, MessageBoxIcon.Warning); submit.Enabled = false; return; } else if (questionID != 0 && SqlManager.query("SELECT 1 from Questions WHERE QuestionTitle='" + title.Text + "' AND QuestionID<>" + questionID).Rows.Count == 1) { MessageBox.Show("Question title already exists, update title and resubmit.", "Title not unique", MessageBoxButtons.OK, MessageBoxIcon.Warning); submit.Enabled = false; return; } // Check if user would like to commit question to database var result = MessageBox.Show("Would you like to commit your question to the database?", "Commit Question", MessageBoxButtons.YesNo); if (result == DialogResult.No) { return; } // Check if course topics refernece exists, otherwise create a new reference var courseTopicRefID = 0; while (true) { DataTable dt = SqlManager.query("SELECT CourseTopicRefID FROM CourseTopics " + "WHERE LocationID=" + locationID + " AND DepartmentID=" + departmentID + " AND CourseID=" + courseID + " AND TopicID=" + topicID); if (dt.Rows.Count == 0) { if (!SqlManager.insert("INSERT INTO CourseTopics (LocationID, DepartmentID, CourseID, TopicID) " + "VALUES (" + locationID + ", " + departmentID + ", " + courseID + ", " + topicID + ")")) { MessageBox.Show("Course topic info could not be inserted into database, contact database admin.", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error); submit.Enabled = false; return; } } else { courseTopicRefID = Convert.ToInt32(dt.Rows[0][0]); break; } } StringBuilder sql; if (questionID != 0) { sql = new StringBuilder("UPDATE Questions SET "); if (!string.IsNullOrWhiteSpace(image.Text)) { sql.Append("QuestionImage='" + image.Text + "', "); } sql.Append("QuestionTitle='" + title.Text + "', "); sql.Append("QuestionBody='" + body.Text + "', "); sql.Append("CourseTopicRefID=" + courseTopicRefID + ", "); sql.Append("DifficultyID=" + difficultyID + ", "); sql.Append("TypeID=" + typeID + ", "); sql.Append("FormatID=" + formatID + " "); sql.Append("WHERE QuestionID=" + questionID); } else { sql = new StringBuilder("INSERT INTO Questions ("); if (!string.IsNullOrWhiteSpace(image.Text)) { sql.Append("QuestionImage, "); } sql.Append("QuestionPublished, QuestionTitle, QuestionBody, CourseTopicRefID, "); sql.Append("DifficultyID, TypeID, FormatID, AuthorID) VALUES (1, '"); sql.Append(title.Text + "', '"); sql.Append(body.Text + "', "); sql.Append(courseTopicRefID + ", "); sql.Append(difficultyID + ", "); sql.Append(typeID + ", "); sql.Append(formatID + ", "); sql.Append(userID + ")"); } if (SqlManager.insert(sql.ToString())) { if (questionID != 0 && subquestionDt.Rows.Count > 0) { SqlManager.update("DELETE FROM SubQuestions WHERE QuestionID=" + questionID); } // Add subquestion(s) if present for (int i = 0; i < subquestionDt.Rows.Count; i++) { var sqlSubQ = new StringBuilder("INSERT INTO SubQuestions (QuestionID, SubQuestionID, SubQuestionText"); if (!string.IsNullOrWhiteSpace(subquestionDt.Rows[i]["SubQuestionImage"].ToString())) { sqlSubQ.Append(", SubQuestionImage) "); } else { sqlSubQ.Append(") "); } sqlSubQ.Append("SELECT QuestionID, "); sqlSubQ.Append(i + ", "); sqlSubQ.Append("'" + subquestionDt.Rows[i]["SubQuestionText"] + "' "); if (!string.IsNullOrWhiteSpace(subquestionDt.Rows[i]["SubQuestionImage"].ToString())) { sqlSubQ.Append(", '" + subquestionDt.Rows[i]["SubQuestionImage"] + "' "); } sqlSubQ.Append("FROM Questions WHERE QuestionTitle='"); sqlSubQ.Append(title.Text + "'"); SqlManager.insert(sqlSubQ.ToString()); } // Add answer(s) if present if (questionID != 0 && answerDt.Rows.Count > 0) { SqlManager.update("DELETE FROM Answers WHERE QuestionID=" + questionID); } for (int i = 0; i < answerDt.Rows.Count; i++) { var sqlAns = new StringBuilder("INSERT INTO Answers (QuestionID, AnswerID, AnswerText"); if (!string.IsNullOrWhiteSpace(answerDt.Rows[i]["AnswerImage"].ToString())) { sqlAns.Append(", AnswerImage) "); } else { sqlAns.Append(") "); } sqlAns.Append("SELECT QuestionID, "); sqlAns.Append(i + ", "); sqlAns.Append("'" + answerDt.Rows[i]["AnswerText"] + "' "); if (!string.IsNullOrWhiteSpace(answerDt.Rows[i]["AnswerImage"].ToString())) { sqlAns.Append(", '" + answerDt.Rows[i]["AnswerImage"] + "' "); } sqlAns.Append("FROM Questions WHERE QuestionTitle='"); sqlAns.Append(title.Text + "'"); SqlManager.insert(sqlAns.ToString()); } // Add history tag sql = new StringBuilder("INSERT INTO History (QuestionID, UserID, EditDescription)"); sql.Append("SELECT QuestionID, AuthorID, '"); if (questionID == 0) { sql.Append("Initial Commit"); } else { var commitMsg = new CommitMessage(); string msg = null; while (string.IsNullOrWhiteSpace(msg)) { commitMsg.ShowDialog(); msg = commitMsg.msg; } sql.Append(msg); } sql.Append("' FROM Questions WHERE QuestionTitle='"); sql.Append(title.Text + "'"); if (SqlManager.insert(sql.ToString())) { MessageBox.Show("Question successfully added to database.", "Question Added Successfully"); this.Close(); } else { MessageBox.Show("An error occured trying to insert into history table, see database admin.", "Error Occured", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("An error occured trying to insert into questions table, see database admin.", "Error Occured", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private bool userAdmin() { DataTable dt = SqlManager.query("SELECT * FROM Admins WHERE AdminID = (SELECT UserID FROM Users WHERE Username = '******')"); return(dt.Rows.Count != 0); }
private bool userActive() { DataTable dt = SqlManager.query("SELECT * FROM NonActive WHERE NonActiveID = (SELECT UserID FROM Users WHERE Username = '******')"); return(dt.Rows.Count != 0); }