C#中調用預存程序(帶返回參數 And 無返回參數)

  /// <summary>
  /// 預存程序執行函數
  /// </summary>
  /// <param name="strSpName">預存程序名</param>
  /// <param name="ht">參數資訊集</param>
  /// <param name="strParameterArray">需返回的參數名數組</param>
  /// <returns>存有傳回值的HashTable</returns>
  public void ExecStoredProcedure(string strSpName,Hashtable ht,string[] strParameterArray)
  { 
   SqlCommand comm = new SqlCommand(strSpName,Conn);
   comm.CommandType = CommandType.StoredProcedure;

   IDictionaryEnumerator htEnumerator = ht.GetEnumerator();
   while (htEnumerator.MoveNext())
    comm.Parameters.Add(htEnumerator.Key.ToString(),htEnumerator.Value);

   foreach (string strParameterName in strParameterArray)
    comm.Parameters[strParameterName].Direction = ParameterDirection.Output;

   try
   {
    OpenConn();                                                           //開啟資料庫連結
    comm.ExecuteNonQuery();
   }
   finally
   {
    CloseConn();                                                          //關閉資料庫連結
   }
   
   ht.Clear();
   foreach (string strParameterName in strParameterArray)
    ht.Add(strParameterName,comm.Parameters[strParameterName].Value);
  }

  /// <summary>
  /// 預存程序執行函數
  /// </summary>
  /// <param name="strSpName">預存程序名</param>
  /// <param name="ht">參數資訊集</param>
  public void ExecStoredProcedure(string strSpName,Hashtable ht)
  { 
   SqlCommand comm = new SqlCommand(strSpName,Conn);
   comm.CommandType = CommandType.StoredProcedure;

   IDictionaryEnumerator htEnumerator = ht.GetEnumerator();
   while (htEnumerator.MoveNext())
    comm.Parameters.Add(htEnumerator.Key.ToString(),htEnumerator.Value);

   try
   {
    OpenConn();                                                                               //開啟資料庫連結
    comm.ExecuteNonQuery();
   }
   finally
   {
    CloseConn();                                                                               //關閉資料庫連結
   }   
  }

調用方法：

  Hashtable ht = new Hashtable();
  ht.Add("@variable_1","參數1");
  ht.Add("@variable_2","參數2");
  ht.Add("@variable_3","參數3");

  //帶返回資料
  string[] variableList = {"@variable_2","@variable_3"};
  ExecStoredProcedure("預存程序名",Hashtable ht,variableList);
  //返回資料在Hashtable ht相對應的鍵中

  //不帶返回資料
  ExecStoredProcedure("預存程序名",Hashtable ht);

  //ExecStoredProcedure方法是一個2次重載的方法