locked
Best strategy to batch emails by developer email address RRS feed

  • Question

  • User-718146471 posted

    Hello folks, I'm trying to determine the best strategy for sorting out the emails into a digest-like email. Here's what the code does:

    1. Step through the grid view to determine which forms to send out

    2. If the box is checked, send the email to the developer

    3. Repeat until through the grid view

    What I would like to do is the following:

    1. Step through the grid view to find all checked items

    2. Send out the email digest to each developer and continue to the end

    Here is my current code, feel free to respond with pseudo-code or if you feel like tackling this, reply with working code. I more need to know strategic operations on what to do when for maximum efficiency. Thanks!

            protected void btnSendChecked_Click(object sender, EventArgs e)
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
                Int32 recID = 0;
                string appName = string.Empty, auditedIssues = string.Empty, auditorComments = string.Empty, auditorName = string.Empty, devEmail = string.Empty, dateSubmitted = string.Empty, evalSent = string.Empty, mailFrom = string.Empty, mailTo = string.Empty, mailBcc = string.Empty, mailCC = string.Empty, mailSubject = string.Empty, mailHtmlBody = string.Empty;
                if (gvUnsent.Rows.Count > 0)
                {
                    foreach (GridViewRow row in gvUnsent.Rows)
                    {
                        Label lblCheckRow = (row.Cells[0].FindControl("lblSendTo") as Label);
                        CheckBox chkRow = (row.Cells[0].FindControl("cbSendTo") as CheckBox);
                        if (chkRow.Checked)
                        {
                            string sql = "SELECT AppName, AuditedIssues, AuditorComments, AuditorName, DateSubmitted, DevEmail, CCEmail FROM EvalSent WHERE RecID = @RecID ORDER BY DevEmail";
                            recID = Convert.ToInt32(lblCheckRow.Text.ToString());
                            {
                                // Query by email address
                                SqlCommand cmd = new SqlCommand(sql, conn);
    
                                try
                                {
                                    try
                                    {
                                        conn.Open();
                                        cmd.Parameters.AddWithValue("@RecID", recID);
                                        SqlDataReader rdr = cmd.ExecuteReader();
                                        while (rdr.Read())
                                        {
                                            appName = rdr.GetValue(0).ToString();
                                            auditedIssues = rdr.GetValue(1).ToString();
                                            auditorComments = rdr.GetValue(2).ToString();
                                            auditorName = rdr.GetValue(3).ToString();
                                            dateSubmitted = rdr.GetValue(4).ToString();
                                            devEmail = rdr.GetValue(5).ToString();
                                            mailCC = rdr.GetValue(6).ToString();
                                            mailTo = rdr.GetValue(5).ToString();
                                        }
                                        rdr = null;
                                    }
                                    catch (Exception ex)
                                    {
                                        Response.Write("Error getting value to send email to. Error: " + ex.ToString());
                                    }
                                    finally
                                    {
                                        conn.Close();
                                    }
                                    mailHtmlBody = "Hello, I have audited " + Server.HtmlEncode(appName.ToString()) + " ";
                                    if (auditedIssues != "No Issues.")
                                    {
                                        mailHtmlBody = mailHtmlBody + "<br><br>We have found the following issues. Please reply in system with your comments/fixes.<br><br> " + auditedIssues + "";
                                    }
                                    else
                                    {
                                        mailHtmlBody = mailHtmlBody + "There are no issues found in this application.";
                                    }
                                    mailHtmlBody = mailHtmlBody + "<br><br>Please let me know if you have any questions.<br><br>Respectfully,<br><br>" + auditorName.ToString();
                                    mailFrom = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
                                    mailSubject = "Application Evaluation: " + appName.ToString();
                                    smtpHelper.SendMailMessage(mailFrom, mailTo, mailBcc, mailCC, mailSubject, mailHtmlBody);
                                    try
                                    {
                                        string sqlUpdateSent = "UPDATE EvalSent SET [EvalSent] = @EvalSent WHERE RecID = @RecID";
                                        SqlCommand cmdUpd = new SqlCommand(sqlUpdateSent, conn);
                                        conn.Open();
                                        cmdUpd.Parameters.Add("@RecID", SqlDbType.Int).Value = recID;
                                        cmdUpd.Parameters.Add("@EvalSent", SqlDbType.Bit).Value = true;
                                        cmdUpd.ExecuteNonQuery();
                                    }
                                    catch (Exception ex)
                                    {
                                        Response.Write("Error occurred updating date & time eval sent. Error: " + ex.ToString());
                                    }
                                    finally
                                    {
                                        conn.Close();
                                    }
                                }
    
                                catch (Exception ex)
                                {
                                    Response.Write("Error occurred sending email. Error: " + ex.ToString());
                                }
                                finally
                                {
                                    // Clearing all used values setting to empty or nothing
                                    appName = string.Empty;
                                    auditedIssues = string.Empty;
                                    auditorComments = string.Empty;
                                    auditorName = string.Empty;
                                    devEmail = string.Empty;
                                    dateSubmitted = string.Empty;
                                    evalSent = string.Empty;
                                }
                            }
                        }
                        recID = 0;
                    }
                    BindData();
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "msgbox1", "alert('Request(s) successfully sent!');", true);
                }
            }
        }
    

    Friday, December 2, 2016 1:59 PM

Answers

  • User-707554951 posted

    Hi bbcompent1,

    From your description, I suggest you could use Jquery each()function to get all checked checkbox in client side, then you could save all checked checkbox id in a hidden filed.

    In the code behind, you could get all check box id, then you could split it and use for or foreach to iterate the string in hidden filed,

    Retrieve data form database then send your email.

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 5, 2016 8:03 AM

All replies

  • User-707554951 posted

    Hi bbcompent1,

    From your description, I suggest you could use Jquery each()function to get all checked checkbox in client side, then you could save all checked checkbox id in a hidden filed.

    In the code behind, you could get all check box id, then you could split it and use for or foreach to iterate the string in hidden filed,

    Retrieve data form database then send your email.

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 5, 2016 8:03 AM
  • User-718146471 posted

    OK, that strategy makes sense. I'll try it on my own and follow up with you thereafter.

    Monday, December 5, 2016 7:44 PM
  • User-718146471 posted

    Perhaps you could show me the JQuery part? I'm kinda rusty on that part.

    Friday, December 9, 2016 12:46 PM
  • User-707554951 posted

    Hi bbcompent1,

    Following code for your reference.

     <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
         <script>
             $(document).ready(function () {
                 $('#part1 input[ type=checkbox]').change(function () {
                     var text = "";
                     $('#part1 input[ type=checkbox]').each(function () {
                         if ($(this).is(":checked")) {
                             // var text = $(this).val();
                             text += $(this)[0].nextSibling.nodeValue + ",";
                         }
                     });
                     $("#HiddenField1").val(text);
                   //  alert($("#HiddenField1").val());
                 });
             })
          
         </script>

     <div id="part1">
         <input type="checkbox" value="Mycalender"/>Mycalender<br/>
           <input type="checkbox" value="MyPainter"/>MyPainter<br/>
           <input type="checkbox"value="Myreminder"/>Myreminder<br/>
             <asp:HiddenField ID="HiddenField1" runat="server"  Value=""/>
        </div>

    Best regards

    Cathy

    Monday, December 12, 2016 2:17 AM