SQL Server 2000 创建数据存储层2 3 -pc6资讯

您的位置：首页 → 精文荟萃 → 软件资讯 → SQL Server 2000 创建数据存储层2 3

SQL Server 2000 创建数据存储层2 3 时间：2004/10/7 19:15:00来源：本站整理作者：蓝点我要评论(0)

下面是一个更复杂的存储过程。此过程用于从数据库中检索单条主题记录。您会发现一些附加项，包括输入参数、返回特定值的输出参数，以及检查输入参数并在需要时返回错误的某些程序代码。

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 int AS 　 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 和 SQL Server 的安全性问题

SQL Server、Internet 信息服务器和 ASP.NET 引擎都提供了坚实可靠的安全模型，它们可以很好地在一起协同工作。为了保证用户数据和应用程序的安全，Microsoft 还为每项服务的默认设置设置了相当低的值。大多数开发人员面临的挑战是如何使用 SQL Server、IIS 和 ASP.NET 在应用程序和数据之间设置适当的信任级别，而不会留下可被别人轻易攻入的安全漏洞。由于涉及三类服务（SQL Server、IIS 和 ASP.NET），所以需要采取三个关键的步骤来确保解决方案的安全。本部分讨论一种为 Web 应用程序设置足够权限和信任级别的更常用（且可靠）的方法。

注意：关于安全性和 Web 解决方案这个大主题，本系列文章难以展开较充分的讨论。要更好地理解此问题和可能的解决方案，请参阅安全 ASP.NET 应用程序的创建模式和实践系列文章：验证、授权和安全通信。

定义 DotNetKB 自定义 IIS 用户帐户

保证 Web 应用程序安全性的最安全的方法是定义一个权限有限的自定义用户，然后对 IIS 进行配置，使之能够在执行您的 Web 应用程序时能作为自定义用户运行。这是相当容易实现的，可以确保访问您的 Web 应用程序的每个访问者都只具有您希望他们具有的权限。

第一步是生成一个新的 Windows 用户（本例中称为 DotNetKB），为其设置一个增强型密码，然后将其添加到 Windows 来宾组 (Guest Windows Group) 中。同时，确保选中Password never expires（密码永不过期）和User cannot change password（用户不能更改密码）复选框。这样将生成一个权限有限的用户，在 IIS 中运行您的 Web 应用程序时，您可以将其用作标识（参见图 7）。

图 7：生成的权限有限的用户

然后，调用 Internet 信息服务器管理员并选择承载这些网页的 Web 应用程序。在本例中，您可以选择承载前文所生成的测试页的 Web 应用程序 (DotNetKB_WebSite)。在树视图中的 Web 应用程序上单击鼠标右键，然后从上下文相关菜单中选择Properties...（属性...）。然后选择Directory Security（目录安全性）并单击该对话框Anonymous access and authentication control（匿名访问和验证控制）部分中的Edit（编辑）按钮。最后，输入自定义用户名 (DotNetKB)，取消选择Allow IIS to control password（允许 IIS 控制密码）复选框，并输入该自定义用户帐户的密码。完成所有这些工作之后，单击OK（确定）按钮，将这些更改保存到 IIS 配置数据库中（参见图 8）。

图 8：Authentication Methods（验证方法）对话框

此时，IIS 将在一个权限有限的自定义帐户下运行。任何访问者访问您应用程序的网页时，都将以这个自定义用户身份运行，且只具有该自定义用户的验证权限。

授权 DotNetKB 用户帐户访问 SQL Server

然后，您需要为该自定义用户授予访问数据库 (DotNetKB) 的相应权限。为此，您可以使用 Microsoft SQL Server 企业管理器或编写一个自定义脚本，以创建一个这样的用户并授予其访问特定对象的权限。本文介绍如何使用 SQL Server 企业管理器完成此操作。您还可以从后文中看到一个脚本示例。

注意：尽管 Visual Studio .NET 2003 具有与 SQL Server 兼容的许多强大的集成功能，但也不允许从 Visual Studio .NET 2003 中轻松管理用户和用户权限。在大型的组织和团队中，这些高级任务通常由数据库管理员完成。

