利用Topshelf搭建Windowns服务实现定期自动添加数据逻辑和原理

 

一、Topshelf基本配置

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Topshelf;

namespace MealTimer
{
    public class Program
    {
        public static void Main()
        {
            HostFactory.Run(x =>                                 //这里通过HostFactory.Run安装这个host，通过其lambda表达式的x来进行各种host级别的参数配置。
            {
                x.Service<MealTimer>(s =>                        //这里我们告诉Topshelf这里有一个‘TownCrier’服务，lambda表达式的s用于设置各种服务级别的配置。
                {
                    s.ConstructUsing(name => new MealTimer());     //这里告诉Topshelf如何给这个服务构建一个实例，这里我们只是简单的将实例new出来，实际使用中通常会通过IoC来注入一个实例，代码类似‘container.GetInstance<TownCrier>()’.
                    s.WhenStarted(tc => tc.Start());              //告诉Topshelf启动这个服务时做什么。
                    s.WhenStopped(tc => tc.Stop());               //告诉Topshelf如何停止这个服务时做什么。
                });

                //以本地帐户身份运行,这里选择的是登录为：本地系统local system
                x.RunAsLocalSystem();

                x.SetDescription("内部系统自动添加报餐表");        //设置该服务的描述
                x.SetDisplayName("CanYouAddmealService");      //设置该服务的显示名称。
                x.SetServiceName("CanYouAddmealService");      //设置该服务自身的名字，即服务名称。

                //服务启动模式
                x.StartAutomatically(); //自动

                x.EnableServiceRecovery(rc => rc.RestartService(1));// 服务如果意外停止，1分钟后恢复启动
            });
        }
    }
}

 

二、配置Start方法

        public void Start()
        {
            Thread sendThread = new Thread(new ThreadStart(delegate()
            {
                try
                {
                    while (true)
                    {
                        if (IsDBOnline())
                        {
                            AddMealSet();
                        }
                        else
                        {
                            Thread.Sleep(60 * 60 * 1000);
                            SaveRecord("\r\n" + "数据库链接失败，线程休眠【" + 60 + "】分钟。");
                        }
                    }

                }
                catch (Exception ex)
                {
                    SaveRecord("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n" + ex);
                    //线程终止，只能在该线程的内部终止，不能写在线程类的外部
                    Thread.CurrentThread.Abort();
                }
            }));

            //启动线程
            sendThread.Start();
            //指定后台线程
            sendThread.IsBackground = true;
        }

