protected void SubmitDB(int marks)
{
UserID = new Guid(Session["QuizTakerUserID"].ToString());
QuizID = new Guid(Session["QuizTakerQuizID"].ToString());
using (SqlConnection SqlConnection1 = new SqlConnection(QuizDBContext.QuizConnectionString()))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
string QueryString = "INSERT INTO QuizScores";
QueryString += " (QuizID, QuizName, QuizTakenOn, QuizTakenBy, QuizScore) ";
QueryString += "VALUES (@QuizID, @QuizName, @QuizTakenOn, @QuizTakenBy, @QuizScore)";
SqlCommand InsertCommand = new SqlCommand(QueryString, SqlConnection1);
InsertCommand.Parameters.AddWithValue("QuizID", QuizID);
InsertCommand.Parameters.AddWithValue("QuizName", tableName);
InsertCommand.Parameters.AddWithValue("QuizTakenOn", DateTime.Now);
InsertCommand.Parameters.AddWithValue("QuizTakenBy", UserID);
InsertCommand.Parameters.AddWithValue("QuizScore", marks);
SqlConnection1.Open();
adapter.InsertCommand = InsertCommand;
InsertCommand.ExecuteNonQuery();
}
catch (Exception e)
{
string error = e.ToString();
}
}
}
protected void LoadTable(string TableName)
{
string Connection = QuizDBContext.QuizConnectionString();
string QueryString = "SELECT * FROM " + TableName;
rowNumbers = new List <int>();
dataTable = new DataTable();
dataTable = QueryExecute(QueryString, Connection, TableName);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
rowNumbers.Add(Convert.ToInt32(dr["QuizTableQuestionNumber"].ToString()));
Session["IsTableLoaded"] = "true";
}
}
var parameterCurrentRow = Session["CurrentRow"];
if (parameterCurrentRow != null)
{
currentRow = Convert.ToInt32(parameterCurrentRow.ToString());
}
else
{
currentRow = rowNumbers[0];
Session["CurrentRow"] = currentRow;
}
RowController(currentRow);
}
private void DeleteQuestion(object sender, EventArgs e)
{
LinkButton DeleteButton = (LinkButton)sender;
if (DeleteButton.Attributes["qnumber"].ToString() != "")
{
int rowNumber = Convert.ToInt16(DeleteButton.Attributes["qnumber"].ToString());
using (SqlConnection SqlConnection1 = new SqlConnection(QuizDBContext.QuizConnectionString()))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
string DeleteQueryString = string.Format("DELETE FROM {0} WHERE QuizTableQuestionNumber = @qnumber", tableName);
SqlCommand UpdateCommand = new SqlCommand(DeleteQueryString, SqlConnection1);
UpdateCommand.Parameters.AddWithValue("@qnumber", rowNumber);
SqlConnection1.Open();
adapter.DeleteCommand = UpdateCommand;
UpdateCommand.ExecuteNonQuery();
Response.Redirect("QuizSetup.aspx");
}
catch (Exception error)
{
string errorString = error.ToString();
}
}
}
}
public void InitializeQuizList()
{
string QueryString = @"Select QuizName, QuizID FROM QuizList";
DataTable datatable = new DataTable();
datatable = QueryExecute(QueryString, QuizDBContext.QuizConnectionString());
if (datatable != null)
{
if (datatable.Rows.Count > 0)
{
foreach (DataRow dr in datatable.Rows)
{
HtmlGenericControl li = new HtmlGenericControl("li");
li.InnerText = dr["QuizName"].ToString();
li.Attributes["class"] = "list-group-item";
HtmlButton editButton = new HtmlButton();
editButton.InnerText = "Edit";
editButton.Attributes["class"] = "btn btn-primary";
editButton.Attributes["value"] = SecurityClass.EncryptString(dr["QuizName"].ToString(), "TableNamePhrase");
editButton.Attributes["quizid"] = SecurityClass.EncryptString(dr["QuizID"].ToString(), "QuizID");
editButton.Attributes["action"] = SecurityClass.EncryptString("Edit", "ActionPhrase");
editButton.Attributes["runat"] = "server";
editButton.ServerClick += EditQuiz;
editButton.Attributes["id"] = "editButton";
HtmlButton DeleteButton = new HtmlButton();
DeleteButton.InnerText = "Delete";
DeleteButton.Attributes["class"] = "btn btn-danger";
DeleteButton.Attributes["value"] = SecurityClass.EncryptString(dr["QuizName"].ToString(), "TableNamePhrase");
DeleteButton.Attributes["action"] = SecurityClass.EncryptString("Delete", "ActionPhrase");
DeleteButton.Attributes["quizid"] = SecurityClass.EncryptString(dr["QuizID"].ToString(), "QuizID");
DeleteButton.Attributes["runat"] = "server";
DeleteButton.ServerClick += DeleteQuiz;
DeleteButton.Attributes["id"] = "deleteButton";
HtmlButton SendToButton = new HtmlButton();
SendToButton.InnerText = "Send To Email";
SendToButton.Attributes["class"] = "btn btn-success";
SendToButton.Attributes["value"] = SecurityClass.EncryptString(dr["QuizName"].ToString(), "TableNamePhrase");
SendToButton.Attributes["action"] = SecurityClass.EncryptString("Delete", "ActionPhrase");
SendToButton.Attributes["quizid"] = SecurityClass.EncryptString(dr["QuizID"].ToString(), "QuizID");
SendToButton.Attributes["runat"] = "server";
SendToButton.ServerClick += SendToEmail;
SendToButton.Attributes["id"] = "sendToEmailButton";
QuizListDiv.Controls.Add(li);
li.Controls.Add(editButton);
li.Controls.Add(DeleteButton);
li.Controls.Add(SendToButton);
}
}
}
}
/// <summary>
/// On Page load Queries the "TableName" table and populates the side bar with the list of questions.
/// </summary>
/// <param name="TableName"></param>
protected void EditTable(string TableName)
{
string QueryString = @"Select * FROM " + TableName;
DataTable datatable = new DataTable();
datatable = QueryExecute(QueryString, QuizDBContext.QuizConnectionString());
if (datatable != null)
{
if (datatable.Rows.Count > 0)
{
foreach (DataRow dr in datatable.Rows)
{
LinkButton li = new LinkButton();
li.Text = dr["QuizTableQuizQuestion"].ToString();
li.Attributes["class"] = "list-group-item quizSetupListGroup";
li.Attributes["qnumber"] = dr["QuizTableQuestionNumber"].ToString();
li.CommandArgument = dr["QuizTableQuestionNumber"].ToString();
li.Attributes["runat"] = "server";
li.Click += new System.EventHandler(QuizSideBarLinkButton_Click);
HtmlGenericControl questionDiv = new HtmlGenericControl("div");
questionDiv.Attributes["class"] = "col-xs-11 quizSetupDiv";
HtmlGenericControl deleteButtonDiv = new HtmlGenericControl("div");
deleteButtonDiv.Attributes["class"] = "col-xs-1 quizSetupDiv";
LinkButton DeleteButton = new LinkButton();
DeleteButton.Text = "Delete";
DeleteButton.Attributes["class"] = "btn btn-danger";
DeleteButton.Attributes["runat"] = "server";
DeleteButton.Attributes["id"] = "quizSetupDeleteButton";
DeleteButton.Attributes["qnumber"] = dr["QuizTableQuestionNumber"].ToString();
DeleteButton.Click += new System.EventHandler(DeleteQuestion);
questionDiv.Controls.Add(li);
deleteButtonDiv.Controls.Add(DeleteButton);
quizQuestionListBar.Controls.Add(questionDiv);
quizQuestionListBar.Controls.Add(deleteButtonDiv);
}
}
}
}
/// <summary>
/// Click event when any question is clicked in EditMode from the sidebar of the QuizSetup.aspx page
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void QuizSideBarLinkButton_Click(object sender, EventArgs e)
{
//Response.Redirect("testpage.aspx");
LinkButton li = (LinkButton)sender;
string parameterTableRowID = li.Attributes["qnumber"].ToString();
if (parameterTableRowID != "")
{
DataSet dataset = new DataSet();
using (SqlConnection SqlConnection1 = new SqlConnection(QuizDBContext.QuizConnectionString()))
{
try
{
DataTable dataTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();
string QueryString = string.Format("SELECT * FROM {0} WHERE QuizTableQuestionNumber = @qnumber", tableName);
SqlCommand Command = new SqlCommand(QueryString, SqlConnection1);
Command.Parameters.AddWithValue("@qnumber", parameterTableRowID);
SqlConnection1.Open();
adapter.SelectCommand = Command;
adapter.Fill(dataset, tableName);
dataTable = dataset.Tables[tableName];
SqlConnection1.Close();
if (dataTable.Rows.Count > 0)
{
AddHTMLElements(false, dataTable);
Session["qnumber"] = parameterTableRowID;
}
}
catch (Exception error)
{
string errorString = error.ToString();
}
}
}
else
{
Response.Redirect("QuizSetup.aspx");
}
}
/// <summary>
/// Deletes the Quiz and removes the list item from the QuizList table tableName=TableName
/// </summary>
/// <param name="TableName"></param>
protected void DeleteTable(string TableName)
{
string ConnectionString = QuizDBContext.QuizConnectionString();
if (TableName != "")
{
string RemoveQuizListQueryString = "DELETE FROM QuizList WHERE QuizID='" + quizid + "'";
string DropQueryString = "IF OBJECT_ID('" + TableName + "', 'U') IS NOT NULL ";
DropQueryString += "BEGIN ";
DropQueryString += "DROP TABLE " + TableName;
DropQueryString += " END";
try
{
NonQueryExecute(DropQueryString, ConnectionString, "DeleteTable");
NonQueryExecute(RemoveQuizListQueryString, ConnectionString, "DeleteTable");
}
catch
{
string s = "";
}
}
}
/// <summary>
/// creates table with the specified table name in the Quiz Database and adds a entry into the QuizList Table
/// </summary>
/// <param name="TableName"></param>
protected void CreateTable(string TableName)
{
string ConnectionString = QuizDBContext.QuizConnectionString();
if (TableName != "")
{
string QuizListQueryString = "INSERT INTO QuizList VALUES (@QuizID, @QuizName, @QuizCreatedByUserID, @QuizCreatedOn, @QuizModifiedOn) ";
string QuizNameQueryString = "CREATE TABLE " + TableName + "(QuizTableQuizID uniqueidentifier NOT NULL,QuizTableQuizName varchar(50) NOT NULL,QuizTableQuestionNumber int IDENTITY(1,1), QuizTableQuizQuestion text NOT NULL, QuizTableQuizAnswer1 text NULL, QuizTableQuizAnswer2 text NULL, QuizTableQuizAnswer3 text NULL,QuizTableQuizAnswer4 text NULL, QuizTableQuizAnswer5 text NULL, QuizTableQuizAnswer6 text NULL, QuizTableAnswer text NOT NULL, PRIMARY KEY (QuizTableQuestionNumber))";
try
{
NonQueryExecute(QuizListQueryString, ConnectionString, "CreateList");
NonQueryExecute(QuizNameQueryString, ConnectionString, "CreateTable");
}
catch (Exception e)
{
testLabel.Text = e.ToString();
}
}
else
{
testLabel.Text = "No Table selected";
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SetDictionaryAnswerFromCheckbox(currentRow);
bool AllAnswered = true;
string ErrorMessage = "";
foreach (int value in rowNumbers)
{
if (AnswerList.ContainsKey(value))
{
if (AnswerList[value] == null)
{
AllAnswered = false;
ErrorMessage = "Please answer all questions";
}
}
else
{
AllAnswered = false;
}
}
//if all the answers are answered, then the database is queried for each question's answer and compared with
//the users answers stored in the local dictionary.
if (AllAnswered)
{
int marks = 0;
SetMessage("Congrats", "alert alert-success");
foreach (int row in rowNumbers)
{
List <int> UserAnswers = new List <int>();
List <int> DBAnswers = new List <int>();
using (SqlConnection SqlConnection1 = new SqlConnection(QuizDBContext.QuizConnectionString()))
{
try
{
DataTable dataTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();
string QueryString = string.Format("SELECT * FROM {0} WHERE QuizTableQuestionNumber = @qnumber", tableName);
SqlCommand Command = new SqlCommand(QueryString, SqlConnection1);
Command.Parameters.AddWithValue("@qnumber", row);
SqlConnection1.Open();
adapter.SelectCommand = Command;
adapter.Fill(dataTable);
SqlConnection1.Close();
if (dataTable.Rows.Count > 0)
{
DataRow[] dr = dataTable.Select("QuizTableQuestionNumber = " + row);
DBAnswers = GetIntFromString(dr[0]["QuizTableAnswer"].ToString());
List <int> c;
if (AnswerList.TryGetValue(row, out c))
{
UserAnswers = AnswerList[row];
}
}
}
catch (Exception error)
{
string errorString = error.ToString();
}
}
if (UserAnswers.Count > 0)
{
if (CompareAnswers(UserAnswers, DBAnswers))
{
marks++;
}
}
}
int MarksPercentage = (marks / rowNumbers.Count) * 100;
SubmitDB(MarksPercentage);
}
else
{
SetMessage(ErrorMessage, "alert alert-danger");
}
}
/// <summary>
/// Triggered when add or Update button on the quiz setup page.
/// Adds a new question or edits the existing one by
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnAddQuestionToDB_Click(object sender, EventArgs e)
{
//css value for bootstrap alert type danger
string alertTypeDanger = "alert alert-danger";
//Checks the set session variable for extra security if the display property of questionWindow is modified on runtime and prevents attacks
if (Session["display"].ToString() == "block")
{
if (txtQuestion.InnerText == "")
{
SetMessage("The Question Cannot be Empty", alertTypeDanger);
}
else
{
int answerCount = 0;
int checkCount = 0;
List <int> answerArray = new List <int>();
if (txtanswer1.Value != "")
{
answerCount++;
}
if (txtanswer2.Value != "")
{
answerCount++;
}
if (txtanswer3.Value != "")
{
answerCount++;
}
if (txtanswer4.Value != "")
{
answerCount++;
}
if (txtanswer5.Value != "")
{
answerCount++;
}
if (txtanswer6.Value != "")
{
answerCount++;
}
if (checkbox1.Checked == true)
{
if (txtanswer1.Value != "")
{
checkCount++;
answerArray.Add(1);
}
else
{
SetMessage("Answer cannot be empty", alertTypeDanger);
}
}
if (checkbox2.Checked == true)
{
if (txtanswer2.Value != "")
{
checkCount++;
answerArray.Add(2);
}
else
{
SetMessage("Answer cannot be empty", alertTypeDanger);
}
}
if (checkbox3.Checked == true)
{
if (txtanswer3.Value != "")
{
checkCount++;
answerArray.Add(3);
}
else
{
SetMessage("Answer cannot be empty", alertTypeDanger);
}
}
if (checkbox4.Checked == true)
{
if (txtanswer4.Value != "")
{
checkCount++;
answerArray.Add(4);
}
else
{
SetMessage("Answer cannot be empty", alertTypeDanger);
}
}
if (checkbox5.Checked == true)
{
if (txtanswer5.Value != "")
{
checkCount++;
answerArray.Add(5);
}
else
{
SetMessage("Answer cannot be empty", alertTypeDanger);
}
}
if (checkbox6.Checked == true)
{
if (txtanswer6.Value != "")
{
checkCount++;
answerArray.Add(6);
}
else
{
SetMessage("Answer cannot be empty", alertTypeDanger);
}
}
if (answerCount > 1)
{
if (checkCount > 0)
{
SetMessage("Success", "alert alert-success");
string answerList = string.Join(",", answerArray);
string InsertQueryString = string.Format("INSERT INTO {0} ", tableName);
InsertQueryString += "(QuizTableQuizID, QuizTableQuizName, QuizTableQuizQuestion, QuizTableQuizAnswer1, QuizTableQuizAnswer2, QuizTableQuizAnswer3, QuizTableQuizAnswer4, QuizTableQuizAnswer5, QuizTableQuizAnswer6, QuizTableAnswer) ";
InsertQueryString += "VALUES (@QuizID, @QuizName, @QuizQuestion, @Answer1, @Answer2, @Answer3, @Answer4, @Answer5, @Answer6,@Answer)";
string UpdateQueryString = string.Format("UPDATE {0} ", tableName);
UpdateQueryString += " SET QuizTableQuizID=@QuizID, QuizTableQuizName= @QuizName, QuizTableQuizQuestion=@QuizQuestion, QuizTableQuizAnswer1=@Answer1, QuizTableQuizAnswer2=@Answer2, QuizTableQuizAnswer3=@Answer3, QuizTableQuizAnswer4=@Answer4, QuizTableQuizAnswer5=@Answer5, QuizTableQuizAnswer6=@Answer6, QuizTableAnswer=@Answer ";
UpdateQueryString += " WHERE QuizTableQuestionNumber=@qnumber";
using (SqlConnection SqlConnection1 = new SqlConnection(QuizDBContext.QuizConnectionString()))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand InsertCommand = new SqlCommand(InsertQueryString, SqlConnection1);
InsertCommand.Parameters.AddWithValue("QuizID", quizid);
InsertCommand.Parameters.AddWithValue("QuizName", tableName);
InsertCommand.Parameters.AddWithValue("QuizQuestion", txtQuestion.Value);
InsertCommand.Parameters.AddWithValue("Answer1", txtanswer1.Value);
InsertCommand.Parameters.AddWithValue("Answer2", txtanswer2.Value);
InsertCommand.Parameters.AddWithValue("Answer3", txtanswer3.Value);
InsertCommand.Parameters.AddWithValue("Answer4", txtanswer4.Value);
InsertCommand.Parameters.AddWithValue("Answer5", txtanswer5.Value);
InsertCommand.Parameters.AddWithValue("Answer6", txtanswer6.Value);
InsertCommand.Parameters.AddWithValue("Answer", answerList);
SqlCommand UpdateCommand = new SqlCommand(UpdateQueryString, SqlConnection1);
UpdateCommand.Parameters.AddWithValue("QuizID", quizid);
UpdateCommand.Parameters.AddWithValue("QuizName", tableName);
UpdateCommand.Parameters.AddWithValue("QuizQuestion", txtQuestion.Value);
UpdateCommand.Parameters.AddWithValue("Answer1", txtanswer1.Value);
UpdateCommand.Parameters.AddWithValue("Answer2", txtanswer2.Value);
UpdateCommand.Parameters.AddWithValue("Answer3", txtanswer3.Value);
UpdateCommand.Parameters.AddWithValue("Answer4", txtanswer4.Value);
UpdateCommand.Parameters.AddWithValue("Answer5", txtanswer5.Value);
UpdateCommand.Parameters.AddWithValue("Answer6", txtanswer6.Value);
UpdateCommand.Parameters.AddWithValue("Answer", answerList);
UpdateCommand.Parameters.AddWithValue("qnumber", Session["qnumber"].ToString());
SqlConnection1.Open();
adapter.InsertCommand = InsertCommand;
adapter.UpdateCommand = UpdateCommand;
if (Session["update"].ToString() == "false")
{
InsertCommand.ExecuteNonQuery();
}
else if (Session["update"].ToString() == "true")
{
UpdateCommand.ExecuteNonQuery();
}
SqlConnection1.Close();
}
catch (Exception p)
{
string s = p.ToString();
}
EditTable(tableName);
Response.Redirect("QuizSetup.aspx");
}
}
else
{
SetMessage("Select Atleast one answer", alertTypeDanger);
}
}
else
{
SetMessage("Atleast two answers are required", alertTypeDanger);
}
}
}
else
{
Response.Redirect("QuizSetup.aspx");
}
}