一步一步用C#写一个检查SQL Server假死警报服务

本文主要是写Windows 服务的一个实际应用。包括一个后台定时执行检查的服务、写文本日志功能、加密解密功能、发送邮件功能、一个XML的配置文件和读取XML配置内容功能、服务的安装和删除功能。

我先说明,我不在这里研究SQL Server为什么有假死这样的现象,实际在工作上就是碰到了多次这样的情况,即服务运行,但不提供服务。

VS中不可以调试服务,所以有很多写LOG的调用,是为了方便调试跟踪。

为防止后续发生SQL Server服务假死(即服务运行,但不提供服务)的情况可以更及时的处理,一发动千均,一出问题,整个生产现场就得停工,所以才有本文。

 

资料和要求:

主要的MES数据库有18个,内容包含以下Instance和Database:

UsedFor

Instance

Database

CPT

[HZXL1\HZXL1]

XLProd_Cree_HZ2

CPT

[HZXL1\HZXL1]

XLProd_Cree_HZ2_Archive

CPT

[HZXL1\HZXL1]

XLSite_Cree_HZ2

Camstar OLTP

[HZCS1\HZCS1]

FASSL

Camstar OLTP

[HZCS1\HZCS1]

InSiteDB

Camstar OLTP

[HZCS1\HZCS1]

InSiteDB_CSIPurgeDB

Camstar ODS

HZCSODS01

CNSSLRTS

Camstar ODS

HZCSODS01

InSiteODS

PNT

HZBACK01

CreeMES_PNT

PNT

HZBACK01

CreeMES_ReplStage

PNT

HZBACK01

CrystalReports2008

PNT

HZBACK01

FAOpto

PNT

HZBACK01

Intranet_Apps

PNT

HZBACK01

PNT_Parameters

PNT

HZBACK01

PNTLampInfo

PNT

HZBACK01

ProberInfo

PNT

HZBACK01

WaferWorks_PT

PNT

HZBACK01

WaferWorks_Sphere

当无法连接数据库时,发出警报。

可以连入时,每个判断语句只需抓取sys.sysindexes的第一笔记录,sample如下:

select top 1 'OK' from sys.sysindexes with(nolock)

判断所有数据库均能抓出数据,如果有不能抓出的数据,发出警报并显示出详细信息.

