asp.net基于windows服務(wù)實(shí)現(xiàn)定時發(fā)送郵件的方法

//定義組件
private System.Timers.Timer time;
public int nowhour;
public int minutes;
public string sendTime;
public Thread th;
public string isOpen;//是否啟用定時發(fā)送
public string strToEUser;
public static int index = -1;
public static ArrayList mulitaddress;
//服務(wù)開始
protected override void OnStart(string[] args)
{
  time = new System.Timers.Timer();
  time.Enabled = true;
  time.AutoReset = true;
  //定時執(zhí)行方法
   time.Elapsed+=new System.Timers.ElapsedEventHandler(time_Elapsed);
    UserEmail toUser =new UserEmail();
    mulitaddress = GetMailAdressName();
   // LogHelper.SaveNote("當(dāng)前索引的值" + index.ToString());
    Thread.Sleep(2000);
   time.Start();
}
public class LogHelper
{
  public static void SaveNote(string note)
  {
    FileStream stream = new FileStream(GetLogDirectory("Common") + DateTime.Now.ToString("yyyy-MM-dd") + ".txt", FileMode.Append, FileAccess.Write, FileShare.Delete | FileShare.ReadWrite);
    StreamWriter writer = new StreamWriter(stream, Encoding.UTF8);
    writer.WriteLine("================================================================");
    writer.WriteLine(string.Format("Note:/t{0}", note));
    writer.WriteLine(string.Format("DateTime:/t{0}/r/n/r/n",DateTime.Now.ToString()));
    stream.Flush();
    writer.Close();
    stream.Close();
    stream.Dispose();
    writer.Dispose();
  }
  public static void SaveNote(string note, string txtname)
  {
    FileStream stream = new FileStream(GetLogDirectory(txtname) + DateTime.Now.ToString("yyyy-MM-dd") + ".txt", FileMode.Append, FileAccess.Write, FileShare.Delete | FileShare.ReadWrite);
    StreamWriter writer = new StreamWriter(stream, Encoding.GetEncoding("gb2312"));
    writer.WriteLine("================================================================");
    writer.WriteLine(string.Format("Note:/t{0}", note));
    writer.WriteLine(string.Format("DateTime:/t{0}/r/n/r/n", DateTime.Now.ToString("yyyyMMddHHmmss")));
    stream.Flush();
    writer.Close();
    stream.Close();
    stream.Dispose();
    writer.Dispose();
  }
  public static void SaveException(Exception e)
  {
    SaveException(e, string.Empty);
  }
  public static void SaveException(Exception e, string memo)
  {
    FileStream stream = new FileStream(GetLogDirectory("Common") + DateTime.Now.ToString("yyyy-MM-dd") + ".txt", FileMode.Append, FileAccess.Write, FileShare.Delete | FileShare.ReadWrite);
    StreamWriter writer = new StreamWriter(stream);
    writer.WriteLine("================================================================");
    writer.WriteLine(string.Format("Memo:/t{0}", memo));
    writer.WriteLine(string.Format("DateTime:/t{0}", DateTime.Now.ToShortTimeString()));
    writer.WriteLine(string.Format("Message:/t{0}", e.Message));
    writer.WriteLine(string.Format("StackTrace:/r/n----------/r/n{0}/r/n----------/r/n/r/n/r/n", e.StackTrace));
    stream.Flush();
    writer.Close();
    stream.Close();
    stream.Dispose();
    writer.Dispose();
  }
  public static string GetLogDirectory(string category)
  {
    string baseDirectory = string.Empty;
    if ((HttpContext.Current != null)  (HttpContext.Current.Server != null))
    {
      baseDirectory = HttpContext.Current.Server.MapPath("~");
    }
    else
    {
      baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
    }
    if ((baseDirectory[baseDirectory.Length - 1] != '/')  (baseDirectory[baseDirectory.Length - 1] != '//'))
    {
      baseDirectory = baseDirectory + @"/";
    }
    baseDirectory = string.Format(@"{0}Log/{1}/", baseDirectory, category);
    if (!Directory.Exists(baseDirectory))
    {
      Directory.CreateDirectory(baseDirectory);
    }
    return baseDirectory;
  }
}
void time_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
  try
  {
    if (mulitaddress != null)
    {
      LogHelper.SaveNote("進(jìn)入time_Elapsed");
      //獲取定時發(fā)送時間
      sendTime = Convert.ToString(GetConfigValue("sendTime"));
      LogHelper.SaveNote("sendTime" + sendTime);
      //是否開啟定時發(fā)送功能
      isOpen = GetConfigValue("isOpen");
      int sleeptime = Convert.ToInt32(GetConfigValue("SleepTime"));
      int stoptime = Convert.ToInt32(GetConfigValue("stoptime"));
      //LogHelper.SaveNote("數(shù)組長度" + mulitaddress.Count);
      // LogHelper.SaveNote("是否開啟定時發(fā)送功能" + isOpen + "定時時間" + sendTime + "定時小時數(shù):" + Convert.ToInt32(sendTime.Split(new char[] { ':' })[0]) + "定時分鐘數(shù):" + Convert.ToInt32(sendTime.Split(new char[] { ':' })[1]));
      if (isOpen == "true")
      {
        //現(xiàn)在時間 小時
        nowhour = Convert.ToInt32(DateTime.Now.Hour.ToString());//10
        //現(xiàn)在分鐘
        minutes = Convert.ToInt32(DateTime.Now.Minute.ToString());//5
        //獲取發(fā)送小時
        int sendhour = Convert.ToInt32(sendTime.Split(new char[] { ':' })[0]);
        //獲取發(fā)送的分鐘
        int sendMinute = Convert.ToInt32(sendTime.Split(new char[] { ':' })[1]);
        LogHelper.SaveNote("進(jìn)入定時發(fā)送郵件服務(wù)! 定時小時為:" + sendhour.ToString() + "定時分鐘為:" + sendMinute.ToString() + "現(xiàn)在小時為:" + nowhour.ToString() + "現(xiàn)在定時的分鐘為:" + minutes.ToString());
        LogHelper.SaveNote("當(dāng)前索引的值" + index.ToString());
        if (nowhour == stoptime)
        {
          LogHelper.SaveNote("停止時間點(diǎn)到了 郵件停止發(fā)送 ?。?！");
          index = -1;
        }
        else
        {
          //如果和當(dāng)前的時間 小時相等 則定時發(fā)送郵件
          if ((Convert.ToDateTime(DateTime.Now.ToShortTimeString()) >= Convert.ToDateTime(sendTime))  index  mulitaddress.Count)
          {
            index++;
            if (index  mulitaddress.Count)
            {
              SendEmail(((UserEmail)mulitaddress[index]));
            }
            else
            {
              LogHelper.SaveNote("發(fā)送完畢 當(dāng)前的索引值為" + index.ToString());
            }
          }
        }
      }
      else
      {
        //LogHelper.SaveNote("當(dāng)前索引的值" + index.ToString());
        index++;
        if (index  mulitaddress.Count)
        {
          SendEmail(((UserEmail)mulitaddress[index]));
        }
        else
        {
          LogHelper.SaveNote("發(fā)送完畢 時間停止");
          time.Enabled = false;
          time.Stop();
        }
      }
      Thread.Sleep(sleeptime);
    }
    else
      LogHelper.SaveNote("mulitaddress=null");
  }
  catch (Exception ex) 
  {
    LogHelper.SaveNote(ex.ToString());
  }
}
public ArrayList GetMailAdressName()
{
  ArrayList list = new ArrayList();
  string strSQL = "select b.mailAddress, a.mailtemplate,a.title from tg_product as a inner join tg_mailOrder as b on a.ccode=b.ccode where a.createtime=(select max(createtime) from tg_product)";
  SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, strSQL, null);
  if (dr.HasRows)
  {
    while (dr.Read())
    {
      UserEmail email = new UserEmail();
      email.Mailaddres = dr["mailAddress"].ToString();
      email.Title = dr["title"].ToString();
      email.Contents = dr["mailtemplate"].ToString();
      list.Add(email);
    }
  }
  return list;
}
/// summary>
/// 定義用戶郵件 標(biāo)題 內(nèi)容 Email地址
/// /summary>
public class UserEmail {
  private string title;
  private string contents;
  public string Contents
  {
    get { return contents; }
    set { contents = value; }
  }
  private string mailaddres;
  public string Mailaddres
  {
    get { return mailaddres; }
    set { mailaddres = value; }
  }
  public string Title { 
  get{return title;}
    set { title = value; }
  }
}
#region 發(fā)送郵件
/// summary>
/// 發(fā)送郵件
/// /summary>
public void SendEmail(UserEmail user)
{
  try
  {
    LogHelper.SaveNote("進(jìn)入SendEmail!!!");
    //郵件服務(wù)器
    string smtpserver = GetConfigValue("smtpserver");
    //發(fā)件人的郵箱名稱
    string emailuserName = GetConfigValue("emailuserName");
    //發(fā)件人的郵箱密碼
    string emailuserpwd = GetConfigValue("emailuserpwd");
    //郵箱地址
    string emailfrom = GetConfigValue("emailfrom");
    int port = Convert.ToInt32(GetConfigValue("serverPort"));
    bool f = true;
    //是否經(jīng)過SSL加密
    string isSSL = GetConfigValue("SSL");
    if (isSSL == "true")
    {
      f = true;
    }
    else
    {
      f = false;
    }
    LogHelper.SaveNote("發(fā)件人的郵箱名稱" + emailuserName + "郵件地址(emailFrom)" + emailfrom + "收件人" + user.Mailaddres);
    SendEmailToUser(smtpserver, port, f, user.Mailaddres, null, emailuserName, emailuserpwd, emailfrom, user.Title,user.Contents);
    LogHelper.SaveNote("Send Mail Success");
  }
  //獲取異常信息
  catch (Exception error)
  {
    //寫入異常信息
    using (StreamWriter sw = new StreamWriter("F://MailErrorlog.txt", true, System.Text.Encoding.UTF8))
    {
      sw.WriteLine(DateTime.Now.ToString() + "：");
      sw.WriteLine("Exception 異常信息如下.................");
      sw.WriteLine(error.ToString());
      sw.WriteLine("---------------------------------------------");
      sw.Close();
    }
  }
}
#endregion
#region 利用.Net自帶類(SmtpClient) 發(fā)送郵件
/// summary>
/// 利用.Net自帶類(SmtpClient) 發(fā)送郵件
/// /summary>
/// param name="stmpserver">郵件服務(wù)器/param>
/// param name="username">用戶名(郵箱名稱)/param>
/// param name="pwd">密碼/param>
/// param name="f">是否經(jīng)過SSL加密/param>
/// param name="port">郵件服務(wù)器端口(沒有加密 一般都是25)/param>
/// param name="strfrom">發(fā)件人/param>
/// param name="strto">收件人/param>
/// param name="subject">主題/param>
/// param name="body">內(nèi)容/param>
/// param name="Mulitaddress">發(fā)送多人 收件人的郵箱地址以逗號隔開/param>
/// param name="attachmentName">發(fā)送的附件名稱 沒有附件則為null or ""/param>
public void SendEmailToUser(string stmpserver,int port,bool f, string Mulitaddress, string attachmentName, string username, string pwd, string strfrom, string subject, string body)
{
  string ErrorLog = GetConfigValue("ErrorLog");
  SmtpClient smtp = new SmtpClient();
  //發(fā)送郵件的方式
  smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
  //指定郵件服務(wù)器
  smtp.Host = stmpserver;
  //Gmail QQ stmp ssl加密使用的端口 
  smtp.Port = port;
  smtp.EnableSsl = f; ;//true 經(jīng)過ssl加密 
  //驗(yàn)證發(fā)件人的身份 用戶名(郵件地址和密碼)
  smtp.Credentials = new System.Net.NetworkCredential(username, pwd);
  //初始化信息(來自 接收人)
  MailMessage _mailmessage = new MailMessage();
  //_mailmessage.To = strto;
  //發(fā)送多個人 接收人郵件地址以,隔開
  _mailmessage.From = new MailAddress(strfrom);
  _mailmessage.To.Add(Mulitaddress);
  //如果發(fā)送失敗，SMTP 服務(wù)器將發(fā)送 失敗郵件通知
  _mailmessage.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
  //優(yōu)先級
  _mailmessage.Priority = MailPriority.High;
  //發(fā)送主題
  _mailmessage.Subject = subject;
  //有附件則添加附件
  if (!string.IsNullOrEmpty(attachmentName))
  {
    System.Net.Mail.Attachment attch = new System.Net.Mail.Attachment(attachmentName);
    _mailmessage.Attachments.Add(attch);
  }
  //郵件主題編碼
  _mailmessage.SubjectEncoding = System.Text.Encoding.UTF8;
  //指定發(fā)送的格式 (Html)
  _mailmessage.IsBodyHtml = true;
  //指定發(fā)送郵件的編碼
  _mailmessage.BodyEncoding = System.Text.Encoding.UTF8;
  //指定郵件內(nèi)容
  _mailmessage.Body = body;
  //發(fā)送郵件
  try
  {
    smtp.Send(_mailmessage);
  }
  catch (Exception ex)
  {
    using (StreamWriter writer = new StreamWriter(ErrorLog+"://MailErrorlog.txt", true, System.Text.Encoding.UTF8))
    {
      writer.WriteLine("---------------------------------------------");
      writer.WriteLine("SendEmail方法發(fā)送郵件錯誤日志................");
      writer.WriteLine(ex.ToString());
      writer.Flush();
      writer.Close();
    }
  }
}
#endregion
#region 獲取郵件配置節(jié)點(diǎn)的值
/// summary>
/// 加載相應(yīng)配置文件 并按節(jié)點(diǎn)名稱獲取對應(yīng)的值
/// /summary>
/// param name="target">當(dāng)前節(jié)點(diǎn)的名稱/param>
/// returns>返回當(dāng)前節(jié)點(diǎn)名稱所對應(yīng)的值/returns>
public string GetConfigValue(string target)
{
  string configpath=System.Windows.Forms.Application.StartupPath.ToString() + "/Email.config";
  return GetConfigValue(configpath, target);
}
/// summary>
/// 根據(jù)節(jié)點(diǎn)名稱獲取配置文件對應(yīng)的值(郵件配置信息)
/// /summary>
/// param name="configpath">配置文件路徑/param>
/// param name="target">要獲取配置節(jié)點(diǎn)的名稱/param>
/// returns>返回當(dāng)前節(jié)點(diǎn)名稱所對應(yīng)的值/returns>
public string GetConfigValue(string configpath, string target)
{
  XmlDocument doc = new XmlDocument();
  //加載文件路徑s
  doc.Load(configpath);
  //獲取當(dāng)前節(jié)點(diǎn)的根節(jié)點(diǎn)
  XmlElement root = doc.DocumentElement;
  //獲取當(dāng)前節(jié)點(diǎn)下所有匹配子節(jié)點(diǎn)元素
  XmlNodeList xmlnode = root.GetElementsByTagName(target);
  //返回值
  return xmlnode[0].InnerXml;
}
#endregion
//服務(wù)結(jié)束
protected override void OnStop()
{
  time.Enabled = false;
}

