ASP.NET 邮件群发以及将数据库的数据以table形式发送

行走在理想边缘 2019-03-24

展开全文

代码写在Global.aszx中，系统自动运行

1.Application_Start（）中设定一个定时器以及邮件发送事件

 protected void Application_Start(object sender, EventArgs e)
        {
            System.Timers.Timer timer = new System.Timers.Timer(30000);
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Send);
            timer.Start();
        }

2.对Send()方法进行编辑，设定发送的时间、发送邮箱和接收邮箱

 public void Send(object sender, System.Timers.ElapsedEventArgs e)
        {
            SqlConnection myconn = new SqlConnection("Data Source=100.0.4.51;Initial Catalog=XRX;User ID=xrx;Password=b879d~c2a81fd#9b8e33@ffd5!85e5c6cfb9");          
            string sql = "select mail from MailTest";
            SqlCommand mycmd = new SqlCommand(sql, myconn);
            if (myconn.State == ConnectionState.Closed)
            {
                myconn.Open();
            }
            SqlDataReader reader = mycmd.ExecuteReader();


            SqlConnection myconn2 = new SqlConnection("Data Source=100.0.4.51;Initial Catalog=XRX;User ID=xrx;Password=b879d~c2a81fd#9b8e33@ffd5!85e5c6cfb9");
            myconn2.Open();
            string sql2 = "select Student.StuID,Student.StuName,Course.CourseName,StudentSign.SignState,StudentSign.OutState, StudentSign.LeaveState,StudentSign.ReMark,StudentSign.LateState from StudentSign join Schedule on Schedule.ScheduleID = StudentSign.ScheduleID join Student on Student.ID = StudentSign.StuID join Course on Course.ID = Schedule.CourseID group by Student.StuID,Student.StuName,Course.CourseName,StudentSign.SignState, StudentSign.OutState,StudentSign.LeaveState,StudentSign.ReMark,StudentSign.LateState";
            SqlCommand mycmd2 = new SqlCommand(sql2, myconn2);                            
            SqlDataReader reader2 = mycmd2.ExecuteReader();
            List<string> record = new List<string>();
            while (reader2.Read())
            {
                string msg = "<td>"+reader2["StuID"].ToString() + "</td><td>" + reader2["StuName"].ToString() + "</td><td>" + reader2["CourseName"].ToString() + "</td><td>" + reader2["SignState"].ToString() + "</td><td>" + reader2["OutState"].ToString() + "</td><td>" + reader2["LeaveState"].ToString() + "</td><td>" + reader2["Remark"].ToString() + "</td><td>" + reader2["LateState"].ToString() + "</td>";
                record.Add(msg);
            }
            myconn2.Close();


            if (DateTime.Now.Minute == 10)
            {
                string mailBody = getMailBody(record);
                while (reader.Read())
                {
                    string aMail = reader["mail"].ToString();
                    //string msg = reader2["StuID"].ToString() +"  "+ reader2["StuName"].ToString() +"  "+ reader2["CourseName"].ToString() + "  " + reader2["SignState"].ToString() + "  " + reader2["OutState"].ToString();
                    SendEMail("'" + aMail + "'", "'" + aMail + "'", "考勤信息统计", "" + mailBody + "");
                }
                //endEMail sm = new SendMail();
                //SendEMail("'"+sql+"'", "'" + sql + "'", "Auto Mail", "This is a auto amil!");
            }
            myconn.Close();          
        }

1.筛选出需要接收邮件的邮箱地址

SqlConnection myconn = new SqlConnection("Data Source=100.0.4.51;Initial Catalog=XRX;User ID=xrx;Password=b879d~c2a81fd#9b8e33@ffd5!85e5c6cfb9");

string sql = "select mail from MailTest";

SqlCommand mycmd = new SqlCommand(sql, myconn);

if (myconn.State == ConnectionState.Closed)

{

myconn.Open();

}

SqlDataReader reader = mycmd.ExecuteReader();

2.筛选出所需要的信息，创建链表，循环读取并储存在链表中，将字段数据以table的形式保存

SqlConnection myconn2 = new SqlConnection("Data Source=100.0.4.51;Initial Catalog=XRX;User ID=xrx;Password=b879d~c2a81fd#9b8e33@ffd5!85e5c6cfb9");

myconn2.Open();

string sql2 = "select Student.StuID,Student.StuName,Course.CourseName,StudentSign.SignState,StudentSign.OutState, StudentSign.LeaveState,StudentSign.ReMark,StudentSign.LateState from StudentSign join Schedule on Schedule.ScheduleID = StudentSign.ScheduleID join Student on Student.ID = StudentSign.StuID join Course on Course.ID = Schedule.CourseID group by Student.StuID,Student.StuName,Course.CourseName,StudentSign.SignState, StudentSign.OutState,StudentSign.LeaveState,StudentSign.ReMark,StudentSign.LateState";

SqlCommand mycmd2 = new SqlCommand(sql2, myconn2);

SqlDataReader reader2 = mycmd2.ExecuteReader();

List<string> record = new List<string>();

while (reader2.Read())

{

string msg = "<td>"+reader2["StuID"].ToString() + "</td><td>" + reader2["StuName"].ToString() + "</td><td>" + reader2["CourseName"].ToString() + "</td><td>" + reader2["SignState"].ToString() + "</td><td>" + reader2["OutState"].ToString() + "</td><td>" + reader2["LeaveState"].ToString() + "</td><td>" + reader2["Remark"].ToString() + "</td><td>" + reader2["LateState"].ToString() + "</td>";

record.Add(msg);

}

myconn2.Close();

3.生成读取邮件内容的方法，创建StringBuilder类

private string getMailBody(List<String> list)
{
StringBuilder result = new StringBuilder();
result.Append("<table><tr><td>学号</td><td>姓名</td><td>课程</td><td>签到状态</td><td>签退状态 </td><td>请假</td><td>标记</td><td>迟到</td></tr>");
foreach (string aStr in list){
result.Append("<tr>").Append(aStr).Append("</tr>");
}
result.Append("</table>");
return result.ToString();

}

3.设定邮件发送时间，读取邮件地址和内容

if (DateTime.Now.Minute == 10)
{
string mailBody = getMailBody(record);
while (reader.Read())
{
string aMail = reader["mail"].ToString();
SendEMail("'" + aMail + "'", "'" + aMail + "'", "考勤信息统计", "" + mailBody + "");
}
}

myconn.Close();

DataTime.Now. xx 可以按照自己的需求去选择发送邮件的间隔

3.对SendEmail()方法进行编写

 public void SendEMail(string To1, string CC1, /*string BC1,*/ string Subject1, string Body1)
        {
            MailMessage msg = new MailMessage("xxxx@qq.com", To1);
            msg.CC.Add(CC1);
            //msg.Bcc.Add(BC1);
            msg.Subject = Subject1;
            msg.Body = Body1;
            msg.IsBodyHtml = true;
            msg.Priority = MailPriority.High;//发送邮件的优先等级 
            SmtpClient c = new SmtpClient("smtp.qq.com", 587);
            System.Net.NetworkCredential basicAuthenticationInfo =
               new System.Net.NetworkCredential("xxxxx@qq.com", "xxxxxx");//用户名与SMTP授权码
            c.Credentials = basicAuthenticationInfo;
            c.EnableSsl = true;//启用SSL加密 
            //c.Port = 587;
            c.Send(msg);
        }