警报地址---(#Asia_IT_Operations; #HZ_IS_Helpdesk)

这样第一时间Helpdesk会收到信息,确认问题无法处理时,可以联系二线人员处理。

 

实际的效果:

生产的服务程序文件列表,包括主程序,安装和删除批处理

clip_image001

 

安装后在服务中可以看到服务的情况

clip_image002

 

单个检查项对应产生的日志文件

clip_image001[6]

主程序日志文件

clip_image001[8]

 

报警邮件

clip_image001[10]

 

功能实现:

由于很多童孩都不多用服务,还是一步一步的写出来,让想试试又未试过的也可以照做。

我要检查的数据库众多,18个,就18个吗?

所以这些都要用配置文件来配置,可加可减才行.

日志是少不了的,那是否一定就要呢?什么时候都是有比无要好,但是可有可无才是灵活的方式,那要还是不要?

功能是要有,但是用与不用,改改参数就可以吧,那参数也放配置文件中好了。

要发邮件,SMTP是固定的吗?有其它的没有?可能想更换的时候也是有的?

也就是说SMTP的信息(服务器、用户名、密码等)都放配置文件。

还有收邮件的人员、日志文件的名称和路径、一些默认值、具体的数据库信息都应该在配置文件中。

由于是给一线人员检查用的,所以敏感信息需要加密后再放配置文件中,最后确定配置文件使用一个自定义的XML最好。

由上边的内容,应该需要写文本日志功能、一个XML的配置文件、加密解密功能、发送邮件功能、一个后台定时执行检查的服务、读取XML配置内容、服务的安装和删除功能.

OK,就一步一步来吧!

 

第一步:新建一个项目

如下图,创建一个名为MonitorSqlServerWindowsService的Windows Service项目。

clip_image001

创建后的默认如下:

image

在属性中更改名称和服务名如下

image

 

第二步:加密解密功能实现

image

新建立一个类,名为MonitorSqlServerWindowsService

image

MonitorSqlServerWindowsService类的代码:

   1:  using System;
   2:  using System.Security.Cryptography;
   3:  using System.IO;
   4:   
   5:  namespace Core.DarrenEncodeOrDecode
   6:  {
   7:      /// <summary>
   8:      /// 描述:EncodeOrDecode是加密解密類
   9:      /// 程序員:谢堂文(Darren Xie)
  10:      /// 創建日期:2012-01-18
  11:      /// 版本:1.0
  12:      /// </summary>
  13:      public class EncodeOrDecode
  14:      {
  15:          const string KEY_64 = "9Hgu#6w!";
  16:          const string IV_64 = "InitVect";
  17:          public EncodeOrDecode()
  18:          {
  19:              //
  20:              // TODO: Add constructor logic here
  21:              //
  22:          }
  23:          /// <summary>
  24:          /// 默認的加密方法,加密傳入的字符串,返回加密後的字符串
  25:          /// </summary>
  26:          /// <param name="data">需要加密的字符串</param>
  27:          /// <returns>加密後的字符串</returns>
  28:          public static string Encode(string data)
  29:          {
  30:              byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
  31:              byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);
  32:   
  33:              DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
  34:              int i = cryptoProvider.KeySize;
  35:              MemoryStream ms = new MemoryStream();
  36:              CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);
  37:   
  38:              StreamWriter sw = new StreamWriter(cst);
  39:              sw.Write(data);
  40:              sw.Flush();
  41:              cst.FlushFinalBlock();
  42:              sw.Flush();
  43:              return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
  44:          }
  45:          /// <summary>
  46:          /// 自定義密鑰的加密方法,加密傳入的字符串,返回加密後的字符串
  47:          /// </summary>
  48:          /// <param name="data">需要加密的字符串</param>
  49:          /// <returns>加密後的字符串</returns>
  50:          public static string Encode(string data,string key,string iv)
  51:          {
  52:              byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(key);
  53:              byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(iv);
  54:   
  55:              DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
  56:              int i = cryptoProvider.KeySize;
  57:              MemoryStream ms = new MemoryStream();
  58:              CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);
  59:   
  60:              StreamWriter sw = new StreamWriter(cst);
  61:              sw.Write(data);
  62:              sw.Flush();
  63:              cst.FlushFinalBlock();
  64:              sw.Flush();
  65:              return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
  66:          }
  67:   
  68:          /// <summary>
  69:          /// 默認的解密方法,傳入加密的字符串,返回解密後的字符串
  70:          /// </summary>
  71:          /// <param name="data">需要解密的字符串</param>
  72:          /// <returns>解密後的字符串</returns>
  73:          public static string Decode(string data)
  74:          {
  75:              byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
  76:              byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);
  77:   
  78:              byte[] byEnc;
  79:              try
  80:              {
  81:                  byEnc = Convert.FromBase64String(data);
  82:              }
  83:              catch
  84:              {
  85:                  return null;
  86:              }
  87:   
  88:              DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
  89:              MemoryStream ms = new MemoryStream(byEnc);
  90:              CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
  91:              StreamReader sr = new StreamReader(cst);
  92:              return sr.ReadToEnd();
  93:          }
  94:          /// <summary>
  95:          /// 自定義密鑰的解密方法,傳入加密的字符串,返回解密後的字符串
  96:          /// </summary>
  97:          /// <param name="data">需要解密的字符串</param>
  98:          /// <returns>解密後的字符串</returns>
  99:          public static string Decode(string data,string key,string iv)
 100:          {
 101:              byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(key);
 102:              byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(iv);
 103:   
 104:              byte[] byEnc;
 105:              try
 106:              {
 107:                  byEnc = Convert.FromBase64String(data);
 108:              }
 109:              catch
 110:              {
 111:                  return null;
 112:              }
 113:   
 114:              DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
 115:              MemoryStream ms = new MemoryStream(byEnc);
 116:              CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
 117:              StreamReader sr = new StreamReader(cst);
 118:              return sr.ReadToEnd();
 119:          }
 120:      }
 121:  }

 

第三步:建立XML配置文件

