SQL存儲(chǔ)過(guò)程在NET數(shù)據(jù)庫(kù)中的運(yùn)用

1、一前言： 存儲(chǔ)過(guò)程（Stored Procedure）是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（假如該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象，任何一個(gè)設(shè)計(jì)良好的數(shù)據(jù)庫(kù)應(yīng)用程序都應(yīng)該用到存儲(chǔ)過(guò)程?？偟膩?lái)講，存儲(chǔ)過(guò)程具有以下一些優(yōu)點(diǎn)： 存儲(chǔ)過(guò)程同意標(biāo)準(zhǔn)組件式編程 存儲(chǔ)過(guò)程能夠?qū)崿F(xiàn)較快的執(zhí)行速度 存儲(chǔ)過(guò)程能夠減少網(wǎng)絡(luò)流量 存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用 本文作者將向大伙兒介紹.NET數(shù)據(jù)庫(kù)應(yīng)用程序中存儲(chǔ)過(guò)程的應(yīng)用，以及如何將它與ADO.NET中的SqlDataAdapter對(duì)象、DataSet對(duì)象等結(jié)合使用以提高

2、.NET數(shù)據(jù)庫(kù)應(yīng)用程序的總體性能。 二系統(tǒng)要求： 開(kāi)發(fā)工具：Visual Studio.NET 數(shù)據(jù)庫(kù)治理系統(tǒng)：SQL Server 2000（其中包含了示例程序所用到的Pubs數(shù)據(jù)庫(kù)） 三創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程： 那個(gè)地點(diǎn)我將向大伙兒介紹如何運(yùn)用Visual Studio.NET IDE來(lái)創(chuàng)建存儲(chǔ)過(guò)程。運(yùn)用Visual Studio.NET IDE創(chuàng)建存儲(chǔ)過(guò)程是特不容易和直觀的，你只要在服務(wù)器資源治理器中導(dǎo)向到Pubs數(shù)據(jù)庫(kù)并展開(kāi)節(jié)點(diǎn)，就會(huì)發(fā)覺(jué)包括存儲(chǔ)過(guò)程在內(nèi)的各種數(shù)據(jù)庫(kù)對(duì)象，如圖1所示。在存儲(chǔ)過(guò)程節(jié)點(diǎn)上點(diǎn)擊右鍵便可彈出一個(gè)菜單，其中包含了“新建存儲(chǔ)過(guò)程”的命令。新建一個(gè)存儲(chǔ)過(guò)程后，IDE中

3、的代碼編輯窗口便出現(xiàn)如下所示的代碼模板： CREATE PROCEDURE dbo.StoredProcedure1 /* ( parameter1 datatype = default value, parameter2 datatype OUTPUT )*/ AS /* SET NOCOUNT ON */ RETURN 上面的代碼模板符合簡(jiǎn)化的創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法規(guī)則，完整的語(yǔ)法規(guī)則如下： CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMP

4、ILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 限于篇幅，各個(gè)參數(shù)的含義在此就不多作介紹了，有興趣的讀者能夠參考有關(guān)SQL Server 2000數(shù)據(jù)庫(kù)治理系統(tǒng)的資料。 下面我對(duì)該代碼模板中的各個(gè)語(yǔ)法成分略作介紹。CREATE PROCEDURE聲明創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，后面跟著該存儲(chǔ)過(guò)程的名稱?！?*/”中的成分是該存儲(chǔ)過(guò)程的參數(shù)，可包括輸入?yún)?shù)和輸出參數(shù)。AS關(guān)鍵字后面的內(nèi)容是該存儲(chǔ)過(guò)程的主體部分，其中是任何數(shù)量和類型的包含在存儲(chǔ)過(guò)程中的SQL語(yǔ)句。RETURN關(guān)鍵字表明存儲(chǔ)過(guò)程結(jié)束并能

5、返回整型狀態(tài)值給調(diào)用者。下面我們就來(lái)創(chuàng)建一個(gè)簡(jiǎn)單的不帶參數(shù)的存儲(chǔ)過(guò)程并運(yùn)用之： CREATE PROCEDURE dbo.up_GetPublisherInfoAS SELECT pub_id, pub_name, city, state, countryFROM publishersRETURN 創(chuàng)建以上存儲(chǔ)過(guò)程后，保存之。保存完畢，與該存儲(chǔ)過(guò)程相對(duì)應(yīng)的節(jié)點(diǎn)就會(huì)出現(xiàn)在服務(wù)器資源治理器中。同時(shí)請(qǐng)注意代碼編輯窗口中的CREATE關(guān)鍵字變?yōu)锳LTER關(guān)鍵字了，該關(guān)鍵字是用于更改任何現(xiàn)有的存儲(chǔ)過(guò)程的。要運(yùn)行上述存儲(chǔ)過(guò)程，只要點(diǎn)擊其節(jié)點(diǎn)并在右鍵彈出菜單中選擇“運(yùn)行存儲(chǔ)過(guò)程”，運(yùn)行的結(jié)果圖示如下：四創(chuàng)建

