Click here to Skip to main content
15,881,204 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am developing a web application that provides the users with short quizzes. The system will check the Quiz table in the database which consists of a QuizID and IsSent column.

If the IsSent column (which is a bit data type) has a value of 0 (which is false), the system will send the quiz to all users. If it has a 1, this means the quiz has already been sent.

I am able to let the application sends emails, but if there is more than quiz without sending it to the users, the system will send all of them and this should not be happened. What should happen is, checking the database if there is more than one quiz there, send the old created one not all of the quizzes starting by the last created one. SO HOW TO DO THAT?

My code-behind is:
C#
protected void Page_Load(object sender, EventArgs e)
    {
        SendEmailTOAllUser();
    }


    protected void SendEmail(string toAddresses, string fromAddress, string MailSubject, string MessageBody, bool isBodyHtml)
    {
        SmtpClient sc = new SmtpClient("SMTP (MAIL) ADDREASS");
        try
        {
            MailMessage msg = new MailMessage();
            msg.From = new MailAddress("pssp@gmail.com", "Our System");

            // In case the mail system doesn't like no to recipients. This could be removed
            msg.To.Add("psTest@gmail.com");

            msg.Bcc.Add(toAddresses);
            msg.Subject = MailSubject;
            msg.Body = MessageBody;
            msg.IsBodyHtml = isBodyHtml;

            sc.Send(msg);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }


    protected void SendEmailTOAllUser()
    {
        string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=spTest;Integrated Security=True";

        using (SqlConnection conn = new SqlConnection(connString))
        {
            var sbEmailAddresses = new System.Text.StringBuilder(1000);
            var quizIds = new List();

            // Open DB connection.
            conn.Open();

            string cmdText = "SELECT QuizID FROM dbo.QUIZ WHERE IsSent <> 1";
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        // There is only 1 column, so just retrieve it using the ordinal position
                        quizIds.Add(reader.GetInt32(0));
                    }
                }
                reader.Close();
            }

            string cmdText2 = "SELECT Username FROM dbo.employee";
            Collection emailAddresses = new Collection();
            using (SqlCommand cmd = new SqlCommand(cmdText2, conn))
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        string emailTo = reader["Username"].ToString();
                        string receiverEmail = emailTo + "@gmail.com";
                        emailAddresses.Add(receiverEmail);
                    }
                }
                reader.Close();
            }

            string cmdText3 = "UPDATE dbo.Quiz SET IsSent = 1 WHERE QuizId = @QuizID";
            using (SqlCommand cmd = new SqlCommand(cmdText3, conn))
            {
                // Add the parameter to the command
                var oParameter = cmd.Parameters.Add("@QuizID", SqlDbType.Int);
                // Get a local copy of the email addresses
                var sEMailAddresses = sbEmailAddresses.ToString();

                foreach (int quizid in quizIds)
                {
                    string link = " Click here to participate ";
                    string body = @" Please try to participate "
                                        + link +
                                        @" 
                    This email was generated using the Safety Portal . 
                    Please do not reply to this email.
                    ";

                    foreach (string email in emailAddresses)
                    {
                        SendEmail(email, "", "Notification Email Subject", body, true);
                    }
                    // Update the parameter for the current quiz
                    oParameter.Value = quizid;
                    // And execute the command
                    cmd.ExecuteNonQuery();
                }
            }
            conn.Close();
        }

    }
Posted
Updated 19-Feb-13 7:24am
v3

Two things occur here:

1) You could always check the value of IsSent in your database, before you send it...
2) Why on earth are you sending the emails that way? What is wrong with the BCC field? Send the email to a dummy-do-not-respond address, and BCC all the other addresses.

I would also add a DateTime field to the QuizID to hold the date submitted - then you could filter to the latest quizzes more easily with the ORDER BY clause. It is also likely that I wouldn't use a IsSent bool, but a DateSent instead - it wouldn't take up much (if any) extra space, and would add a layer of traceability.
 
Share this answer
 
Comments
matrix388 1-Jan-12 3:19am    
Thanks for your help but unfortunately you did not get my question. Please see the edited version of my question.

Also, I totally agree with your second point.
Manfred Rudolf Bihy 1-Jan-12 5:59am    
Very unfortunately for you my dear friend you didn't understand the solution. Griff told you the perfect solution to your "problem" the way it is stated. It is most likely if the solution doesn't fit your requirements then the question is plain wrong.

Easy really!
Manfred Rudolf Bihy 1-Jan-12 6:02am    
Perfect solution to the question as posted by OP. 5+
If OP thinks the solution doesn't fit I'm sure the question must be wrong.

HNY!
You can sort by descending and then pick up the second one.
Something like below
select * from quiz where id not in (select top 1 * from quiz desc).
 
Share this answer
 
Comments
matrix388 1-Jan-12 3:18am    
Thanks for your help but unfortunately you did not get my question. Please see the edited version of my question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900