C# windows 服务 数据库 读取数据 自动邮件

上次用asp.net 利用 Global.asax 定时发送邮件

自动邮件,不稳定,用windows服务比较稳定,还有一个核心点没有解决,就是写excel到指定文件夹还不能实现,不知道权限哪里出了问题,待研究.

C,C++都可以写windows服务,这里只介绍一下用C#创建一个windows服务

我用的工具是:Microsoft Visual Studio 2010

数据库是:sql server 2008

打开vs,新建->项目->创建Windows服务项目(我的项目是Service1)->在设计视图-工具箱-组件-拖拽一个Timer控件->创建完成后切换到代码视图(F5)

Service1.cs代码如下(添加自己需要引用的dll)

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 jmail;
using Excel;
using System.IO;
using System.Reflection;
using System.Timers;
using System.Threading;
using System.Data.SqlClient;

namespace WindowsService1
{
    public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            // TODO: 在此处添加代码以启动服务。
            this.timEmail.Enabled = true;//是否执行System.Timers.Timer.Elapsed事件;
           this.timEmail.AutoReset = true;//设置是执行一次（false）还是一直执行(true);
            this.timEmail.Elapsed += new ElapsedEventHandler(timEmail_Elapsed);
            this.timEmail.Start();
            //System.Timers.Timer myTimer = new System.Timers.Timer(3600000);//1小时
            //myTimer.Elapsed += new ElapsedEventHandler(timEmail_Elapsed);
            //myTimer.AutoReset = true;//设置是执行一次（false）还是一直执行(true);
            //myTimer.Enabled = true;//是否执行System.Timers.Timer.Elapsed事件;
            //this.tSendEmail();
        }

        protected override void OnStop()
        {
            // TODO: 在此处添加代码以执行停止服务所需的关闭操作。
            this.timEmail.Enabled = false;
            this.timEmail.Stop();
        }
        private void tSendEmail()
        {
            Thread t = new Thread(new ThreadStart(sendEmail));
            t.Start();
        }
        DataSet Bind()
        {
            string sr = "";
            sr += "SELECT *";
            sr += " FROM [table]";
            DataSet ds1 = ExecuteDataSet(sr);
            return ds1;
        }
        private void sendEmail()
        {
            //定时器      
            string name = System.DateTime.Now.ToString("yyyyMMddhhmmss");
            name += "windows";
            string pFilePath = null;
            string receiver = "XXXXXX@163.com";//收件箱（分号隔开）
            string AddRecipientCC = "";// "wangqiang@harmony.com.cn";//抄送箱（分号隔开）
            string table = "windows服务启动定时邮件";
            table += '\n';
            table += "看到此信息代表发送成功！";
            DataSet result = Bind();
            System.Data.DataTable dt = result.Tables[0];
            if (dt.Rows.Count > 0)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    table += '\n';
                    table += dt.Rows[j][0].ToString();
                }
            }
            //if (result != null)
            //{
            //    WriteExcel(result, name);
            //    pFilePath = @"C:\" + name + ".xlsx";//附件，分号隔开
            //}
            sendEmail("hsf", "XXXXXX@163.com", "123333444", receiver, AddRecipientCC, "windows自动邮件", table, "smtp.163.com", pFilePath);

        }
        private void timEmail_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            //定时器
            this.tSendEmail();

        }
        public DataSet ExecuteDataSet(string sql)
        {
            string Cconstring = @"user id=sa;password=123123;Data Source=.;Database=HAHA";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
            da.SelectCommand = cmd;
            using (SqlConnection con = new SqlConnection(Cconstring))
            {
                cmd.Connection = con;
                cmd.CommandText = sql;
                try
                {
                    da.Fill(ds);
                }
                catch (Exception ex)
                {
                    string msg = ex.Message;
                }
                finally
                {

                }
            }
            return ds;
        }
        /// <summary>
        /// DataSet数据写入Excel保存自定义名称到默认文件夹内（C:\SaveFile）
        /// </summary>
        /// <param name="ds">System.Data.DataSet数据</param>
        /// <param name="filename">文件名称（不含后缀）如"hahahah"</param>
        public static void WriteExcel(System.Data.DataSet ds, string filename)
        {
            System.Data.DataTable dt = ds.Tables[0];
            //dt.Columns[0]   
            //Excel.Application excel = new Excel.Application();//引用Excel对象
            //excel.Application.Workbooks.Add(true );//引用Excel工作簿
            //excel.Cells[ 1 , 1 ] = "First Row First Column";
            //excel.Cells[ 1 , 2 ] = "First Row Second Column";
            //excel.Cells[ 2 , 1 ] = "Second Row First Column";
            //excel.Cells[2, 2] = "Second Row Second Column";
            //excel.Visible = true; //使Excel可视
            //创建Application对象 
            Excel.Application xApp = new Excel.ApplicationClass();

            xApp.Visible = false;
            得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件 
            //Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\SaveFile\az.xls",
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value
            //, Missing.Value, Missing.Value, Missing.Value, Missing.Value
            //, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value);//新建文件的代码 
            //指定要操作的Sheet，两种方式： 

            Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];
            //Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet; 

            读取数据，通过Range对象 
            //Excel.Range rng1 = xSheet.get_Range("A1", Type.Missing);
            //rng1.Value2 = "aaa";

            读取，通过Range对象，但使用不同的接口得到Range 
            Excel.Range rng2 = (Excel.Range)xSheet.Cells[1,2];
            rng2.Value2 = "bbb";
            //((Excel.Range)xSheet.Cells[1, 2]).Value="ccc";
            写入数据 
            //Excel.Range rng3 = xSheet.get_Range("B2", Missing.Value);
            //rng3.Value2 = "Hello";
            //rng3.Interior.ColorIndex = 6; //设置Range的背景色 
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ((Excel.Range)xSheet.Cells[1, i + 1]).Value = dt.Columns[i].ColumnName;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    ((Excel.Range)xSheet.Cells[j + 2, i + 1]).Value = dt.Rows[j][i];
                }
            }

            string pFilePath = @"C:\" + filename ?? System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
            保存方式一：保存WorkBook 
            if (File.Exists(pFilePath))
            {
                File.Delete(pFilePath);
            }
            xBook.SaveAs(pFilePath,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);

            保存方式二：保存WorkSheet 
            //xSheet.SaveAs(@"../SaveFile/CData2.xls",
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //保存方式三 
            //xBook.Save();
            xSheet = null;
            xBook = null;
            xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 
            xApp = null;
        }
        
        /// <summary>
        /// 发送邮件方法
        /// </summary>
        /// <param name="senderuser">发件人显示发送时的名称</param>
        /// <param name="euser">发件人邮箱地址</param>
        /// <param name="epwd">发件人邮箱密码</param>
        /// <param name="receiver">收件人邮箱地址</param>
        /// <param name="AddRecipientCC">cc抄送邮箱地址</param>
        /// <param name="subject">邮件标题</param>
        /// <param name="body">邮件内容</param>
        /// <param name="eserver">邮件服务器smtp.harmony.com.cn</param>
        /// <param name="attachment">邮件附件;隔开,附件为空可传null</param>
        /// <returns>bool</returns>
        public bool sendEmail(string senderuser/*发件人显示发送时的名称*/, string euser/*发件人邮箱地址*/, string epwd/*发件人邮箱密码*/
            , string receiver/*收件人邮箱地址*/, string AddRecipientCC/*cc抄送邮箱地址*/, string subject/*邮件标题*/, string body/*邮件内容*/
            , string eserver/*邮件服务器smtp.harmony.com.cn*/, string attachment/*邮件附件;隔开,附件为空可传null*/)//9个参数
        {
            jmail.MessageClass jmMessage = new jmail.MessageClass();
            jmMessage.Charset = "GB2312";
            jmMessage.ISOEncodeHeaders = false;
            jmMessage.From = euser;
            jmMessage.FromName = senderuser;
            jmMessage.Subject = subject;
            jmMessage.MailServerUserName = euser;
            jmMessage.MailServerPassWord = epwd;
            if (!string.IsNullOrEmpty(receiver))
            {
                string[] recip = receiver.Replace('；', ';').Split(';');
                foreach (string item in recip)
                {
                    jmMessage.AddRecipient(item.Trim(), "", "");
                }
            }
            if (!string.IsNullOrEmpty(AddRecipientCC))
            {
                string[] cc = AddRecipientCC.Replace('；', ';').Split(';');
                foreach (string item in cc)
                {
                    jmMessage.AddRecipientCC(item.Trim(), "", "");
                }
            }
            jmMessage.Body = body;
            #region 附件
            if (!string.IsNullOrEmpty(attachment))
            {
                string[] attach = attachment.Split(';');
                foreach (string item in attach)
                {
                    string ContentType = "";
                    switch (item.Trim().Substring(item.LastIndexOf(".") + 1).ToLower())
                    {
                        case "pdf":
                            ContentType = "application/pdf";
                            break;
                        case "jpg":
                        case "jpeg":
                            ContentType = "image/jpeg";
                            break;
                        case "gif":
                            ContentType = "image/gif";
                            break;
                        case "png":
                            ContentType = "image/png";
                            break;
                        case "bmp":
                            ContentType = "image/bmp";
                            break;
                        case "zip":
                            ContentType = "application/zip";
                            break;
                        case "txt":
                            ContentType = "text/plain";
                            break;
                        case "htm":
                        case "html":
                            ContentType = "text/html";
                            break;
                        case "doc":
                            ContentType = "application/msword";
                            break;
                        case "xls":
                            ContentType = "application/vnd.ms-excel";
                            break;
                        case "ppt":
                            ContentType = "application/vnd.ms-powerpoint";
                            break;
                        default:
                            ContentType = "application/octet-stream";
                            break;
                    }

                    jmMessage.AddAttachment(item, true, ContentType);
                }

            }
            #endregion
            bool result = jmMessage.Send(eserver, false);
            jmMessage = null;
            return result;
        } 
    }
}

