I need to loop through the email addresses in a table within a SQL Server database and all an Email

Question

I was wondering if someone could help me. I've got a simple Suppliers table that has email addresses for my suppliers.

I need to loop through the email addresses 'SuppEmail' in the Suppliers table in the SQL Server database SpecCars and send them all the email below.

I've been at it for a few days now, looking on-line and trying many different variations, but no matter what I do, it only sends one email to the first entry frontdesk@jacksauto.com.au in the table and that's it.

If you could help, that would be fantastic. It's an ASP.NET C# solution.

This is the Suppliers table, it just has two records in there:

CREATE table Suppliers
(
    SuppId      INT IDENTITY(1,1)  PRIMARY KEY,
    SuppName    NVARCHAR(60)       NOT NULL, 
    SuppAddress NVARCHAR(150)      NOT NULL, 
    SuppSuburb  NVARCHAR(60)       NOT NULL, 
    SuppState   NVARCHAR(30)       NOT NULL, 
    SuppPost    NVARCHAR(10)       NOT NULL,
    SuppPhone   NVARCHAR(10)       NOT NULL,
    SuppEmail   NVARCHAR(100)      NOT NULL,
    SuppCode    NVARCHAR(10)       NOT NULL
)

Insert into Suppliers (SuppName, SuppAddress, SuppSuburb, SuppState, SuppPost, SuppPhone, SuppEmail, SuppCode) 
values ('Jacks Auto', '2 Jill Street', 'Belgrade', 'VIC', '3299', '9555 4457', 'frontdesk@jacksauto.com.au', 'JACBLA')

Insert into Suppliers (SuppName, SuppAddress, SuppSuburb, SuppState, SuppPost, SuppPhone, SuppEmail, SuppCode) 
values ('Ultimate Lights', '205 Browns Road', 'Tullamarine', 'VIC', '3011', '9877 2255', 'orders@ultimatlights.com.au', 'ULTTUL') 

This is the code snippet :

  SqlDataReader sqlData;
  SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SpecCars;Integrated Security=True");

  connection.Open();
  sqlData = new SqlCommand("Select SuppEmail From Suppliers", connection).ExecuteReader();

  int count = sqlData.FieldCount;

  while (sqlData.Read())
  {
     for (int i = 0; i < count; i++)
    {
      string emailnew = sqlData[i].ToString();

        MailMessage mailMessage = new MailMessage();

        mailMessage.From = new MailAddress("myemail.com");
        mailMessage.To.Add("myemail.com");
        mailMessage.To.Add(emailnew);
        //mailMessage.CC.Add(emailnew);
        mailMessage.Subject = "Assembly Line Stop";
        mailMessage.Priority = MailPriority.High;

        mailMessage.Body = "Please be advised that the assembly line at Specialised Cars has STOPPED. You will be notified once the line has started again. Any Services between the LINE STOP and the LINE START will be carried out after 19:00 (7pm).";
        mailMessage.IsBodyHtml = true;

        SmtpClient smtpClient = new SmtpClient("smtp-mail.myprovider.com", 587);
        smtpClient.EnableSsl = true;
        smtpClient.Credentials = new System.Net.NetworkCredential("myemail.com", "password");
        smtpClient.Send(mailMessage);
    }
}
connection.Close();

Answer 1

Try this:

var mailMessage = CreateMessage();
using(var connection = new SqlConnection("Data Source=.;Initial Catalog=SpecCars;Integrated Security=True"))
{
    connection.Open();
    using(var sqlData = new SqlCommand("Select SuppEmail From Suppliers", connection).ExecuteReader())
    {
        while (sqlData.Read())
        {
            mailMessage.Bcc.Add(emailnew);
        }
    }
}
SendMessage(mailMessage)


private MailMessage CreateMessage()
{
    var mailMessage = new MailMessage();
    mailMessage.From = new MailAddress("myemail.com");
    mailMessage.To.Add("myemail.com");

    mailMessage.Subject = "Assembly Line Stop";
    mailMessage.Priority = MailPriority.High;

    mailMessage.Body = "Please be advised that the assembly line at Specialised Cars has STOPPED. You will be notified once the line has started again. Any Services between the LINE STOP and the LINE START will be carried out after 19:00 (7pm).";
    mailMessage.IsBodyHtml = true;
    return mailMessage;
}

private void SendMessage(MailMessage mailMessage)
{
    var smtpClient = new SmtpClient("smtp-mail.myprovider.com", 587);
    smtpClient.EnableSsl = true;
    smtpClient.Credentials = new System.Net.NetworkCredential("myemail.com", "password");
    smtpClient.Send(mailMessage);
}

Note: this code can be and should be better. Consider refactoring to get the connectionstring and mail settings from web.config instead of having them hard-coded in your application.

Answer 2

 //-----------------

//Hi All, just a quick update:


//Trying8.aspx - Finally Works, it sends LINE STOP Email to every SuppEmail recipient in Suppliers Table

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Trying8.aspx.cs" Inherits="SpecCars.Admin.Trying8" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
      <br />
      <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

//-----------------

//Trying8.aspx.cs - Finally Works, it sends LINE STOP Email to every SuppEmail recipient in Suppliers Table

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;
using System.Text;

namespace SpecCars.Admin
{
  public partial class Trying8 : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {

      SqlDataReader sqlData;
      SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SpecCars;Integrated Security=True");
      connection.Open();

      sqlData = new SqlCommand("Select * From Suppliers", connection).ExecuteReader();
      using (SmtpClient smtpClient = new SmtpClient("smtp-mail.provider.com", 587))
      {
        while (sqlData.Read())
        {
          string emailnew = sqlData["SuppEmail"].ToString();
          Label1.Text = emailnew.ToString();

          using (MailMessage message = new MailMessage())
          {
            try
            {
              message.From = new MailAddress("myemail@outlook.com");
              // This doesn't Send (To:) myotheremail@yahoo.com.au
              MailAddress AddressTo = new MailAddress("myotheremail@yahoo.com.au");
              // This does Send (To:) to SuppEmail recipient in Suppliers Table
              message.To.Add(emailnew);
              //This does Send a (CC:) myotheremail@yahoo.com.au
              message.CC.Add("myotheremail@yahoo.com.au");
              message.Subject = "Assembly Line Stop";
              message.Priority = MailPriority.High;

              message.Body = "Please be advised that the assembly line at Specialised Cars has STOPPED. You will be notified once the line has started again. Any Services between the LINE STOP and the LINE START will be carried out after 19:00 (7pm).";
              message.IsBodyHtml = true;

              smtpClient.EnableSsl = true;
              smtpClient.Credentials = new System.Net.NetworkCredential("myemail@outlook.com", "password");
              smtpClient.Send(message);
              // smtpClient.Dispose();
              // message.Dispose();
            }
            catch (Exception ex)
            {
              //log exceptions here, you can write it to a txt file, or to a label in your form for testing purpose
              //we are trying to see if you get an exception..
              Label1.Text = ex.Message;
            }
          }
        }
      }

    }
  }
}

//-----------------