摘要：在各種系統開發中，使用存儲過程是一個良好的習慣，不僅可以帶來臨時表、函數、游標等特性，而且調試、升級、維護都變得方便。可是，幾乎所有的存儲過程的調用都是一種模式，主要差別也就是每個存儲過程的參數不同。那麼，可不可以采用一種方法來統一所有的存儲過程調用，減少不必要的編程呢？在研究了SQL Server數據庫及ASP.Net的基礎上，我們實現了統一調用的方法，該方法只需要提供要調用的存儲過程名，以及調用時提供具體的參數值就可實現任何存儲過程的調用。
關鍵字：存儲過程、系統表、信息結構視圖、ADO.Net 文獻標識碼：②實用性技術成果報告(科技)、理論學習與社會實踐總結(社科)
Call stored procedures in a same way in .Net
Abstract: Using stored procedures is a good habit in developing projects. It provides temporary table, functions and cursors, and debugging, upgrading, maintainence can benefit from it too. However, almost all calling to a stored procedure is a same pattern, the main difference between them is the parameters of every stored procedure. Then, can we call stored procedure in a same way in spite of their differences and reduce the programming code. We did it after studying SQL Server and .NET. Only information you provide is the stored procedure name and the values of its parameters, you needn’t to create the parameters yourself. Key Word: Stord Procedure, System table, Information Schema, ADO.Net
摘要：在一個項目的開發中，經常會調用數據庫中的存儲過程。可是，幾乎所有存儲過程的調用都是同一個模式，主要區別就在於創建的每個參數類型、值等不一樣。那麼，能不能實現通過一個函數（或者類）調用所有的存儲過程呢？本文在利用數據庫提供的系統表原理上，實現了統一調用的方法，該方法只需要提供要調用的存儲過程名，以及調用時提供具體的參數值就可實現任何存儲過程的調用。
Abstract: We have to call stored procedures of database systems during a development of a project. However, calling a stored procedures are almost the same, the main difference is the difference between parameters’ type or value etc. Can we call any stored procedures through a function (or a class)? Based on the system tables provided by database systems, We wrote a class to call any stored procedures in this article. To call a stored procedure, the only parameters you provide are the name of the stored procedure and the value of all parameters of the stored procedure.
<DIV class=text4><B>1．引言</B></DIV>
在各種系統開發中，使用存儲過程是一個良好的習慣，不僅可以帶來臨時表、函數、游標等特性，而且調試、升級、維護都變得方便。在存儲過程中能夠把數據經過處理再返回，這樣能夠對數據提供更多的分析和控制。在存儲過程的調用中，我們發現存儲過程的調用都幾乎是如下的模式：
1．聲明SqlConnection
2．聲明SqlCommand，並且設置其Connection屬性為剛聲明的SqlConnection實例，設置CommandName為存儲過程名，CommandType為存儲過程。
3．往剛聲明的SqlCommand實例的Parameters集合中添加所有的存儲過程調用需要的參數 4．呼叫SqlCommand的ExecuteReader()方法來得到存儲過程的返回行集
4．聲明SqlDataAdapter和DataSet，設置SqlDataAdapter的SelectCommand屬性為3中聲明的實例，再調用其Fill方法來把返回的行集填充到DataSet中
5．關閉SqlConnection對象
6．釋放聲明的各對象實例（說明：4指的是兩種數據提取方法）在這個調用過程中，我們發現幾乎所有的存儲過程調用都是這個模式，之間的區別就在第2步中的存儲過程名不同和第3步中各個存儲過程調用使用的參數是不一樣的，他們有參數名字、方向、數據類型、長度等的區別。那麼，有沒有一種方法可以實現所有的存儲過程調用？即只需要提供存儲過程名，然後把參數值傳入調用方法即可實現存儲過程的調用，再用某些數據結構來保存返回的行集、傳出參數值、過程返回值。經過研究SQL Server的系統表，我們發現這個想法是切實可行的。
2．系統表與信息結構視圖
SQL Server等關系型數據庫都將元數據以某種方式保存在數據庫中，在SQL Server中就是系統數據庫和系統表。安裝SQL Server後會自動生成四個系統數據庫：master, model, msdb與tempdb。master數據庫是SQL Server中所有系統級信息的倉庫。登錄帳號、配置設置、系統存儲過程和其他數據庫的存在性都記錄在master數據庫中。msdb數據庫保存SQL Server Agent的信息。定義作業、操作員和警報時，他們存放在msdb中。model是個模框，用於所有用戶生成的數據庫。生成新數據庫時，將model復制，建立所要的對象。tempdb保存SQL Server中的臨時對象。顯示生成的臨時表和臨時存儲過程以及系統生成的臨時對象都利用tempdb。[1] 而且每個數據庫中都有自己的系統表。這些系統表被用來保存配置和對象信息。從這些系統表中，我們就可以得到每個存儲過程的所有參數的信息。syscolumns表中就保存了這些信息。其中有參數名、類型、長度、方向等需要用到我們方法中的信息。 不過，系統表中的字段會隨著SQL Server版本的變化而變化。比如syscolumns中的type和xtype就是這樣的一個變化例子，他們都保存了類型的信息。要讓我們的方法適應SQL Server的版本變化要求，就要用到信息結構視圖。 ANSI-92將信息結構視圖定義為一組提供系統數據的視圖。通過利用該視圖，可以將實際系統表從應用程序中隱藏起來。系統表的改變就不會影響到應用程序，這樣應用程序就可以獨立於數據庫廠家和版本。[1] ANSI-92和SQL Server支持用三段命名結構引用本地服務器上的對象。ANSI-92術語稱為catalog.schema.object，而SQL Server稱為database.owner.object。[1]比如我們要找到所有存儲過程的所有參數信息，就可以用： select * from INFORMATION_SCHEMA.PARAMETERS 如果要找到某個存儲過程的所有參數信息，就是： select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME =’Proc1’ 有了信息結構視圖，我們的問題就解決了一大半了。下面我們看如何在.Net中實現我們的方法。
3．實現方法
實現的重點就放在如何根據存儲過程名來得到它的所有的參數信息，再根據這些參數信息自動的創建各個參數。為了讓這些動作自動化，聲明SqlConnection、SqlCommand、SqlParameter的過程，創建各個SqlParameter的過程對用戶來說都應該不可見。用戶唯一需要提供的就是存儲過程的名字，然後就是在調用的時候提供各個參數，甚至連他們的類型都不需要提供。
3．1獲得和創建存儲過程的參數
如何獲得並且創建要調用的存儲過程的參數是一個重點，通過信息結構視圖我們可以自動的實現這個步驟。
// 獲得和創建存儲過程的參數
private void GetProcedureParameter(params object[] parameters)
{ SqlCommand myCommand2 = new SqlCommand();
myCommand2.Connection = this.myConnection;
myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + this.ProcedureName + "' order by ORDINAL_POSITION";
SqlDataReader reader = null; reader = myCommand2.ExecuteReader(); // 創建返回參數
myParameter = new SqlParameter();
myParameter.ParameterName = "@Value";
myParameter.SqlDbType = SqlDbType.Int;
myParameter.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(myParameter);
int i = 0; // 創建各個參數,在這個地方可以自動的創建SqlParameter的類型,值,方向等屬性
while(reader.Read())
{
myParameter = new SqlParameter();
myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();
myParameter.Direction = reader["PARAMETER_MODE"].ToString()=="IN"?ParameterDirection.Input:ParameterDirection.Output;
switch(reader["DATA_TYPE"].ToString()) {
case "int" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (int)parameters[i];
myParameter.SqlDbType = SqlDbType.Int;
break; //...省略了很多具體的類型處理
default : break; }
i++;
myCommand.Parameters.Add(myParameter);
}
}
3．2返回結果數據集、返回值、傳出參數集
創建好存儲過程的參數之後，我們就可以調用這個存儲過程了。由於在.Net中，常用的返回結果集的類為SqlDataReader和DataSet，而SqlDataReader必須在保持連接的狀態下才可以使用，DataSet卻不需要。在我們的實現中，連接應該在調用之後就斷開，因此采用DataSet來保存返回結果集。
public SqlResult Call(params object[] parameters){ // SqlResult是自己定義的用於保存結果數據集、返回值、傳出參數集的類 SqlResult result = new SqlResult(); // 根據需要定義自己的連接字符串
myConnection = new SqlConnection(ConnectionString);
myCommand = new SqlCommand(this.ProcedureName, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myConnection.Open(); // 獲得和創建存儲過程的參數,並且設置好值
GetProcedureParameter(parameters);
myAdapter.Fill(result.dataSet, "Table"); // 獲得存儲過程的傳出參數值和名字對,保存在一個Hashtable中 GetOutputValue(result); // 在這裡釋放各種資源,斷開連接
myAdapter.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
return result;}
4．進一步工作
雖然我們在這裡的實現是針對SQL Server數據庫，但是對於任何提供了信息結構視圖，符合ANSI-92標准，或者是提供了元數據的數據庫都可以使用這種方法來實現。我們把它封裝成一個SqlProcedure類，在需要的時候可以很簡單的就調用了存儲過程，減少了大量基本上是重復的代碼工作。 為了讓SqlProcedure類支持更過的數據類型，在GetProcedureParameter()方法中需要根據自己的需要來分析各個參數的類型、方向、長度、默認值等信息，然後來創建這個參數。基本上任何類型都是能夠實現的，甚至連image類型都可以采用這種方式創建。這樣這個類就可以很通用，在任何項目中都可以發揮作用。
參考文獻
[1] Ray Rankins, Paul Jensen, Paul Bertucci，SQL Server 2000實用全書，北京：電子工業出版社，2002
[2] MSDN Library January 2003, Microsoft Corporation.
作者簡介：劉志波（1979-），男，湖南新化人，碩士，主要研究方向：神經網絡與模式識別，辦公自動化信息系統
