調(diào)用存儲(chǔ)過程

1、.NET中統(tǒng)一的存儲(chǔ)過程調(diào)用方法(收藏)摘要：在一個(gè)項(xiàng)目的開發(fā)中，經(jīng)常會(huì)調(diào)用數(shù)據(jù)庫(kù)中的存儲(chǔ)過程?？墒?，幾乎所有存儲(chǔ)過程的調(diào)用都是同一個(gè)模式，主要區(qū)別就在于創(chuàng)建的每個(gè)參數(shù)類型、值等不一樣。那么，能不能實(shí)現(xiàn)通過一個(gè)函數(shù)（或者類）調(diào)用所有的存儲(chǔ)過程呢？本文在利用數(shù)據(jù)庫(kù)提供的系統(tǒng)表原理上，實(shí)現(xiàn)了統(tǒng)一調(diào)用的方法，該方法只需要提供要調(diào)用的存儲(chǔ)過程名，以及調(diào)用時(shí)提供具體的參數(shù)值就可實(shí)現(xiàn)任何存儲(chǔ)過程的調(diào)用。  Abstract: We have to call stored procedures of database systems during a development of a projec

2、t. 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 pro

3、cedures 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ā)中，使用存儲(chǔ)過程是一個(gè)良好的習(xí)慣，不僅可以帶來(lái)臨時(shí)表、函數(shù)、游標(biāo)等特性，而且調(diào)試、升級(jí)、維護(hù)都變得方便。在存儲(chǔ)過程中能夠把數(shù)據(jù)經(jīng)過處理再返回，這樣能

4、夠?qū)?shù)據(jù)提供更多的分析和控制。在存儲(chǔ)過程的調(diào)用中，我們發(fā)現(xiàn)存儲(chǔ)過程的調(diào)用都幾乎是如下的模式：1聲明SqlConnection2聲明SqlCommand，并且設(shè)置其Connection屬性為剛聲明的SqlConnection實(shí)例，設(shè)置CommandName為存儲(chǔ)過程名，CommandType為存儲(chǔ)過程。3往剛聲明的SqlCommand實(shí)例的Parameters集合中添加所有的存儲(chǔ)過程調(diào)用需要的參數(shù) 4呼叫SqlCommand的ExecuteReader()方法來(lái)得到存儲(chǔ)過程的返回行集 4聲明SqlDataAdapter和DataSet，設(shè)置SqlDataAdapter的Sel

5、ectCommand屬性為3中聲明的實(shí)例，再調(diào)用其Fill方法來(lái)把返回的行集填充到DataSet中 5關(guān)閉SqlConnection對(duì)象6釋放聲明的各對(duì)象實(shí)例（說明：4指的是兩種數(shù)據(jù)提取方法）在這個(gè)調(diào)用過程中，我們發(fā)現(xiàn)幾乎所有的存儲(chǔ)過程調(diào)用都是這個(gè)模式，之間的區(qū)別就在第2步中的存儲(chǔ)過程名不同和第3步中各個(gè)存儲(chǔ)過程調(diào)用使用的參數(shù)是不一樣的，他們有參數(shù)名字、方向、數(shù)據(jù)類型、長(zhǎng)度等的區(qū)別。那么，有沒有一種方法可以實(shí)現(xiàn)所有的存儲(chǔ)過程調(diào)用？即只需要提供存儲(chǔ)過程名，然后把參數(shù)值傳入調(diào)用方法即可實(shí)現(xiàn)存儲(chǔ)過程的調(diào)用，再用某些數(shù)據(jù)結(jié)構(gòu)來(lái)保存返回的行集、傳出參數(shù)值、過程返回值。經(jīng)過研究SQL Serve

6、r的系統(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ǔ)過程和其他數(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í)

7、，將model復(fù)制，建立所要的對(duì)象。tempdb保存SQL Server中的臨時(shí)對(duì)象。顯示生成的臨時(shí)表和臨時(shí)存儲(chǔ)過程以及系統(tǒng)生成的臨時(shí)對(duì)象都利用tempdb。1而且每個(gè)數(shù)據(jù)庫(kù)中都有自己的系統(tǒng)表。這些系統(tǒng)表被用來(lái)保存配置和對(duì)象信息。從這些系統(tǒng)表中，我們就可以得到每個(gè)存儲(chǔ)過程的所有參數(shù)的信息。syscolumns表中就保存了這些信息。其中有參數(shù)名、類型、長(zhǎng)度、方向等需要用到我們方法中的信息。不過，系統(tǒng)表中的字段會(huì)隨著SQL Server版本的變化而變化。比如syscolumns中的type和xtype就是這樣的一個(gè)變化例子，他們都保存了類型的信息。要讓我們的方法適應(yīng)SQL Server的版本變化要

