存儲過程更新操作

1、一.前言：存儲過程（StoredProcedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中。用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執(zhí)行它。存儲過程是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程?？偟膩碚f，存儲過程具有以下一些優(yōu)點： 存儲過程允許標準組件式編程。 存儲過程能夠實現較快的執(zhí)行速度。 存儲過程能夠減少網絡流量。 存儲過程可被作為一種安全機制來充分利用。本文作者將向大家介紹.NET數據庫應用程序中存儲過程的應用，以及如何將它與ADO.NET中的SqlDataAdapter對象、DataSet對象等結合使用以提

2、高.NET數據庫應用程序的總體性能。2 .系統(tǒng)要求：開發(fā)工具：VisualStudio.NET數據庫管理系統(tǒng)：SQLServer2000（其中包含了示例程序所用到的Pubs數據庫）3 .創(chuàng)建一個簡單的存儲過程：這里我將向大家介紹如何運用VisualStudio.NETIDE來創(chuàng)建存儲過程。運用VisualStudio.NETIDE創(chuàng)建存儲過程是非常容易和直觀的，你只要在服務器資源管理器中導向到Pubs數據庫并展開節(jié)點，就會發(fā)現包括存儲過程在內的各種數據庫對象。在存儲過程節(jié)點上點擊右鍵便可彈出一個菜單，其中包含了新建存儲過程”的命令。新建一個存儲過程后，IDE中的代碼編輯窗口便出現如下所示的代碼

3、模板：CREATEPROCEDUREdbo.StoredProcedure1/*parameter1datatype=defaultvalue,parameter2datatypeOUTPUT)*/AS/*SETNOCOUNTON*/RETURN上面的代碼模板符合簡化的創(chuàng)建存儲過程的語法規(guī)則，完整的語法規(guī)則如下：CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT，nWITHRECOMPILEENCRYPTIQNRECOMPILEENCRYPTIONFORREPLICATIONASsql_stat

4、ement.n限于篇幅，各個參數的含義在此就不多作介紹了，有興趣的讀者可以參考有關SQLServer2000數據庫管理系統(tǒng)的資料。下面我對該代碼模板中的各個語法成分略作介紹。CREATEPROCEDURE聲明創(chuàng)建一個存儲過程，后面跟著該存儲過程的名稱。"/*/”中的成分是該存儲過程的參數，可包括輸入參數和輸出參數。AS關鍵字后面的內容是該存儲過程的主體部分，其中是任何數量和類型的包含在存儲過程中的SQL語句。RETURN關鍵字表明存儲過程結束并能返回整型狀態(tài)值給調用者。下面我們就來創(chuàng)建一個簡單的不帶參數的存儲過程并運用之：CREATEPROCEDUREdbo.up_GetPublis

5、herInfoASSELECTpub_id,pub_name,city,state,countryFROMpublishersRETURN創(chuàng)建以上存儲過程后，保存之。保存完畢，與該存儲過程相對應的節(jié)點就會出現在服務器資源管理器中。同時請注意代碼編輯窗口中的CREATE關鍵字變?yōu)锳LTER關鍵字了，該關鍵字是用于更改任何現有的存儲過程的。要運行上述存儲過程，只要點擊其節(jié)點并在右鍵彈出菜單中選擇運行存儲過程4 .創(chuàng)建一個帶參數的存儲過程：以上我們創(chuàng)建了一個簡單的不帶參數的存儲過程，而在實際的應用中往往會用到很多帶有參數的存儲過程。帶有參數的存儲過程一般是用于更新數據或是插入數據的。下面我們可以運用

6、同樣的操作方法創(chuàng)建一個帶參數的存儲過程：CREATEPROCEDUREdbo.up_UpdatePublisherInfopub_idchar(4),pub_namevarchar(40),cityvarchar(20),statechar(2),countryvarchar(30)ASUPDATEpublishersSETpub_name=pub_name,city=city,state=state,country=countryWHERE(pub_id=pub_id)RETURN在上面的創(chuàng)建存儲過程的代碼中，我們通過在名稱前添加一個“標志來聲明存儲過程的局部變量一參數，同時還聲明了各個參數