因此，启动 SQL Server 企业管理器之后，您可以按照以下步骤将自定义用户 (DotNetKB) 添加数据库中（参见图 9）：

在左侧的树视图中，展开节点以显示DotNetKB数据库。在我的计算机上，树视图的结构如下：Console Root | SQL Server Group | (LOCAL) (Windows NT) | Databases | DotNetKB。

然后，在数据库下的Users（用户）节点上单击鼠标右键，并选择New Database User...（新建数据库用户...）。显示Database User Properties - New User（数据库用户属性 - 新建用户）对话框时，从Login name（登录名）下拉框中选择（

<新建>）。

显示SQL Server Login Properties - New Login（SQL Server 登录属性 - 新建登录）对话框时，选择General（常规）选项卡，并在Name（名称）输入框中输入DotNetKB。确保选中Windows Authentication（Windows 验证）单选按钮，并从Domain（域）下拉框中选择自定义用户帐户所在的计算机的名称。然后从Database（数据库）下拉框中选择DotNetKB。

现在，选择Databases（数据库）选项卡，在对话框顶部的列表中找到DotNetKB数据库并选中它。然后，确保选中对话框底部列表中的public（公共）角色。最后，单击对话框底部的OK（确定）按钮，保存您的更改。

图 9：在数据库中添加自定义用户

然后，您需要为 DotNetKB 数据库中的所有存储过程和自定义函数添加执行权限。为此，您只需为 public（公共）角色授予权限。您可以将权限授予 DotNetKB 用户，这样将使以后的登录（当这些用户获得访问 DotNetKB 的权限时）更容易执行存储过程，而不需要为每个用户添加新的权限。

下面是为 DotNetKB 数据库中的存储过程和函数授予执行权限的步骤：

突出显示树视图中 DotNetKB 数据库下的Users（用户）节点，以显示此数据库的用户列表。找到DotNetKB用户并在其上双击，打开Database Users Properties（数据库用户属性）对话框。

突出显示（选中）public（公共）角色时，单击Properties...（属性...）按钮，打开Database Role Properties（数据库角色属性）对话框。然后单击Permissions...（权限...）按钮，显示数据库对象和权限设置列表。

选中对话框顶部Database role（数据库角色）下拉列表中的public（公共）角色之后，找到为此数据库定义的所有存储过程和自定义函数（可能需要展开对话框才能看到全名），并确保选中各项旁边的EXECUTE（执行）复选框。您可能会发现某些系统对象的其他一些复选框也被选中了，请不要更改这些选项。

最后，设置所有的 EXECUTE（执行）权限后，单击OK（确定）按钮，保存更改并关闭对话框。依次单击OK（确定）按钮，直到所有对话框均被关闭。

至此，您已为 IIS 创建了自定义用户，并设置了该用户在 SQL Server 中的相应权限。现在，您需要在 ASP.NET Web 项目中进行一个配置更改，确保 ASP.NET 使用同一个用户帐户执行对 SQL Server 的所有调用。

设置您的 ASP.NET 应用程序以模拟 DotNetKB 用户

为 IIS 下运行的 ASP.NET Web 应用程序生成坚实可靠的配置的最后一个步骤是：配置 ASP.NET Web 应用程序，使之能够接受来自 IIS 的 Windows 用户标识并能用于访问其他操作系统资源。为此，您只需在 web.config 根文件中输入一行代码。

注意：尽管目前我们还没有真正开发出用于承载我们的页面的 ASP.NET Web 应用程序，但您可以使用这些信息在生成测试页的下一节中验证数据访问层的功能。

修改后的 web.config 文件如下所示：

... 其他要素 ... 　　... 其他要素 ...

请注意，您只需添加元素并将模拟特性设置为true（真）。不必输入用户帐户或密码，因为这些信息将由 IIS 提供。也就是说，即使其他人能够读取您的配置文件，他们也无法确定使用哪些标识凭据来执行您的 Web 应用程序。

