目錄：
為什么不使用特殊的查詢語句
將存儲過程添加到 Visual Studio .NET 數據庫項目中
使用 Visual Studio .NET 添加存儲過程
使用自定義標量函數

下載 JumpPart2Sample.msi。（請注意，在示例文件中，程序員的注釋使用的是英文，本文中將其譯為中文是為了便于讀者理解。）

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

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

我們經常會看到如下所示的文章和代碼示例：

Private Function GetSomeData(ByVal ID As Integer) As SqlDataReader        Dim strSQL As String        strSQL = "SELECT * FROM MyTable WHERE ID=" & ID.ToString()        cd = New SqlCommand        With cd            .CommandText = strSQL            .CommandType = CommandType.Text            .Connection = cn            .Connection.Open()            Return .ExecuteReader(CommandBehavior.CloseConnection)        End With    End Function

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

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

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

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

將存儲過程添加到 Visual Studio .NET 數據庫項目中

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

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

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

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

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

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

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

CREATE PROCEDURE TopicsGetListAS   SET NOCOUNT ON -- 不返回受影響行的值   SELECT       ID,      Title,      Description   FROM       Topics   ORDER BY       Title   RETURN @@ERROR

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

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

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

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

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

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

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

使用自定義標量函數

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

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

以下是自定義函數的代碼：

CREATE FUNCTION dbo.fn_QuestionsGetResponseCount   (      @ID int   )RETURNS intAS   BEGIN      DECLARE @ResponseCount int      Set @ResponseCount =       (         SELECT             COUNT(Responses.ID)          FROM             Responses         WHERE            Responses.QuestionID=@ID      )      RETURN @ResponseCount   END

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

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

了解如何編寫存儲過程和自定義函數之后，我們還將討論使用 Visual Studio .NET 2003 創建數據層時的另一個問題，即安全性問題。請閱讀下一篇文章《IIS、ASP.NET和SQLServer的安全性問題》

(責任編輯：戰瑩)