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.

1.       引言

在各種系統(tǒng)開發(fā)中，使用存儲過程是一個良好的習慣，不僅可以帶來臨時表、函數(shù)、游標等特性，而且調(diào)試、升級、維護都變得方便。在存儲過程中能夠把數(shù)據(jù)經(jīng)過處理再返回，這樣能夠?qū)?shù)據(jù)提供更多的分析和控制。

在存儲過程的調(diào)用中，我們發(fā)現(xiàn)存儲過程的調(diào)用都幾乎是如下的模式：

1．聲明SqlConnection

2．聲明SqlCommand，并且設置其Connection屬性為剛聲明的SqlConnection實例，設置CommandName為存儲過程名，CommandType為存儲過程。

3．往剛聲明的SqlCommand實例的Parameters集合中添加所有的存儲過程調(diào)用需要的參數(shù)

4．呼叫SqlCommand的ExecuteReader()方法來得到存儲過程的返回行集

4．聲明SqlDataAdapter和DataSet，設置SqlDataAdapter的SelectCommand屬性為3中聲明的實例，再調(diào)用其Fill方法來把返回的行集填充到DataSet中

5．關閉SqlConnection對象

6．釋放聲明的各對象實例

（說明：4指的是兩種數(shù)據(jù)提取方法）

在這個調(diào)用過程中，我們發(fā)現(xiàn)幾乎所有的存儲過程調(diào)用都是這個模式，之間的區(qū)別就在第2步中的存儲過程名不同和第3步中各個存儲過程調(diào)用使用的參數(shù)是不一樣的，他們有參數(shù)名字、方向、數(shù)據(jù)類型、長度等的區(qū)別。

那么，有沒有一種方法可以實現(xiàn)所有的存儲過程調(diào)用？即只需要提供存儲過程名，然后把參數(shù)值傳入調(diào)用方法即可實現(xiàn)存儲過程的調(diào)用，再用某些數(shù)據(jù)結(jié)構來保存返回的行集、傳出參數(shù)值、過程返回值。經(jīng)過研究SQL Server的系統(tǒng)表，我們發(fā)現(xiàn)這個想法是切實可行的。

2．系統(tǒng)表與信息結(jié)構視圖

       SQL Server等關系型數(shù)據(jù)庫都將元數(shù)據(jù)以某種方式保存在數(shù)據(jù)庫中，在SQL Server中就是系統(tǒng)數(shù)據(jù)庫和系統(tǒng)表。安裝SQL Server后會自動生成四個系統(tǒng)數(shù)據(jù)庫：master, model, msdb與tempdb。master數(shù)據(jù)庫是SQL Server中所有系統(tǒng)級信息的倉庫。登錄賬號、配置設置、系統(tǒng)存儲過程和其他數(shù)據(jù)庫的存在性都記錄在master數(shù)據(jù)庫中。msdb數(shù)據(jù)庫保存SQL Server Agent的信息。定義作業(yè)、操作員和警報時，他們存放在msdb中。model是個?？?，用于所有用戶生成的數(shù)據(jù)庫。生成新數(shù)據(jù)庫時，將model復制，建立所要的對象。tempdb保存SQL Server中的臨時對象。顯示生成的臨時表和臨時存儲過程以及系統(tǒng)生成的臨時對象都利用tempdb。[1]

       而且每個數(shù)據(jù)庫中都有自己的系統(tǒng)表。這些系統(tǒng)表被用來保存配置和對象信息。從這些系統(tǒng)表中，我們就可以得到每個存儲過程的所有參數(shù)的信息。syscolumns表中就保存了這些信息。其中有參數(shù)名、類型、長度、方向等需要用到我們方法中的信息。

       不過，系統(tǒng)表中的字段會隨著SQL Server版本的變化而變化。比如syscolumns中的type和xtype就是這樣的一個變化例子，他們都保存了類型的信息。要讓我們的方法適應SQL Server的版本變化要求，就要用到信息結(jié)構視圖。

       ANSI-92將信息結(jié)構視圖定義為一組提供系統(tǒng)數(shù)據(jù)的視圖。通過利用該視圖，可以將實際系統(tǒng)表從應用程序中隱藏起來。系統(tǒng)表的改變就不會影響到應用程序，這樣應用程序就可以獨立于數(shù)據(jù)庫廠家和版本。[1]

       ANSI-92和SQL Server支持用三段命名結(jié)構引用本地服務器上的對象。ANSI-92術語稱為catalog.schema.object，而SQL Server稱為database.owner.object。[1]比如我們要找到所有存儲過程的所有參數(shù)信息，就可以用：

       select * from INFORMATION_SCHEMA.PARAMETERS