增加一个XML文件到项目中,名为ServerConfig.xml,代码如下:

   1:  <?xml version="1.0" encoding="utf-8" ?>
   2:  <parameters>
   3:   
   4:    <!--標準基礎信息-->
   5:    <istest val="0">測試標識,1是測試,非1是正式</istest>
   6:    <checkTime val="120">檢查週期,單位是秒</checkTime>
   7:    <smtp name="你的SMTP服务器" from="你的邮箱地址" user="你的用户名" pwd="M/R3ib7M0OVPpDWmAZjGGw==">郵件服務器信息</smtp>
   8:   
   9:    <defsqlUser dbuser="D+Zox91emVaNWnUtiLez9g==" userpwd="bCIkRm+dTA1kJO0oRlOLxg==">默認數據庫訪問賬號</defsqlUser>
  10:    <to email="#Asia_IT_Operations@XXXX.com; #HZ_IS_Helpdesk@XXXX.com;DarrenXie@XXXX.com"></to>
  11:    <isLog val="1">是否產生日誌文件,1產生,非1就不產生</isLog>
  12:    <LogFilePath val="">日誌文件存放路徑</LogFilePath>
  13:    <LogFileName val="">日誌文件名</LogFileName>
  14:    <sql val="select top 1 'OK' from sys.sysindexes with(nolock)"></sql>
  15:    <dbsrv srvname="HZXL1\HZXL1" dbname="XLProd_Cree_HZ2" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  16:    <dbsrv srvname="HZXL1\HZXL1" dbname="XLProd_Cree_HZ2_Archive" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  17:    <dbsrv srvname="HZXL1\HZXL1" dbname="XLSite_Cree_HZ2" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  18:    <dbsrv srvname="HZCS1\HZCS1" dbname="FASSL" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  19:    <dbsrv srvname="HZCS1\HZCS1" dbname="InSiteDB" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  20:    <dbsrv srvname="HZCS1\HZCS1" dbname="InSiteDB_CSIPurgeDB" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  21:    <dbsrv srvname="HZCSODS01" dbname="CNSSLRTS" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  22:    <dbsrv srvname="HZCSODS01" dbname="InSiteODS" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  23:    <dbsrv srvname="HZBACK01" dbname="CreeMES_PNT" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  24:    <dbsrv srvname="HZBACK01" dbname="CreeMES_ReplStage" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  25:    <dbsrv srvname="HZBACK01" dbname="CrystalReports2008" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  26:    <dbsrv srvname="HZBACK01" dbname="FAOpto" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  27:    <dbsrv srvname="HZBACK01" dbname="Intranet_Apps" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  28:    <dbsrv srvname="HZBACK01" dbname="PNT_Parameters" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  29:    <dbsrv srvname="HZBACK01" dbname="PNTLampInfo" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  30:    <dbsrv srvname="HZBACK01" dbname="ProberInfo" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  31:    <dbsrv srvname="HZBACK01" dbname="WaferWorks_PT" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  32:    <dbsrv srvname="HZBACK01" dbname="WaferWorks_Sphere" dbuser="" userpwd="" sql="" descr="" LogFileName="">數據庫信息</dbsrv>
  33:  </parameters>

 

第四步:写文本日志功能

增加一个类,名为FileLog.cs

代码如下:

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Text;
   5:  using System.IO;//StreamWriter
   6:  using System.Collections;//arraylist
   7:   
   8:  namespace Core.DarrenCoreLib.Log
   9:  {
  10:      /// <summary>
  11:      /// 描述:寫TXT格式的LOG
  12:      /// 程序員:谢堂文(Darren Xie)
  13:      /// 創建日期:2012-02-09
  14:      /// 版本:1.0
  15:      /// </summary>
  16:      public static class FileLog
  17:      {
  18:          public static void writeTotxt(string fullFilepath, string ppContent)
  19:          {
  20:              StreamWriter Sw1 = null;
  21:              try
  22:              {
  23:                  Sw1 = new StreamWriter(fullFilepath, true, System.Text.Encoding.UTF8);
  24:                  {
  25:                      Sw1.WriteLine(ppContent);
  26:                  }
  27:              }
  28:              catch (Exception ef)
  29:              {
  30:                  throw new Exception(ef.Message);
  31:              }
  32:              finally
  33:              {
  34:                  try
  35:                  {
  36:                      Sw1.Close();
  37:                  }
  38:                  catch
  39:                  {
  40:                  }
  41:              }
  42:   
  43:          }
  44:      }
  45:  }
  46:   