至此，您已生成了自定义用户，并为其设置了访问 SQL Server 和 IIS 的相应权限。现在，我们来创建一些测试页，确保它能够正常工作。从这里您可以看出一切正常。

创建 ASP.NET 测试页

创建测试页始终是访问 SQL Server 数据层并验证输入和输出参数是否得到正确处理的好办法。实际上，这是确保以后的生产解决方案中的 ASP.NET 页和组件能够按照预期方式工作的唯一办法。这对于从解决方案中的某个层调用其他层时的验证信任边界和安全性问题尤其正确。

另外，在进行测试时，请勿拘泥于创建生产类接口。您只需测试目标方法。实际上，故意创建一些您不愿以之为最终生产解决方案的“丑陋”测试页是一个好的策略！本文中，我创建了一些非常简单的 ASP.NET 页，其中包含一个测试记录列表和一个用于添加、编辑和删除测试记录的输入表单。

例如，以下是用于测试主题记录的 WebForm 布局。您会发现，它包含错误消息或其他消息的状态标签、记录计数标签、显示记录列表的数据网格、用于输入检索时使用的记录 ID 的输入框以及支持添加、编辑和删除记录的小表格（参见图 10）。

图 10：用于测试主题记录的 WebForm 布局

在创建测试页时，最好使代码简洁明了。我通常会为每个按钮添加一小段代码，以调用本地方法来处理数据库操作。以下是 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) ' 进行数据库调用　　　　　　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

请注意，本方法中实际执行的唯一操作是由GetItem(ID)方法调用处理的。它执行数据库调用并使用返回的值设置本地变量。以下是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 　　　　' 设置返回值　　　　mTitle = pTitle.Value.ToString() 　　　　mDescription = pDescription.Value.ToString() 　　Catch ex As Exception 　　　　Throw New Exception(ex.Message, ex) 　　End Try End Sub

GetItem方法的另一个重要方面是使用了返回值参数。它在前几行代码中进行声明，并在执行存储过程后进行检查。请注意，我检查了已知错误代码 100 和 101。有关其他错误的处理方法，我们将在以后介绍如何创建成熟的中间层时进行介绍。问题在于，我要利用返回值并在需要时抛出一个自定义异常。

对于本解决方案示例，我最终生成了六个 Web 表单，并用它们测试了将近 30 个存储过程和自定义函数。您可在本文开始部分列出的下载软件包中找到所有这些完成的表单。

现在我们已经定义了表、创建了存储过程和函数并生成了 ASP.NET Web 表单，因此可以使用 Visual Studio .NET 2003 生成数据库层的安装脚本了。数据库管理员（有时是您自己）可以将此脚本应用到生产服务器上。

生成源代码和安装脚本

Visual Studio .NET 的另一个重要功能是它能够为现有数据库生成一个完整的生成脚本。实际上，您可以使用 Visual Studio .NET 为整个数据库层生成源代码（包括生成表和索引、授权、存储过程等），还可以生成一个可用于在现有 SQL Server 上安装这些数据库对象的命令行脚本。

生成安装脚本非常容易，它包括两个步骤：首先，需要生成 T-SQL 脚本来创建数据库对象（表、索引、过程等）。然后，生成一个针对目标 SQL Server 执行 T-SQL 脚本的命令行脚本。

生成 T-SQL 脚本

生成安装脚本之前，需要生成一个脚本集合，包括创建数据库中的所有对象（表、索引、约束条件、用户等）。

图 11：生成脚本集合

以下是生成 T-SQL 脚本的步骤：

在Server Explorer（服务器资源管理器）中，在选定的数据库节点 (DotNetKB) 上单击鼠标右键，然后从上下文相关菜单中选择Generate Create Script...（生成创建脚本...），打开Generate Create Scripts（生成创建脚本）对话框。

在General（常规）选项卡上，选中Script all objects（编写全部对象脚本）复选框。

