net中統一的預存程序呼叫方法

作者：Eve Cole 更新時間：2009-07-01 16:45:17

聲明：該文已發表在《電腦應用》第23卷第11期上

摘要：在各種系統開發中，使用預存程序是一個良好的習慣，不僅可以帶來臨時表、函數、遊標等特性，而且調試、升級、維護都變得方便。可是，幾乎所有的預存程序的呼叫都是一種模式，主要差異也就是每個預存程序的參數不同。那麼，可不可以採用一種方法來統一所有的預存程序調用，減少不必要的編程呢？在研究了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 mal to store store ncing from it too. 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 Server and .NET. 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 paramers' type value al. 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 youvidicle. To call a stored procedure, the only parameters youvide procureed of the store the value of all parameters of the stored procedure.

1．引言

在各種系統開發中，使用預存程序是一個良好的習慣，不僅可以帶來臨時表、函數、遊標等特性，而且調試、升級、維護都變得方便。在儲存過程中能夠把資料經過處理再返回，這樣能夠對資料提供更多的分析和控制。在預存程序的呼叫中，我們發現預存程序的呼叫都幾乎是如下的模式：

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_S. '" + this.ProcedureName + "' order by ORDINAL_POSITION";

SqlDataReader reader = null; reader = myCommand2.ExecuteReader(); // 建立回傳參數

myParameter = new SqlParameter();

myParameter.ParameterName = "@Parameter";

Parameter.Parameter.Parameter. 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.. ;

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); SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand); myConnection

);預存程序的參數,並且設定好值

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-），男，湖南新化人，碩士，主要研究方向：神經網路與模式識別，辦公室自動化資訊系統
email:[email protected]