第五步:增加一个类用于实际的数据库检查对象,并且保存配置文件中对应的属性以及写日志、执行检查、发送邮件等,具体看代码注释会更明白,就名为Srv.cs

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Text;
   5:  using System.Net.Mail;
   6:  using System.Data.SqlClient;
   7:  using System.Data;
   8:  using System.Net;
   9:   
  10:  namespace MonitorSqlServerWindowsService
  11:  {
  12:      /// <summary>
  13:      /// 描述:
  14:      /// 程序員:谢堂文(Darren Xie)
  15:      /// 創建日期:
  16:      /// 版本:1.0
  17:      /// </summary>
  18:      public class Srv
  19:      {
  20:          
  21:          string smtpName;
  22:          string smtpPwd;
  23:          string smtpUser;
  24:          string from;
  25:          string[] to;
  26:          string srvname;
  27:          string dbname;
  28:          string dbuser;
  29:          string userpwd;
  30:          string sql;
  31:          string descr;
  32:          int isLog;
  33:          string logFilePath;
  34:          string logFileName;
  35:   
  36:          /// <summary>
  37:          /// 生產日誌文件名稱
  38:          /// </summary>
  39:          public string LogFileName
  40:          {
  41:              get { return logFileName; }
  42:              set { logFileName = value; }
  43:          }
  44:          /// <summary>
  45:          /// 日誌文件存放的路徑
  46:          /// </summary>
  47:          public string LogFilePath
  48:          {
  49:              get { return logFilePath; }
  50:              set { logFilePath = value; }
  51:          }
  52:          /// <summary>
  53:          /// 是否產生日誌文件
  54:          /// </summary>
  55:          public int IsLog
  56:          {
  57:              get { return isLog; }
  58:              set { isLog = value; }
  59:          }
  60:   
  61:          /// <summary>
  62:          /// SMTP服務器
  63:          /// </summary>
  64:          public string SmtpName
  65:          {
  66:              get { return smtpName; }
  67:              set { smtpName = value; }
  68:          }
  69:         
  70:          /// <summary>
  71:          /// 用於發郵件的SMTP用戶密碼
  72:          /// </summary>
  73:          public string SmtpPwd
  74:          {
  75:              get { return smtpPwd; }
  76:              set { smtpPwd = value; }
  77:          }
  78:          
  79:          /// <summary>
  80:          /// 用於發郵件的SMTP用戶名
  81:          /// </summary>
  82:          public string SmtpUser
  83:          {
  84:              get { return smtpUser; }
  85:              set { smtpUser = value; }
  86:          }
  87:          
  88:          /// <summary>
  89:          /// 用於發郵件的SMTP用戶名郵件地址
  90:          /// </summary>
  91:          public string From
  92:          {
  93:              get { return from; }
  94:              set { from = value; }
  95:          }
  96:          
  97:         /// <summary>
  98:         /// 收件人地址
  99:         /// </summary>
 100:          public string[] To
 101:          {
 102:              get { return to; }
 103:              set { to = value; }
 104:          }
 105:          /// <summary>
 106:          /// 連接字符串
 107:          /// </summary>
 108:          public string ConnectionString
 109:          {
 110:              get
 111:              {
 112:                  return @"server="+Srvname+";database="+Dbname+";uid="+Core.DarrenEncodeOrDecode.EncodeOrDecode.Decode( Dbuser)+";pwd="+Core.DarrenEncodeOrDecode.EncodeOrDecode.Decode(Userpwd)+"";
 113:              }
 114:          }
 115:          
 116:   
 117:          public string Srvname
 118:          {
 119:              get { return srvname; }
 120:              set { srvname = value; }
 121:          }
 122:          
 123:          /// <summary>
 124:          /// 數據庫所在的服務器名
 125:          /// </summary>
 126:          public string Dbname
 127:          {
 128:              get { return dbname; }
 129:              set { dbname = value; }
 130:          }
 131:          
 132:          /// <summary>
 133:          /// 數據庫用戶名
 134:          /// </summary>
 135:          public string Dbuser
 136:          {
 137:              get { return dbuser; }
 138:              set { dbuser = value; }
 139:          }
 140:          
 141:          /// <summary>
 142:          /// 數據庫用戶的密碼
 143:          /// </summary>
 144:          public string Userpwd
 145:          {
 146:              get { return userpwd; }
 147:              set { userpwd = value; }
 148:          }
 149:          
 150:          /// <summary>
 151:          /// 檢查用的SQL語句
 152:          /// </summary>
 153:          public string Sql
 154:          {
 155:              get { return sql; }
 156:              set { sql = value; }
 157:          }
 158:          
 159:          /// <summary>
 160:          /// 描述信息
 161:          /// </summary>
 162:          public string Descr
 163:          {
 164:              get { return descr; }
 165:              set { descr = value; }
 166:          }
 167:          /// <summary>
 168:          /// 執行連接和查詢測試
 169:          /// </summary>
 170:          public void Chk()
 171:          {
 172:              using (SqlConnection conn = new SqlConnection(ConnectionString))
 173:              {
 174:                  try
 175:                  {
 176:                      writestr("开始连接" + Srvname + "服務器上的數據庫" + Dbname);
 177:                      conn.Open();
 178:                      writestr("连接" + Srvname + "服務器上的數據庫" + Dbname + "成功");
 179:   
 180:                      writestr("開始測試查詢" + Srvname + "服務器上的數據庫" + Dbname + "上的數據");
 181:   
 182:                      if (Core.DarrenCoreLib.DB.SqlHelper.ExecuteScalar(conn, CommandType.Text, Sql).ToString().ToUpper() == "OK")
 183:                      {
 184:                          writestr("查詢" + Srvname + "服務器上的數據庫" + Dbname + "數據測試成功");
 185:                      }
 186:                      else
 187:                      {
 188:                          throw new Exception("查詢" + Srvname + "服務器上的數據庫" + Dbname + "數據測試失敗");
 189:                      }
 190:                  }
 191:                  catch (Exception ee)
 192:                  {
 193:                      
 194:                          writestr(Srvname + "服務器上的數據庫" + Dbname + "檢查失敗" + ee.Message);
 195:                          writestr("準備發送郵件");
 196:                          writestr(this.To[0]);
 197:                          string errMsg = ee.Message;
 198:                      try
 199:                      {
 200:                          if (this.From.Trim() == string.Empty)
 201:                          {
 202:                              throw new Exception("沒有指定發件者地址。");
 203:                          }
 204:                          if (this.Srvname.Trim() == string.Empty)
 205:                          {
 206:                              throw new Exception("沒有指定服務器。");
 207:                          }
 208:                          if (this.Dbname.Trim() == string.Empty)
 209:                          {
 210:                              throw new Exception("沒有指定數據庫。");
 211:                          }
 212:                          if (this.To.Length == 0)
 213:                          {
 214:                              throw new Exception("沒有指定收件者地址。");
 215:                          }
 216:   
 217:                          if (this.SendMail(this.From, this.To, "檢查服務器 " + this.Srvname + "上的數據庫 " + this.Dbname + " 失敗", "<H1>SQL Server假死警报服務! </H1><br/>   <b>信息內容:<b/>" + errMsg + "<br/>服務器:" + this.Srvname + "<br/>數據庫:" + this.Dbname +"<br/>來自配置文件的數據庫描述信息:"+this.Descr.ToString()+ "<br/>請注意檢查確認,這是郵系統自動檢查發出的信息。<br/>來自服務器:" + Dns.GetHostName()))
 218:                          {
 219:                              writestr("發送郵件成功");
 220:                          }
 221:                      }
 222:                      catch (Exception em)
 223:                      {
 224:                          writestr("發送郵件失敗,"+em.Message);
 225:                      }
 226:                  }
 227:                  finally
 228:                  {
 229:                      try
 230:                      {
 231:                          conn.Close();
 232:                      }
 233:                      catch { }
 234:                  }
 235:              }
 236:          }
 237:          /// <summary>
 238:          /// 寫日誌文件
 239:          /// </summary>
 240:          /// <param name="readme"></param>
 241:          private void writestr(string readme)
 242:          {
 243:              if (IsLog == 1)
 244:              {
 245:                  Core.DarrenCoreLib.Log.FileLog.writeTotxt((LogFilePath + LogFileName), "\r\n事件:" + readme + "\r\n操作时间:" + System.DateTime.Now.ToString("yyy-MM-dd HH:mm:ss"));
 246:              }
 247:          }
 248:          /// <summary>
 249:          /// 發送郵件 
 250:          /// </summary>
 251:          /// <param name="messagefrom"></param>
 252:          /// <param name="MessageTo"></param>
 253:          /// <param name="MessageSubject"></param>
 254:          /// <param name="MessageBody"></param>
 255:          /// <returns></returns>
 256:          public bool SendMail(string messagefrom, string[] MessageTo, string MessageSubject, string MessageBody)
 257:          {
 258:              MailMessage message = new MailMessage();
 259:              message.SubjectEncoding = System.Text.Encoding.Unicode;
 260:              SmtpClient sc = new SmtpClient();
 261:              try
 262:              {
 263:                  MailAddress Messagefrom = new MailAddress(messagefrom);
 264:                  message.From = Messagefrom;
 265:                 
 266:                  foreach (string to in MessageTo)
 267:                  {
 268:                      writestr(to);
 269:                      message.To.Add(to);
 270:                  }//收件人邮箱地址可以是多个以实现群发
 271:                  if (MessageSubject.Trim() == string.Empty)
 272:                  {
 273:                      throw new Exception("沒有指定郵件標題。");
 274:                  }
 275:                  message.Subject = MessageSubject; 
 276:                  if (messagefrom.Trim() == string.Empty)
 277:                  {
 278:                      throw new Exception("沒有指定郵件內容。");
 279:                  }
 280:                  message.Body = MessageBody;
 281:                  message.IsBodyHtml = true;              //是否为html格式
 282:                  message.Priority = MailPriority.High;  //发送邮件的优先等级  
 283:                  if (SmtpName.Trim() == string.Empty)
 284:                  {
 285:                      throw new Exception("沒有指定SMTP地址。");
 286:                  }
 287:                  if (SmtpUser.Trim() == string.Empty)
 288:                  {
 289:                      throw new Exception("沒有指定SMTP用戶名。");
 290:                  }
 291:                  if (SmtpPwd.Trim() == string.Empty)
 292:                  {
 293:                      throw new Exception("沒有指定SMTP密碼。");
 294:                  }
 295:                  sc.Host = SmtpName;              //指定发送邮件的服务器地址或IP
 296:                  //sc.Port = 212;                          //指定发送邮件端口
 297:                  //   sc.UseDefaultCredentials = true;
 298:                  // sc.EnableSsl = true;
 299:                  sc.Credentials = new System.Net.NetworkCredential(SmtpUser, Core.DarrenEncodeOrDecode.EncodeOrDecode.Decode(SmtpPwd)); //指定登录服务器的用户名和密码
 300:              }
 301:              catch (Exception ee)
 302:              {
 303:                  throw new Exception(ee.Message);       
 304:              }
 305:              
 306:              try
 307:              {
 308:                  sc.Send(message);      //发送邮件
 309:              }
 310:              catch (Exception e)
 311:              {
 312:                  throw new Exception(e.Message);                
 313:              }
 314:              return true;
 315:          }
 316:      }
 317:  }

