當(dāng)前位置：首頁 > 人文社科 > 生活经验 >内容正文

生活经验

使用VS.NET2003编写存储过程

發(fā)布時間：2023/11/27 生活经验 38 豆豆

生活随笔收集整理的這篇文章主要介紹了使用VS.NET2003编写存储过程小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

作者：未知?? 請作者速與本人聯(lián)系

數(shù)據(jù)表定義了如何在數(shù)據(jù)庫中存儲數(shù)據(jù)，但沒有說明如何存取數(shù)據(jù)。我們還需要了解讀寫記錄以便從表中再次調(diào)用選定行和列的詳細(xì)信息。開發(fā)人員通常會在其代碼中編寫一些特殊的查詢語句，用于讀寫數(shù)據(jù)。這不僅會導(dǎo)致效率低下，還會帶來安全性問題。在本應(yīng)用中，所有數(shù)據(jù)存取工作都將通過 SQL Server 存儲過程（stored procedures，有時稱作“stored procs”或“sprocs”）來處理。使用存儲過程可以提高解決方案的性能并使之更安全。此外，使用存儲過程可以增加數(shù)據(jù)層的抽象級別，從而保護解決方案的其他部分不受小的數(shù)據(jù)布局和格式變化帶來的影響。這樣可使您的解決方案更可靠，更易于維護。

為什么不使用特殊的查詢語句

我們經(jīng)常會看到如下所示的文章和代碼示例：

Private Function GetSomeData(ByVal ID As Integer) As SqlDataReaderDim strSQL As StringstrSQL = "SELECT * FROM MyTable WHERE ID=" & ID.ToString()cd = New SqlCommandWith cd.CommandText = strSQL.CommandType = CommandType.Text.Connection = cn.Connection.Open()Return .ExecuteReader(CommandBehavior.CloseConnection)End WithEnd Function

上述代碼不符合要求的原因有以下幾個。首先，如果將 SQL 查詢語句嵌套在代碼中，那么只要數(shù)據(jù)層發(fā)生任何變化，都必須編輯并重新編譯代碼層。這樣就會帶來諸多不便。還可能會導(dǎo)致其他錯誤，而且通常會造成數(shù)據(jù)服務(wù)和代碼之間的混亂。

其次，如果使用不經(jīng)過輸入驗證的字符串連接 ("...WHERE ID=" & ID.ToString())，將可能使您的應(yīng)用程序暴露在黑客的攻擊之下。更重要的是，這樣就會為惡意用戶提供了在您的代碼中添加其他 SQL 關(guān)鍵字的機會。例如，根據(jù)您的輸入模式，惡意用戶不僅可以輸入 13 或 21 作為有效的表 ID，還可以輸入 13; DELETE FROM USERS 或其他可能會帶來危害的語句。完善的輸入驗證可以保護您的系統(tǒng)免受大多數(shù) SQL 插入代碼的攻擊，所以最好將所有內(nèi)置的 SQL 語句完全刪除，使攻擊者很難濫用您的應(yīng)用程序數(shù)據(jù)。

最后，內(nèi)置 SQL 語句的執(zhí)行速度要比存儲過程慢得多。創(chuàng)建存儲過程并將其存儲到數(shù)據(jù)庫中時，SQL Server 會對其文本進行評估并以優(yōu)化的形式進行存儲，從而使之更容易在運行時為 SQL Server 所用。如果使用內(nèi)置的特殊查詢語句，就必須在每次運行該代碼之前進行這種評估。對于那些供大量用戶使用的應(yīng)用程序而言，每分鐘就可能需要對同一查詢語句進行數(shù)百次評估。

相反，存儲過程可以保持代碼的簡潔明了，可以提供額外的安全保護，并能提高解決方案的性能。這些都是摒棄內(nèi)置查詢語句而使用存儲過程的原因。

將存儲過程添加到 Visual Studio .NET 數(shù)據(jù)庫項目中

使用 Visual Studio .NET 2003 創(chuàng)建存儲過程非常簡單。首先，您需要打開一個數(shù)據(jù)庫項目。這一操作已在《使用VS.NET2003創(chuàng)建數(shù)據(jù)庫圖》中完成。然后，您可以使用代碼模板創(chuàng)建存儲過程，也可以針對 Server Explorer（服務(wù)器資源管理器）窗口中連接的數(shù)據(jù)庫，使用 Visual Studio .NET 2003 直接編輯新的存儲過程。本文重點介紹如何針對連接的數(shù)據(jù)庫服務(wù)器直接編輯存儲過程。稍后會介紹如何為以后的遠程服務(wù)器安裝生成所有結(jié)果腳本。