在Start方法里面，定义一个while（true）的死循环，死循环里面执行一次添加方法，就定时休眠，在执行添加方法前，先检测一下数据库连接状态，如果暂时连接失败，等60分钟后再启动服务。

        /// <summary>
        /// 数据库是否在线
        /// </summary>
        /// <returns></returns>
        public bool IsDBOnline()
        {
            string connString = ConfigurationSettings.AppSettings["MsSql"];
            SqlConnection Connection = new SqlConnection(connString);
            try
            {
                Connection.Open();
            }
            catch (Exception ex)
            {
                SaveRecord("\r\n" + "数据库链接错误：" + ex.ToString());
                return false;

            }
            finally
            {
                Connection.Close();
            }

            return true;

        }

 三、定时添加数据方法

        /// <summary>
        /// 根据当前时间和系统设置时间对比，进行自动报餐处理
        /// </summary>
        public void AddMealSet()
        {
            while (true)//构建一个死循环，里面只有线程休眠时间，到了时间重新判断，无限循环执行
            {
                #region 参数配置
                //取出配置文件的定时新增报餐数据
                string AddTime = ConfigurationSettings.AppSettings["AddTime"];
                //取出配置文件的星期值
                string dayofweek = ConfigurationSettings.AppSettings["Day"];
                //取出配置里面的数据要放在while (true)循环里面，放在外面，如果更新了配置文件，这些数据不能被读取到，只能重新启动才能读取到最新
                DateTime Day = DateTime.Now;//DateTime.Now要放在while (true)循环里面，否则DateTime.Now永远是第一次启动时的时间，没有更新
                string MealAutoAddstring = MealAutoAdd.AddMeal(Day);// 根据时间判断自动插入数据库一条报餐表数据语句，返回语句

                if (string.IsNullOrEmpty(AddTime))
                {
                    throw new Exception("从App.config文件获取定时发送短信时间AddTime报错，线程已经退出，请排查错误后，重启服务器。");
                }
                if (string.IsNullOrEmpty(MealAutoAddstring))
                {
                    throw new Exception("报餐表数据语句，返回值为空！");
                }
                int HHNow = Convert.ToInt32(Day.ToString("HH"));//定义现在时间小时数
                int mmNow = Convert.ToInt32(Day.ToString("mm"));//定义现在时间分钟数
                int AddTime_Val = Convert.ToInt32(AddTime);//获取添加报餐时间小时数
                int _SleepTime = 0;
                string monthfirstday = DateTime.Now.Date.ToString("dd");//获取每月1号
                #endregion


                if (MealAutoAdd.Week(Day.DayOfWeek.ToString()) != dayofweek & MealAutoAdd.Week(Day.DayOfWeek.ToString()) != "星期二")//非周二、三时间，线程休眠24小时.
                {
                    #region 非周二、三时间，线程休眠24小时
                    _SleepTime = 86400000;

                    if (monthfirstday == "01")
                    {
                        AddHumanCost();//每月1号，自动添加人力成本数据
                        
                    }

                    SaveRecord("\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n" +
                               MealAutoAdd.Week(Day.DayOfWeek.ToString()) + "，线程休眠：" + 24 + "小时，共(毫秒)：" + _SleepTime);
                    Thread.Sleep(_SleepTime);
                    #endregion
                }

                else if (MealAutoAdd.Week(Day.DayOfWeek.ToString()) == "星期二")//为了避免在周二重启服务器或服务重启，如果休眠24小时，可能会错过周三的报餐时间
                {
                    #region 星期二，如果当前小时数小于0，则休眠时间可以是24小时，休眠结束之后，还在定时报餐时间范围内
                    if (HHNow - AddTime_Val < 0)//如果当前小时数小于0，则休眠时间可以是24小时，休眠结束之后，还在定时报餐时间范围内
                    {
                        if (monthfirstday == "01")
                        {
                           
                            AddHumanCost();//每月1号，自动添加人力成本数据
                            
                        }

                        _SleepTime = ((24 + AddTime_Val - HHNow - 1) * 60000 * 60) + (60 - mmNow) * 60000;//当前时间  如果是7：45，休眠24小时之后，还是7：45，所以要加上15分钟，正好8点
                        string SaveRecordstring = mmNow == 0
                                                      ? "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                        "\r\n周二时间，当前时间在定时报餐之前，线程休眠：" +
                                                        (24 + AddTime_Val - HHNow) + "小时，共(毫秒)：" + _SleepTime
                                                      : "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                        "\r\n周二时间，当前时间在定时报餐之前，线程休眠：" +
                                                        (24 + AddTime_Val - HHNow - 1) + "小时" + (60 - mmNow) +
                                                        "分钟，共(毫秒)：" + _SleepTime;
                        SaveRecord(SaveRecordstring);

                        Thread.Sleep(_SleepTime);
                    }
                    #endregion

                    #region 如果当前小时数大于0，则休眠时间不能是24小时，否则会造成休眠结束之后，错过报餐时间
                    else if (HHNow - AddTime_Val > 0)//如果当前小时数大于0，则休眠时间不能是24小时，否则会造成休眠结束之后，错过报餐时间
                    {
                        if (monthfirstday == "01")
                        {
                            AddHumanCost();//每月1号，自动添加人力成本数据
                          
                        }
                        _SleepTime = (24 - HHNow - 1) * 60000 * 60 + (60 - mmNow) * 60000;//当前时间  如果是9：45，需要休眠14：15小时之后，为整点
                        string SaveRecordstring = mmNow == 0
                                                      ? "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                        "\r\n周二时间，当前时间在定时报餐之后" + (HHNow - AddTime_Val) + "小时，线程休眠：" +
                                                        (24 - HHNow) + "小时，共(毫秒)：" +
                                                        _SleepTime
                                                      : "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                        "\r\n周二时间，当前时间在定时报餐之后" + (HHNow - AddTime_Val) + "小时，线程休眠：" +
                                                        (24 - HHNow - 1) + "小时" + (60 - mmNow) + "分钟，共(毫秒)：" +
                                                        _SleepTime;
                        SaveRecord(SaveRecordstring);

                        Thread.Sleep(_SleepTime);
                    }
                    #endregion

                    #region 时间在当前时段，如果当前时间是整点数
                    else//时间在当前时段，如果当前时间是整点数
                    {
                        _SleepTime = 23 * 3600000 + (60 - mmNow) * 60000;//当前时间  如果是8：45，需要休眠23：15小时之后，为整点

                        if (monthfirstday == "01")
                        {
                            AddHumanCost();//每月1号，自动添加人力成本数据
                          
                        }

                        string SaveRecordstring = mmNow == 0
                                                      ? "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                        "\r\n周二时间，当前时间在定时报餐之后" + (HHNow - AddTime_Val) + "小时，线程休眠：" + 24 +
                                                        "小时，共(毫秒)：" + _SleepTime
                                                      : "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                        "\r\n周二时间，当前时间在定时报餐之后" + (HHNow - AddTime_Val) + "小时，线程休眠：" + 23 +
                                                        "小时" + (60 - mmNow) + "分钟，共(毫秒)：" + _SleepTime;
                        SaveRecord(SaveRecordstring);
                        Thread.Sleep(_SleepTime);
                    }
                    #endregion
                }
                else//当前时间为周三时间
                {
                    #region 当前时间为周三时间,也就是报餐时间
                    if (DateTime.Now.ToString("HH") == AddTime & MealAutoAdd.Week(Day.DayOfWeek.ToString()) == dayofweek)//判断是否到小时点，和对应的星期几
                    {
                        //关键一步：不管服务器是否重新启动，都要判断数据是否已经添加过
                        string Holiday = MealAutoAdd.MealDay_1(Day);
                        if (Holiday == "" & !isAddMealSet())//先判断是不是已经新增了节假日的报餐数据，再判断是否新增周末报餐的数据
                        {
                            SqlHelper.GetSingle(MealAutoAddstring);
                            SqlHelper.GetSingle(
                                "INSERT INTO sys_Event (E_U_LoginName, E_DateTime,E_Record) VALUES ('报餐定时器', '" +
                                Day.ToString("yyyy-MM-dd HH:mm:ss") + "', '自动添加“" +
                                SqlHelper.GetSingle("SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") +
                                "”报餐数据')");
                            SaveRecord("\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n自动添加“" +
                                       SqlHelper.GetSingle(
                                           "SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") + "”报餐数据'");//记录到本地日志
                            AddMeal();//根据自动报餐设置，自动添加报餐记录

                        }
                        if (Holiday != "" & !isHolidayAdd())//先判断是不是已经新增了节假日的报餐数据，再判断是否新增周末报餐的数据
                        {
                            SqlHelper.GetSingle(MealAutoAddstring);
                            SqlHelper.GetSingle(
                                "INSERT INTO sys_Event (E_U_LoginName, E_DateTime,E_Record) VALUES ('报餐定时器', '" +
                                Day.ToString("yyyy-MM-dd HH:mm:ss") + "', '自动添加“" +
                                SqlHelper.GetSingle("SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") +
                                "”报餐数据')");
                            SaveRecord("\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n自动添加“" +
                                       SqlHelper.GetSingle(
                                           "SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") + "”报餐数据'");
                        }
                        else//这个else里面一定要进行休眠，否则在这一个小时之内无数次循环添加报餐数据
                        {
                            if (monthfirstday == "01")
                            {
                                AddHumanCost();//每月1号，自动添加人力成本数据
                               
                            }

                            SaveRecord("\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n在自动报餐时间范围内,已经进行报餐数据新增，线程休眠(分钟)：" + (60 - mmNow));
                            //注意此处不能终止循环，否则跳出循环，不能再次新增数据                           
                            Thread.Sleep((60 - mmNow) * 60000);
                        }

                    }
                    #endregion

                    #region 如果不是在报餐的正点数
                    else//如果不是在报餐的正点数
                    {
                        if (HHNow < AddTime_Val & MealAutoAdd.Week(Day.DayOfWeek.ToString()) == dayofweek)//判断小时点，和对应的星期几
                        {
                            _SleepTime = (AddTime_Val - HHNow - 1) * 60000 * 60 + (60 - mmNow) * 60000;//假如现在是7：45，，8点为报餐整点，则休眠15分钟即可
                            string SaveRecordstring = mmNow == 0
                                                          ? "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                            "\r\n不在自动报餐时间范围内，线程休眠：" + (AddTime_Val - HHNow) + "小时，共(毫秒)：" + _SleepTime
                                                          : "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                            "\r\n不在自动报餐时间范围内，线程休眠：" + (AddTime_Val - HHNow - 1) + "小时" +
                                                            (60 - mmNow) + "分钟，共(毫秒)：" + _SleepTime;
                            SaveRecord(SaveRecordstring);
                            if (monthfirstday == "01")
                            {
                                AddHumanCost();//每月1号，自动添加人力成本数据
                              
                            }

                            Thread.Sleep(_SleepTime);//当天时间，时间小时数小于报餐小时数，休眠当前时间小时数和报餐时间点数的差值，例如：当前时间是7点，则休眠1个小时
                        }
                        if (HHNow > AddTime_Val & MealAutoAdd.Week(Day.DayOfWeek.ToString()) == dayofweek)//判断是否到小时点，和对应的星期几
                        {
                            _SleepTime = (24 - HHNow - 1) * 60000 * 60 + (60 - mmNow) * 60000;//假如现在是9：45，则休眠14：15分钟，可到24整点
                            string SaveRecordstring = mmNow == 0
                                                          ? "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                            "\r\n不在自动报餐时间范围内，线程休眠：" + (24 - HHNow) + "小时，共(毫秒)：" + _SleepTime
                                                          : "\r\n" + Day.ToString("yyyy-MM-dd HH:mm:ss") +
                                                            "\r\n不在自动报餐时间范围内，线程休眠：" + (24 - HHNow - 1) + "小时" +
                                                            (60 - mmNow) + "分钟，共(毫秒)：" + _SleepTime;
                            SaveRecord(SaveRecordstring);//当天时间，时间小时数大于报餐小时数，休眠当天的剩余小时数，例如：当前时间是9点，则休眠24-9=15个小时
                            if (monthfirstday == "01")
                            {
                                AddHumanCost();//每月1号，自动添加人力成本数据
                              
                            }
                            Thread.Sleep(_SleepTime);
                        }
                    }
                    #endregion
                }
            }
        } 