?xml version="1.0" encoding="utf-8"?>
Emailconfig>
 !--郵件服務(wù)器地址 Gmail stmp.gmail.com-->
 smtpserver>*****/smtpserver>
 !--郵箱用戶名 -->
 emailuserName>***/emailuserName>
 !--郵箱密碼-->
 emailuserpwd>****/emailuserpwd>
 !--郵箱地址-->
 emailfrom>*****/emailfrom>
 !--是否啟用定時發(fā)送郵件功能-->
 isOpen>true/isOpen>
 !--前提下isOpen為true 定在每天某個時間點(diǎn)發(fā)送郵件-->
 sendTime>8:53/sendTime>
  !--是否經(jīng)過SSL加密-->
  SSL>false/SSL>
 !--Gmail smtp SSL加密 (Gmail:587) 服務(wù)器端口(沒有加密(SSL 配置為false) 一般都是25
(163,qq))-->
 serverPort>25/serverPort>
 !--線程睡眠時間-->
 SleepTime>1000/SleepTime>
 !--定停發(fā)送時間-->
 stoptime>0/stoptime>
 !--發(fā)送郵件錯誤日志路徑 盤根目錄-->
 ErrorLog>E/ErrorLog>
 !--定義數(shù)據(jù)庫連接字符串-->
 Connstr>/Connstr>
