使用SQLServer2000创建数据存储层1[2]

摘要：学习如何使用 SQL Server 2000 和 Visual Studio .NET 2003 为 DotNetKB ASP.NET 解决方案创建数据存储层。此外，还讨论了有关 SQL Server、IIS 和 ASP.NET 的安全性问题。 下载 JumpPart2Sample.msi。（请注意，在示例文件中，程序员的注释使用的是英文，本文中将其译为中文是为了便于读者理解。） 目录 简介
使用 Visual Studio .NET 2003 创建数据库图
使用 Visual Studio .NET 2003 编写存储过程
IIS、ASP.NET 和 SQL Server 的安全性问题
创建 ASP.NET 测试页
生成源代码和安装脚本
小结 简介 在“进入 ASP.NET 世界”的开始部分（进入 ASP.NET 世界 - 第 1 部分：应用程序规划与设计）中，我们讨论了名为 DotNetKB 的 ASP.NET 解决方案的基础规划和设计知识。本部分将详细讨论如何使用 Microsoft® SQL Server™ 2000 和 Microsoft Visual Studio® .NET 2003 创建数据存储层。其中包括创建数据库（添加表、索引、约束条件和关系）以及编写用于存取数据的存储过程。同时，我们还将讨论与 SQL Server、Internet 信息服务器 (IIS) 和 ASP.NET 有关的安全性问题。到本部分结束时，我们将获得一个适用于 DotNetKB 解决方案的功能完备且安全的数据存储系统。 使用 Visual Studio .NET 2003 创建数据库图 Visual Studio .NET 2003 的众多优势之一是，用户可将其用作主要的 SQL Server 编辑器来完成大多数任务。获得目标数据库服务器的适当权限后，您就可以轻松地使用 Visual Studio .NET 2003 创建各种数据库、表、索引、约束条件、关系、视图、存储过程和功能了。Visual Studio .NET 提供了一个可供您完成上述操作的默认数据库项目，还包括了用于创建表、触发器、存储过程等的大量模板，非常便于使用。最后，因为使用 Visual Studio .NET 作为 SQL Server 的编辑环境，所以还可以使用它将所有 SQL Server 脚本存储到 Microsoft Visual SourceSafe® 中。这对于共享项目和其他需要长期维护的情况而言非常方便。 引用用户方案 针对本系列文章中的项目 DotNetKB，我创建了 30 多个用户方案，用于标识支持本系列文章第 1 部分所概括的应用程序所需的任务。我们将使用这些用户方案来标识表中存储的数据以及为管理这些数据而在运行时执行的存储过程。下面是部分用户方案列表。完整的列表可以从相关支持站点 User Scenarios for DotNetKB Project（英文）上找到。 查看按关键字搜索的问题列表（按日期倒序排列） 查看按日期排序的问题列表（按日期倒序排列） 查看按主题排序的问题列表（按主题的字母顺序/问题的日期倒序排列） 查看某个特定主题的问题列表（按日期倒序排列） 查看无任何解答的问题列表（按日期倒序排列） 查看问题计数 查看无解答的问题计数 查看某个特定主题的问题计数 查看由某位专家解答的问题计数 查看某个问题及其解答列表（按解答日期倒序排列） 添加新问题 编辑现有问题 删除现有问题及其相关解答 如您所见，列表中仅仅涉及到问题记录的任务就有许多。而且您还需要处理解答、主题和专家记录。而在实际工作中，这才刚刚开始。首先，您必须标识需要为每条记录存储的数据元素（问题、解答、主题和专家），还需要将结果以表格的形式组织到 SQL Server 中的数据库中。 创建数据库项目 首先要打开 Visual Studio .NET 2003 并创建一个新的数据库项目。Visual Studio .NET 数据库项目的类型目前还不太确定，因为开始新项目时它隐藏在选项列表中。但开始使用后，我想您会发现数据库项目类型有许多优点，所以非常值得花费精力去掌握它们。 要使用 Visual Studio .NET 创建一个新的数据库项目，需要完成以下任务： 启动 Visual Studio .NET，如果新建项目对话框没有自动出现，请从主菜单中选择 File（文件）-> New（新建）-> Project（项目）。 当显示 New Project（新建项目）对话框时，展开左侧树视图列表中的 Other Projects（其他项目）文件夹，然后单击 Database Projects（数据库项目）文件夹。此时右侧将显示 Database Project（数据库项目）模板。 现在，在 Name:（名称:）输入框中键入项目名称。在我的例子中，键入的是 DotNetKB_Database，不过您可以根据需要键入任何内容。 然后单击 OK（确定）按钮，创建项目并在 Visual Studio .NET 中打开它。 屏幕上将出现一个对话框，要求您选择要与该项目相关联的数据库。此时，先单击 Cancel（取消）。下一步将创建一个新数据库并将其添加到您的项目中。 图 1 所示为您创建项目时，该项目在 Visual Studio .NET 中的外观。
图" 1：创建一个新的数据库项目 创建要使用的新数据库之后，可以在该数据库与您的项目之间建立一个连接，以便在 Visual Studio .NET 2003 中对其进行操作。为此，需要完成以下任务： 在 Solution Explorer（解决方案资源管理器）窗口中，展开您的 dotNETKB_Database 项目，以显示 Database References（数据库引用）项。 在 Database References（数据库引用）项上单击鼠标右键，然后从上下文相关菜单中选择 New Database Reference...（新建数据库引用...），打开 Data Link Properties（数据链接属性）对话框。 输入您在其中添加 DotNetKB 数据库的数据库服务器的名称，然后输入您的登录凭据并从下拉菜单中选择 DotNetKB。 单击 OK（确定）按钮，将引用添加到您的项目中。 图 2 所示为完成上述操作时对话框的外观。
图" 2：Data Link Properties（数据链接属性）对话框 至此，数据库创建完毕并被添加为您项目的引用。下一步，定义存储 DotNetKB 解决方案数据所需的表。 使用 Visual Studio .NET 定义数据库表 在 Visual Studio .NET 中定义数据库表的最简单的方法是创建一个数据库图。这样，您可以在一个类似“所见即所得”的编辑器中定义所有细节。您只需展开 Server Explorer（服务器资源管理器）中相应的树，在 Database Diagrams（数据库图）项上单击鼠标右键，然后从上下文相关菜单中选择 New Database Diagram...（新建数据库图...），即可启动一个新的空白图。第一次启动某个图时，系统将要求您从数据库中选择一个表。因为您尚未创建任何表，所以可以忽略该对话框。现在，可以开始定义您的表了。 本系列文章第 1 部分中生成的用户方案文档包含定义表所需的信息。学习定义如何在系统中添加新记录的方案，通常是了解需要存储哪些数据的最佳途径。有时，您需要查看诸如记录更新甚至是报告之类的其他方案，以确保没有遗漏其他字段。在本示例中，“添加记录”方案就是一个很好的参考方案。 例如，以下是用于添加问题的方案： 添加新问题 向系统中添加一条新问题记录，其中包括标题、日期/时间、指明该问题所属类别的主题 ID 以及问题正文。有时还需要提供问题提出者姓名及其电子邮件地址。添加新问题之后，将向调用函数返回一个唯一的整数问题 ID。 重要名词以粗体表示。阅读方案说明时，这些名词或表名（例如，“问题记录”就是一个很好的例子）往往能够表明需要存储哪些数据。使用上述信息，您可以在数据库图中添加一个新表并定义所需的列。下面的示例详细介绍了如何在数据库图中添加表。 在图“surface”上单击鼠标右键并从上下文相关菜单中选择 New Table...（新建表...）。输入 Questions（问题）作为表名，然后单击 OK（确定）将其添加到图中。 在 Questions（问题）表对话框中，键入上文所述方案中提供的字段信息。例如，Column Name（列名）= ID、Data Type（数据类型）= int、Length（长度）= 4，并取消选择 Allow Nulls（允许为空）复选框。对该表的其余部分重复上述操作（参见图 3）。
图" 3：Questions（问题）表 您将看到，第一列 (ID) 旁边有一个小的金色键。它表示该字段是该表的主键字段。要设置主键字段，可以在列表中的列名上单击鼠标右键，然后从上下文相关菜单中选择 Primary Key（主键）。另外，还应将此 ID 字段设置为以增量方式自动增加的标识列。这样，SQL Server 就可以为添加到表中的每条记录自动生成一个唯一的整数值。要进行此设置，请在该列上单击鼠标右键，从上下文相关菜单中选择 Properties（属性），然后在 Property Pages（属性页）对话框中选择 Columns（列）选项卡。其他的操作就很容易了（参见图 4）。
图" 4：Property Pages（属性页）对话框中的 Columns（列）选项卡 使用“添加主题记录”和“添加解答记录”方案中的信息，可以创建其他两个表。请务必为每个表创建 ID 列，并将这些列标记为标识列和主键。下面的图 5 显示了三个已完成的表。
图" 5：三个已完成的表 您会发现，这些表都通过连接线与数据库图连接起来。这些连接线表明表之间存在外键关系。例如，Questions（问题）表中的 TopicID 列与 Topics（主题）表中的 ID 列相关联。通过将这种关系存储到数据库中，您可以制定用以防止在数据库中保存非法数据的规则。本示例中的关系规则是，Questions.TopicID 列所允许的有效值只能是 Topics.ID 列中已存在的某条记录的值。 您可以通过将 Questions（问题）表中的 TopicID 列拖放到 Topics（主题）表中的 ID 列上，来定义这些关系。此时将出现一个对话框，显示规则定义的详细信息并要求您按下 OK（确定）按钮进行确认（参见图 6）。
图" 6：Create Relationship（创建关系）对话框 您可能会发现，Responses.QuestionID 和 Questions.ID 之间也定义了一个关系。 注意：您可能已经注意到，我们还没有为专家定义任何表。我决定将有关专家的信息存储在一个 XML 文件中，而不是存储在数据库中。这样做的主要原因是我们可以借此讨论一下如何读写 XML 数据，以便在同一个应用中融合 XML 数据和关系数据。我们将在下一部分中讨论有关专家数据的问题。 至此，数据库和表都已定义完毕。以上介绍了解决方案的实际数据存储过程。但是，我们还需要了解如何在表中读写信息。为此，我们将定义 SQL Server 中的存储过程。 使用 Visual Studio .NET 2003 编写存储过程 数据表定义了如何在数据库中存储数据，但没有说明如何存取数据。我们还需要了解读写记录以便从表中再次调用选定行和列的详细信息。开发人员通常会在其代码中编写一些特殊的查询语句，用于读写数据。这不仅会导致效率低下，还会带来安全性问题。在本应用中，所有数据存取工作都将通过 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 创建存储过程非常简单。首先，您需要打开一个数据库项目。这一操作已在本文第一部分中完成。然后，您可以使用代码模板创建存储过程，也可以针对 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 将使用存储过程的名称将该项内容保存到数据库中。如果键入的内容有误，编辑器会向您报告这些错误，您可以在保存存储过程之前修正这些错误（参见图 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 中发生的错误的整数值。您可以在调用例程中使用此代码完成其他诊断和错误处理操作。您现在并不需要执行任何操作，但它们是创建存储过程时应该遵循的两个好习惯。

原文转自：http://www.ltesting.net