四、自动添加报餐方法

        /// <summary>
        /// 添加报餐方法
        /// </summary>
        public void AddMeal()
        {
            DataTable dt = SqlHelper.QueryTable("SELECT * FROM sys_MealDefaultValues");
            string MealName = ConfigurationSettings.AppSettings["MealName"];
            string Password = ConfigurationSettings.AppSettings["Password"];
            string host = ConfigurationSettings.AppSettings["host"];

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int MS_ID = Convert.ToInt32(SqlHelper.GetSingle("SELECT TOP 1 MS_ID FROM sys_MealSet order by MS_ID DESC").ToString());
                if (dt.Rows[i][1].ToString() == "0")//dt.Rows[i][1].ToString()
                {
                    string M_UserID = dt.Rows[i][2].ToString();
                    string M_day_1_Lunch = dt.Rows[i][3].ToString();
                    string M_day_1_Dinner = dt.Rows[i][4].ToString();
                    string M_day_2_Lunch = dt.Rows[i][5].ToString();
                    string M_day_2_Dinner = dt.Rows[i][6].ToString();
                    string M_IsMail = dt.Rows[i][7].ToString();
                    string AddString =
                        "INSERT INTO sys_Meal (M_UserID, M_MS_ID,M_day_1_Lunch,M_day_1_Dinner,M_day_2_Lunch,M_day_2_Dinner) VALUES ('" +
                        M_UserID + "','" + MS_ID + "','" + M_day_1_Lunch + "','" + M_day_1_Dinner + "','" +
                        M_day_2_Lunch + "','" + M_day_2_Dinner + "')";

                    int info = SqlHelper.ExecuteSql(AddString);

                    #region 设置MealTo,Content
                    string MealTo =
                                SqlHelper.GetSingle("SELECT U_CompanyMail FROM sys_User where UserID='" + dt.Rows[i][2] + "'").ToString() !=
                                  ""
                                      ? SqlHelper.GetSingle("SELECT U_CompanyMail FROM sys_User where UserID='" + dt.Rows[i][2] + "'").ToString()
                                      : SqlHelper.GetSingle("SELECT U_Email FROM sys_User where UserID='" + dt.Rows[i][2] + "'").ToString();

                    string Content = "尊敬的：" +
                                     SqlHelper.GetSingle("SELECT U_LoginName FROM sys_User where UserID='" +
                                                         dt.Rows[i][2] + "'") + "，您的报餐情况为：" +
                                     DisabilityType_Value.GetSaturdayDate().ToString("yyyy-MM-dd-星期六") + "（上午：" +
                                     dt.Rows[i][3].ToString().Substring(0, 2) + "，下午：" +
                                     dt.Rows[i][4].ToString().Substring(0, 2) + "）；" +
                                     DisabilityType_Value.GetSaturdayDate().AddDays(1).ToString("yyyy-MM-dd-星期日") +
                                     "（上午：" + dt.Rows[i][5].ToString().Substring(0, 2) + "，下午：" +
                                     dt.Rows[i][6].ToString().Substring(0, 2) + "）。报餐邮件是否发送，现已可在系统中进行设置！";
                    #endregion

                    if (info != 0)
                    {
                        bool SentOrNot = M_IsMail == "1" ? SendEmail(MealTo, MealName, Password, host, Content) : false;

                        #region 发送邮件成功，则日志记录中显示“并发送邮件成功”的字段，否则不显示这几个字
                        if (SentOrNot)//发送邮件成功，则日志记录中显示“并发送邮件成功”的字段，否则不显示这几个字
                        {
                            SqlHelper.GetSingle(
                                "INSERT INTO sys_Event (E_U_LoginName, E_DateTime,E_Record) VALUES ('报餐定时器', '" +
                                DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '自动添加“" +
                                SqlHelper.GetSingle("SELECT U_LoginName FROM sys_User where UserID='" + dt.Rows[i][2] +
                                                    "'") + "，" +
                                SqlHelper.GetSingle("SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") +
                                "”的报餐数据,并发送邮件成功！')");

                            SaveRecord("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n自动添加“" +
                                       SqlHelper.GetSingle("SELECT U_LoginName FROM sys_User where UserID='" +
                                                           dt.Rows[i][2] + "'") + "，" +
                                       SqlHelper.GetSingle(
                                           "SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") +
                                       "”的报餐数据,并发送邮件成功！')");

                        }
                        #endregion

                        #region 发送邮件成功，则日志记录中显示“并发送邮件成功”的字段，否则不显示这几个字
                        else
                        {
                            SqlHelper.GetSingle(
                                "INSERT INTO sys_Event (E_U_LoginName, E_DateTime,E_Record) VALUES ('报餐定时器', '" +
                                DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '自动添加“" +
                                SqlHelper.GetSingle("SELECT U_LoginName FROM sys_User where UserID='" + dt.Rows[i][2] +
                                                    "'") + "，" +
                                SqlHelper.GetSingle("SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") +
                                "”的报餐数据！')");
                            SaveRecord("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n自动添加“" +
                                       SqlHelper.GetSingle("SELECT U_LoginName FROM sys_User where UserID='" +
                                                           dt.Rows[i][2] + "'") + "，" +
                                       SqlHelper.GetSingle(
                                           "SELECT TOP 1 MS_MealType FROM sys_MealSet order by MS_ID DESC") +
                                       "”的报餐数据！')");
                        }
                        #endregion
                    }

                }
            }
        }

