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)開(kāi)發(fā)中，使用存儲(chǔ)過(guò)程是一個(gè)良好的習(xí)慣，不僅可以帶來(lái)臨時(shí)表、函數(shù)、游標(biāo)等特性，而且調(diào)試、升級(jí)、維護(hù)都變得方便。在存儲(chǔ)過(guò)程中能夠把數(shù)據(jù)經(jīng)過(guò)處理再返回，這樣能夠?qū)?shù)據(jù)提供更多的分析和控制。
在存儲(chǔ)過(guò)程的調(diào)用中，我們發(fā)現(xiàn)存儲(chǔ)過(guò)程的調(diào)用都幾乎是如下的模式：
1．聲明SqlConnection
2．聲明SqlCommand，并且設(shè)置其Connection屬性為剛聲明的SqlConnection實(shí)例，設(shè)置CommandName為存儲(chǔ)過(guò)程名，CommandType為存儲(chǔ)過(guò)程。
3．往剛聲明的SqlCommand實(shí)例的Parameters集合中添加所有的存儲(chǔ)過(guò)程調(diào)用需要的參數(shù)
4．呼叫SqlCommand的ExecuteReader()方法來(lái)得到存儲(chǔ)過(guò)程的返回行集
4．聲明SqlDataAdapter和DataSet，設(shè)置SqlDataAdapter的SelectCommand屬性為3中聲明的實(shí)例，再調(diào)用其Fill方法來(lái)把返回的行集填充到DataSet中
5．關(guān)閉SqlConnection對(duì)象
6．釋放聲明的各對(duì)象實(shí)例
（說(shuō)明：4指的是兩種數(shù)據(jù)提取方法）
在這個(gè)調(diào)用過(guò)程中，我們發(fā)現(xiàn)幾乎所有的存儲(chǔ)過(guò)程調(diào)用都是這個(gè)模式，之間的區(qū)別就在第2步中的存儲(chǔ)過(guò)程名不同和第3步中各個(gè)存儲(chǔ)過(guò)程調(diào)用使用的參數(shù)是不一樣的，他們有參數(shù)名字、方向、數(shù)據(jù)類(lèi)型、長(zhǎng)度等的區(qū)別。
那么，有沒(méi)有一種方法可以實(shí)現(xiàn)所有的存儲(chǔ)過(guò)程調(diào)用？即只需要提供存儲(chǔ)過(guò)程名，然后把參數(shù)值傳入調(diào)用方法即可實(shí)現(xiàn)存儲(chǔ)過(guò)程的調(diào)用，再用某些數(shù)據(jù)結(jié)構(gòu)來(lái)保存返回的行集、傳出參數(shù)值、過(guò)程返回值。經(jīng)過(guò)研究SQL Server的系統(tǒng)表，我們發(fā)現(xiàn)這個(gè)想法是切實(shí)可行的。
2．系統(tǒng)表與信息結(jié)構(gòu)視圖
SQL Server等關(guān)系型數(shù)據(jù)庫(kù)都將元數(shù)據(jù)以某種方式保存在數(shù)據(jù)庫(kù)中，在SQL Server中就是系統(tǒng)數(shù)據(jù)庫(kù)和系統(tǒng)表。安裝SQL Server后會(huì)自動(dòng)生成四個(gè)系統(tǒng)數(shù)據(jù)庫(kù)：master, model, msdb與tempdb。master數(shù)據(jù)庫(kù)是SQL Server中所有系統(tǒng)級(jí)信息的倉(cāng)庫(kù)。登錄賬號(hào)、配置設(shè)置、系統(tǒng)存儲(chǔ)過(guò)程和其他數(shù)據(jù)庫(kù)的存在性都記錄在master數(shù)據(jù)庫(kù)中。msdb數(shù)據(jù)庫(kù)保存SQL Server Agent的信息。定義作業(yè)、操作員和警報(bào)時(shí)，他們存放在msdb中。model是個(gè)?？?，用于所有用戶生成的數(shù)據(jù)庫(kù)。生成新數(shù)據(jù)庫(kù)時(shí)，將model復(fù)制，建立所要的對(duì)象。tempdb保存SQL Server中的臨時(shí)對(duì)象。顯示生成的臨時(shí)表和臨時(shí)存儲(chǔ)過(guò)程以及系統(tǒng)生成的臨時(shí)對(duì)象都利用tempdb。[1]
而且每個(gè)數(shù)據(jù)庫(kù)中都有自己的系統(tǒng)表。這些系統(tǒng)表被用來(lái)保存配置和對(duì)象信息。從這些系統(tǒng)表中，我們就可以得到每個(gè)存儲(chǔ)過(guò)程的所有參數(shù)的信息。syscolumns表中就保存了這些信息。其中有參數(shù)名、類(lèi)型、長(zhǎng)度、方向等需要用到我們方法中的信息。
不過(guò)，系統(tǒng)表中的字段會(huì)隨著SQL Server版本的變化而變化。比如syscolumns中的type和xtype就是這樣的一個(gè)變化例子，他們都保存了類(lèi)型的信息。要讓我們的方法適應(yīng)SQL Server的版本變化要求，就要用到信息結(jié)構(gòu)視圖。
ANSI-92將信息結(jié)構(gòu)視圖定義為一組提供系統(tǒng)數(shù)據(jù)的視圖。通過(guò)利用該視圖，可以將實(shí)際系統(tǒng)表從應(yīng)用程序中隱藏起來(lái)。系統(tǒng)表的改變就不會(huì)影響到應(yīng)用程序，這樣應(yīng)用程序就可以獨(dú)立于數(shù)據(jù)庫(kù)廠家和版本。[1]
ANSI-92和SQL Server支持用三段命名結(jié)構(gòu)引用本地服務(wù)器上的對(duì)象。ANSI-92術(shù)語(yǔ)稱為catalog.schema.object，而SQL Server稱為database.owner.object。[1]比如我們要找到所有存儲(chǔ)過(guò)程的所有參數(shù)信息，就可以用：
select * from INFORMATION_SCHEMA.PARAMETERS
如果要找到某個(gè)存儲(chǔ)過(guò)程的所有參數(shù)信息，就是：
select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME =’Proc1’
有了信息結(jié)構(gòu)視圖，我們的問(wèn)題就解決了一大半了。下面我們看如何在.NET中實(shí)現(xiàn)我們的方法。
3．實(shí)現(xiàn)方法
實(shí)現(xiàn)的重點(diǎn)就放在如何根據(jù)存儲(chǔ)過(guò)程名來(lái)得到它的所有的參數(shù)信息，再根據(jù)這些參數(shù)信息自動(dòng)的創(chuàng)建各個(gè)參數(shù)。為了讓這些動(dòng)作自動(dòng)化，聲明SqlConnection、SqlCommand、SqlParameter的過(guò)程，創(chuàng)建各個(gè)SqlParameter的過(guò)程對(duì)用戶來(lái)說(shuō)都應(yīng)該不可見(jiàn)。用戶唯一需要提供的就是存儲(chǔ)過(guò)程的名字，然后就是在調(diào)用的時(shí)候提供各個(gè)參數(shù)，甚至連他們的類(lèi)型都不需要提供。
3．1獲得和創(chuàng)建存儲(chǔ)過(guò)程的參數(shù)
如何獲得并且創(chuàng)建要調(diào)用的存儲(chǔ)過(guò)程的參數(shù)是一個(gè)重點(diǎn)，通過(guò)信息結(jié)構(gòu)視圖我們可以自動(dòng)的實(shí)現(xiàn)這個(gè)步驟。
// 獲得和創(chuàng)建存儲(chǔ)過(guò)程的參數(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)建各個(gè)參數(shù),在這個(gè)地方可以自動(dòng)的創(chuàng)建SqlParameter的類(lèi)型,值,方向等屬性 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; //...省略了很多具體的類(lèi)型處理 default : break; } i++; myCommand.Parameters.Add(myParameter); } }
3．2返回結(jié)果數(shù)據(jù)集、返回值、傳出參數(shù)集
創(chuàng)建好存儲(chǔ)過(guò)程的參數(shù)之后，我們就可以調(diào)用這個(gè)存儲(chǔ)過(guò)程了。由于在.NET中，常用的返回結(jié)果集的類(lèi)為SqlDataReader和DataSet，而SqlDataReader必須在保持連接的狀態(tài)下才可以使用，DataSet卻不需要。在我們的實(shí)現(xiàn)中，連接應(yīng)該在調(diào)用之后就斷開(kāi)，因此采用DataSet來(lái)保存返回結(jié)果集。
public SqlResult Call(params object[] parameters){ // SqlResult是自己定義的用于保存結(jié)果數(shù)據(jù)集、返回值、傳出參數(shù)集的類(lèi) 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)建存儲(chǔ)過(guò)程的參數(shù),并且設(shè)置好值 GetProcedureParameter(parameters); myAdapter.Fill(result.dataSet, "Table"); // 獲得存儲(chǔ)過(guò)程的傳出參數(shù)值和名字對(duì),保存在一個(gè)Hashtable中 GetOutputValue(result); // 在這里釋放各種資源,斷開(kāi)連接 myAdapter.Dispose(); myCommand.Dispose(); myConnection.Close(); myConnection.Dispose(); return result;}
4.進(jìn)一步工作
雖然我們?cè)谶@里的實(shí)現(xiàn)是針對(duì)SQL Server數(shù)據(jù)庫(kù)，但是對(duì)于任何提供了信息結(jié)構(gòu)視圖，符合ANSI-92標(biāo)準(zhǔn)，或者是提供了元數(shù)據(jù)的數(shù)據(jù)庫(kù)都可以使用這種方法來(lái)實(shí)現(xiàn)。我們把它封裝成一個(gè)SqlProcedure類(lèi)，在需要的時(shí)候可以很簡(jiǎn)單的就調(diào)用了存儲(chǔ)過(guò)程，減少了大量基本上是重復(fù)的代碼工作。
為了讓SqlProcedure類(lèi)支持更過(guò)的數(shù)據(jù)類(lèi)型，在GetProcedureParameter()方法中需要根據(jù)自己的需要來(lái)分析各個(gè)參數(shù)的類(lèi)型、方向、長(zhǎng)度、默認(rèn)值等信息，然后來(lái)創(chuàng)建這個(gè)參數(shù)?；旧先魏晤?lèi)型都是能夠?qū)崿F(xiàn)的，甚至連image類(lèi)型都可以采用這種方式創(chuàng)建。這樣這個(gè)類(lèi)就可以很通用，在任何項(xiàng)目中都可以發(fā)揮作用。
作者簡(jiǎn)介：
劉志波（1979-），男，湖南新化人，碩士，主要研究方向：神經(jīng)網(wǎng)絡(luò)與模式識(shí)別，辦公自動(dòng)化信息系統(tǒng)
email:jasper_liu@msn.com
聯(lián)系客服
