摘要：學(xué)習(xí)如何使用 SQL Server 2000 和 Visual Studio .NET 2003 為 DotNetKB ASP.NET 解決方案創(chuàng)建數(shù)據(jù)存儲層。此外，還討論了有關(guān) SQL Server、IIS 和 ASP.NET 的安全性問題。
簡介
在《ASP.NET應(yīng)用程序規(guī)劃與設(shè)計》中，我們討論了名為 DotNetKB 的 ASP.NET 解決方案的基礎(chǔ)規(guī)劃和設(shè)計知識。本部分將詳細討論如何使用 Microsoft? SQL Server? 2000 和 Microsoft Visual Studio? .NET 2003 創(chuàng)建數(shù)據(jù)存儲層。其中包括創(chuàng)建數(shù)據(jù)庫（添加表、索引、約束條件和關(guān)系）以及編寫用于存取數(shù)據(jù)的存儲過程。同時，我們還將討論與 SQL Server、Internet 信息服務(wù)器 (IIS) 和 ASP.NET 有關(guān)的安全性問題。到本部分結(jié)束時，我們將獲得一個適用于 DotNetKB 解決方案的功能完備且安全的數(shù)據(jù)存儲系統(tǒng)。
使用 Visual Studio .NET 2003 創(chuàng)建數(shù)據(jù)庫圖
Visual Studio .NET 2003 的眾多優(yōu)勢之一是，用戶可將其用作主要的 SQL Server 編輯器來完成大多數(shù)任務(wù)。獲得目標數(shù)據(jù)庫服務(wù)器的適當權(quán)限后，您就可以輕松地使用 Visual Studio .NET 2003 創(chuàng)建各種數(shù)據(jù)庫、表、索引、約束條件、關(guān)系、視圖、存儲過程和功能了。Visual Studio .NET 提供了一個可供您完成上述操作的默認數(shù)據(jù)庫項目，還包括了用于創(chuàng)建表、觸發(fā)器、存儲過程等的大量模板，非常便于使用。最后，因為使用 Visual Studio .NET 作為 SQL Server 的編輯環(huán)境，所以還可以使用它將所有 SQL Server 腳本存儲到 Microsoft Visual SourceSafe? 中。這對于共享項目和其他需要長期維護的情況而言非常方便。
引用用戶方案
針對本系列文章中的項目 DotNetKB，我創(chuàng)建了 30 多個用戶方案，用于標識支持本系列文章第 1 部分所概括的應(yīng)用程序所需的任務(wù)。我們將使用這些用戶方案來標識表中存儲的數(shù)據(jù)以及為管理這些數(shù)據(jù)而在運行時執(zhí)行的存儲過程。下面是部分用戶方案列表。完整的列表可以從相關(guān)支持站點 User Scenarios for DotNetKB Project（英文）上找到。
1、查看按關(guān)鍵字搜索的問題列表（按日期倒序排列）
2、查看按日期排序的問題列表（按日期倒序排列）
3、查看按主題排序的問題列表（按主題的字母順序/問題的日期倒序排列）
4、查看某個特定主題的問題列表（按日期倒序排列）
5、查看無任何解答的問題列表（按日期倒序排列）
6、查看問題計數(shù)
7、查看無解答的問題計數(shù)
8、查看某個特定主題的問題計數(shù)
9、查看由某位專家解答的問題計數(shù)
10、查看某個問題及其解答列表（按解答日期倒序排列）
11、添加新問題
12、編輯現(xiàn)有問題
13、刪除現(xiàn)有問題及其相關(guān)解答
如您所見，列表中僅僅涉及到問題記錄的任務(wù)就有許多。而且您還需要處理解答、主題和專家記錄。而在實際工作中，這才剛剛開始。首先，您必須標識需要為每條記錄存儲的數(shù)據(jù)元素（問題、解答、主題和專家），還需要將結(jié)果以表格的形式組織到 SQL Server 中的數(shù)據(jù)庫中。
創(chuàng)建數(shù)據(jù)庫項目
首先要打開 Visual Studio .NET 2003 并創(chuàng)建一個新的數(shù)據(jù)庫項目。Visual Studio .NET 數(shù)據(jù)庫項目的類型目前還不太確定，因為開始新項目時它隱藏在選項列表中。但開始使用后，我想您會發(fā)現(xiàn)數(shù)據(jù)庫項目類型有許多優(yōu)點，所以非常值得花費精力去掌握它們。
要使用 Visual Studio .NET 創(chuàng)建一個新的數(shù)據(jù)庫項目，需要完成以下任務(wù)：
啟動 Visual Studio .NET，如果新建項目對話框沒有自動出現(xiàn)，請從主菜單中選擇 File（文件）-> New（新建）-> Project（項目）。
當顯示 New Project（新建項目）對話框時，展開左側(cè)樹視圖列表中的 Other Projects（其他項目）文件夾，然后單擊 Database Projects（數(shù)據(jù)庫項目）文件夾。此時右側(cè)將顯示 Database Project（數(shù)據(jù)庫項目）模板。
現(xiàn)在，在 Name:（名稱:）輸入框中鍵入項目名稱。在我的例子中，鍵入的是 DotNetKB_Database，不過您可以根據(jù)需要鍵入任何內(nèi)容。
然后單擊 OK（確定）按鈕，創(chuàng)建項目并在 Visual Studio .NET 中打開它。
屏幕上將出現(xiàn)一個對話框，要求您選擇要與該項目相關(guān)聯(lián)的數(shù)據(jù)庫。此時，先單擊 Cancel（取消）。下一步將創(chuàng)建一個新數(shù)據(jù)庫并將其添加到您的項目中。
圖 1 所示為您創(chuàng)建項目時，該項目在 Visual Studio .NET 中的外觀。
圖 1：創(chuàng)建一個新的數(shù)據(jù)庫項目
創(chuàng)建要使用的新數(shù)據(jù)庫之后，可以在該數(shù)據(jù)庫與您的項目之間建立一個連接，以便在 Visual Studio .NET 2003 中對其進行操作。為此，需要完成以下任務(wù)：
在 Solution Explorer（解決方案資源管理器）窗口中，展開您的 dotNETKB_Database 項目，以顯示 Database References（數(shù)據(jù)庫引用）項。
在 Database References（數(shù)據(jù)庫引用）項上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 New Database Reference...（新建數(shù)據(jù)庫引用...），打開 Data Link Properties（數(shù)據(jù)鏈接屬性）對話框。
輸入您在其中添加 DotNetKB 數(shù)據(jù)庫的數(shù)據(jù)庫服務(wù)器的名稱，然后輸入您的登錄憑據(jù)并從下拉菜單中選擇 DotNetKB。
單擊 OK（確定）按鈕，將引用添加到您的項目中。
圖 2 所示為完成上述操作時對話框的外觀。
圖 2：Data Link Properties（數(shù)據(jù)鏈接屬性）對話框
至此，數(shù)據(jù)庫創(chuàng)建完畢并被添加為您項目的引用。下一步，定義存儲 DotNetKB 解決方案數(shù)據(jù)所需的表。
使用 Visual Studio .NET 定義數(shù)據(jù)庫表
在 Visual Studio .NET 中定義數(shù)據(jù)庫表的最簡單的方法是創(chuàng)建一個數(shù)據(jù)庫圖。這樣，您可以在一個類似“所見即所得”的編輯器中定義所有細節(jié)。您只需展開 Server Explorer（服務(wù)器資源管理器）中相應(yīng)的樹，在 Database Diagrams（數(shù)據(jù)庫圖）項上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 New Database Diagram...（新建數(shù)據(jù)庫圖...），即可啟動一個新的空白圖。第一次啟動某個圖時，系統(tǒng)將要求您從數(shù)據(jù)庫中選擇一個表。因為您尚未創(chuàng)建任何表，所以可以忽略該對話框?，F(xiàn)在，可以開始定義您的表了。
在生成的《ASP.NET應(yīng)用程序規(guī)劃與設(shè)計》用戶方案文檔包含定義表所需的信息。學(xué)習(xí)定義如何在系統(tǒng)中添加新記錄的方案，通常是了解需要存儲哪些數(shù)據(jù)的最佳途徑。有時，您需要查看諸如記錄更新甚至是報告之類的其他方案，以確保沒有遺漏其他字段。在本示例中，“添加記錄”方案就是一個很好的參考方案。
例如，以下是用于添加問題的方案：
添加新問題
向系統(tǒng)中添加一條新問題記錄，其中包括標題、日期/時間、指明該問題所屬類別的主題 ID 以及問題正文。有時還需要提供問題提出者姓名及其電子郵件地址。添加新問題之后，將向調(diào)用函數(shù)返回一個唯一的整數(shù)問題 ID。
重要名詞以粗體表示。閱讀方案說明時，這些名詞或表名（例如，“問題記錄”就是一個很好的例子）往往能夠表明需要存儲哪些數(shù)據(jù)。使用上述信息，您可以在數(shù)據(jù)庫圖中添加一個新表并定義所需的列。下面的示例詳細介紹了如何在數(shù)據(jù)庫圖中添加表。
在圖“surface”上單擊鼠標右鍵并從上下文相關(guān)菜單中選擇 New Table...（新建表...）。輸入 Questions（問題）作為表名，然后單擊 OK（確定）將其添加到圖中。
在 Questions（問題）表對話框中，鍵入上文所述方案中提供的字段信息。例如，Column Name（列名）= ID、Data Type（數(shù)據(jù)類型）= int、Length（長度）= 4，并取消選擇 Allow Nulls（允許為空）復(fù)選框。對該表的其余部分重復(fù)上述操作（參見圖 3）。
圖 3：Questions（問題）表
您將看到，第一列 (ID) 旁邊有一個小的金色鍵。它表示該字段是該表的主鍵字段。要設(shè)置主鍵字段，可以在列表中的列名上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 Primary Key（主鍵）。另外，還應(yīng)將此 ID 字段設(shè)置為以增量方式自動增加的標識列。這樣，SQL Server 就可以為添加到表中的每條記錄自動生成一個唯一的整數(shù)值。要進行此設(shè)置，請在該列上單擊鼠標右鍵，從上下文相關(guān)菜單中選擇 Properties（屬性），然后在 Property Pages（屬性頁）對話框中選擇 Columns（列）選項卡。其他的操作就很容易了（參見圖 4）。
圖 4：Property Pages（屬性頁）對話框中的 Columns（列）選項卡
使用“添加主題記錄”和“添加解答記錄”方案中的信息，可以創(chuàng)建其他兩個表。請務(wù)必為每個表創(chuàng)建 ID 列，并將這些列標記為標識列和主鍵。下面的圖 5 顯示了三個已完成的表。
圖 5：三個已完成的表
您會發(fā)現(xiàn)，這些表都通過連接線與數(shù)據(jù)庫圖連接起來。這些連接線表明表之間存在外鍵關(guān)系。例如，Questions（問題）表中的 TopicID 列與 Topics（主題）表中的 ID 列相關(guān)聯(lián)。通過將這種關(guān)系存儲到數(shù)據(jù)庫中，您可以制定用以防止在數(shù)據(jù)庫中保存非法數(shù)據(jù)的規(guī)則。本示例中的關(guān)系規(guī)則是，Questions.TopicID 列所允許的有效值只能是 Topics.ID 列中已存在的某條記錄的值。
您可以通過將 Questions（問題）表中的 TopicID 列拖放到 Topics（主題）表中的 ID 列上，來定義這些關(guān)系。此時將出現(xiàn)一個對話框，顯示規(guī)則定義的詳細信息并要求您按下 OK（確定）按鈕進行確認（參見圖 6）。
圖 6：Create Relationship（創(chuàng)建關(guān)系）對話框
您可能會發(fā)現(xiàn)，Responses.QuestionID 和 Questions.ID 之間也定義了一個關(guān)系。
注意：您可能已經(jīng)注意到，我們還沒有為專家定義任何表。我決定將有關(guān)專家的信息存儲在一個 XML 文件中，而不是存儲在數(shù)據(jù)庫中。這樣做的主要原因是我們可以借此討論一下如何讀寫 XML 數(shù)據(jù)，以便在同一個應(yīng)用中融合 XML 數(shù)據(jù)和關(guān)系數(shù)據(jù)。我們將在下一部分中討論有關(guān)專家數(shù)據(jù)的問題。
至此，數(shù)據(jù)庫和表都已定義完畢。以上介紹了解決方案的實際數(shù)據(jù)存儲過程。但是，我們還需要了解如何在表中讀寫信息。為此，我們將定義 SQL Server 中的存儲過程。
使用 Visual Studio .NET 2003 編寫存儲過程
數(shù)據(jù)表定義了如何在數(shù)據(jù)庫中存儲數(shù)據(jù)，但沒有說明如何存取數(shù)據(jù)。我們還需要了解讀寫記錄以便從表中再次調(diào)用選定行和列的詳細信息。開發(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 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 查詢語句嵌套在代碼中，那么只要數(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ù)庫項目。這一操作已在本文第一部分中完成。然后，您可以使用代碼模板創(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)秀組件層的詳細內(nèi)容。更重要的是，應(yīng)像在其他高級編程環(huán)境中那樣訪問 T-SQL 語言，而不是僅僅將其作為一種生成數(shù)據(jù)庫查詢的方式。
注意：現(xiàn)在，我懷疑有些讀者可能在想他們并不打算對 SQL Server 進行編程，或者認為這項工作最好留給那些 DBA 們來完成。雖然具備數(shù)據(jù)庫管理員經(jīng)驗會有所幫助，但并一定非要成為火箭科學(xué)家（這里指技藝高超的編程專家）才能很好地完成 SQL Server 編程工作。像其他語言一樣，這種語言也需要花費一定的時間并通過一定的實踐才能熟練掌握，在這一點上它與其他語言并沒有太大的不同。如果您能夠在 Microsoft Visual Basic? .NET 中編程，也就能夠在 T-SQL 中編程。
使用 Visual Studio .NET 添加存儲過程
下面詳細介紹如何在 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é)點上單擊鼠標右鍵，打開上下文相關(guān)菜單。
從上下文相關(guān)菜單中選擇 New Stored Procedure（新建存儲過程），在 Visual Studio .NET 編輯器空間中打開一個存儲過程模板?，F(xiàn)在，可以鍵入內(nèi)容了。
完成編輯后，只需關(guān)閉編輯器中正在編輯的頁面，Visual Studio .NET 將使用存儲過程的名稱將該項內(nèi)容保存到數(shù)據(jù)庫中。如果鍵入的內(nèi)容有誤，編輯器會向您報告這些錯誤，您可以在保存存儲過程之前修正這些錯誤（參見圖 11）。
下面是存儲過程的一個簡單示例，它返回一個主題列表。
CREATE PROCEDURE TopicsGetList
AS
SET NOCOUNT ON -- 不返回受影響行的值
SELECT
ID,
Title,
Description
FROM
Topics
ORDER BY
Title
RETURN @@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
)
AS
SET NOCOUNT ON -- 不返回受影響行的值
-- 確保是一個 Admin 用戶
IF @AdminCode<>‘a(chǎn)dm‘
BEGIN
RETURN 100 -- 無效 admin 錯誤
END
-- 檢查記錄是否存在
IF (SELECT Count(ID) FROM Topics WHERE ID=@ID)=0
BEGIN
RETURN 101 --- 無效 ID 代碼
END
-- 繼續(xù)執(zhí)行并返回該記錄
SELECT
@Title=Title,
@Description=Description
FROM
Topics
WHERE
ID=@ID
-- 返回錯誤，如果成功則返回 0
RETURN @@ERROR
在本示例中，還有幾點需要指出。首先，您會在存儲過程頂端看到一個參數(shù)列表。除前兩個參數(shù)外，其他參數(shù)均被標記為 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)置標量函數(shù)。
使用自定義標量函數(shù)
有時，單獨一個存儲過程不足以解決問題。例如，我們的用戶方案中就有一個方案要求列出某個問題的解答數(shù)目。解決此問題的方法之一是生成一個對問題的解答進行計數(shù)的子查詢。另外一種方法是生成一個自定義函數(shù)，返回標量值并將其包含在問題查詢中。這種方法還有一個好處，那就是我們可以在其他存儲過程中再次使用該標量函數(shù)。
添加自定義函數(shù)的操作類似于添加存儲過程。在 Server Explorer（服務(wù)器資源管理器）樹中，在選定數(shù)據(jù)庫的 Functions（函數(shù)）節(jié)點上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 New Scalar-Valued Function（新建標量值函數(shù)）。然后在編輯器中編輯該文檔，并像保存存儲過程那樣保存該文檔。
以下是自定義函數(shù)的代碼：
CREATE FUNCTION dbo.fn_QuestionsGetResponseCount
(
@ID int
)
RETURNS int
AS
BEGIN
DECLARE @ResponseCount int
Set @ResponseCount =
(
SELECT
COUNT(Responses.ID)
FROM
Responses
WHERE
Responses.QuestionID=@ID
)
RETURN @ResponseCount
END
以下是使用自定義函數(shù)的存儲過程：
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
了解如何編寫存儲過程和自定義函數(shù)之后，我們還將討論使用 Visual Studio .NET 2003 創(chuàng)建數(shù)據(jù)層時的另一個問題，即安全性問題。
IIS、ASP.NET 和 SQL Server 的安全性問題
SQL Server、Internet 信息服務(wù)器和 ASP.NET 引擎都提供了堅實可靠的安全模型，它們可以很好地在一起協(xié)同工作。為了保證用戶數(shù)據(jù)和應(yīng)用程序的安全，Microsoft 還為每項服務(wù)的默認設(shè)置設(shè)置了相當?shù)偷闹?。大多?shù)開發(fā)人員面臨的挑戰(zhàn)是如何使用 SQL Server、IIS 和 ASP.NET 在應(yīng)用程序和數(shù)據(jù)之間設(shè)置適當?shù)男湃渭墑e，而不會留下可被別人輕易攻入的安全漏洞。由于涉及三類服務(wù)（SQL Server、IIS 和 ASP.NET），所以需要采取三個關(guān)鍵的步驟來確保解決方案的安全。本部分討論一種為 Web 應(yīng)用程序設(shè)置足夠權(quán)限和信任級別的更常用（且可靠）的方法。
注意：關(guān)于安全性和 Web 解決方案這個大主題，本系列文章難以展開較充分的討論。要更好地理解此問題和可能的解決方案，請參閱安全 ASP.NET 應(yīng)用程序的創(chuàng)建模式和實踐系列文章：驗證、授權(quán)和安全通信。
定義 DotNetKB 自定義 IIS 用戶帳戶。
保證 Web 應(yīng)用程序安全性的最安全的方法是定義一個權(quán)限有限的自定義用戶，然后對 IIS 進行配置，使之能夠在執(zhí)行您的 Web 應(yīng)用程序時能作為自定義用戶運行。這是相當容易實現(xiàn)的，可以確保訪問您的 Web 應(yīng)用程序的每個訪問者都只具有您希望他們具有的權(quán)限。
第一步是生成一個新的 Windows 用戶（本例中稱為 DotNetKB），為其設(shè)置一個增強型密碼，然后將其添加到 Windows 來賓組 (Guest Windows Group) 中。同時，確保選中 Password never expires（密碼永不過期）和 User cannot change password（用戶不能更改密碼）復(fù)選框。這樣將生成一個權(quán)限有限的用戶，在 IIS 中運行您的 Web 應(yīng)用程序時，您可以將其用作標識（參見圖 7）。
圖 7：生成的權(quán)限有限的用戶
然后，調(diào)用 Internet 信息服務(wù)器管理員并選擇承載這些網(wǎng)頁的 Web 應(yīng)用程序。在本例中，您可以選擇承載前文所生成的測試頁的 Web 應(yīng)用程序 (DotNetKB_WebSite)。在樹視圖中的 Web 應(yīng)用程序上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 Properties...（屬性...）。然后選擇 Directory Security（目錄安全性）并單擊該對話框 Anonymous access and authentication control（匿名訪問和驗證控制）部分中的 Edit（編輯）按鈕。最后，輸入自定義用戶名 (DotNetKB)，取消選擇 Allow IIS to control password（允許 IIS 控制密碼）復(fù)選框，并輸入該自定義用戶帳戶的密碼。完成所有這些工作之后，單擊 OK（確定）按鈕，將這些更改保存到 IIS 配置數(shù)據(jù)庫中（參見圖 8）。
圖 8：Authentication Methods（驗證方法）對話框
此時，IIS 將在一個權(quán)限有限的自定義帳戶下運行。任何訪問者訪問您應(yīng)用程序的網(wǎng)頁時，都將以這個自定義用戶身份運行，且只具有該自定義用戶的驗證權(quán)限。
授權(quán) DotNetKB 用戶帳戶訪問 SQL Server
然后，您需要為該自定義用戶授予訪問數(shù)據(jù)庫 (DotNetKB) 的相應(yīng)權(quán)限。為此，您可以使用 Microsoft SQL Server 企業(yè)管理器或編寫一個自定義腳本，以創(chuàng)建一個這樣的用戶并授予其訪問特定對象的權(quán)限。本文介紹如何使用 SQL Server 企業(yè)管理器完成此操作。您還可以從后文中看到一個腳本示例。
注意：盡管 Visual Studio .NET 2003 具有與 SQL Server 兼容的許多強大的集成功能，但也不允許從 Visual Studio .NET 2003 中輕松管理用戶和用戶權(quán)限。在大型的組織和團隊中，這些高級任務(wù)通常由數(shù)據(jù)庫管理員完成。
因此，啟動 SQL Server 企業(yè)管理器之后，您可以按照以下步驟將自定義用戶 (DotNetKB) 添加數(shù)據(jù)庫中（參見圖 9）：
在左側(cè)的樹視圖中，展開節(jié)點以顯示 DotNetKB 數(shù)據(jù)庫。在我的計算機上，樹視圖的結(jié)構(gòu)如下：Console Root | SQL Server Group | (LOCAL) (Windows NT) | Databases | DotNetKB。
然后，在數(shù)據(jù)庫下的 Users（用戶）節(jié)點上單擊鼠標右鍵，并選擇 New Database User...（新建數(shù)據(jù)庫用戶...）。顯示 Database User Properties - New User（數(shù)據(jù)庫用戶屬性 - 新建用戶）對話框時，從 Login name（登錄名）下拉框中選擇 <new>（<新建>）。
顯示 SQL Server Login Properties - New Login（SQL Server 登錄屬性 - 新建登錄）對話框時，選擇 General（常規(guī)）選項卡，并在 Name（名稱）輸入框中輸入 DotNetKB。確保選中 Windows Authentication（Windows 驗證）單選按鈕，并從 Domain（域）下拉框中選擇自定義用戶帳戶所在的計算機的名稱。然后從 Database（數(shù)據(jù)庫）下拉框中選擇 DotNetKB。
現(xiàn)在，選擇 Databases（數(shù)據(jù)庫）選項卡，在對話框頂部的列表中找到 DotNetKB 數(shù)據(jù)庫并選中它。然后，確保選中對話框底部列表中的 public（公共）角色。最后，單擊對話框底部的 OK（確定）按鈕，保存您的更改。
圖 9：在數(shù)據(jù)庫中添加自定義用戶
然后，您需要為 DotNetKB 數(shù)據(jù)庫中的所有存儲過程和自定義函數(shù)添加執(zhí)行權(quán)限。為此，您只需為 public（公共）角色授予權(quán)限。您可以將權(quán)限授予 DotNetKB 用戶，這樣將使以后的登錄（當這些用戶獲得訪問 DotNetKB 的權(quán)限時）更容易執(zhí)行存儲過程，而不需要為每個用戶添加新的權(quán)限。
下面是為 DotNetKB 數(shù)據(jù)庫中的存儲過程和函數(shù)授予執(zhí)行權(quán)限的步驟：
突出顯示樹視圖中 DotNetKB 數(shù)據(jù)庫下的 Users（用戶）節(jié)點，以顯示此數(shù)據(jù)庫的用戶列表。找到 DotNetKB 用戶并在其上雙擊，打開 Database Users Properties（數(shù)據(jù)庫用戶屬性）對話框。
突出顯示（選中）public（公共）角色時，單擊 Properties...（屬性...）按鈕，打開 Database Role Properties（數(shù)據(jù)庫角色屬性）對話框。然后單擊 Permissions...（權(quán)限...）按鈕，顯示數(shù)據(jù)庫對象和權(quán)限設(shè)置列表。
選中對話框頂部 Database role（數(shù)據(jù)庫角色）下拉列表中的 public（公共）角色之后，找到為此數(shù)據(jù)庫定義的所有存儲過程和自定義函數(shù)（可能需要展開對話框才能看到全名），并確保選中各項旁邊的 EXECUTE（執(zhí)行）復(fù)選框。您可能會發(fā)現(xiàn)某些系統(tǒng)對象的其他一些復(fù)選框也被選中了，請不要更改這些選項。
最后，設(shè)置所有的 EXECUTE（執(zhí)行）權(quán)限后，單擊 OK（確定）按鈕，保存更改并關(guān)閉對話框。依次單擊 OK（確定）按鈕，直到所有對話框均被關(guān)閉。
至此，您已為 IIS 創(chuàng)建了自定義用戶，并設(shè)置了該用戶在 SQL Server 中的相應(yīng)權(quán)限?，F(xiàn)在，您需要在 ASP.NET Web 項目中進行一個配置更改，確保 ASP.NET 使用同一個用戶帳戶執(zhí)行對 SQL Server 的所有調(diào)用。
設(shè)置您的 ASP.NET 應(yīng)用程序以模擬 DotNetKB 用戶
為 IIS 下運行的 ASP.NET Web 應(yīng)用程序生成堅實可靠的配置的最后一個步驟是：配置 ASP.NET Web 應(yīng)用程序，使之能夠接受來自 IIS 的 Windows 用戶標識并能用于訪問其他操作系統(tǒng)資源。為此，您只需在 web.config 根文件中輸入一行代碼。
注意：盡管目前我們還沒有真正開發(fā)出用于承載我們的頁面的 ASP.NET Web 應(yīng)用程序，但您可以使用這些信息在生成測試頁的下一節(jié)中驗證數(shù)據(jù)訪問層的功能。
修改后的 web.config 文件如下所示：
＜configuration＞
＜system.web＞
... 其他要素 ...
＜identity impersonate="true"/＞＜!-- 假設(shè) IIS 用戶標識 --＞
... 其他要素 ...
＜/system.web＞
＜/configuration＞
請注意，您只需添加 <identity> 元素并將模擬特性設(shè)置為 true（真）。不必輸入用戶帳戶或密碼，因為這些信息將由 IIS 提供。也就是說，即使其他人能夠讀取您的配置文件，他們也無法確定使用哪些標識憑據(jù)來執(zhí)行您的 Web 應(yīng)用程序。
至此，您已生成了自定義用戶，并為其設(shè)置了訪問 SQL Server 和 IIS 的相應(yīng)權(quán)限?，F(xiàn)在，我們來創(chuàng)建一些測試頁，確保它能夠正常工作。從這里您可以看出一切正常。
創(chuàng)建 ASP.NET 測試頁
創(chuàng)建測試頁始終是訪問 SQL Server 數(shù)據(jù)層并驗證輸入和輸出參數(shù)是否得到正確處理的好辦法。實際上，這是確保以后的生產(chǎn)解決方案中的 ASP.NET 頁和組件能夠按照預(yù)期方式工作的唯一辦法。這對于從解決方案中的某個層調(diào)用其他層時的驗證信任邊界和安全性問題尤其正確。
另外，在進行測試時，請勿拘泥于創(chuàng)建生產(chǎn)類接口。您只需測試目標方法。實際上，故意創(chuàng)建一些您不愿以之為最終生產(chǎn)解決方案的“丑陋”測試頁是一個好的策略！本文中，我創(chuàng)建了一些非常簡單的 ASP.NET 頁，其中包含一個測試記錄列表和一個用于添加、編輯和刪除測試記錄的輸入表單。
例如，以下是用于測試主題記錄的 WebForm 布局。您會發(fā)現(xiàn)，它包含錯誤消息或其他消息的狀態(tài)標簽、記錄計數(shù)標簽、顯示記錄列表的數(shù)據(jù)網(wǎng)格、用于輸入檢索時使用的記錄 ID 的輸入框以及支持添加、編輯和刪除記錄的小表格（參見圖 10）。
圖 10：用于測試主題記錄的 WebForm 布局
在創(chuàng)建測試頁時，最好使代碼簡潔明了。我通常會為每個按鈕添加一小段代碼，以調(diào)用本地方法來處理數(shù)據(jù)庫操作。以下是 TopicTest.aspx 頁上 Get Record（獲取記錄）按鈕的代碼。
Private Sub btnGetTopic_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnGetTopic.Click
Try
Dim ID As Integer = Int32.Parse(txQueryID.Text)
GetItem(ID) ‘ 進行數(shù)據(jù)庫調(diào)用
txID.Text = txQueryID.Text
txTitle.Text = mTitle
txDescription.Text = mDescription
lbStatus.Text = "success!"
Catch ex As Exception
lbStatus.Text = ex.Message
End Try
End Sub
請注意，本方法中實際執(zhí)行的唯一操作是由 GetItem(ID) 方法調(diào)用處理的。它執(zhí)行數(shù)據(jù)庫調(diào)用并使用返回的值設(shè)置本地變量。以下是 GetItem 方法的代碼。請注意，它使用了大量的 SqlParameter 對象來處理輸入和輸出值。
Private Sub GetItem(ByVal ID As Integer)
Try
pr = New SqlParameter("RETURN_VALUE", SqlDbType.Int)
pr.Direction = ParameterDirection.ReturnValue
Dim pTitle As SqlParameter = New SqlParameter
With pTitle
.Direction = ParameterDirection.Output
.DbType = DbType.String
.ParameterName = "@Title"
.Size = 30
End With
Dim pDescription As SqlParameter = New SqlParameter
With pDescription
.Direction = ParameterDirection.Output
.DbType = DbType.String
.ParameterName = "@Description"
.Size = 500
End With
cd = New SqlCommand
With cd
.CommandText = "TopicsGetItem"
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@AdminCode", "adm"))
.Parameters.Add(New SqlParameter("@ID", ID))
.Parameters.Add(pTitle)
.Parameters.Add(pDescription)
.Parameters.Add(pr)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
End With
‘ 檢查返回代碼
If Not pr.Value Is Nothing Then
Select Case Int32.Parse(pr.Value)
Case 100 : Throw New ApplicationException("Access violation")
Case 101 : Throw New ApplicationException("Invalid ID")
End Select
End If
‘ 設(shè)置返回值
mTitle = pTitle.Value.ToString()
mDescription = pDescription.Value.ToString()
Catch ex As Exception
Throw New Exception(ex.Message, ex)
End Try
End Sub
GetItem 方法的另一個重要方面是使用了返回值參數(shù)。它在前幾行代碼中進行聲明，并在執(zhí)行存儲過程后進行檢查。請注意，我檢查了已知錯誤代碼 100 和 101。有關(guān)其他錯誤的處理方法，我們將在以后介紹如何創(chuàng)建成熟的中間層時進行介紹。問題在于，我要利用返回值并在需要時拋出一個自定義異常。
對于本解決方案示例，我最終生成了六個 Web 表單，并用它們測試了將近 30 個存儲過程和自定義函數(shù)。您可在本文開始部分列出的下載軟件包中找到所有這些完成的表單。
現(xiàn)在我們已經(jīng)定義了表、創(chuàng)建了存儲過程和函數(shù)并生成了 ASP.NET Web 表單，因此可以使用 Visual Studio .NET 2003 生成數(shù)據(jù)庫層的安裝腳本了。數(shù)據(jù)庫管理員（有時是您自己）可以將此腳本應(yīng)用到生產(chǎn)服務(wù)器上。
生成源代碼和安裝腳本
Visual Studio .NET 的另一個重要功能是它能夠為現(xiàn)有數(shù)據(jù)庫生成一個完整的生成腳本。實際上，您可以使用 Visual Studio .NET 為整個數(shù)據(jù)庫層生成源代碼（包括生成表和索引、授權(quán)、存儲過程等），還可以生成一個可用于在現(xiàn)有 SQL Server 上安裝這些數(shù)據(jù)庫對象的命令行腳本。
生成安裝腳本非常容易，它包括兩個步驟：首先，需要生成 T-SQL 腳本來創(chuàng)建數(shù)據(jù)庫對象（表、索引、過程等）。然后，生成一個針對目標 SQL Server 執(zhí)行 T-SQL 腳本的命令行腳本。
生成 T-SQL 腳本
生成安裝腳本之前，需要生成一個腳本集合，包括創(chuàng)建數(shù)據(jù)庫中的所有對象（表、索引、約束條件、用戶等）。
圖 11：生成腳本集合
以下是生成 T-SQL 腳本的步驟：
在 Server Explorer（服務(wù)器資源管理器）中，在選定的數(shù)據(jù)庫節(jié)點 (DotNetKB) 上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 Generate Create Script...（生成創(chuàng)建腳本...），打開 Generate Create Scripts（生成創(chuàng)建腳本）對話框。
在 General（常規(guī)）選項卡上，選中 Script all objects（編寫全部對象腳本）復(fù)選框。
在 Formatting（格式化）選項卡上，選中除最后一個復(fù)選框以外的所有復(fù)選框（僅與 7.0 版腳本兼容的功能）。僅在您的目標服務(wù)器是 SQL Server 7.0 而不是 SQL Server 2000 時，才需要最后一項。
在 Options（選項）選項卡上，在 Security Scripting Options（安全性腳本選項）部分，選中除 Script SQL Server logins（撰寫 SQL Server 登錄腳本）之外的所有選項。確保選中 Table Scripting Options（表腳本選項）部分中的所有復(fù)選框。同時保留 File Format（文件格式）和 Files to Generate（生成的文件）的默認單選按鈕。最后，當所有設(shè)置均已設(shè)置正確時，單擊 OK（確定）按鈕開始腳本生成過程。
系統(tǒng)將提示您指定文件位置。默認情況下，Visual Studio .NET 將指向現(xiàn)有數(shù)據(jù)庫項目中的 Create Scripts（創(chuàng)建腳本）文件夾。單擊 OK（確定）按鈕接受此默認位置。
該過程完成后，您將獲得保存數(shù)據(jù)庫中各對象的文件列表。此腳本集合還包含了用于創(chuàng)建相應(yīng)的用戶并為其授予正確權(quán)限的腳本。您甚至可以將這些信息保存到 Visual SourceSafe 中，用于處理以后的版本問題。最后，您可以根據(jù)需要將這些文件傳送給其他人，使他們可以直接更新或更改這些文件。至此，已經(jīng)完成了數(shù)據(jù)庫層的完整源代碼。
生成安裝腳本
最后一個步驟是讓 Visual Studio .NET 2003 生成一個命令行腳本，用于讀取所有 T-SQL 腳本并根據(jù)目標 SQL Server 運行這些腳本。為此，需要完成以下步驟。
在 Solution Explorer（解決方案資源管理器）中，在項目名稱 (DotNetKB) 上單擊鼠標右鍵，然后從上下文相關(guān)菜單中選擇 Create Command File...（創(chuàng)建命令文件...），打開 Create Command File（創(chuàng)建命令文件）對話框。
如果需要，可以更新 Name of Command File（命令文件名稱）輸入框，然后選擇合適的驗證方案（除非您需要遠程連接服務(wù)器，否則請使用 Microsoft Windows? NT?）。最后，單擊 Add All（全部添加）按鈕，以便將所有 T-SQL 腳本都包含在安裝操作中。
然后，單擊 OK（確定）按鈕生成腳本。這樣即可將完整的腳本加載到編輯器窗口（參見圖 12）中，您可以在該窗口中檢查腳本，所做的更改將在您關(guān)閉窗口時得到保存。
圖 12：生成安裝腳本
使用此腳本和 T-SQL 文件集，現(xiàn)在您可以將新數(shù)據(jù)庫層安裝到任何您具有相應(yīng)權(quán)限的目標 SQL Server 2000 上了。
小結(jié)
本部分討論了很多內(nèi)容。包括如何使用 Visual Studio .NET 2003 創(chuàng)建數(shù)據(jù)庫項目，如何創(chuàng)建新數(shù)據(jù)庫以及定義表、索引、約束條件和關(guān)系的數(shù)據(jù)庫圖，還介紹了使用 Visual Studio .NET 2003 添加可以存取表中存儲的數(shù)據(jù)的存儲過程和自定義函數(shù)。通過本文的學(xué)習(xí)，您還學(xué)會了如何使用自定義的 Windows 帳戶以及 IIS 和 Web 應(yīng)用程序中的 web.config 文件設(shè)置，在 SQL Server 和您的 ASP.NET 解決方案之間建立一種可靠的信任關(guān)系。本文最后還介紹了用于驗證數(shù)據(jù)層程序設(shè)計的測試 Web 表單示例，并說明了如何生成可用于在任何目標 SQL Server 上安裝完成的這個數(shù)據(jù)層的 T-SQL 腳本和命令行腳本。
也許您已經(jīng)注意到，數(shù)據(jù)庫層的相關(guān)討論中未涉及到專家記錄的存儲和再調(diào)用過程。這是因為我決定使用 XML 文件代替它。這樣，我們可以借此機會學(xué)習(xí)如何將 XML 作為數(shù)據(jù)源，以及如何將這種數(shù)據(jù)格式與 SQL Server 數(shù)據(jù)結(jié)合起來以創(chuàng)建一個完整的解決方案。在下一部分中，我們將定義 XML 存儲格式和讀寫這種數(shù)據(jù)的組件層，還將學(xué)習(xí)有關(guān) XML 序列化以及 ASP.NET 中的內(nèi)置數(shù)據(jù)高速緩存服務(wù)的相關(guān)知識。