五、发送邮件方法

        /// <summary>
        /// 发送邮件方法
        /// </summary>
        /// <param name="MealTo">邮件接收者的帐号</param>
        /// <param name="MealUserName">注册的邮箱</param>
        /// <param name="MealPassword">邮箱密码</param>
        /// <param name="Host">邮箱对应的SMTP服务器</param>
        /// <param name="Content">邮件内容</param>
        /// <returns></returns>
        public bool SendEmail(string MealTo, string MealUserName, string MealPassword, string Host, string Content)
        {
            MailMessage msg = new MailMessage();
            msg.To.Add(MealTo);//邮件接收者的帐号
            msg.From = new MailAddress(MealUserName, "残友内部系统邮件", System.Text.Encoding.UTF8);//发送邮件的帐号及显示名称和字符编码
            msg.Subject = "残友内部系统自动增加报餐数据信息";//邮件标题 
            msg.SubjectEncoding = System.Text.Encoding.UTF8;//邮件标题编码 
            msg.Body = Content;//邮件内容 
            msg.BodyEncoding = System.Text.Encoding.UTF8;//邮件内容编码 
            msg.IsBodyHtml = false;//是否是HTML邮件 
            msg.Priority = MailPriority.Normal;//邮件优先级
            SmtpClient client = new SmtpClient();
            client.Credentials = new System.Net.NetworkCredential(MealUserName, MealPassword);//注册的邮箱和密码，就QQ邮箱而言，如果设置了独立密码，要用独立密码代替密码             
            client.Host = Host;//QQ邮箱对应的SMTP服务器
            object userState = msg;
            try
            {
                client.SendAsync(msg, userState);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

 六、SqlHelper类

using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.Configuration;
using System.Web;

namespace FrameWork
{
    public   class SqlHelper
    {

        public static string connectionString = ConfigurationManager.ConnectionStrings["FrameWorkConnectionString"].ConnectionString;

        public SqlHelper()
        {
        }

        #region 公用方法
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = SqlHelper.GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static bool Exists(string strSql)
        {
            object obj = SqlHelper.GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = SqlHelper.GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region  执行简单SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数 适用于select语句
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql2(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = Convert.ToInt32(cmd.ExecuteScalar());
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.VarChar);

                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Binary);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        /// <summary>
        /// 执行查询语句，返回datatable
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataTable QueryTable(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds.Tables[0];
            }
        }
        #endregion

        #region 执行带参数的SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        public static DataTable QueryTable(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds.Tables[0];
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        #region 存储过程操作
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            return returnReader;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        #endregion
    }
}