使用VS.NET2003編寫存儲過程

<col>

作者：未知   請作者速與本人聯系

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

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

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

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

其次，如果使用不經過輸入驗證的字元串連接配接 ("...where id=" &amp; 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 将使用存儲過程的名稱将該項内容儲存到資料庫中。如果鍵入的内容有誤，編輯器會向您報告這些錯誤，您可以在儲存存儲過程之前修正這些錯誤。

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

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

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

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

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

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

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

使用自定義标量函數

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

添加自定義函數的操作類似于添加存儲過程。在 server explorer（伺服器資料總管）樹中，在標明資料庫的 functions（函數）節點上單擊滑鼠右鍵，然後從上下文相關菜單中選擇 new scalar-valued function（建立标量值函數）。然後在編輯器中編輯該文檔，并像儲存存儲過程那樣儲存該文檔。

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

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