在Formatting（格式化）选项卡上，选中除最后一个复选框以外的所有复选框（仅与 7.0 版脚本兼容的功能）。仅在您的目标服务器是 SQL Server 7.0 而不是 SQL Server 2000 时，才需要最后一项。

在Options（选项）选项卡上，在Security Scripting Options（安全性脚本选项）部分，选中除Script SQL Server logins（撰写 SQL Server 登录脚本）之外的所有选项。确保选中Table Scripting Options（表脚本选项）部分中的所有复选框。同时保留File Format（文件格式）和Files to Generate（生成的文件）的默认单选按钮。最后，当所有设置均已设置正确时，单击OK（确定）按钮开始脚本生成过程。

系统将提示您指定文件位置。默认情况下，Visual Studio .NET 将指向现有数据库项目中的Create Scripts（创建脚本）文件夹。单击 OK（确定）按钮接受此默认位置。

该过程完成后，您将获得保存数据库中各对象的文件列表。此脚本集合还包含了用于创建相应的用户并为其授予正确权限的脚本。您甚至可以将这些信息保存到 Visual SourceSafe 中，用于处理以后的版本问题。最后，您可以根据需要将这些文件传送给其他人，使他们可以直接更新或更改这些文件。至此，已经完成了数据库层的完整源代码。

生成安装脚本

最后一个步骤是让 Visual Studio .NET 2003 生成一个命令行脚本，用于读取所有 T-SQL 脚本并根据目标 SQL Server 运行这些脚本。为此，需要完成以下步骤。

在Solution Explorer（解决方案资源管理器）中，在项目名称 (DotNetKB) 上单击鼠标右键，然后从上下文相关菜单中选择Create Command File...（创建命令文件...），打开Create Command File（创建命令文件）对话框。

如果需要，可以更新Name of Command File（命令文件名称）输入框，然后选择合适的验证方案（除非您需要远程连接服务器，否则请使用 Microsoft Windows? NT?）。最后，单击Add All（全部添加）按钮，以便将所有 T-SQL 脚本都包含在安装操作中。

然后，单击OK（确定）按钮生成脚本。这样即可将完整的脚本加载到编辑器窗口（参见图 12）中，您可以在该窗口中检查脚本，所做的更改将在您关闭窗口时得到保存。

图 12：生成安装脚本

使用此脚本和 T-SQL 文件集，现在您可以将新数据库层安装到任何您具有相应权限的目标 SQL Server 2000 上了。

小结

本部分讨论了很多内容。包括如何使用 Visual Studio .NET 2003 创建数据库项目，如何创建新数据库以及定义表、索引、约束条件和关系的数据库图，还介绍了使用 Visual Studio .NET 2003 添加可以存取表中存储的数据的存储过程和自定义函数。通过本文的学习，您还学会了如何使用自定义的 Windows 帐户以及 IIS 和 Web 应用程序中的 web.config 文件设置，在 SQL Server 和您的 ASP.NET 解决方案之间建立一种可靠的信任关系。本文最后还介绍了用于验证数据层程序设计的测试 Web 表单示例，并说明了如何生成可用于在任何目标 SQL Server 上安装完成的这个数据层的 T-SQL 脚本和命令行脚本。

也许您已经注意到，数据库层的相关讨论中未涉及到专家记录的存储和再调用过程。这是因为我决定使用 XML 文件代替它。这样，我们可以借此机会学习如何将 XML 作为数据源，以及如何将这种数据格式与 SQL Server 数据结合起来以创建一个完整的解决方案。在下一部分中，我们将定义 XML 存储格式和读写这种数据的组件层，还将学习有关 XML 序列化以及 ASP.NET 中的内置数据高速缓存服务的相关知识。

注意：您可以通过访问相关的 DotNetKB（英文）Web 站点随时了解此项目的最新信息。在 MSDN? 上发布各部分之前，我会随时在此站点上张贴更新内容、读者意见的反馈和其他信息。

Mike Amundsen 提供培训、演讲和咨询服务。要了解他的详细信息或与他联络，请访问他的站点 http://amundsen.com/。此外，还可以在