修改Service1.Designer.cs如下

namespace WindowsService1
{
    partial class Service1
    {
        /// <summary> 
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region 组件设计器生成的代码

        /// <summary> 
        /// 设计器支持所需的方法 - 不要
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {

            this.components = new System.ComponentModel.Container();

            //this.timEmail = new System.Windows.Forms.Timer(this.components);原            

            this.timEmail = new System.Timers.Timer();//改            

            //this.timEmail.Interval = 60000*60*2;//1分钟==2小时
            this.timEmail.Interval = 10000;//10秒

            this.ServiceName = "Service1";

        }

        #endregion

        private System.Timers.Timer timEmail;//改
    }
}

然后切换到设计视图，右键“添加安装程序”

serviceProcessInstaller1,右键属性更改属性视图中的Account属性为LocalService(本地服务)

serviceInstaller1,右键属性更改属性视图中的StartType属性为Automatic(自动)

运行程序(会报错,不过没关系只要已经生成了Service1.exe就没问题)

2.0版本找到文件C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe

3.0版本找到文件C:\WINDOWS\Microsoft.NET\Framework\v3.0\InstallUtil.exe

我用的framework 4.0

找到C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe

另外找到项目Service1\Service1\bin\Debug\Service1.exe

把这两个文件复制到单独的文件夹比如D:\autoMail

然后去Windows服务器注册就好了

开始--运行--输入注册命令--确定

注册命令:D:/autoMail/InstallUtil.exe D:/autoMail/Service1.exe

如果想注销用:D:/autoMail/InstallUtil.exe D:/autoMail/Service1.exe -U

注册成功,在计算机右键管理-服务里就会看到已经注册好的Service1服务,右键启动即可.