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); } }