SQL預存程序在.NET資料庫中的應用

來源:互聯網
上載者:User
預存程序|資料|資料庫 一.前言:

預存程序(Stored Procedure)是一組為了完成特定功能的SQL語句集,經編譯後儲存在資料庫中。使用者通過指定預存程序的名字並給出參數(如果該預存程序帶有參數)來執行它。預存程序是資料庫中的一個重要對象,任何一個設計良好的資料庫應用程式都應該用到預存程序。總的來說,預存程序具有以下一些優點:

◆預存程序允許標準組件式編程

◆預存程序能夠實現較快的執行速度

◆預存程序能夠減少網路流量

◆預存程序可被作為一種安全機制來充分利用

本文作者將向大家介紹.NET資料庫應用程式中預存程序的應用,以及如何將它與ADO.NET中的SqlDataAdapter對象、DataSet對象等結合使用以提高.NET資料庫應用程式的總體效能。

二.系統要求:

開發工具:Visual Studio.NET

資料庫管理系統:SQL Server 2000(其中包含了樣本程式所用到的Pubs資料庫)

三.建立一個簡單的預存程序:

這裡我將向大家介紹如何運用Visual Studio.NET IDE來建立預存程序。運用Visual Studio.NET IDE建立預存程序是非常容易和直觀的,你只要在伺服器總管中導向到Pubs資料庫並展開節點,就會發現包括預存程序在內的各種資料庫物件,如圖1所示。


在預存程序節點上點擊右鍵便可彈出一個菜單,其中包含了“建立預存程序”的命令。建立一個預存程序後,IDE中的代碼編輯視窗便出現如下所示的代碼模板:



CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN



上面的代碼模板符合簡化的建立預存程序的文法規則,完整的文法規則如下:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]



限於篇幅,各個參數的含義在此就不多作介紹了,有興趣的讀者可以參考有關SQL Server 2000資料庫管理系統的資料。

下面我對該代碼模板中的各個文法成分略作介紹。CREATE PROCEDURE聲明建立一個預存程序,後面跟著該預存程序的名稱。“/*……*/”中的成分是該預存程序的參數,可包括輸入參數和輸出參數。AS關鍵字後面的內容是該預存程序的主體部分,其中是任何數量和類型的包含在預存程序中的SQL語句。RETURN關鍵字表明預存程序結束並能返回整型狀態值給調用者。下面我們就來建立一個簡單的不帶參數的預存程序並運用之:

CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN






建立以上預存程序後,儲存之。儲存完畢,與該預存程序相對應的節點就會出現在伺服器總管中。同時請注意代碼編輯視窗中的CREATE關鍵字變為ALTER關鍵字了,該關鍵字是用於更改任何現有的預存程序的。要運行上述預存程序,只要點擊其節點並在右鍵快顯功能表中選擇“運行預存程序”,啟動並執行結果圖示如下:



四.建立一個帶參數的預存程序:

以上我們建立了一個簡單的不帶參數的預存程序,而在實際的應用中往往會用到很多帶有參數的預存程序。帶有參數的預存程序一般是用於更新資料或是插入資料的。下面我們可以運用同樣的操作方法建立一個帶參數的預存程序:



CREATE PROCEDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@city varchar (20),
@state char (2),
@country varchar (30)
)
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state,
 country = @country
WHERE ( pub_id = @pub_id )
RETURN




在上面的建立預存程序的代碼中,我們通過在名稱前添加一個“@”標誌來聲明預存程序的局部變數-參數,同時還聲明了各個參數的類型,確定了各個參數的方向值,也即表明該參數是輸入型的還是輸出型的或者是輸入輸出型的或者是傳回值型的。使用者通過相應的預存程序名稱以及正確有效參數便可調用該預存程序了。還有,你可以通過運用OUTPUT關鍵字在參數中添加輸出型的參數,具體方法請參考上面的文法規則。輸出型的參數能返回給調用者相關的資訊。

上面的預存程序能更新publishers表中相應出版商的資訊。你可以通過點擊該預存程序的節點,在右鍵快顯功能表中選擇“運行預存程序”來執行它。一旦執行,IDE中便彈出一個輸入出版商資訊的對話方塊(如圖3所示)。在該對話方塊中填入正確有效更新資訊,注意pub_id的值在原來的表中必須存在,然後點擊“確定”按鈕便可更新資料了。

五.建立簡單預存程序的資料庫應用程式:

下面我們就運用上述的不帶參數的預存程序來一個資料庫應用程式,其中還用到了ADO.NET中的SqlDataAdapter對象以及DataSet對象。其中的SqlDataAdapter對象作為SQL Server資料庫和DataSet對象的橋樑將兩者聯絡在一起。SqlDataAdapter對象包含了兩個常用的方法:Fill()方法和Update()方法。其中的Fill()方法能從資料庫中擷取相應資料並填充到DataSet對象中,而Update()方法顧名思義就是更新資料集的意思了。在調用Fill()方法以前,我們必須設定好SqlDataAdapter對象的SelectCommand屬性,該屬性其實是一個SqlCommand對象。SelectCommand屬性中包含有效SQL語句,並能據此從資料庫中擷取相應資料並填充到DataSet對象中。

首先,我們建立一個Windows Forms應用程式,程式設計語言為C#。在Visual Studio.NET中建立一個新的項目後,給該項目添加一個新的類-Publishers類,該類封裝了串連到後台資料庫並擷取資料集對象的商務邏輯。步驟如下:

1.添加必要的命名空間引用:using System.Data.SqlClient;

2.給該類添加如下一些必要的變數:

private SqlConnection cnPubs;
private SqlCommand cmdPubs;
private SqlDataAdapter daPubs;
private DataSet dsPubs;



3.在該類的建構函式中完成串連後台資料庫,擷取SqlDataAdapter對象等商務邏輯:

public Publishers()
{
try
{
// 建立一個資料庫連接對象
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
// 建立一個SqlCommand對象,並指明其命令類型為預存程序
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
// 建立一個SqlDataAdapter對象,設定其SelectCommand屬性為上面的SqlCommand對象
daPubs = new SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
// 建立一個DataSet對象
dsPubs = new DataSet();
}
catch( Exception ) {}
}



4.最後為該類提供一個GetPublisherInfo()方法,該方法用SqlDataAdapter對象填充DataSet對象並返回填充後的DataSet對象,方法如下(值得注意的是:SqlDataAdapter對象會隱式地開啟資料庫連接並在擷取資料後隱式地關閉串連,這就是說DataSet對象是工作在非串連模式下的。而當你顯式地開啟資料庫連接並擷取資料後,SqlDataAdapter對象並不會將該串連關閉):

public DataSet GetPublisherInfo()



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。