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

淡淡的烟草味﹌ 2021-11-11 12:20 335阅读 0赞

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

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

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

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

**资料和要求：**

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

<table> 
 <tbody> 
 <tr> 
 <td> UsedFor </td> 
 <td> Instance </td> 
 <td> Database </td> 
 </tr> 
 <tr> 
 <td> CPT </td> 
 <td> [HZXL1\HZXL1] </td> 
 <td> XLProd_Cree_HZ2 </td> 
 </tr> 
 <tr> 
 <td> CPT </td> 
 <td> [HZXL1\HZXL1] </td> 
 <td> XLProd_Cree_HZ2_Archive </td> 
 </tr> 
 <tr> 
 <td> CPT </td> 
 <td> [HZXL1\HZXL1] </td> 
 <td> XLSite_Cree_HZ2 </td> 
 </tr> 
 <tr> 
 <td> Camstar OLTP </td> 
 <td> [HZCS1\HZCS1] </td> 
 <td> FASSL </td> 
 </tr> 
 <tr> 
 <td> Camstar OLTP </td> 
 <td> [HZCS1\HZCS1] </td> 
 <td> InSiteDB </td> 
 </tr> 
 <tr> 
 <td> Camstar OLTP </td> 
 <td> [HZCS1\HZCS1] </td> 
 <td> InSiteDB_CSIPurgeDB </td> 
 </tr> 
 <tr> 
 <td> Camstar ODS </td> 
 <td> HZCSODS01 </td> 
 <td> CNSSLRTS </td> 
 </tr> 
 <tr> 
 <td> Camstar ODS </td> 
 <td> HZCSODS01 </td> 
 <td> InSiteODS </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> CreeMES_PNT </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> CreeMES_ReplStage </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> CrystalReports2008 </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> FAOpto </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> Intranet_Apps </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> PNT_Parameters </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> PNTLampInfo </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> ProberInfo </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> WaferWorks_PT </td> 
 </tr> 
 <tr> 
 <td> PNT </td> 
 <td> HZBACK01 </td> 
 <td> WaferWorks_Sphere </td> 
 </tr> 
 </tbody> 
</table>

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

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

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

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

警报地址---(\#Asia\_IT\_Operations; \#HZ\_IS\_Helpdesk)

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

实际的效果：

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

[![clip\_image001][clip_image001]][clip_image001_clip_image001]

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

[![clip\_image002][clip_image002]][clip_image002_clip_image002]

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

[![clip\_image001\[6\]][clip_image001_6]][clip_image001_6_clip_image001_6]

主程序日志文件

[![clip\_image001\[8\]][clip_image001_8]][clip_image001_8_clip_image001_8]

报警邮件

[![clip\_image001\[10\]][clip_image001_10]][clip_image001_10_clip_image001_10]

功能实现：

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

> 我要检查的数据库众多，18个，就18个吗？
> 
> 所以这些都要用配置文件来配置，可加可减才行.

> 日志是少不了的，那是否一定就要呢？什么时候都是有比无要好，但是可有可无才是灵活的方式，那要还是不要？
> 
> 功能是要有，但是用与不用，改改参数就可以吧，那参数也放配置文件中好了。

> 要发邮件，SMTP是固定的吗？有其它的没有？可能想更换的时候也是有的？
> 
> 也就是说SMTP的信息（服务器、用户名、密码等）都放配置文件。

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

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

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

OK,就一步一步来吧！

第一步：新建一个项目

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

[![clip\_image001][clip_image001 1]][clip_image001_clip_image001 1]

创建后的默认如下：

[![image][]][image 1]

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

[![image][image 2]][image_image 2]

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

[![image][image 3]][image_image 3]

新建立一个类，名为MonitorSqlServerWindowsService

[![image][image 4]][image_image 4]

MonitorSqlServerWindowsService类的代码：

1:  using System;

2:  using System.Security.Cryptography;

3:  using System.IO;

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>

4:

5: <istest val="0">測試標識,1是測試，非1是正式</istest>

6: <checkTime val="120">檢查週期，單位是秒</checkTime>

7: <smtp name="你的SMTP服务器" from="你的邮箱地址" user="你的用户名" pwd="M/R3ib7M0OVPpDWmAZjGGw==">郵件服務器信息</smtp>

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

8:  namespace Core.DarrenCoreLib.Log

9:  {

10: /// <summary>

11:      /// 描述：寫ＴＸＴ格式的ＬＯＧ

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;

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> 信息內容：" + errMsg + " 服務器：" + this.Srvname + " 數據庫：" + this.Dbname +" 來自配置文件的數據庫描述信息："+this.Descr.ToString()+ " 請注意檢查確認，這是郵系統自動檢查發出的信息。 來自服務器：" + 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 5]][image_image 5]

之后你会看到

[![image][image 6]][image_image 6]

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

先建立一个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:  //

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][image 7]][image_image 7]

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

[![image][image 8]][image_image 8]

第九步：生成

先取Releasc的方式进行Build.

[![image][image 9]][image_image 9]

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

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

[![image][image 10]][image_image 10]

从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个字的内容，看你的提醒内容吧。

文墨所限，请多多指教。

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

转载于:https://www.cnblogs.com/yiyumeng/archive/2012/03/16/2400911.html

[clip_image001]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162128055951.jpg
[clip_image001_clip_image001]: /images/20211109/fa2dcec393cd4d6dacf8999750417d46.png
[clip_image002]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162128299047.jpg
[clip_image002_clip_image002]: /images/20211109/e4718aa1f3f04586bc4f3da7833147ac.png
[clip_image001_6]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162128381055.jpg
[clip_image001_6_clip_image001_6]: /images/20211109/6cbc5741408b4f08876bbe733b7cd5d0.png
[clip_image001_8]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162128467198.jpg
[clip_image001_8_clip_image001_8]: /images/20211109/9eedd6d69b5d4ec0975d3334c13b7e2c.png
[clip_image001_10]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162128587928.jpg
[clip_image001_10_clip_image001_10]: /images/20211109/5ac8a41be42440d685ea1728f6166ff5.png
[clip_image001 1]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162129137979.png
[clip_image001_clip_image001 1]: /images/20211109/14dd7c095a334b48b3b5b2f754de0efb.png
[image]: /images/20211109/dc88f473bd434c9188aaea11de38945c.png
[image 1]: http://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162129198682.png
[image 2]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162129293165.png
[image_image 2]: /images/20211109/5016c3820fa74216bbd5ac36b7aa5147.png
[image 3]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/20120316212938288.png
[image_image 3]: /images/20211109/4f23563697124625959d36ec11f188ec.png
[image 4]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162129485295.png
[image_image 4]: /images/20211109/c6fe3a3e14884b8db832f4818368a1ff.png
[image 5]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162130073804.png
[image_image 5]: /images/20211109/8cc0f9e98c37427ba5d940595e334167.png
[image 6]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162130174209.png
[image_image 6]: /images/20211109/77916b45545a4a488ad8ccf9ba174cef.png
[image 7]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162130306759.png
[image_image 7]: /images/20211109/ebf389cac2154dd4a67f1c15c5bcc6a8.png
[image 8]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162130397405.png
[image_image 8]: /images/20211109/caa6a0cf7af34ffab51c1af23ecd729b.png
[image 9]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162131078643.png
[image_image 9]: /images/20211109/1bc7d4cf3d664f98aa7802598ddd752e.png
[image 10]: https://images.cnblogs.com/cnblogs_com/yiyumeng/201203/201203162131198718.png
[image_image 10]: /images/20211109/2d07b3537f9c4d9d9f18d4efb64c02b9.png