場景

• 當初客戶要求給自己的營業部和營業所推送郵件（收件人超1000個）
• 郵件正文是去下載報表WORD（檔案大小5M以上）資料檔案轉成HTML
• 三個附件檔案也是要去報表上下載EXCEL（檔案大小3M以上）
• 郵件要在10點之間推送完，但是這裡報表的資料是8點以後ETL才刷好（資料刷好的時間不準確）

解決方案

• 1000個郵箱用迴圈去跑肯定慢；就算1分鐘10封郵件也發不完；而且也沒有這麼高的效能
• 為了提高傳送郵件的效能不得不去使用C#執行緒程式設計；我在這裡使用了執行緒池這個東西，主要是因為執行緒池有佇列、可以設定執行緒池中有多個執行緒一起做任務、執行緒池可以更好得控制CPU資源自己回收

A.SQL

CREATE DATABASE [SendEmailTest]

USE [SendEmailTest]
GO

CREATE TABLE [dbo].[mailboxUser](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[subject] [nvarchar](120) NULL,
	[mailBody] [nvarchar](255) NULL,
	[toEmail] [nvarchar](100) NULL,
	[toEmailBCC] [nvarchar](100) NULL,
	[State] [nchar](10) NULL,
	[Creater] [nvarchar](100) NULL,
	[CreateTime] [datetime] NULL,
	[Updater] [nvarchar](100) NULL,
	[UpdateTime] [datetime] NULL,
 CONSTRAINT [PK_mailboxUser] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


DECLARE @count INT,@i INT
SET @count=120
SET @i=1

WHILE @i<=@count
BEGIN
	INSERT INTO [mailboxUser] VALUES 
	(
	'今天又是有希望得一天;'+CAST(@i AS NVARCHAR),
	'今天要把任務做完;'+CAST(@i AS NVARCHAR),
	'yincongpeng2019@163.com',
	'',
	'1',
	'abel',
	GETDATE(),
	null,
	null
	)
	SET @i=@i+1
END

B.控制檯程式碼

class Program
{
    private static string userEmail = ConfigHelper.GetConfigStr("userEmail"); //發件人郵箱
    private static string userPswd = ConfigHelper.GetConfigStr("userPswd"); //郵箱帳號密碼
    private static string mailServer = ConfigHelper.GetConfigStr("mailServer"); //郵件伺服器
    private static string attachFilesPath = ConfigHelper.GetConfigStr("attachFilesPath"); //檔案地址
    private static string ServerDataDB = ConfigHelper.GetConfigStr("ServerDataDB");

    static void Main(string[] args)
    {
        Console.WriteLine("------------------------------------------------------------------------------1.程式開始");
        Stopwatch watch = new Stopwatch();
        watch.Start(); //開始監視
        ThreadUseAndConstruction();
        //StartSendMail(1,"test","郵件傳送測試", "yincongpeng2019@163.com","");
        watch.Stop(); //  停止監視
        Console.WriteLine("------------------------------------------------------------------------------2.程式結束");
        TimeSpan timespan = watch.Elapsed; //  獲取當前例項測量得出的總時間
        Console.WriteLine("主執行緒執行結束；費時：" + timespan.TotalMilliseconds + "(總毫秒數)");
        Console.ReadLine();
    }

    /// <summary>
    /// 啟動推送郵件執行緒池方法 2020-10-15 11:03 abel
    /// </summary>
    public static void ThreadUseAndConstruction()
    {
        //1.設定程式在執行時最小執行緒數和最大執行緒數
        ThreadPool.SetMinThreads(1, 1); 
        ThreadPool.SetMaxThreads(5, 5); 

        //2.查詢推送的郵箱使用者
        DataTable dt = new DbHelperSQL(ServerDataDB).Query(SelectMailboxUser_SQL).Tables[0];
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            int code = int.Parse(dt.Rows[i]["ID"].ToString());
            string subject = dt.Rows[i]["subject"].ToString();
            string mailBody = dt.Rows[i]["mailBody"].ToString();
            string toEmail = dt.Rows[i]["toEmail"].ToString();
            string toEmailBCC = dt.Rows[i]["toEmailBCC"].ToString();

            //3.把推送郵件的方法放入WaitCallback委託
            WaitCallback callback = index =>
            {
                Stopwatch watch = new Stopwatch();
                watch.Start(); //開始監視
                StartSendMail(code, subject, mailBody, toEmail, toEmailBCC);
                watch.Stop(); //  停止監視
                TimeSpan timespan = watch.Elapsed; //  獲取當前例項測量得出的總時間
                Console.WriteLine("編號：" + code + "的郵件推送結束；費時：" + timespan.TotalMilliseconds + "(總毫秒數)");
            };

            //4.線上程池中加入執行緒佇列
            ThreadPool.QueueUserWorkItem(callback, i);
        }
    }

    /// <summary>
    /// 開始推送郵件 2020-10-15 10:30 abel
    /// </summary>
    /// <param name="code">使用者郵箱ID</param>
    /// <param name="subject">主題</param>
    /// <param name="mailBody">內容</param>
    /// <param name="toEmail">接收人</param>
    /// <param name="toEmailBCC">抄送人</param>
    public static void StartSendMail(int code, string subject, string mailBody, string toEmail, string toEmailBCC) 
    {
        bool SendState = new EmailHelper(toEmail, toEmailBCC, subject, mailBody, new string[0], userEmail, userPswd, mailServer).SendEmail();
        if (SendState == true) 
            new DbHelperSQL(ServerDataDB).ExecuteSql(UpdateSendMailboxState_SQL, new SqlParameter[] { new SqlParameter("ID", code) });
    }

    /// <summary>
    /// 查詢推送的郵件使用者 2020-10-15 10:52 abel
    /// </summary>
    private const string SelectMailboxUser_SQL = "select * from [dbo].[mailboxUser] where [State]=1";

    /// <summary>
    /// 更新郵件推送成功後的狀態 2020-10-15 10:50 abel
    /// </summary>
    private const string UpdateSendMailboxState_SQL = "update [dbo].[mailboxUser] set [State]='0',[Updater]='abel_2',[UpdateTime]=getdate() where [ID]=@ID";
}

C.Achievements

資源

百度網盤連結地址：https://pan.baidu.com/s/1_vJB3tXta42t52f39fAiZQ
提取碼：bhss