8、求，就要用到信息結(jié)構(gòu)視圖。ANSI-92將信息結(jié)構(gòu)視圖定義為一組提供系統(tǒng)數(shù)據(jù)的視圖。通過利用該視圖，可以將實(shí)際系統(tǒng)表從應(yīng)用程序中隱藏起來(lái)。系統(tǒng)表的改變就不會(huì)影響到應(yīng)用程序，這樣應(yīng)用程序就可以獨(dú)立于數(shù)據(jù)庫(kù)廠家和版本。1ANSI-92和SQL Server支持用三段命名結(jié)構(gòu)引用本地服務(wù)器上的對(duì)象。ANSI-92術(shù)語(yǔ)稱為，而SQL Server稱為。1比如我們要找到所有存儲(chǔ)過程的所有參數(shù)信息，就可以用：select * from INFORMATION_SCHEMA.PARAMETERS如果要找到某個(gè)存儲(chǔ)過程的所有參數(shù)信息，就是：select * from INFORMATION_SCHEMA.PA

9、RAMETERS where SPECIFIC_NAME =Proc1有了信息結(jié)構(gòu)視圖，我們的問題就解決了一大半了。下面我們看如何在.NET中實(shí)現(xiàn)我們的方法。 3實(shí)現(xiàn)方法實(shí)現(xiàn)的重點(diǎn)就放在如何根據(jù)存儲(chǔ)過程名來(lái)得到它的所有的參數(shù)信息，再根據(jù)這些參數(shù)信息自動(dòng)的創(chuàng)建各個(gè)參數(shù)。為了讓這些動(dòng)作自動(dòng)化，聲明SqlConnection、SqlCommand、SqlParameter的過程，創(chuàng)建各個(gè)SqlParameter的過程對(duì)用戶來(lái)說都應(yīng)該不可見。用戶唯一需要提供的就是存儲(chǔ)過程的名字，然后就是在調(diào)用的時(shí)候提供各個(gè)參數(shù)，甚至連他們的類型都不需要提供。31獲得和創(chuàng)建存儲(chǔ)過程的參數(shù)如何獲得并且創(chuàng)建要調(diào)用

