使用 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 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 创建数据层时的另一个问题，即安全性问题。