6、一個(gè)帶參數(shù)的存儲(chǔ)過(guò)程： 以上我們創(chuàng)建了一個(gè)簡(jiǎn)單的不帶參數(shù)的存儲(chǔ)過(guò)程，而在實(shí)際的應(yīng)用中往往會(huì)用到專門(mén)多帶有參數(shù)的存儲(chǔ)過(guò)程。帶有參數(shù)的存儲(chǔ)過(guò)程一般是用于更新數(shù)據(jù)或是插入數(shù)據(jù)的。下面我們能夠運(yùn)用同樣的操作方法創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過(guò)程： CREATE PROCEDURE dbo.up_UpdatePublisherInfo (pub_id char (4), pub_name varchar (40),city varchar (20), state char (2),country varchar (30) ASUPDATE publishers SET pub_name = pub_name, c

7、ity = city, state = state, country = country WHERE ( pub_id = pub_id ) RETURN 在上面的創(chuàng)建存儲(chǔ)過(guò)程的代碼中，我們通過(guò)在名稱前添加一個(gè)“”標(biāo)志來(lái)聲明存儲(chǔ)過(guò)程的局部變量參數(shù)，同時(shí)還聲明了各個(gè)參數(shù)的類型，確定了各個(gè)參數(shù)的方向值，也即表明該參數(shù)是輸入型的依舊輸出型的或者是輸入輸出型的或者是返回值型的。用戶通過(guò)相應(yīng)的存儲(chǔ)過(guò)程名稱以及正確有效的參數(shù)便可調(diào)用該存儲(chǔ)過(guò)程了。還有，你能夠通過(guò)運(yùn)用OUTPUT關(guān)鍵字在參數(shù)中添加輸出型的參數(shù)，具體方法請(qǐng)參考上面的語(yǔ)法規(guī)則。輸出型的參數(shù)能返回給調(diào)用者相關(guān)的信息。 上面的存儲(chǔ)過(guò)程能更新publ

8、ishers表中相應(yīng)出版商的信息。你能夠通過(guò)點(diǎn)擊該存儲(chǔ)過(guò)程的節(jié)點(diǎn)，在右鍵彈出菜單中選擇“運(yùn)行存儲(chǔ)過(guò)程”來(lái)執(zhí)行它。一旦執(zhí)行，IDE中便彈出一個(gè)輸入出版商信息的對(duì)話框（如圖3所示）。在該對(duì)話框中填入正確有效的更新信息，注意pub_id的值在原來(lái)的表中必須存在，然后點(diǎn)擊“確定”按鈕便可更新數(shù)據(jù)了。五創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)應(yīng)用程序： 下面我們就運(yùn)用上述的不帶參數(shù)的存儲(chǔ)過(guò)程來(lái)一個(gè)數(shù)據(jù)庫(kù)應(yīng)用程序，其中還用到了ADO.NET中的SqlDataAdapter對(duì)象以及DataSet對(duì)象。其中的SqlDataAdapter對(duì)象作為SQL Server數(shù)據(jù)庫(kù)和DataSet對(duì)象的橋梁將兩者聯(lián)系在一起。SqlDat

9、aAdapter對(duì)象包含了兩個(gè)常用的方法：Fill（）方法和Update（）方法。其中的Fill（）方法能從數(shù)據(jù)庫(kù)中獵取相應(yīng)數(shù)據(jù)并填充到DataSet對(duì)象中，而Update（）方法顧名思義確實(shí)是更新數(shù)據(jù)集的意思了。在調(diào)用Fill（）方法往常，我們必須設(shè)置好SqlDataAdapter對(duì)象的SelectCommand屬性，該屬性事實(shí)上是一個(gè)SqlCommand對(duì)象。SelectCommand屬性中包含有效的SQL語(yǔ)句，并能據(jù)此從數(shù)據(jù)庫(kù)中獵取相應(yīng)數(shù)據(jù)并填充到DataSet對(duì)象中。 首先，我們創(chuàng)建一個(gè)Windows Forms應(yīng)用程序，編程語(yǔ)言為C#。在Visual Studio.NET中創(chuàng)建一個(gè)新

