C#執行預存程序的簡化

下面的方法是我在實際開發中摸索出來的，可以在很大程度上簡化調用預存程序的代碼。

首先來看一下C#調用預存程序的一般過程：
1、開啟資料庫連接SqlConnection；
2、產生一個SqlCommand；
3、向命令對象填充參數；
4、執行預存程序；
5、關閉串連；
6、其他動作。

我這裡講的主要是簡化第3步操作，最終在調用預存程序的時候只需要傳遞預存程序的名字和相應的參數值。調用樣本如下：
        dbAccess.run("p_am_deleteFile", new object[]{LoginId, Request.UserHostAddress, fileId});

由於在填充參數的時候必須要兩個值，一個是參數的名字，一個是參數的值。參數值是由外部傳入的，不用考慮；而參數名稱是和預存程序相關的東西，應該可以由預存程序名稱來確定而不用每次調用的時候寫上一遍。對於這個問題，如果能將預存程序的參數儲存到一個全域的地方，那麼在調用預存程序的時候只要能根據預存程序的名字去索引就可以了。具體實現的時候我是將這些資訊儲存在資料庫訪問組件裡面，採用名字/值對的方式。代碼如下：
public class InfoTable : NameObjectCollectionBase
{
   public object this[string key] 
   {
    get 
    {
     return(this.BaseGet(key));
    }
    set 
    {
     this.BaseSet(key, value);
    }
   }
}
。。。。。。
protected static InfoTable procInfoTable = new InfoTable();
。。。。。。
public static InfoTable ProcInfoTable
{
   get
   {
    return procInfoTable;
   }
}

這樣的話，在實際調用預存程序的時候就只需要去查這張表就可以知道預存程序的參數名了。實現代碼如下：
public DataTable run(string procName, object[] parms, ref int retValue)
{
 string[] paramInfo = (string[])(procInfoTable[procName]);
 if (paramInfo == null)
 {
  ErrorInfo.setErrorInfo("未取得" + procName + "的參數！");
  return null;
 }

 bool bOpened = (dbConn.State == ConnectionState.Open);
 if (!bOpened && !connect())
 {
  return null;
 }

 DataSet ds = new DataSet();   
 try
 {
  SqlCommand cmd = new SqlCommand(procName, dbConn);
  cmd.CommandType = CommandType.StoredProcedure;

  for (int i = 0; i < parms.Length && i < paramInfo.Length; ++i)
  {
   cmd.Parameters.Add(new SqlParameter(paramInfo[i], parms[i]));
  }

  SqlParameter parmsr = new SqlParameter("return", SqlDbType.Int);
  parmsr.Direction = ParameterDirection.ReturnValue;
  cmd.Parameters.Add(parmsr);

  SqlDataAdapter adp = new SqlDataAdapter(cmd);
  adp.Fill(ds);
  retValue = (int)(cmd.Parameters["return"].Value);
 }
 catch (Exception ex)
 {
  ErrorInfo.setErrorInfo(ex.Message);

  retValue = -1;
 }

 if (!bOpened)
  close();

 if (ds.Tables.Count > 0)
  return ds.Tables[0];
 else
  return null;
}

可以看出，每個預存程序的參數列表格儲存體為了一個string[]。接下來的工作就是將系統裡頭許許多多的預存程序的參數填充到表ProcInfoTable中。我所用的資料庫是Sql Server 2000，下面給出一個預存程序來解決這個煩人的問題：
create PROCEDURE dbo.p_am_procInfo
(
 @procName t_str64 --預存程序的名字
)
AS
begin
 set nocount on

 if @procName = '' begin
  select name as procName
  from sysobjects
  where substring(sysobjects.name, 1, 5) = 'p_am_'
 end
 else begin
  select
   syscolumns.name as paramName
  from sysobjects, syscolumns
  where sysobjects.id = syscolumns.id
   and sysobjects.name = @procName
  order by colid
 end
 
end

這個預存程序有兩個作用，在沒有傳遞預存程序的名字的時候，該預存程序返回所有以”p_am_”開頭的預存程序的名字；在傳入了相應的預存程序名字後，該預存程序返回該預存程序的參數列表。這樣一來，我們在程式開始的地方就可以將系統裡的預存程序參數列表取出來並儲存到資料庫訪問組件的ProcInfoTable屬性中了。具體代碼如下：
span.DBAccess dbAccess = new span.DBAccess();

//
//構造取預存程序的參數表
//
span.DBAccess.ProcInfoTable["p_am_procInfo"] = new string[]{"@procName"};

//
//取得其他預存程序列表
//
DataTable dt = dbAccess.run("p_am_procInfo", new object[]{""});
if (dt == null || dt.Rows.Count <= 0)
{
 return;
}

//
//取得其他預存程序的參數表
//
foreach (DataRow dr in dt.Rows)
{
 DataTable dtParams = dbAccess.run("p_am_procInfo", new object[]{dr["procName"]});
 if (dtParams != null)
 {
  string[] paramInfo = new string[dtParams.Rows.Count];
  for (int i = 0; i < dtParams.Rows.Count; ++i)
   paramInfo[i] = dtParams.Rows[i]["paramName"].ToString();

  span.DBAccess.ProcInfoTable[dr["procName"].ToString()] = paramInfo;
 }
}

至此，全部技術細節介紹完畢。另外，資料庫訪問對象的幾個介面函數也一併給出：

//開啟、關閉資料庫連接
public bool connect(string strConn)
public bool connect()
public bool close()

//執行SQL命令(只有一個int返回)
public int exec(string procName, object[] parms)
public int exec(string sql)

//運行(返回一個DataTable)
public DataTable run(string procName, object[] parms, ref int retValue)
public DataTable run(string procName, object[] parms)
public DataTable run(string sql)

//分頁查詢(頁號從1開始,返回一個DataTable)
public DataTable pageQuery
  (
   string selectCmd,
   int pageSize,
   int pageNumber
  )