7、的類型，確定了各個參數的方向值，也即表明該參數是輸入型的還是輸出型的或者是輸入輸出型的或者是返回值型的。用戶通過相應的存儲過程名稱以及正確有效的參數便可調用該存儲過程了。還有，你可以通過運用OUTPUT關鍵字在參數中添加輸出型的參數，具體方法請參考上面的語法規(guī)則。輸出型的參數能返回給調用者相關的信息。上面的存儲過程能更新publishers表中相應出版商的信息。你可以通過點擊該存儲過程的節(jié)點，在右鍵彈出菜單中選擇運行存儲過程”來執(zhí)行它。一旦執(zhí)行，IDE中便彈出一個輸入出版商信息的對話框。在該對話框中填入正確有效的更新信息，注意pub_id的值在原來的表中必須存在，然后點擊確定”按鈕便可更新數據

8、了。5 .創(chuàng)建簡單存儲過程的數據庫應用程序：下面我們就運用上述的不帶參數的存儲過程來一個數據庫應用程序，其中還用到了ADO.NET中的SqlDataAdapter對象以及DataSet對象。其中的SqlDataAdapter對象彳為SQLServer數據庫和DataSet對象的橋梁將兩者聯(lián)系在一起。SqlDataAdapter對象包含了兩個常用的方法：Fill()方法和Update()方法。其中的Fill()方法能從數據庫中獲取相應數據并填充到DataSet對象中，而Update()方法顧名思義就是更新數據集的意思了。在調用Fill()方法以前，我們必須設置好SqlDataAdapter對象的

9、SelectCommand屬性，該屬性其實是一個SqlCommand對象。SelectCommand屬性中包含有效的SQL語句，并能據此從數據庫中獲取相應數據并填充到DataSet對象中。首先，我們創(chuàng)建一個WindowsForms應用程序，編程語言為C#。在VisualStudio.NET中創(chuàng)建一個新的項目后，給該項目添加一個新的類Publishers類，該類封裝了連接到后臺數據庫并獲取數據集對象的業(yè)務邏輯。步驟如下：1 .添加必要的命名空間引用：usingSystem.Data.SqlClient;2 .給該類添加如下一些必要的變量：privateSqlConnectioncnPubs;pr

10、ivateSqlCommandcmdPubs;privateSqlDataAdapterdaPubs;privateDataSetdsPubs;3.在該類的構造函數中完成連接后臺數據庫，獲取SqlDataAdapter對象等業(yè)務邏輯:publicPublishers()try/創(chuàng)建一個數據庫連接對象cnPubs=newSqlConnection("server=localhost;integratedsecurity=true;database=pubs");/創(chuàng)建一個SqlCommand寸象，并指明其命令類型為存儲過程cmdPubs=newSqlCommand();cmd

11、Pubs.Connection=cnPubs;cmdPubs.CommandTypeCommandType.StoredProcedure;cmdPubs.CommandText="up_GetPublisherInfo"/創(chuàng)建一個SqlDataAdapter對象，設定其SelectCommancH性為上面的SqlCommand寸象daPubs=newSqlDataAdapter();daPubs.SelectCommand=cmdPubs;/創(chuàng)建一個DataSet對象dsPubs=newDataSet();catch(Exception)4.最后為該類提供一個GetPub

12、lisherInfo()方法，該方法用SqlDataAdapter對象填充DataSet對象并返回填充后的DataSet對象，方法如下(彳！得注意的是：SqlDataAdapter對象會隱式地打開數據庫連接并在獲取數據后隱式地關閉連接，這就是說DataSet對象是工作在非連接模式下的。而當你顯式地打開數據庫連接并獲取數據后，SqlDataAdapter對象并不會將該連接關閉)：publicDataSetGetPublisherInfo()/調用SqlDataAdapter對象的Fill()方法并返回數據集對象daPubs.Fill(dsPubs);returndsPubs;完成Publishe

13、rs類的設計后，我們給主窗體添加一個DataGrid控件并用它來顯示DataSet對象中的數據。首先給主窗體類添加如下成員變量：privatePublisherspubs;privateDataSetds;之后，修改主窗體類的構造函數如下:publicForm1()/Windows窗體設計器支持所必需的/InitializeComponent();/TODO:在InitializeComponent調用后添加任何構造函數代碼/pubs=newPublishers。;ds=pubs.GetPublisherInfo();dataGrid1.DataSource=ds.Tables0;這樣該應用程

