使用 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 將使用存儲過程的名稱將該項內容保存到數據庫中。如果鍵入的內容有誤，編輯器會向您報告這些錯誤，您可以在保存存儲過程之前修正這些錯誤（參見圖 11）。

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

create procedure topicsgetlist
as
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 [email protected])=0
begin
return 101 --- 無效 id 代碼
end
-- 繼續執行并返回該記錄
select
@title=title,
@description=description
from
topics
where
[email protected]
-- 返回錯誤，如果成功則返回 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 int
as
begin
declare @responsecount int
set @responsecount =
(
select
count(responses.id)
from
responses
where
[email protected]
)
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 創建數據層時的另一個問題，即安全性問題。