Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
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:
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 31-Dec-11 21:49pm
Edited 19-Feb-13 8:24am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
Comments
matrix388 at 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 R. Bihy at 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 R. Bihy at 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!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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).
  Permalink  
Comments
matrix388 at 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)

  Print Answers RSS
0 DamithSL 365
1 Maciej Los 232
2 OriginalGriff 213
3 BillWoodruff 79
4 Jose Garcia Sevilla 75
0 OriginalGriff 7,953
1 DamithSL 6,139
2 Sergey Alexandrovich Kryukov 5,454
3 Maciej Los 5,293
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 19 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100