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

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

 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();          
        }

3:筛选出需要接收邮件的邮箱地址--获取群发邮箱

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();
 

4；筛选出所需要的信息，创建链表，循环读取并储存在链表中，将字段数据以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();

5：生成读取邮件内容的方法，创建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();

        }

6：设定邮件发送时间，读取邮件地址和内容 这里也可设置  if (DateTime.Now.Day == 19)   //当前时间如果等19号  每个月19号都会自动发送

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

            myconn.Close();    

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

 public void SendEMail(string To1, string CC1, /*string BC1,*/ string Subject1, string Body1)
        {
            MailMessage msg = new MailMessage("[email protected]", 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("[email protected]", "xxxxxx");//用户名与SMTP授权码
            c.Credentials = basicAuthenticationInfo;
            c.EnableSsl = true;//启用SSL加密 
            //c.Port = 587;
            c.Send(msg);
        }

8：程序运行会以邮件的方式发送出去