如何只从数据库向所有用户发送一个测验？ [英] How to send only one quiz from the database to all users?

问题描述

我正在开发一个Web应用程序，为用户提供简短的测验。系统将检查数据库中的Quiz表，该表包含QuizID和IsSent列。



如果IsSent列（它是位数据类型）具有值为0（这是假的），系统将测验发送给所有用户。如果它有1，这意味着测验已经发送。



我可以让应用程序发送电子邮件，但如果有超过测验但没有发送它对用户来说，系统将发送所有这些，这不应该发生。 应该发生的事情是，检查数据库中是否有多个测验，发送旧创建的测验，而不是从最后创建的测验开始的所有测验。 那怎么办？



我的代码隐藏是：

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("[e-mail]", "Our System");

            // In case the mail system doesn't like no to recipients. This could be removed
            msg.To.Add("[e-mail]");

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

    }

推荐答案

这里有两件事：



1）您可以在发送之前检查数据库中IsSent的值...

2）为什么你要发送这样的电子邮件？ ？ BCC领域有什么问题？将电子邮件发送到虚拟回复地址，并将所有其他地址BCC发送。



我还要在QuizID中添加一个DateTime字段来保存提交日期 - 然后您可以使用ORDER BY子句更轻松地过滤到最新的测验。也许我不会使用IsSent bool，而是使用DateSent - 它不会占用太多（如果有的话）额外空间，并且会增加一层可追溯性。

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.

您可以按降序排序，然后选择第二个。

如下所示

从测验中选择*，其中id不在（从测验desc中选择前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).

这篇关于如何只从数据库向所有用户发送一个测验？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！