14、序一啟動主窗體的DataGrid控件中便顯示了運用上述不帶參數的存儲過程從Pubs數據庫中獲取的相應數據。六.創(chuàng)建帶參數的存儲過程的數據庫應用程序上面我們創(chuàng)建了一個不帶參數的存儲過程的應用程序，下面我們就來創(chuàng)建一個更加復雜的數據庫應用程序。在實際的數據庫應用中，我們往往需要獲取數據并更新、插入或刪除數據，這時我們就需要用到帶有參數的存儲過程了，同時在運用SqlDataAdapter對象時，我們會調用它的Update()方法。該Update()方法會自動根據DataSet對象中的DataTable對象內各條記錄的變化情況完成相應操作。SqlDataAdapter對象還包含了UpdateComma

15、nd、Insertcommand、DeleteCommand等屬性，這些屬性其實都是SqlCommand對象。Update()方法會根據操作的類型選用相應的屬性。在運用帶有參數的存儲過程建立數據庫應用程序時，我們一般都要用到SqlParameter類，該類封裝了各種與Sql參數相關的屬性和方法。其中的屬性包括了ParameterName,SqlDBType,DirectionSize,Value,SourceColumn以及SourceVersion等。其中ParameterName,SqlDBType,DirectionSize等屬性是用于匹配存儲過程中定義的參數的。比如下面定義的SqlPa

16、rameter對象就是用來匹配前面定義的up_UpdatePublisherInfo存儲過程中的"pub_id參數的。SqlParameterupdParam=newSqlParameter("pub_id",SqlDbType.Char,4);在上面的定義中，雖然Direction屬性沒有明確地給出，但是它的默認值為Input，所以也就滿足了我們的需要。而如果一個SqlParameter對象的Direction屬性為InputOutput或Output或ReturnValue,那么其Direction屬性就必須明確地說明了，比如下面的代碼就明確地聲明了一個Sql

17、Parameter對象的Direction屬性為Output。oParam.Direction=ParameterDirection.Output;其中的SourceColumn屬性是用于匹配一個DataTable對象中的DataColumn對象的，這種匹配能在調用Update()方法更新DataTable對象時隱式地導入所需的SqlParameter對象。如果在定義時沒有聲明該屬性，那么你必須在代碼中顯式地說明SqlParameter對象的SourceColumn屬性。其中的SourceVersion屬性的默認值是DataRow對象相應字段中的當前值，也就是要更新到數據庫中的值。當然，Sou

18、rceVersion屬性也可以指向DataRow對象相應字段中的原始值，也即從數據庫中獲取的初始值。在數據庫事務處理系統(tǒng)中，數據的同步性問題非常重要，下面我們來建立一個能檢測數據同步性的存儲過程。CREATEPROCEDUREdbo.up_UpdatePublisherNamepub_idchar(4),pub_namevarchar(40),Original_pub_namevarchar(40)ASifexists(selectpub_idfrompublisherswhere(pub_id=pub_id)AND(pub_name=Original_pub_name)BeginUPDATE

19、publishersSETpub_name=pub_nameWHERE(pub_id=pub_id)EndRETURN接著，我們在上面的應用程序中調用該存儲過程以更新發(fā)行商的名稱。首先，在原有應用程序的基礎上完善其業(yè)務邏輯類Publishers類：1 .添加一個新的SqlCommand對象，該對象能作為SqlDataAdapter對象的UpdateCommand屬性被使用：privateSqlCommandcmdUpdPubs;2 .更新該類的構造函數Publishers()函數，添加以下內容：/創(chuàng)建另一個SqlCommand寸象，該對象引用更新發(fā)行商名稱的存儲過程cmdUpdPubs=new

20、SqlCommand();cmdUpdPubs.Connection=cnPubs;cmdUpdPubs.CommandType=CommandType.StoredProcedure;cmdUpdPubs.CommandText"up_UpdatePublisherName"/為上面的SqlCommand寸象添力口必要的參數cmdUpdPubs.Parameters.Add("pub_id",SqlDbType.Char,4,"pub_id");cmdUpdPubs.Parameters.Add("pub_name&quo

21、t;,SqlDbType.VarChar,40,"pub_name");SqlParameterupdParam=newSqlParameter("Original_pub_name",SqlDbType.VarChar,40,"pub_name");updParam.SourceVersion=DataRowVersion.Original;cmdUpdPubs.Parameters.Add(updParam);3 .指定SqlDataAdapter對象的UpdateCommand性為上面定義的SqlCommand寸象：daPubs.UpdateCommand=cmdUpdPubs;4