10、的存儲(chǔ)過程的參數(shù)是一個(gè)重點(diǎn)，通過信息結(jié)構(gòu)視圖我們可以自動(dòng)的實(shí)現(xiàn)這個(gè)步驟。/ 獲得和創(chuàng)建存儲(chǔ)過程的參數(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='&

11、quot; +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 = Parame

12、terDirection.ReturnValue;  int i = 0;/ 創(chuàng)建各個(gè)參數(shù),在這個(gè)地方可以自動(dòng)的創(chuàng)建SqlParameter的類型,值,方向等屬性while(reader.Read()myParameter = new SqlParameter(); myParameter.ParameterName = reader"PARAMETER_NAME".ToString();myParameter.Direction = reader"PARAMETER_MODE".ToString()="IN&qu

13、ot;?ParameterDirection.Input:ParameterDirection.Output; switch(reader"DATA_TYPE".ToString()case "int" :if(myParameter.Direction = ParameterDirection.Input)myParameter.Value = (int)parametersi;myParameter.SqlDbType = SqlDbType.Int;break;/.省略了很多具體的類型處理default : break;i+; 

14、     32返回結(jié)果數(shù)據(jù)集、返回值、傳出參數(shù)集創(chuàng)建好存儲(chǔ)過程的參數(shù)之后，我們就可以調(diào)用這個(gè)存儲(chǔ)過程了。由于在.NET中，常用的返回結(jié)果集的類為SqlDataReader和DataSet，而SqlDataReader必須在保持連接的狀態(tài)下才可以使用，DataSet卻不需要。在我們的實(shí)現(xiàn)中，連接應(yīng)該在調(diào)用之后就斷開，因此采用DataSet來(lái)保存返回結(jié)果集。public SqlResult Call(params object parameters)/ SqlResult是自己定義的用于保存結(jié)果數(shù)據(jù)集、返回值、傳出參數(shù)集的類SqlResult re

15、sult = new SqlResult(); / 根據(jù)需要定義自己的連接字符串myConnection = new SqlConnection(ConnectionString); myCommand = new SqlCommand(this.ProcedureName, myConnection);myCommand.CommandType = CommandType.StoredProcedure; SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand); myConnection.Ope

16、n();/ 獲得和創(chuàng)建存儲(chǔ)過程的參數(shù),并且設(shè)置好值GetProcedureParameter(parameters); myAdapter.Fill(result.dataSet, "Table");/ 獲得存儲(chǔ)過程的傳出參數(shù)值和名字對(duì),保存在一個(gè)Hashtable中GetOutputValue(result); / 在這里釋放各種資源,斷開連接myAdapter.Dispose();myCommand.Dispose();myConnection.Close();myConnection.Dispose();  return res

17、ult;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類，在需要的時(shí)候可以很簡(jiǎn)單的就調(diào)用了存儲(chǔ)過程，減少了大量基本上是重復(fù)的代碼工作。為了讓SqlProcedure類支持更過的數(shù)據(jù)類型，在GetProcedureParameter()方法中需要根據(jù)自己的需要來(lái)分析各個(gè)參數(shù)的類型、方向、長(zhǎng)度、默認(rèn)值等信息，然后來(lái)創(chuàng)建這個(gè)參數(shù)。基本上任何類型都是能夠?qū)崿F(xiàn)的，甚至連image類型都可以采用這種方式創(chuàng)建。這樣這個(gè)類就可以很通用，在任

18、何項(xiàng)目中都可以發(fā)揮作用。 在ASP.NET中調(diào)用存儲(chǔ)過程方法新解2005-11-14 08:29作者：朱江艷出處：計(jì)算機(jī)與信息技術(shù)責(zé)任編輯：方舟摘要 存儲(chǔ)過程的調(diào)用在B/S系統(tǒng)中用的很多。傳統(tǒng)的調(diào)用方法不僅速度慢，而且代碼會(huì)隨著存儲(chǔ)過程的增多不斷膨脹，難以維護(hù)。新的方法在一定程度上解決了這些問題。關(guān)鍵詞 ASP.NET；存儲(chǔ)過程 在使用.NET的過程中，數(shù)據(jù)庫(kù)訪問是一個(gè)很重要的部分，特別是在B/S系統(tǒng)的構(gòu)建過程中，數(shù)據(jù)庫(kù)操作幾乎成為了一個(gè)必不可少的操作。調(diào)用存儲(chǔ)過程實(shí)現(xiàn)數(shù)據(jù)庫(kù)操作使很多程序員使用的方法，而且大多數(shù)的程序員都是能使用存儲(chǔ)過程就使用存儲(chǔ)過程，很少直接使用SQL語(yǔ)句，所以存

19、儲(chǔ)過程是很有用而且很重要的。存儲(chǔ)過程簡(jiǎn)介簡(jiǎn)單的說，存儲(chǔ)過程是由一些SQL語(yǔ)句和控制語(yǔ)句組成的被封裝起來(lái)的過程，它駐留在數(shù)據(jù)庫(kù)中，可以被客戶應(yīng)用程序調(diào)用，也可以從另一個(gè)過程或觸發(fā)器調(diào)用。它的參數(shù)可以被傳遞和返回。與應(yīng)用程序中的函數(shù)過程類似，存儲(chǔ)過程可以通過名字來(lái)調(diào)用，而且它們同樣有輸入?yún)?shù)和輸出參數(shù)。根據(jù)返回值類型的不同，我們可以將存儲(chǔ)過程分為三類：返回記錄集的存儲(chǔ)過程， 返回?cái)?shù)值的存儲(chǔ)過程（也可以稱為標(biāo)量存儲(chǔ)過程），以及行為存儲(chǔ)過程。顧名思義，返回記錄集的存儲(chǔ)過程的執(zhí)行結(jié)果是一個(gè)記錄集，典型的例子是從數(shù)據(jù)庫(kù)中檢索出符合某一個(gè)或幾個(gè)條件的記錄；返回?cái)?shù)值的存儲(chǔ)過程執(zhí)行完以后返回一個(gè)值，例如在數(shù)據(jù)庫(kù)

