public static void makeSolveStatus()
{
try
{
// step 1: Create a connection
var result = Path.GetFullPath("InformationDatabase.mdf");
string strConnection = "Data Source=.\\sqlexpress;AttachDbFilename=" + result + ";User Instance=true;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open();
// step 2: fire a command
string username = UserInformation.getUserName();
int questionno = UserInformation.getQuestionNo();
// MessageBox.Show("QSN: "+questionno.ToString());
string strCommand = "select userID, QSN from Solve where userID=@UI and QSN=@QN";
SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
objCommand.Parameters.Add(new SqlParameter("@UI", username));
objCommand.Parameters.Add(new SqlParameter("@QN", questionno));
// step 3: bind the result data with user interface
SqlDataReader reader = objCommand.ExecuteReader();
if (reader.Read())
{
}
else
{
string strCommand1 = "insert into Solve values( @userID , @QSN, @Status)";
SqlCommand objCommand1 = new SqlCommand(strCommand1, objConnection);
objCommand1.Parameters.Add(new SqlParameter("@userID", username));
objCommand1.Parameters.Add(new SqlParameter("@QSN", questionno));
objCommand1.Parameters.Add(new SqlParameter("@Status", "Solved"));
objCommand1.ExecuteNonQuery();
}
reader.Close();
objConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error in making Solved! Error: " + ex.Message);
}
}
public void collectUserInformation()
{
// step 1: Create a connection
var result = Path.GetFullPath("InformationDatabase.mdf");
string strConnection = "Data Source=.\\sqlexpress;AttachDbFilename=" + result + ";User Instance=true; Integrated Security=True;Pooling=False";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open();
// step 2: fire a command
string userName = UserInformation.getUserName();
string strCommand = "select userID, userPassword, FullName, userCountry, Birthday, MailAddress, userGender, userPicture from [User] where userID = @username";
SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
objCommand.Parameters.Add(new SqlParameter("@username", userName));
// step 3: bind the result data with user interface
SqlDataReader reader = objCommand.ExecuteReader();
if (reader.Read())
{
// string givenDate = DateTimePicker.Value.ToString("yyyy/MM/dd");
// DateTimePicker myDT = new DateTimePicker();
labelName.Text = reader[2].ToString();
labelUserName.Text = reader[1].ToString();
labelGender.Text = reader[6].ToString();
labelBirthday.Text = reader.GetDateTime(reader.GetOrdinal("Birthday")).ToString("MMMM dd, yyyy");
labelCountry.Text = reader[3].ToString();
labelEmail.Text = reader[5].ToString();
byte[] img = (byte[])(reader[7]);
MemoryStream ms = new MemoryStream(img);
pictureBoxPic.Image = Image.FromStream(ms);
}
reader.Close();
objConnection.Close();
}
public void OnetimeQuizChecker()
{
try
{
// step 1: Create a connection
var result = Path.GetFullPath("InformationDatabase.mdf");
string strConnection = "Data Source=.\\sqlexpress;AttachDbFilename=" + result + ";User Instance=true;Integrated Security=True;Pooling=False";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open();
// step 2: fire a command
string username = UserInformation.getUserName();
string quizno = onlineQuizSerialNo;
string strCommand = "select QuizNo, userID from IndividualOnlineQuizTracker where QuizNo=@QN and userID=@UI";
SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
objCommand.Parameters.Add(new SqlParameter("@QN", quizno));
objCommand.Parameters.Add(new SqlParameter("@UI", username));
// step 3: bind the result data with user interface
SqlDataReader reader = objCommand.ExecuteReader();
if (reader.Read())
{
MessageBox.Show("You participated this quiz once! \n Please wait for the next round..");
this.Hide();
Question q = new Question();
q.ShowDialog();
}
else
{
}
reader.Close();
objConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void makeIndividualOnlineQuizTracker()
{
try
{
userName = UserInformation.getUserName();
QuizNo = UserInformation.getQuizNo();
// step 1: Create a connection
var result = Path.GetFullPath("InformationDatabase.mdf");
string strConnection = "Data Source=.\\sqlexpress;AttachDbFilename=" + result + ";User Instance=true; Integrated Security=True;Pooling=False;MultipleActiveResultSets=true";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open();
string strCommand = "insert into IndividualOnlineQuizTracker values( @QN , @UN )";
SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
objCommand.Parameters.Add(new SqlParameter("@QN", QuizNo));
objCommand.Parameters.Add(new SqlParameter("@UN", userName));
objCommand.ExecuteNonQuery();
objConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void selectQuestionNo()
{
try
{
// step 1: Create a connection
var result = Path.GetFullPath("InformationDatabase.mdf");
string strConnection = "Data Source=.\\sqlexpress;AttachDbFilename=" + result + ";User Instance=true;Integrated Security=True;Pooling=False;MultipleActiveResultSets=true";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open();
// step 2: fire a command
string username = UserInformation.getUserName();
// string quizno = onlineQuizSerialNo;
AllorUnsolved = UserInformation.getAllorSolved();
string strCommand = "";
if (AllorUnsolved == "Unsolved")
{
Unsolved = true;
strCommand = "SELECT QSN FROM Question EXCEPT SELECT QSN FROM Solve WHERE userID=@UN";
}
else if (AllorUnsolved == "All")
{
All = true;
strCommand = "SELECT QSN FROM Question";
}
SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
objCommand.Parameters.Add(new SqlParameter("@UN", username));
// step 3: bind the result data with user interface
SqlDataReader reader = objCommand.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
unsolvedQuestion.Add(reader[i].ToString());
}
}
reader.Close();
objConnection.Close();
if (unsolvedQuestion.Count == 0 && Unsolved == true)
{
MessageBox.Show("You have no unsolved Questions!");
Unsolved = false;
Question q = new Question();
q.ShowDialog();
}
else if (unsolvedQuestion.Count == 0 && All == true)
{
MessageBox.Show("Please Update Questions!\nDatabase is empty.");
All = false;
Question q = new Question();
q.ShowDialog();
}
makeValue(unsolvedQuestion);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Question q = new Question();
q.ShowDialog();
//MessageBox.Show(ex.Message);
}
}