我想知道是否有人能帮我。我有一个简单的Suppliers表，里面有我的供应商的电子邮件地址。
我需要循环浏览SQL Server数据库SpecCars中Suppliers表中的电子邮件地址"SuppEmail"，并向它们发送下面的所有电子邮件。
我已经用了几天了，在网上搜索并尝试了许多不同的变体，但无论我做什么，它都只发送一封电子邮件到表中的第一个条目frontdesk@jacksauto.com.au，就这样
如果你能帮上忙，那就太棒了。这是一个ASP.NET C#解决方案。
这是Suppliers表格，里面只有两条记录：
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')
这是代码片段：
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();
试试这个：
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);
}
注意：这个代码可以而且应该更好。考虑重构以从web.config获取连接字符串和邮件设置，而不是在应用程序中对它们进行硬编码。
//-----------------
//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;
}
}
}
}
}
}
}
//-----------------