SQLServer2000创建数据存储层2[3]

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