如果要找到某個存儲過程的所有參數(shù)信息，就是：

       select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME =’Proc1’

有了信息結(jié)構視圖，我們的問題就解決了一大半了。下面我們看如何在.NET中實現(xiàn)我們的方法。

3．實現(xiàn)方法

實現(xiàn)的重點就放在如何根據(jù)存儲過程名來得到它的所有的參數(shù)信息，再根據(jù)這些參數(shù)信息自動的創(chuàng)建各個參數(shù)。為了讓這些動作自動化，聲明SqlConnection、SqlCommand、SqlParameter的過程，創(chuàng)建各個SqlParameter的過程對用戶來說都應該不可見。用戶唯一需要提供的就是存儲過程的名字，然后就是在調(diào)用的時候提供各個參數(shù)，甚至連他們的類型都不需要提供。

3．1獲得和創(chuàng)建存儲過程的參數(shù)

如何獲得并且創(chuàng)建要調(diào)用的存儲過程的參數(shù)是一個重點，通過信息結(jié)構視圖我們可以自動的實現(xiàn)這個步驟。

// 獲得和創(chuàng)建存儲過程的參數(shù)

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();

                // 創(chuàng)建返回參數(shù)

                myParameter = new SqlParameter();

                myParameter.ParameterName = "@Value";

                myParameter.SqlDbType = SqlDbType.Int;

                myParameter.Direction = ParameterDirection.ReturnValue;

                myCommand.Parameters.Add(myParameter);

                int i = 0;

                // 創(chuàng)建各個參數(shù),在這個地方可以自動的創(chuàng)建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返回結(jié)果數(shù)據(jù)集、返回值、傳出參數(shù)集

創(chuàng)建好存儲過程的參數(shù)之后，我們就可以調(diào)用這個存儲過程了。由于在.NET中，常用的返回結(jié)果集的類為SqlDataReader和DataSet，而SqlDataReader必須在保持連接的狀態(tài)下才可以使用，DataSet卻不需要。在我們的實現(xiàn)中，連接應該在調(diào)用之后就斷開，因此采用DataSet來保存返回結(jié)果集。

public SqlResult Call(params object[] parameters)

{

        // SqlResult是自己定義的用于保存結(jié)果數(shù)據(jù)集、返回值、傳出參數(shù)集的類

        SqlResult result = new SqlResult();

        // 根據(jù)需要定義自己的連接字符串

        myConnection  = new SqlConnection(ConnectionString);

        myCommand = new SqlCommand(this.ProcedureName, myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

        myConnection.Open();

        // 獲得和創(chuàng)建存儲過程的參數(shù),并且設置好值

        GetProcedureParameter(parameters);

        myAdapter.Fill(result.dataSet, "Table");

        // 獲得存儲過程的傳出參數(shù)值和名字對,保存在一個Hashtable中

        GetOutputValue(result);

        // 在這里釋放各種資源,斷開連接

        myAdapter.Dispose();

        myCommand.Dispose();

        myConnection.Close();

        myConnection.Dispose();

        return result;

}

4.進一步工作

       雖然我們在這里的實現(xiàn)是針對SQL Server數(shù)據(jù)庫，但是對于任何提供了信息結(jié)構視圖，符合ANSI-92標準，或者是提供了元數(shù)據(jù)的數(shù)據(jù)庫都可以使用這種方法來實現(xiàn)。我們把它封裝成一個SqlProcedure類，在需要的時候可以很簡單的就調(diào)用了存儲過程，減少了大量基本上是重復的代碼工作。

       為了讓SqlProcedure類支持更過的數(shù)據(jù)類型，在GetProcedureParameter()方法中需要根據(jù)自己的需要來分析各個參數(shù)的類型、方向、長度、默認值等信息，然后來創(chuàng)建這個參數(shù)?；旧先魏晤愋投际悄軌?qū)崿F(xiàn)的，甚至連image類型都可以采用這種方式創(chuàng)建。這樣這個類就可以很通用，在任何項目中都可以發(fā)揮作用。

作者簡介：

劉志波（1979-），男，湖南新化人，碩士，主要研究方向：神經(jīng)網(wǎng)絡與模式識別，辦公自動化信息系統(tǒng)

email:jasper_liu@msn.com