介紹使用 Visual Studio .NET 2003 編寫存儲過程的機制之前，還要重點強調(diào)一下與創(chuàng)建可靠的存儲過程相關(guān)的幾個一般問題。首先，最好將創(chuàng)建和執(zhí)行存儲過程的整個過程看作是多層應(yīng)用程序模型的一個成熟成員。存儲過程提供了一種對您的數(shù)據(jù)存取進行編程的方法。這樣，您可以更好地控制整個解決方案并提高其效率。也就是說，應(yīng)將存儲過程集合看作是應(yīng)用程序中一個獨立的層。優(yōu)秀的數(shù)據(jù)存取策略應(yīng)允許存儲過程作為獨立的組件而存在。也就是說，存儲過程層中需要具備安全性、錯誤處理以及其他構(gòu)成優(yōu)秀組件層的詳細(xì)內(nèi)容。更重要的是，應(yīng)像在其他高級編程環(huán)境中那樣訪問 T-SQL 語言，而不是僅僅將其作為一種生成數(shù)據(jù)庫查詢的方式。

注意：現(xiàn)在，我懷疑有些讀者可能在想他們并不打算對 SQL Server 進行編程，或者認(rèn)為這項工作最好留給那些 DBA 們來完成。雖然具備數(shù)據(jù)庫管理員經(jīng)驗會有所幫助，但并一定非要成為火箭科學(xué)家（這里指技藝高超的編程專家）才能很好地完成 SQL Server 編程工作。像其他語言一樣，這種語言也需要花費一定的時間并通過一定的實踐才能熟練掌握，在這一點上它與其他語言并沒有太大的不同。如果您能夠在 Microsoft Visual Basic? .NET 中編程，也就能夠在 T-SQL 中編程。

使用 Visual Studio .NET 添加存儲過程

下面詳細(xì)介紹如何在 Visual Studio .NET 2003 中將存儲過程添加到現(xiàn)有 SQL Server 數(shù)據(jù)庫中。您需要使用服務(wù)器資源管理器打開一個新的存儲過程模板，進行編輯，然后再將其保存到數(shù)據(jù)庫中。下面是分步實現(xiàn)這一過程的示例：

·打開 Visual Studio .NET，然后打開一個現(xiàn)有的數(shù)據(jù)庫項目（如本文前面所啟動的項目）或啟動一個新項目。
·在 Server Explorer（服務(wù)器資源管理器）中，展開 Data Connections（數(shù)據(jù)連接）樹，找到您要使用的數(shù)據(jù)庫 (DotNetKB)，然后在 Stored Procedures（存儲過程）節(jié)點上單擊鼠標(biāo)右鍵，打開上下文相關(guān)菜單。
·從上下文相關(guān)菜單中選擇 New Stored Procedure（新建存儲過程），在 Visual Studio .NET 編輯器空間中打開一個存儲過程模板。現(xiàn)在，可以鍵入內(nèi)容了。
·完成編輯后，只需關(guān)閉編輯器中正在編輯的頁面，Visual Studio .NET 將使用存儲過程的名稱將該項內(nèi)容保存到數(shù)據(jù)庫中。如果鍵入的內(nèi)容有誤，編輯器會向您報告這些錯誤，您可以在保存存儲過程之前修正這些錯誤。

下面是存儲過程的一個簡單示例，它返回一個主題列表。

CREATE PROCEDURE TopicsGetList ASSET NOCOUNT ON -- 不返回受影響行的值SELECT ID,Title,DescriptionFROM TopicsORDER BY TitleRETURN @@ERROR

在本示例中，有幾點需要指出。首先，請注意 SET NOCOUNT ON 行。它告訴 SQL Server 停止為該查詢計算受影響的行數(shù)，并停止向調(diào)用函數(shù)返回該值。這是一項不必要的額外工作。其次，結(jié)尾處的 RETURN @@ERROR 一行很重要。此行代碼返回 SQL Server 中發(fā)生的錯誤的整數(shù)值。您可以在調(diào)用例程中使用此代碼完成其他診斷和錯誤處理操作。您現(xiàn)在并不需要執(zhí)行任何操作，但它們是創(chuàng)建存儲過程時應(yīng)該遵循的兩個好習(xí)慣。

下面是一個更復(fù)雜的存儲過程。此過程用于從數(shù)據(jù)庫中檢索單條主題記錄。您會發(fā)現(xiàn)一些附加項，包括輸入?yún)?shù)、返回特定值的輸出參數(shù)，以及檢查輸入?yún)?shù)并在需要時返回錯誤的某些程序代碼。

