使用windows服務(wù)+SQL存儲(chǔ)過程實(shí)現(xiàn)某項(xiàng)信息定時(shí)自動(dòng)發(fā)送郵件

項(xiàng)目需求：

每日凌晨3點(diǎn)前總結(jié)昨天的某項(xiàng)數(shù)據(jù)，自動(dòng)發(fā)送郵件到指定的郵箱。

實(shí)現(xiàn)方法（大致流程）：

1、數(shù)據(jù)總結(jié)使用MsSql的存儲(chǔ)過程來實(shí)現(xiàn)，結(jié)合MsSql的自動(dòng)代理作業(yè)，定時(shí)凌晨導(dǎo)出數(shù)據(jù)到某個(gè)目錄。

2、windows服務(wù)定時(shí)查詢導(dǎo)出的目錄里是否有新文件，有新文件則執(zhí)行發(fā)送郵件流程。

代碼：

1、Windows服務(wù)上報(bào)數(shù)據(jù)的代碼（定時(shí)器要用System.Timers.Timer，發(fā)送郵件的類請(qǐng)自行百度）：

using?System;
using?System.Collections.Generic;
using?System.ComponentModel;
using?System.Data;
using?System.Diagnostics;
using?System.Linq;
using?System.ServiceProcess;
using?System.Text;
using?System.IO;
namespace?SendEmail
{
????public?partial?class?AutoSendEmail?:?ServiceBase
????{
????????public?AutoSendEmail()
????????{
????????????InitializeComponent();
????????????base.AutoLog?=?true;?//?記錄windows事件日志
????????????base.ServiceName?=?"自動(dòng)發(fā)送郵件";
????????}

????????protected?override?void?OnStart(string[]?args)
????????{
????????????timer1.Start();
????????????base.EventLog.WriteEntry("服務(wù)器已啟動(dòng)",?EventLogEntryType.Information);
????????}

????????protected?override?void?OnStop()
????????{
????????????timer1.Stop();
????????????base.EventLog.WriteEntry("服務(wù)器已停止",?EventLogEntryType.Information);
????????}

????????private?void?timer1_Elapsed(object?sender,?System.Timers.ElapsedEventArgs?e)
????????{
????????????Mail?mail?=?new?Mail();
????????????mail.Logs(DateTime.Now.ToString()?+?"定時(shí)器已激活");
????????????string?file?=?"H:\Web\Data\"?+?DateTime.Now.ToString("yyyy-MM-dd")+".txt";
????????????string?fileModel?=?@"導(dǎo)入代金券（新版）
帶*號(hào)的為必填項(xiàng)
開始日期：格式要求：YYYY-MM-DD，需要在方案的開始和結(jié)束日期之間；
結(jié)束日期：格式要求：YYYY-MM-DD，需要在方案的開始和結(jié)束日期之間；
							
/********/";
????????????if?(File.Exists(file))
????????????{
????????????????File.WriteAllText(file,?fileModel?+?Environment.NewLine?+?File.ReadAllText(file,?Encoding.GetEncoding("GB2312")),?Encoding.GetEncoding("GB2312"));????????????????
????????????????
????????????????mail.AddTo("1111111@qq.com");?//?收件人
????????????????mail.AddCC("2222222@qq.com");?//?抄送人
????????????????mail.Subject?=?"信息自動(dòng)上報(bào)";
????????????????mail.Content?=?"信息自動(dòng)上報(bào)，數(shù)據(jù)詳情請(qǐng)查看附件。";
????????????????mail.AddAttchment(file);?//?附件
????????????????mail.Send();
????????????????mail.Logs(DateTime.Now.ToString()?+?"定時(shí)器已執(zhí)行完發(fā)送流程");
????????????????FileInfo?f?=?new?FileInfo(file);
????????????????f.MoveTo("H:\Web\Data\"?+?f.Name?+?"_send"?+?f.Extension);??//發(fā)送成功后把記錄文件改名字，防止多次重復(fù)發(fā)送（方法之一）
????????????}
????????????else
????????????{
????????????????mail.Logs(DateTime.Now.ToString()?+?"沒有檢測(cè)到需要發(fā)送郵件的數(shù)據(jù)");
????????????}
????????}
????}
}

2、MsSql定時(shí)導(dǎo)出的存儲(chǔ)過程

ALTER?PROCEDURE?[dbo].[ExportToExcel]
	?
AS
BEGIN
	?
	SET?NOCOUNT?ON;
	EXEC?sp_configure?'show?advanced?options',1
	reconfigure
	EXEC?sp_configure?'xp_cmdshell',1
	reconfigure
	DECLARE?@D?VARCHAR(2000)
	SET?@D='bcp?"select?''序列號(hào)(*)'',''密碼(*)'',''券面金額(*)'',''開始日期(*)'',''結(jié)束日期(*)''?UNION?ALL?select?[CardNum],[CardPwd],CONVERT(VARCHAR(2),[CardPrice])?AS?''CardPrice'',CONVERT(varchar(100),[CardStartDay],23)?AS?''CardStartDay'',CONVERT(varchar(100),[CardEndDay],23)?AS?''CardEndDay''?from?SMS..CardInfo?where?CardIsActive=1?AND?CardActiveDate?BETWEEN?CONVERT(varchar(100),GETDATE()-1,23)+''?00:00:00''?AND?CONVERT(varchar(100),GETDATE()-1,23)+''?23:59:59''"?queryout?H://Web//Data//'+CONVERT(varchar(100),?GETDATE(),?23)+'.txt??-t?","??-c?-U登錄名?-P登錄密碼'

	exec?master..xp_cmdshell?@D;
?????
	EXEC?sp_configure?'show?advanced?options',1
	reconfigure
	EXEC?sp_configure?'xp_cmdshell',0--完成后記得關(guān)閉'xp_cmdshell'
	reconfigure
END

3、在MsSql的SQL?Server代理中新建一個(gè)定時(shí)作業(yè)，定時(shí)執(zhí)行這個(gè)存儲(chǔ)過程。

??