第六步:在服务的设计视图中,增加一个timer控件名为timerChkSql,有一点要注意,timer有不同的,,要加正确的命名空间下的timer控件,请注意清楚如下的区另,红色标记的才是正确的,否则到时会不工作;

在控件的选择项中可以看到它们

image

之后你会看到

image

 

第七步:回到服务的功能上,需要组装上边几个功能在一起,对了,先要读取配置文件,读取时建立每个数据库对象

先建立一个List来放对象,在MyService.cs中增加以下属性

private System.Collections.Generic.List<Srv> srvList = new List<Srv>();
        /// <summary>
        /// 用城保存需要檢查的數據庫的信息對象列表
        /// </summary>
        public System.Collections.Generic.List<Srv> SrvList
        {
            get { return srvList; }
            set { srvList = value; }
        }

OnStart中读取配置文件时,用string filexml = System.Windows.Forms.Application.StartupPath.ToString() + @"\ServerConfig.xml"来取配置文件的实际路径,这样可以取到安装的目录。

在timerChkSql_Elapsed中增加定时检查的功能代码

//執行檢查
           writestr("執行檢查。");
           try
           {               
               foreach (Srv s in SrvList)
               {
                   writestr("執行檢查" + s.Dbname);
                   s.Chk();
               }
           }
           catch (Exception ee)
           {
               writestr("執行檢查出錯。" + ee.Message);
               EventLog.WriteEntry("執行檢查出錯。" + ee.Message);
           }

 