CREATE PROCEDURE TopicsGetItem(@AdminCode char(3),@ID int,@Title varchar(30) OUTPUT,@Description varchar(500) OUTPUT) ASSET NOCOUNT ON -- 不返回受影響行的值-- 確保是一個 Admin 用戶IF @AdminCode<>'adm'BEGINRETURN 100 -- 無效 admin 錯誤END-- 檢查記錄是否存在IF (SELECT Count(ID) FROM Topics WHERE ID=@ID)=0BEGINRETURN 101 --- 無效 ID 代碼END-- 繼續(xù)執(zhí)行并返回該記錄SELECT @Title=Title, @Description=DescriptionFROM TopicsWHERE ID=@ID-- 返回錯誤，如果成功則返回 0RETURN @@ERROR

在本示例中，還有幾點需要指出。首先，您會在存儲過程頂端看到一個參數(shù)列表。除前兩個參數(shù)外，其他參數(shù)均被標(biāo)記為 OUTPUT 參數(shù)。這些參數(shù)用于返回選定記錄的值。使用一條記錄的返回值要比返回帶有所有字段的記錄集合更為高效。

其次，您會發(fā)現(xiàn)用于檢查 @AdminCode 參數(shù)值的 T-SQL 數(shù)據(jù)塊，以確保傳遞正確的代碼。如果傳遞的代碼不正確，則傳遞返回代碼 100 并停止執(zhí)行該過程。再其次，您會發(fā)現(xiàn)檢查 @ID 參數(shù)，以確保其代表一條現(xiàn)有記錄。如果不是現(xiàn)有記錄，則傳送返回代碼 101 并終止執(zhí)行。最后，如果輸入變量都有效，存儲過程將嘗試選擇記錄并返回相應(yīng)的值。如果此時發(fā)生任何錯誤，將由該過程的最后一行代碼進行處理。

注意：通常情況下，最好將自定義錯誤代碼及其含義保存在數(shù)據(jù)庫中的一個單獨的表格中，或保存在解決方案可以訪問的文本文件中。這樣就可以輕松更新這些錯誤代碼，并與解決方案中的其他子系統(tǒng)共享。因為這只是一個短小的示例，其中只使用了兩個錯誤代碼，所以我決定創(chuàng)建一個包含大量代碼和消息的文檔，以供其他子系統(tǒng)參考。

該解決方案中包含的存儲過程超過 25 個。本文僅舉一例進行說明，其他代碼可以通過本文開始處的鏈接進行下載。最后這個示例使用一個自定義的內(nèi)置標(biāo)量函數(shù)。

使用自定義標(biāo)量函數(shù)

有時，單獨一個存儲過程不足以解決問題。例如，我們的用戶方案中就有一個方案要求列出某個問題的解答數(shù)目。解決此問題的方法之一是生成一個對問題的解答進行計數(shù)的子查詢。另外一種方法是生成一個自定義函數(shù)，返回標(biāo)量值并將其包含在問題查詢中。這種方法還有一個好處，那就是我們可以在其他存儲過程中再次使用該標(biāo)量函數(shù)。

添加自定義函數(shù)的操作類似于添加存儲過程。在 Server Explorer（服務(wù)器資源管理器）樹中，在選定數(shù)據(jù)庫的 Functions（函數(shù)）節(jié)點上單擊鼠標(biāo)右鍵，然后從上下文相關(guān)菜單中選擇 New Scalar-Valued Function（新建標(biāo)量值函數(shù)）。然后在編輯器中編輯該文檔，并像保存存儲過程那樣保存該文檔。

以下是自定義函數(shù)的代碼：

CREATE FUNCTION dbo.fn_QuestionsGetResponseCount(@ID int) RETURNS int ASBEGINDECLARE @ResponseCount intSet @ResponseCount = (SELECT COUNT(Responses.ID) FROM ResponsesWHEREResponses.QuestionID=@ID)RETURN @ResponseCountEND

以下是使用自定義函數(shù)的存儲過程：

CREATE PROCEDURE QuestionsGetCountWithNoResponses(@Total int OUTPUT) ASSET NOCOUNT ON -- 不返回受影響行的值SELECT @Total=Count(ID)FROM QuestionsWHERE dbo.fn_QuestionsGetResponseCount(Questions.ID)=0RETURN @@ERROR

總結(jié)

以上是生活随笔為你收集整理的使用VS.NET2003编写存储过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

存储过程

上一篇： DataGrid入门经典(C#)
下一篇： ASP.NET结合COM组件发送Emai