在Visual Basic .NET中使用存储过程(3)

从存储过程返回值

　　以上示例有一个不足之处。Northwind Customers 表使用数字字母形式的主键，并且必须由插入数据的应用程序生成。也就是说，如果使用以上程序插入新记录，则必须为 CustomerID 自行创建由五个字符组成的值。

　　在真实软件中，为新记录自动生成主键更为常见。主键通常是按顺序分配的长整数。

　　为新记录设置主键有两种基本技术。应用程序可调用生成下一个可用 ID 的存储过程，然后将此 ID 直接放到 DataSet 的新行中。或者，用于插入记录的存储过程可以为记录派生新 ID，然后将其作为返回值传递回应用程序。

　　第一种技术需要一点额外的逻辑来获取新 ID 并将其放到新记录的相应位置。使用存储过程执行插入操作与以上示例类似。

　　但第二种技术要求在存储过程中使用一种新型参数。到目前为止我们见到的所有参数都是默认类型，即输入参数。实际上参数分四种类型：

Input	此参数只用于将信息从应用程序传输到存储过程。
InputOutput	此参数可将信息从应用程序传输到存储过程，并将信息从存储过程传输回应用程序。
Output	此参数只用于将信息从存储过程传输回应用程序。
ReturnValue	此参数表示存储过程的返回值。SQL Server 的存储过程参数列表中不显示该参数。它只与存储过程的 RETURN 语句中的值相关联。

　　存储过程为主键生成新值后，通常使用存储过程中的 RETURN 语句返回该值，因此用来访问该值的参数类型是 ReturnValue 参数。

　　ReturnValue 参数与其他类型的参数有一个重要的区别。通常，在 ADO.NET 中为 Command 对象配置的参数的顺序并不重要。参数名称只用来与存储过程中相应的参数相匹配。但是，对于 ReturnValue 参数，它必须是列表中的第一个参数。

　　也就是说，为 Command 对象配置 ReturnValue 参数时，必须首先在代码中配置该参数，这样它才能获取集合中的第一个数字索引。如果先配置任何其他参数，ReturnValue 参数将不能正常工作。

　　为了说明带返回值的存储过程的用法，我们编写一个在 Northwind Products 表中插入记录的示例。此表被设置为使用 Identity 列自动创建新产品 ID。遗憾的是，Northwind 示例数据库不包含执行所需操作的存储过程，所以在完成示例其余部分之前，我们需要向数据库插入一个这样的存储过程。

　　转到 Visual Studio .NET 中的 Server Explorer（服务器资源管理器）。打开 SQL Server 的节点，打开 SQL Server 实例的节点，然后打开 Northwind 数据库的节点。

　　右键单击 Stored Procedures（存储过程）节点，选择 New Stored Procedure（新建存储过程）。在出现的编辑窗口中，用以下文本替换其中的所有文本：

ALTER PROCEDURE dbo.MSDNInsertProduct
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
declare @ProductID int

SET NOCOUNT OFF;
INSERT INTO Products(ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES
(@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice,
@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT @ProductID = @@IDENTITY

RETURN @ProductID

　　现在关闭编辑窗口，当系统询问您是否要保存更改时，单击 Yes（是）。现在存储过程就已保存到数据库中，并被命名为 MSDNInsertProduct。

　　现在便可以编写代码来使用此存储过程。新建 Windows 应用程序，在空白 Form1 上，放置锚定到所有四个边的 DataGrid，还需添加名为 btnFill 和 btnInsertProduct 的两个按钮。将 btnFill 的 Text 属性设置为 Fill，将 btnInsertProduct 的 Text 属性设置为 Insert Product。

　　在 btnFill 的 Click 事件中，放置以下代码：

Dim sConnectionString As String = _
"server=localhost;uid=sa;pwd=;database=Northwind"
Dim sSQL As String = "SELECT * FROM Products"
Dim daGetProducts As New SqlDataAdapter(sSQL, sConnectionString)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts

　　它与本文前面所讲的代码大致相同，所以我们不再赘述。不要忘记必要时更改连接字符串，并在项目代码的顶部为 SQLClient 命名空间放置 Imports 语句。然后在 btnInsertProduct 的 Click 事件中放置以下代码：

Dim sConnectionString As String = _
"server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdInsertProduct As New SqlCommand("MSDNInsertProduct", cnNorthwind)
cmdInsertProduct.CommandType = CommandType.StoredProcedure
' 为存储过程设置参数
cmdInsertProduct.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, "ProductID"))
cmdInsertProduct.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue

cmdInsertProduct.Parameters.Add(New SqlParameter("@ProductName", _
SqlDbType.NVarChar, 40, "ProductName"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@SupplierID", _
SqlDbType.Int, 4, "SupplierID"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@CategoryID", _
SqlDbType.Int, 4, "CategoryID"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@QuantityPerUnit", _
SqlDbType.NVarChar, 20, "QuantityPerUnit"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitPrice", _
SqlDbType.Money, 8, "UnitPrice"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitsInStock", _
SqlDbType.SmallInt, 2, "UnitsInStock"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitsOnOrder", _
SqlDbType.SmallInt, 2, "UnitsOnOrder"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@ReorderLevel", _
SqlDbType.SmallInt, 2, "ReorderLevel"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@Discontinued", _
SqlDbType.Bit, 1, "Discontinued"))

Dim daInsertProduct As New SqlDataAdapter()
daInsertProduct.InsertCommand = cmdInsertProduct
Dim dsProducts As DataSet = CType(DataGrid1.DataSource, DataSet)

Dim drNewProduct As DataRow
drNewProduct = dsProducts.Tables("Products").NewRow
drNewProduct.Item("ProductName") = "Billy's Sesame Oil"
drNewProduct.Item("SupplierID") = 4
drNewProduct.Item("CategoryID") = 7
drNewProduct.Item("QuantityPerUnit") = "6 10oz bottles"
drNewProduct.Item("UnitPrice") = 69
drNewProduct.Item("UnitsInStock") = 12
drNewProduct.Item("UnitsOnOrder") = 0
drNewProduct.Item("ReorderLevel") = 6
drNewProduct.Item("Discontinued") = False
dsProducts.Tables("Products").Rows.Add(drNewProduct)

daInsertProduct.Update(dsProducts.Tables("Products"))

MsgBox(drNewProduct.Item("ProductID"))

　　此代码与如上所示的代码类似，只是为返回值配置参数的代码行不同。请注意，它是第一个参数，并被设置为将返回值放回到 ProductID 字段中。

　　用于向数据集中插入新行的代码是标准 ADO.NET 代码，所以我们就不再赘述。它为产品记录创建一行新的适当结构（使用产品 DataTable 的 NewRow 方法），然后将数据放入行中，最后向产品 DataTable 的 Rows 集合中添加行。

　　现在运行程序进行测试。单击 Fill 按钮，但不对网格中的数据进行任何更改。然后按 Insert Product 按钮。将插入 Billy's Sesame Oil 的新产品记录，并且出现的消息框会通知您为其返回的 ProductID。还可以打开网格中的 Products 表，滚动到底部，并看到已添加了新行。

　　使用 Server Explorer（服务器资源管理器）编写参数代码

　　以上代码编写起来既冗长又繁琐。但是，DataAdapter Configuration Wizard（数据适配器配置向导）提示可以使用 Visual Studio 为我们编写此代码。DataAdapter Configuration Wizard（数据适配器配置向导）为完整配置所需的四个存储过程（分别是 Select、Update、Insert 和 Delete）生成了代码。假设您象以上示例一样只需要一个存储过程的代码，可以将其截短。要获得只与一个存储过程通信的预先编写好的代码，只需展开 Server Explorer（服务器资源管理器）以显示需要访问的存储过程，然后将该存储过程拖到设计界面上。将看到为该存储过程创建的 DataAdapter 和 Command 对象，代码的设计器部分包含为该存储过程配置参数所需的所有代码。可以按原样使用该代码，也可以根据需要复制并调整后使用。

　　小结

　　本文中的示例仍是演示软件，但至少足以向您说明如何访问存储过程，以便您开始编写自己的真实软件。当然，您需要了解要访问的存储过程，并且可能需要向数据库管理员 (DBA) 或其他组员咨询以获取该信息。

　　对于复杂系统，存储过程有许多优势。希望您在本文中学到了足够的知识，可以不必担心如何开始使用它们。第一次尝试编写代码时，您可能希望使用 DataAdapter Wizard（DataAdapter 向导）或 Server Explorer（服务器资源管理器）。但如果您能在必要时自行编写访问代码，则可以更有效地使用存储过程。