20、中執(zhí)行一個(gè)有返回值的函數(shù)或命令；最后，行為存儲(chǔ)過程僅僅是用來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)的某個(gè)功能，而沒有返回值，例如在數(shù)據(jù)庫(kù)中的更新和刪除操作。使用存儲(chǔ)過程的好處相對(duì)于直接使用SQL語(yǔ)句，在應(yīng)用程序中直接調(diào)用存儲(chǔ)過程有以下好處：(1)減少網(wǎng)絡(luò)通信量。調(diào)用一個(gè)行數(shù)不多的存儲(chǔ)過程與直接調(diào)用SQL語(yǔ)句的網(wǎng)絡(luò)通信量可能不會(huì)有很大的差別，可是如果存儲(chǔ)過程包含上百行SQL語(yǔ)句，那么其性能絕對(duì)比一條一條的調(diào)用SQL語(yǔ)句要高得多。(2)執(zhí)行速度更快。有兩個(gè)原因：首先，在存儲(chǔ)過程創(chuàng)建的時(shí)候，數(shù)據(jù)庫(kù)已經(jīng)對(duì)其進(jìn)行了一次解析和優(yōu)化。其次，存儲(chǔ)過程一旦執(zhí)行，在內(nèi)存中就會(huì)保留一份這個(gè)存儲(chǔ)過程，這樣下次再執(zhí)行同樣的存儲(chǔ)過程時(shí)，可以從內(nèi)存中

21、直接調(diào)用。(3)更強(qiáng)的適應(yīng)性：由于存儲(chǔ)過程對(duì)數(shù)據(jù)庫(kù)的訪問是通過存儲(chǔ)過程來(lái)進(jìn)行的，因此數(shù)據(jù)庫(kù)開發(fā)人員可以在不改動(dòng)存儲(chǔ)過程接口的情況下對(duì)數(shù)據(jù)庫(kù)進(jìn)行任何改動(dòng)，而這些改動(dòng)不會(huì)對(duì)應(yīng)用程序造成影響。(4) 布式工作：應(yīng)用程序和數(shù)據(jù)庫(kù)的編碼工作可以分別獨(dú)立進(jìn)行，而不會(huì)相互壓制。由以上的分析可以看到，在應(yīng)用程序中使用存儲(chǔ)過程是很有必要的。兩種不同的存儲(chǔ)過程調(diào)用方法SqlConnection conn=new SqlConnection(“connectionString”);SqlDataAdapter da = new SqlDataAdapter();da.SelectCommand = new SqlC

22、ommand();da.SelectCommand.Connection = conn;da.SelectCommand.CommandText = "NameOfProcedure"da.SelectCommand.CommandType = CommandType.StoredProcedure;然后只要選擇適當(dāng)?shù)姆绞綀?zhí)行此處過程，用于不同的目的即可。(2)執(zhí)行一個(gè)有參數(shù)的存儲(chǔ)過程的代碼如下（我們可以將調(diào)用存儲(chǔ)過程的函數(shù)聲明為ExeProcedure(string inputdate)）：SqlConnection conn=new SqlConnection(“con

23、nectionString”);SqlDataAdapter da = new SqlDataAdapter();da.SelectCommand = new SqlCommand();da.SelectCommand.Connection = conn;da.SelectCommand.CommandText = "NameOfProcedure"da.SelectCommand.CommandType = CommandType.StoredProcedure;（以上代碼相同，以下為要添加的代碼）param = new SqlParameter("Parame

24、terName", SqlDbType.DateTime);param.Direction = ParameterDirection.Input;param.Value = Convert.ToDateTime(inputdate);da.SelectCommand.Parameters.Add(param);這樣就添加了一個(gè)輸入?yún)?shù)。若需要添加輸出參數(shù)：param = new SqlParameter("ParameterName", SqlDbType.DateTime);param.Direction = ParameterDirection.Output;

25、param.Value = Convert.ToDateTime(inputdate);da.SelectCommand.Parameters.Add(param);若要獲得參儲(chǔ)過程的返回值：param = new SqlParameter("ParameterName", SqlDbType.DateTime);param.Direction = ParameterDirection.ReturnValue;param.Value = Convert.ToDateTime(inputdate);da.SelectCommand.Parameters.Add(param);從上面的代碼我們可以看出，當(dāng)存儲(chǔ)過程比較多或者存儲(chǔ)過程的參數(shù)比較多時(shí)，這種方法會(huì)大大影響開發(fā)的速度；