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");
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();
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())
{
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))
{
var oParameter = cmd.Parameters.Add("@QuizID", SqlDbType.Int);
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);
}
oParameter.Value = quizid;
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}