10、的項(xiàng)目后，給該項(xiàng)目添加一個(gè)新的類Publishers類，該類封裝了連接到后臺(tái)數(shù)據(jù)庫(kù)并獵取數(shù)據(jù)集對(duì)象的業(yè)務(wù)邏輯。步驟如下： 1添加必要的命名空間引用：using System.Data.SqlClient; 2給該類添加如下一些必要的變量： private SqlConnection cnPubs; private SqlCommand cmdPubs;private SqlDataAdapter daPubs; private DataSet dsPubs;3在該類的構(gòu)造函數(shù)中完成連接后臺(tái)數(shù)據(jù)庫(kù)，獵取SqlDataAdapter對(duì)象等業(yè)務(wù)邏輯： public Publishers() try

11、/ 創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象cnPubs = new SqlConnection( server=localhost;integrated security=true;database=pubs ); / 創(chuàng)建一個(gè)SqlCommand對(duì)象，并指明其命令類型為存儲(chǔ)過(guò)程 cmdPubs = new SqlCommand(); cmdPubs.Connection = cnPubs; cmdPubs.CommandType = CommandType.StoredProcedure; cmdPubs.CommandText = up_GetPublisherInfo; / 創(chuàng)建一個(gè)SqlDataAda

12、pter對(duì)象，設(shè)定其SelectCommand屬性為上面的SqlCommand對(duì)象 daPubs = new SqlDataAdapter(); daPubs.SelectCommand = cmdPubs; / 創(chuàng)建一個(gè)DataSet對(duì)象 dsPubs = new DataSet(); catch( Exception ) 4最后為該類提供一個(gè)GetPublisherInfo（）方法，該方法用SqlDataAdapter對(duì)象填充DataSet對(duì)象并返回填充后的DataSet對(duì)象，方法如下（值得注意的是：SqlDataAdapter對(duì)象會(huì)隱式地打開(kāi)數(shù)據(jù)庫(kù)連接并在獵取數(shù)據(jù)后隱式地關(guān)閉連接，這確實(shí)

13、是講DataSet對(duì)象是工作在非連接模式下的。而當(dāng)你顯式地打開(kāi)數(shù)據(jù)庫(kù)連接并獵取數(shù)據(jù)后，SqlDataAdapter對(duì)象并可不能將該連接關(guān)閉）： public DataSet GetPublisherInfo()/ 調(diào)用SqlDataAdapter對(duì)象的Fill（）方法并返回?cái)?shù)據(jù)集對(duì)象 daPubs.Fill( dsPubs ); return dsPubs; 完成Publishers類的設(shè)計(jì)后，我們給主窗體添加一個(gè)DataGrid控件并用它來(lái)顯示DataSet對(duì)象中的數(shù)據(jù)。首先給主窗體類添加如下成員變量： private Publishers pubs; private DataSet ds;

14、之后，修改主窗體類的構(gòu)造函數(shù)如下：public Form1() / / Windows 窗體設(shè)計(jì)器支持所必需的 / InitializeComponent();/ TODO: 在 InitializeComponent 調(diào)用后添加任何構(gòu)造函數(shù)代碼 / pubs = new Publishers();ds = pubs.GetPublisherInfo();dataGrid1.DataSource = ds.Tables0; 如此該應(yīng)用程序一啟動(dòng)主窗體的DataGrid控件中便顯示了運(yùn)用上述不帶參數(shù)的存儲(chǔ)過(guò)程從Pubs數(shù)據(jù)庫(kù)中獵取的相應(yīng)數(shù)據(jù)，程序運(yùn)行圖示如下： 六創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)應(yīng)用

15、程序： 上面我們創(chuàng)建了一個(gè)不帶參數(shù)的存儲(chǔ)過(guò)程的應(yīng)用程序，下面我們就來(lái)創(chuàng)建一個(gè)更加復(fù)雜的數(shù)據(jù)庫(kù)應(yīng)用程序。在實(shí)際的數(shù)據(jù)庫(kù)應(yīng)用中，我們往往需要獵取數(shù)據(jù)并更新、插入或刪除數(shù)據(jù)，這時(shí)我們就需要用到帶有參數(shù)的存儲(chǔ)過(guò)程了，同時(shí)在運(yùn)用SqlDataAdapter對(duì)象時(shí)，我們會(huì)調(diào)用它的Update（）方法。該Update（）方法會(huì)自動(dòng)依照DataSet對(duì)象中的DataTable對(duì)象內(nèi)各條記錄的變化情況完成相應(yīng)操作。SqlDataAdapter對(duì)象還包含了UpdateCommand、InsertCommand、DeleteCommand等屬性，這些屬性事實(shí)上差不多上SqlCommand對(duì)象。Update（）方法會(huì)