完整的MyService.cs代码:

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.ComponentModel;
   4:  using System.Data;
   5:  using System.Diagnostics;
   6:  using System.Linq;
   7:  using System.ServiceProcess;
   8:  using System;
   9:  using System.Collections.Generic;
  10:  using System.ComponentModel;
  11:  using System.Data;
  12:  using System.Diagnostics;
  13:  using System.ServiceProcess;
  14:  using System.IO;
  15:  using System.Text;
  16:  using System.Timers;
  17:  using System.Threading;
  18:  using Core.DarrenCoreLib;
  19:  using System.Net.Mail;
  20:  using System.Data.SqlClient;
  21:  using System.Xml;
  22:   
  23:  //=================================================================================  
  24:  //  
  25:  //        Copyright (C) 2012, 谢堂文(Darren Xie)     
  26:  //        All rights reserved  
  27:  //        Created by Darren at 12-03-15 14:12:57   
  28:  //        Email: 13923613791@139.com  
  29:  //        http://www.cnblogs.com/yiyumeng/  
  30:  //  
  31:  //================================================================================== 
  32:   
  33:  namespace MonitorSqlServerWindowsService
  34:  {
  35:      public partial class MyService : ServiceBase
  36:      {
  37:          
  38:          private System.Collections.Generic.List<Srv> srvList = new List<Srv>();
  39:          /// <summary>
  40:          /// 用城保存需要檢查的數據庫的信息對象列表
  41:          /// </summary>
  42:          public System.Collections.Generic.List<Srv> SrvList
  43:          {
  44:              get { return srvList; }
  45:              set { srvList = value; }
  46:          }
  47:          public MyService()
  48:          {
  49:              InitializeComponent();
  50:          }
  51:   
  52:          protected override void OnStart(string[] args)
  53:          {
  54:   
  55:              int istest = 0;
  56:              string defsqlUser_dbuser = string.Empty;
  57:              string defsqlUser_userpwd = string.Empty;
  58:              string smtpName = string.Empty;
  59:              string smtpFrom = string.Empty;
  60:              string smtpUser = string.Empty;
  61:              string smtpPwd = string.Empty;
  62:              string[] to = null;
  63:              int isLog = 0;
  64:              string LogFilePath = string.Empty;
  65:              string LogFileName = string.Empty;
  66:              string sql = string.Empty;
  67:   
  68:              EventLog.WriteEntry("檢查SQL服務器的服务启动。");//在系统事件查看器里的应用程序事件里来源的描述     
  69:              writestr("服务启动");//自定义文本日志   
  70:              XmlDocument xmlDoc = new XmlDocument();
  71:              try
  72:              {
  73:                  string filexml = System.Windows.Forms.Application.StartupPath.ToString() + @"\ServerConfig.xml";
  74:                  if (!System.IO.File.Exists(filexml))
  75:                  {
  76:                      throw new Exception("找不到配置文件" + filexml);
  77:                  }
  78:                  writestr("讀取檢查週期時間");
  79:                  xmlDoc.Load(filexml);
  80:              }
  81:              catch (Exception ee)
  82:              {
  83:                  EventLog.WriteEntry("檢查SQL服務器的服务启动時讀取檢查週期時間出錯。" + ee.Message);
  84:                  writestr("檢查SQL服務器的服务启动時讀取檢查週期時間出錯。" + ee.Message);
  85:              }
  86:              XmlNodeList nodeList = xmlDoc.SelectSingleNode("parameters").ChildNodes;
  87:              try
  88:              {
  89:                  writestr("開始讀取配置內容");
  90:                  foreach (XmlNode xn in nodeList)
  91:                  {
  92:   
  93:                      if (xn.Name == "checkTime")
  94:                      {
  95:                          timerChkSql.Interval = (double.Parse(xn.Attributes["val"].Value))*1000;
  96:                          writestr("讀取到檢查週期時間" + xn.Attributes["val"].Value);
  97:                      }
  98:                      if (xn.Name == "istest")
  99:                      {
 100:                          istest = int.Parse(xn.Attributes["val"].Value);
 101:   
 102:                      }
 103:                      if (xn.Name == "smtp")
 104:                      {
 105:                          smtpName = xn.Attributes["name"].Value;
 106:   
 107:                          smtpFrom = xn.Attributes["from"].Value;
 108:   
 109:                          smtpUser = xn.Attributes["user"].Value;
 110:   
 111:                          smtpPwd = xn.Attributes["pwd"].Value;
 112:   
 113:   
 114:                      }
 115:                      if (xn.Name == "defsqlUser")
 116:                      {
 117:   
 118:                          defsqlUser_dbuser = xn.Attributes["dbuser"].Value;
 119:   
 120:                          defsqlUser_userpwd = xn.Attributes["userpwd"].Value;
 121:   
 122:                      }
 123:                      if (xn.Name == "to")
 124:                      {
 125:                          to = xn.Attributes["email"].Value.Split(new char[] { ';' });
 126:   
 127:                      }
 128:                      if (xn.Name == "isLog")
 129:                      {
 130:                          isLog = int.Parse(xn.Attributes["val"].Value);
 131:   
 132:                      }
 133:                      if (xn.Name == "LogFilePath")
 134:                      {
 135:                          LogFilePath = xn.Attributes["val"].Value == "" ? System.Windows.Forms.Application.StartupPath.ToString() +@"\": xn.Attributes["val"].Value;
 136:   
 137:                      }
 138:                      if (xn.Name == "sql")
 139:                      {
 140:                          sql = xn.Attributes["val"].Value;
 141:   
 142:                      }
 143:                      if (xn.Name == "LogFileName")
 144:                      {
 145:   
 146:                          LogFileName = xn.Attributes["val"].Value;
 147:   
 148:                      }
 149:                      if (xn.Name == "dbsrv")
 150:                      {
 151:                          Srv s = new Srv();
 152:                          s.IsLog = isLog;
 153:                          s.Srvname = xn.Attributes["srvname"].Value;
 154:                          s.Dbname = xn.Attributes["dbname"].Value;
 155:                          s.Dbuser = xn.Attributes["dbuser"].Value != "" ? xn.Attributes["dbuser"].Value : defsqlUser_dbuser;
 156:                          s.Userpwd = xn.Attributes["userpwd"].Value != "" ? xn.Attributes["userpwd"].Value : defsqlUser_userpwd;
 157:                          s.Sql = xn.Attributes["sql"].Value != "" ? xn.Attributes["sql"].Value : sql;
 158:                          s.Descr = xn.Attributes["descr"].Value;
 159:                          s.LogFileName = xn.Attributes["LogFileName"].Value != "" ? xn.Attributes["LogFileName"].Value : (LogFileName != "" ? LogFileName : s.Dbname + "ChkLog.txt");
 160:                          s.LogFilePath = LogFilePath;
 161:                          s.To = to;
 162:                          s.From = smtpFrom;
 163:                          s.SmtpName = smtpName;
 164:                          s.SmtpPwd = smtpPwd;
 165:                          s.SmtpUser = smtpUser;           
 166:                          SrvList.Add(s);
 167:   
 168:                      }
 169:   
 170:   
 171:                  }
 172:                  writestr("讀取配置內容完成。");
 173:   
 174:              }
 175:              catch (Exception ee)
 176:              {
 177:                  EventLog.WriteEntry("檢查SQL服務器的服务启动時讀取配置內容出錯。" + ee.Message);
 178:                  writestr("檢查SQL服務器的服务启动時讀取配置內容出錯。" + ee.Message);
 179:              }
 180:   
 181:          }
 182:   
 183:          protected override void OnStop()
 184:          {
 185:              writestr("服务停止");
 186:              EventLog.WriteEntry("檢查SQL服務器的服务停止。");
 187:          }
 188:   
 189:          private void timerChkSql_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
 190:          {
 191:              //執行檢查
 192:              writestr("執行檢查。");
 193:              try
 194:              {                
 195:                  foreach (Srv s in SrvList)
 196:                  {
 197:                      writestr("執行檢查" + s.Dbname);
 198:                      s.Chk();
 199:                  }
 200:              }
 201:              catch (Exception ee)
 202:              {
 203:                  writestr("執行檢查出錯。" + ee.Message);
 204:                  EventLog.WriteEntry("執行檢查出錯。" + ee.Message);
 205:              }
 206:   
 207:   
 208:   
 209:   
 210:          }
 211:          public void writestr(string readme)
 212:          {
 213:              Core.DarrenCoreLib.Log.FileLog.writeTotxt((System.Windows.Forms.Application.StartupPath.ToString() + @"\" + "MonitorSqlServerWindowsService.txt"), "\r\n事件:" + readme + "\r\n操作时间:" + System.DateTime.Now.ToString("yyy-MM-dd HH:mm:ss"));
 214:          }
 215:   
 216:   
 217:   
 218:      }
 219:  }

 

第八步:增加安装

在设计页面点右键增加安装,之后你会看到以下的样子,并分别进行设定。

注意设定你的显示信息和服务名称,不是控件名。

同时也要设定StartType,我设为自动,这样一开机就会自动启用。

image

 

注意使用LocalSystem账号的设定,否则无法自动运行。

image

 

第九步:生成

先取Releasc的方式进行Build.

image

你会看到在BIN目录下有一个Release。

就会有以下图文件,除标了颜色的四个

image

 

从C:\Windows\Microsoft.NET\Framework\v2.0.50727中复制InstallUtil.exe和InstallUtilLib.dll这两个文件出来。

从源代码文件中复制配置文件ServerConfig.xml出来。

建立安装批处理文件Install.bat,假设安装目录是C:\MonitorSqlServerWindowsService:

c:
cd C:\MonitorSqlServerWindowsService
InstallUtil MonitorSqlServerWindowsService.exe
net start MonitorSqlServerStatus

 

建立反安装批处理文件UnInstall.bat,假设安装目录是C:\MonitorSqlServerWindowsService:

c:
cd C:\MonitorSqlServerWindowsService
net stop MonitorSqlServerStatus
InstallUtil -u MonitorSqlServerWindowsService.exe

之后把所有这个文件夹下的文件复制到一个MonitorSqlServerWindowsService文件夹下。要在那台电脑站安装,说复制到C盘再运行安装批处理就可以了。

 

另外,我把发邮件的地址加入139邮箱,并设好139邮件用长信息通知,那就可以在收到邮件时,也收到信息,在信息内容中就可以有350个字的内容,看你的提醒内容吧。

 

文墨所限,请多多指教。

请尊重原创版权,转载注明出处。

posted @ 2012-03-16 21:31  谢堂文(Darren Xie)  阅读(2870)  评论(8编辑  收藏  举报