private void populateFormat() { // If there are no questions, just return if (question.Items.Count == 0) { format.Items.Clear(); return; } StringBuilder sql = new StringBuilder("SELECT FormatDescription FROM OutputFormats " + "WHERE FormatID IN (SELECT FormatID 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(format, dt); }
private void submit_Click(object sender, EventArgs e) { var sql = "SELECT 1 FROM Topics WHERE TopicDescription = '" + topic.Text + "'"; if (SqlManager.query(sql).Rows.Count == 0) { sql = "INSERT INTO Topics (TopicDescription) VALUES ('" + topic.Text + "')"; if (SqlManager.insert(sql)) { topic.Items.Add(topic.Text); submit.Enabled = false; MessageBox.Show("Topic " + topic.Text + " added to database.", "Topic Added"); } else { MessageBox.Show("Topic not added, contact database admin.", "Topic Not Added", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Topic already exists, database not updated.", "Topic Not Added", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
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]); enableSubmit(); }
private void editquestion_Click(object sender, EventArgs e) { int questionid = Convert.ToInt32(SqlManager.query("SELECT QuestionID FROM Questions WHERE QuestionTitle='" + question.Text + "'").Rows[0][0]); new AddQuestion(userID, questionid).ShowDialog(); populateQuestions(); }
private void submit_Click(object sender, EventArgs e) { var sql = "SELECT 1 FROM Locations WHERE LocationName = '" + location.Text + "'"; if (SqlManager.query(sql).Rows.Count == 0) { sql = "INSERT INTO Locations (LocationName) VALUES ('" + location.Text + "')"; if (SqlManager.insert(sql)) { location.Items.Add(location.Text); submit.Enabled = false; MessageBox.Show("Location " + location.Text + " added to database.", "Location Added"); } else { MessageBox.Show("Location not added, contact database admin.", "Location Not Added", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Location already exists, database not updated.", "Location Not Added", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
private void submit_Click(object sender, EventArgs e) { // Check if location department combo already exists var sql = "SELECT 1 FROM Departments WHERE DepartmentName = '" + department.Text + "' " + "AND LocationID = (SELECT LocationID FROM Locations WHERE LocationName = '" + location.Text + "')"; if (SqlManager.query(sql).Rows.Count == 0) { sql = "INSERT INTO Departments (LocationID, DepartmentName) " + "SELECT LocationID, '" + department.Text + "' FROM Locations WHERE LocationName = '" + location.Text + "'"; if (SqlManager.insert(sql)) { department.Items.Add(department.Text); submit.Enabled = false; MessageBox.Show("Department " + department.Text + " added to database.", "Department Added"); } else { MessageBox.Show("Department not added, contact database admin.", "Department Not Added", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Department already exists, database not updated.", "Department Not Added", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
private void department_SelectedIndexChanged(object sender, EventArgs e) { departmentID = Convert.ToInt32(SqlManager.query("SELECT DepartmentID FROM Departments " + "WHERE DepartmentName='" + department.Text + "' AND LocationID=" + locationID).Rows[0][0]); populateQuestions(); course.Enabled = true; }
private void location_SelectedIndexChanged(object sender, EventArgs e) { locationID = Convert.ToInt32(SqlManager.query("SELECT LocationID FROM Locations " + "WHERE LocationName='" + location.Text + "'").Rows[0][0]); populateQuestions(); department.Enabled = true; }
private void populateDropDown(ComboBox combobox, string sql) { DataTable dt = SqlManager.query(sql); combobox.Items.Clear(); for (int i = 0; i < dt.Rows.Count; i++) { combobox.Items.Add(dt.Rows[i][0]); } }
private void department_SelectedIndexChanged(object sender, EventArgs e) { departmentID = Convert.ToInt32(SqlManager.query("SELECT DepartmentID FROM Departments " + "WHERE DepartmentName='" + department.Text + "' AND LocationID=" + locationID).Rows[0][0]); // Populate course dropdown course.Enabled = true; var sql = "SELECT CourseName FROM Courses WHERE LocationID=" + locationID + " AND DepartmentID=" + departmentID; populateDropDown(course, sql); enableSubmit(); }
public UpdateUser() { InitializeComponent(); // Populate Users dropdown DataTable dt = SqlManager.query("SELECT Username FROM Users"); for (int i = 0; i < dt.Rows.Count; i++) { users.Items.Add(dt.Rows[i][0]); } }
public AddDepartment() { InitializeComponent(); // Populate Locations dropdown DataTable dt = SqlManager.query("SELECT LocationName FROM Locations"); for (int i = 0; i < dt.Rows.Count; i++) { location.Items.Add(dt.Rows[i][0]); } }
private void history_Click(object sender, EventArgs e) { var sql = "SELECT EditTimestamp, UserName, EditDescription FROM History H, Users U " + "WHERE QuestionID = (SELECT QuestionID FROM Questions WHERE QuestionTitle='" + question.Text + "') AND H.UserID = U.UserID"; DataTable dt = SqlManager.query(sql); dt.Columns[0].ColumnName = "Edit Timestamp"; dt.Columns[1].ColumnName = "Username"; dt.Columns[2].ColumnName = "Description"; new History(dt).ShowDialog(); }
public AddTopic() { InitializeComponent(); // Populate Topics dropdown DataTable dt = SqlManager.query("SELECT TopicDescription FROM Topics"); for (int i = 0; i < dt.Rows.Count; i++) { topic.Items.Add(dt.Rows[i][0]); } }
private void location_SelectedIndexChanged(object sender, EventArgs e) { // Populate Departments dropdown department.Items.Clear(); DataTable dt = SqlManager.query("SELECT DepartmentName FROM Departments WHERE LocationID =" + "(SELECT LocationID FROM Locations WHERE LocationName = '" + location.Text + "')"); for (int i = 0; i < dt.Rows.Count; i++) { department.Items.Add(dt.Rows[i][0]); } department.Enabled = true; setButtonVisibility(); }
private void location_SelectedIndexChanged(object sender, EventArgs e) { locationID = Convert.ToInt32(SqlManager.query("SELECT LocationID FROM Locations " + "WHERE LocationName='" + location.Text + "'").Rows[0][0]); // Populate department dropdown department.Enabled = true; var sql = "SELECT DepartmentName FROM Departments WHERE LocationID=" + locationID; populateDropDown(department, sql); // Clear course items if this field was previously selected course.Items.Clear(); course.Enabled = false; enableSubmit(); }
private void submit_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='******'"; DataTable datatable = SqlManager.query(sql); // Test SQL query if (datatable.Rows.Count == 1) { userExists(); } else { sql = "INSERT INTO Users " + "(UserName, UserPassword) VALUES " + "('" + Username + "', '" + Password + "')"; if (SqlManager.insert(sql)) { if (admin.Checked) { sql = "INSERT INTO Admins SELECT UserID FROM Users WHERE UserName = '******'"; if (SqlManager.insert(sql)) { userAdded(Username); } else { databaseError(); } } else { userAdded(Username); } } else { databaseError(); } } }
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 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 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 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 bool userActive() { DataTable dt = SqlManager.query("SELECT * FROM NonActive WHERE NonActiveID = (SELECT UserID FROM Users WHERE Username = '******')"); return(dt.Rows.Count != 0); }
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); } } }