16、依照操作的類型選用相應(yīng)的屬性。 在運(yùn)用帶有參數(shù)的存儲(chǔ)過(guò)程建立數(shù)據(jù)庫(kù)應(yīng)用程序時(shí)，我們一般都要用到SqlParameter類，該類封裝了各種與Sql參數(shù)相關(guān)的屬性和方法。其中的屬性包括了ParameterName，SqlDBType，Direction，Size，Value，SourceColumn以及SourceVersion等。其中ParameterName，SqlDBType，Direction，Size等屬性是用于匹配存儲(chǔ)過(guò)程中定義的參數(shù)的。比如下面定義的SqlParameter對(duì)象確實(shí)是用來(lái)匹配前面定義的up_UpdatePublisherInfo存儲(chǔ)過(guò)程中的“pub_id”參數(shù)的。 S

17、qlParameter updParam = new SqlParameter( pub_id, SqlDbType.Char, 4 ); 在上面的定義中，盡管Direction屬性沒(méi)有明確地給出，然而它的默認(rèn)值為Input，因此也就滿足了我們的需要。而假如一個(gè)SqlParameter對(duì)象的Direction屬性為InputOutput或Output或ReturnValue，那么其Direction屬性就必須明確地講明了，比如下面的代碼就明確地聲明了一個(gè)SqlParameter對(duì)象的Direction屬性為Output。 oParam.Direction = ParameterDirectio

18、n.Output; 其中的SourceColumn屬性是用于匹配一個(gè)DataTable對(duì)象中的DataColumn對(duì)象的，這種匹配能在調(diào)用Update（）方法更新DataTable對(duì)象時(shí)隱式地導(dǎo)入所需的SqlParameter對(duì)象。假如在定義時(shí)沒(méi)有聲明該屬性，那么你必須在代碼中顯式地講明SqlParameter對(duì)象的SourceColumn屬性。 其中的SourceVersion屬性的默認(rèn)值是DataRow對(duì)象相應(yīng)字段中的當(dāng)前值，也確實(shí)是要更新到數(shù)據(jù)庫(kù)中的值。因此，SourceVersion屬性也能夠指向DataRow對(duì)象相應(yīng)字段中的原始值，也即從數(shù)據(jù)庫(kù)中獵取的初始值。在數(shù)據(jù)庫(kù)事務(wù)處理系統(tǒng)中，

19、數(shù)據(jù)的同步性問(wèn)題特不重要，下面我們來(lái)建立一個(gè)能檢測(cè)數(shù)據(jù)同步性的存儲(chǔ)過(guò)程。 CREATE PROCEDURE dbo.up_UpdatePublisherName (pub_id char(4),pub_name varchar(40), Original_pub_name varchar(40) ) AS if exists(select pub_id from publishers where (pub_id = pub_id) AND (pub_name = Original_pub_name) BeginUPDATE publishers SET pub_name = pub_name

20、WHERE (pub_id = pub_id)EndRETURN 接著，我們?cè)谏厦娴膽?yīng)用程序中調(diào)用該存儲(chǔ)過(guò)程以更新發(fā)行商的名稱。首先，在原有應(yīng)用程序的基礎(chǔ)上完善其業(yè)務(wù)邏輯類Publishers類： 1添加一個(gè)新的SqlCommand對(duì)象，該對(duì)象能作為SqlDataAdapter對(duì)象的 UpdateCommand屬性被使用：private SqlCommand cmdUpdPubs; 2更新該類的構(gòu)造函數(shù)Publishers（）函數(shù)，添加以下內(nèi)容： / 創(chuàng)建另一個(gè)SqlCommand對(duì)象，該對(duì)象引用更新發(fā)行商名稱的存儲(chǔ)過(guò)程 cmdUpdPubs = new SqlCommand(); cmdUp

21、dPubs.Connection = cnPubs;cmdUpdPubs.CommandType = CommandType.StoredProcedure;cmdUpdPubs.CommandText = up_UpdatePublisherName;/ 為上面的SqlCommand對(duì)象添加必要的參數(shù) cmdUpdPubs.Parameters.Add( pub_id, SqlDbType.Char, 4, pub_id ); cmdUpdPubs.Parameters.Add( pub_name, SqlDbType.VarChar, 40, pub_name );SqlParameter updParam = new SqlParameter ( Original_pub_name, SqlDbType.VarChar, 40, pub_name );updParam.SourceVersion = DataRowVersion.Original;cmdUpdPubs.Parameters.Add( updParam ); 3指定SqlDataAdapter對(duì)象的UpdateCommand屬性為上面定義的SqlCommand對(duì)象： daPubs.UpdateCommand = cmdUpdPubs; 4添加方法UpdatePublishe