/Emailconfig>

#region 數(shù)據(jù)庫操作類的封裝
public abstract class SqlHelper
{
  //Database connection strings
  public static readonly string ConnectionStringLocalTransaction = Connstr;
  // Hashtable to store cached parameters
  private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  /*返回?cái)?shù)據(jù)庫連接*/
  public static SqlConnection getConnection()
  {
    SqlConnection c = new SqlConnection(ConnectionStringLocalTransaction);
    return c;
  }
  /// summary>
  /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
  /// using the provided parameters.
  /// /summary>
  /// remarks>
  /// e.g.: 
  /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  /// /remarks>
  /// param name="connectionString">a valid connection string for a SqlConnection/param>
  /// param name="commandType">the CommandType (stored procedure, text, etc.)/param>
  /// param name="commandText">the stored procedure name or T-SQL command/param>
  /// param name="commandParameters">an array of SqlParamters used to execute the command/param>
  /// returns>A SqlDataReader containing the results/returns>
  public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  {
    SqlCommand cmd = new SqlCommand();
    SqlConnection conn = new SqlConnection(connectionString);
    // we use a try/catch here because if the method throws an exception we want to 
    // close the connection throw code, because no datareader will exist, hence the 
    // commandBehaviour.CloseConnection will not work
    try
    {
      PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
      SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      cmd.Parameters.Clear();
      return rdr;
    }
    catch
    {
      conn.Close();
      throw;
    }
  }
  public static DataSet ReturnDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  {
    SqlCommand cmd = new SqlCommand();
    SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction);
    // we use a try/catch here because if the method throws an exception we want to 
    // close the connection throw code, because no datareader will exist, hence the 
    // commandBehaviour.CloseConnection will not work
    try
    {
      PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
      DataSet ds = new DataSet();
      SqlDataAdapter sda = new SqlDataAdapter(cmd);
      sda.Fill(ds);
      return ds;
    }
    catch
    {
      conn.Close();
      throw;
    }
  }
  /// summary>
  /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
  /// using the provided parameters.
  /// /summary>
  /// remarks>
  /// e.g.: 
  /// SqlDataReader r = ExecuteReader(SqlConnection, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  /// /remarks>
  /// param name="connectionString">a valid connection string for a SqlConnection/param>
  /// param name="commandType">the CommandType (stored procedure, text, etc.)/param>
  /// param name="commandText">the stored procedure name or T-SQL command/param>
  /// param name="commandParameters">an array of SqlParamters used to execute the command/param>
  /// returns>A SqlDataReader containing the results/returns>
  public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  {
    SqlCommand cmd = new SqlCommand();
    // we use a try/catch here because if the method throws an exception we want to 
    // close the connection throw code, because no datareader will exist, hence the 
    // commandBehaviour.CloseConnection will not work
    try
    {
      PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
      SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      cmd.Parameters.Clear();
      return rdr;
    }
    catch
    {
      conn.Close();
      throw;
    }
  }
  /// summary>
  /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
  /// using the provided parameters.
  /// /summary>
  /// remarks>
  /// e.g.: 
  /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  /// /remarks>
  /// param name="connectionString">a valid connection string for a SqlConnection/param>
  /// param name="commandType">the CommandType (stored procedure, text, etc.)/param>
  /// param name="commandText">the stored procedure name or T-SQL command/param>
  /// param name="commandParameters">an array of SqlParamters used to execute the command/param>
  /// returns>A SqlDataReader containing the results/returns>
  public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  {
    SqlCommand cmd = new SqlCommand();
    SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction);
    // we use a try/catch here because if the method throws an exception we want to 
    // close the connection throw code, because no datareader will exist, hence the 
    // commandBehaviour.CloseConnection will not work
    try
    {
      PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
      SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      cmd.Parameters.Clear();
      return rdr;
    }
    catch
    {
      conn.Close();
      throw;
    }
  }
  /// summary>
  /// add parameter array to the cache
  /// /summary>
  /// param name="cacheKey">Key to the parameter cache/param>
  /// param name="cmdParms">an array of SqlParamters to be cached/param>
  public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
  {
    parmCache[cacheKey] = commandParameters;
  }
  /// summary>
  /// Retrieve cached parameters
  /// /summary>
  /// param name="cacheKey">key used to lookup parameters/param>
  /// returns>Cached SqlParamters array/returns>
  public static SqlParameter[] GetCachedParameters(string cacheKey)
  {
    SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
    if (cachedParms == null)
      return null;
    SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
    for (int i = 0, j = cachedParms.Length; i  j; i++)
      clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
    return clonedParms;
  }
  /// summary>
  /// Prepare a command for execution
  /// /summary>
  /// param name="cmd">SqlCommand object/param>
  /// param name="conn">SqlConnection object/param>
  /// param name="trans">SqlTransaction object/param>
  /// param name="cmdType">Cmd type e.g. stored procedure or text/param>
  /// param name="cmdText">Command text, e.g. Select * from Products/param>
  /// param name="cmdParms">SqlParameters to use in the command/param>
  private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, 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 = cmdType;
    if (cmdParms != null)
    {
      foreach (SqlParameter parm in cmdParms)
        cmd.Parameters.Add(parm);
    }
  }
  /// summary>
  /// 傳入輸入?yún)?shù)
  /// /summary>
  /// param name="ParamName">存儲過程名稱/param>
  /// param name="DbType">參數(shù)類型/param>/param>
  /// param name="Size">參數(shù)大小/param>
  /// param name="Value">參數(shù)值/param>
  /// returns>新的 parameter 對象/returns>
  public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
  {
    return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
  }
  public void MakeInParam(ref string OldString, string ParamName, SqlDbType DbType, int Size, object Value)
  {
    OldString = OldString.Replace(ParamName, (string)Value);
  }
  /// summary>
  /// 傳入返回值參數(shù)
  /// /summary>
  /// param name="ParamName">存儲過程名稱/param>
  /// param name="DbType">參數(shù)類型/param>
  /// param name="Size">參數(shù)大小/param>
  /// returns>新的 parameter 對象/returns>
  public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
  {
    return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
  }
  /// summary>
  /// 傳入返回值參數(shù)
  /// /summary>
  /// param name="ParamName">存儲過程名稱/param>
  /// param name="DbType">參數(shù)類型/param>
  /// param name="Size">參數(shù)大小/param>
  /// returns>新的 parameter 對象/returns>
  public static SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size)
  {
    return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
  }
  /// summary>
  /// 生成存儲過程參數(shù)
  /// /summary>
  /// param name="ParamName">存儲過程名稱/param>
  /// param name="DbType">參數(shù)類型/param>
  /// param name="Size">參數(shù)大小/param>
  /// param name="Direction">參數(shù)方向/param>
  /// param name="Value">參數(shù)值/param>
  /// returns>新的 parameter 對象/returns>
  public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
  {
    SqlParameter param;
    if (Size > 0)
      param = new SqlParameter(ParamName, DbType, Size);
    else
      param = new SqlParameter(ParamName, DbType);
    param.Direction = Direction;
    if (!(Direction == ParameterDirection.Output  Value == null))
      param.Value = Value;
    return param;
  }
  /// summary>
  /// 創(chuàng)建 SqlCommand 對象實(shí)例(用來返回一個整數(shù)值)    
  /// /summary>
  /// param name="storedProcName">存儲過程名/param>
  /// param name="parameters">存儲過程參數(shù)/param>
  /// returns>SqlCommand 對象實(shí)例/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;
  }
  /// summary>
  /// 構(gòu)建 SqlCommand 對象(用來返回一個結(jié)果集，而不是一個整數(shù)值)
  /// /summary>
  /// param name="connection">數(shù)據(jù)庫連接/param>
  /// param name="storedProcName">存儲過程名/param>
  /// param name="parameters">存儲過程參數(shù)/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;
  }
  #region 執(zhí)行查詢語句 返回dataset
  /// summary>
  /// 執(zhí)行查詢語句，返回DataSet
  /// /summary>
  /// param name="cmdText">查詢語句/param>
  /// returns>DataSet/returns>
  public static DataSet GetDataSet(string cmdText)
  {
    using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
    {
      SqlCommand sqlcmd = new SqlCommand(cmdText, conn);
      sqlcmd.CommandTimeout = 1000;
      SqlDataAdapter cmd = new SqlDataAdapter(sqlcmd);
      DataSet ds = new DataSet();
      try
      {
        conn.Open();
        cmd.Fill(ds, "mydt");
        return ds;
      }
      catch (Exception ex)
      {
        conn.Close();
        cmd.Dispose();
        conn.Dispose();
        throw new Exception(ex.Message);
      }
      finally
      {
        cmd.Dispose();
        conn.Close();
        conn.Dispose();
      }
    }
  }
  #endregion
}
#endregion