数据访问应用块 .NET 2.0：获取返回值、输出值
在 .NET 2.0 中使用 SqlHelper 并获取返回值和输出值。
SqlHelper 的一个变体...如果您使用 SqlHelper，必读
文章的第一部分将向您展示如何从 ExecuteDataSet 获取返回值。文章的第二部分将向您展示如何从 ExecuteNonQuery 获取返回值和输出值。
本文将向您展示如何使用 Microsoft 应用程序块并能够获取您的存储过程的返回值和输出参数。您会在网上找到许多关于调用存储过程并使用 ADO.NET 获取返回值和输出值的文章...但是如果您想使用 SqlHelper，那么您就无法获取返回值了。
我将深入 SqlHelper 类的代码，并解释我们如何修改代码（只需一点点，不要跑开）以从我们的存储过程中获取我们珍贵的返回值。
Sqlhelper 类的小回顾
SqlHelper 有四个主要功能。让我们回顾一下它们
ExecuteDataset从 SQL 查询生成DataSet。ExecuteReader从 SQL 查询生成SqlDataReader。ExecuteScalar从 SQL 查询生成单个值对象。ExecuteNonQuery运行不带返回值的 SQL 查询（我们很快就会改变这一点）。
ExecuteDataset：ExecuteDataset 将运行您的基本 SELECT 查询并生成一个 DataSet，然后可以将其绑定到服务器对象或用于创建 DataView。与所有方法一样，有许多重载。
ExecuteReader：ExecuteReader 也用于 SELECT 语句，但通常保留用于性能真正重要的场景。SqlDataReader 就像 ADO classic 中的只进、只读记录集。它们适用于填充 ListBox 和 CheckBoxList。
ExecuteScalar：ExecuteScalar 方法有多种用途，例如返回只有一个值的 SELECT 查询，例如 COUNT。但最常见的用法是运行一个返回新行 ID 的 INSERT 语句。这是 Transact SQL 中一个相当常见的技巧，但它需要在存储过程中进行 CAST 以确保以最易于 .NET 处理的格式返回结果行 ID。
INSERT (ColumnName1, ColumnName2) VALUES (@parameter1, @parameter2)
SELECT CAST(@@Identity AS INTEGER)
开发人员被迫使用此方法将值从存储过程返回到您的业务类。以下是如果您想返回 Insert 的最后一个 ID 号，您必须如何做的示例。为了保持简单，该值以 Object 的形式返回到 .NET。要获取整数行 ID，请使用 Convert 语句。
int newRowId = Convert.ToInt32(SqlHelper.ExecuteScalar(connString,
"usp_InsertStuffProcedure",
parameter1,
parameter2));
第一部分 - ExecuteDataset 详细示例
目标：从存储过程获取数据集，并使用 SqlHelper 中的 ExecuteDataset 获取存储过程的返回值。
好的，让我们来看一些代码
- SqlProductProvider.cs 将扮演我们业务对象的角色。
- SqlHelper.cs 将扮演我们数据层的角色（Microsoft 代码）。
- default.aspx (default.cs) 将扮演 UI 层的角色。
- web.config - 将保存我们的连接字符串。
这是我们存储过程的快照
SELECT
ProductName
FROM
Products
WHERE
ProductName like @pProductName +'%'
....
....
Return (1) -- This is the Return Value
UI (default.cs) 有一个按钮，用于获取 DataSet 对象以链接到 GridView（非常简单）。我们正在调用业务层以获取我们的 DataSet（业务层将与数据层通信以提供 DataSet 对象）。
// Pass the Letter "a" to get all products that start with the letter "a"
protected void Button2_Click(object sender, EventArgs e)
{
GridView2.DataSource = sqlProductProvider.dsGetProductListsTest("a");
GridView2.DataBind();
}
sqlProductProvider 具有此代码
public static DataSet dsGetProductListsTest(String owner)
{
int ReturnValue;
object[] objParams = { 0, owner };
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
ReturnValue = (int)objParams[0];
return ds;
}
这就是我要求 SqlHelper 给我 DataSet 的部分。请注意，有 9 个重载方法可以获取 DataSet，但在本文中，我将只关注一种方式。下图显示了我将使用的重载方法（9 种方法中的 6 种）。
让我们回顾一下我们必须为 ExecuteDataset 重载 #6 传递的参数
connectionString-SqlConnection的有效连接字符串。spName- 存储过程的名称。parameterValues- 一个对象数组，用作存储过程的输入值（我们稍后讨论这个...）。Return- 包含命令生成的结果集的DataSet。
让我们逐行回顾代码。
这里，我们应该有返回值
int ReturnValue;
这是我将发送给 ExecuteDataset 的参数数组。在索引 [0] 中，我的值为 0，第二个值是我传递给存储过程的字符串。索引 [0] 用于存储过程的返回值，索引 [1] 是存储过程的输入参数。
object[] objParams = { 0, owner };
调用 ExecuteDataset 并获取我的返回值
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
ReturnValue = (int)objParams[0];
让我们回顾一下
- 有一个包含存储过程参数值的数组，其中索引 [0] 始终用于返回值。
- 我们执行方法，在
ExecuteDataset期间，SqlHelper类将返回给我们返回值（魔法？不，我们还有工作要做）。 - 将数组索引 [0] 赋值给我们的
int变量。
仔细查看 Sqlhelper 地牢... 小心，非常小心（开玩笑...这很简单）
所有魔法都发生在 ExecuteDataset 部分。以下是获取返回值的全局分解，然后我将用调试器证明它
- 传递一个值数组，其中索引 0 用于返回值。我们称之为
ArrayA。 - 调用
SqlHelperParameterCache.GetSpParameterSet，它返回一个 SQL 参数对象数组。我们称之为ArrayB。 - 此步骤只告诉我们存储过程有多少个参数（包括返回值），但我还没有在这些参数中赋值。
- 调用
AssignParameterValues为我们的参数赋值。您将ArrayA值映射到ArrayB并赋值参数值。 - 调用
ExecuteDataset- 通过运行此方法，我们应该在ArrayB中获得更新的ReturnValue参数，而不是ArrayA。
这就是我向您展示如何获取返回值或您可能拥有的任何输出值的部分。我们将详细查看 ExecuteDataset（重载 #6），并向您展示如何在代码改动很小的情况下从存储过程获取返回值。首先，让我们全局地看一下 ExecuteDataset（重载 #6）方法。稍后我将逐行详细解释。
internal static DataSet ExecuteDataset(string connectionString,
string spName, params object[] parameterValues)
{
DataSet dsReturn;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0)
throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter
// cache (or discover them & populate the cache)
//Original code from sqlHelper
//SqlParameter[] commandParameters =
// SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Added Parameter true to support ReturnValues
SqlParameter[] commandParameters =
// SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true );
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
//return ExecuteDataset(connectionString,
// CommandType.StoredProcedure, spName, commandParameters);
//Modify code - just store the dataset to dsReturn
dsReturn = ExecuteDataset(connectionString,
CommandType.StoredProcedure, spName, commandParameters);
//Update the array - parameterValues from the new CommandParameters
//that should have the ReturnValue
UpdateParameterValues(commandParameters, parameterValues);
}
else
{
// Otherwise we can just call the SP without params
//return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
//Modify code
dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
//Modify code
return dsReturn;
}
代码很简单。从参数缓存中提取此存储过程的参数（或发现它们并填充缓存）。
// Pull the parameters for this stored procedure from
// the parameter cache (or discover them & populate the cache)
//Original code from sqlHelper
//SqlParameter[] commandParameters =
// SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Added Parameter true to support ReturnValues
SqlParameter[] commandParameters =
SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);
我可以告诉 SqlHelperParameterCache.GetSpParameterSet 包含我的返回值（原始代码不支持 SqlHelperParameterCache.GetSpParameterSet 中的返回值）。请看下图以查看重载方法
调用后
// Added Parameter true to support ReturnValues
SqlParameter[] commandParameters =
SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);
调试器显示 SqlHelper 找到了我的 ReturnValue 参数和 ProductName 参数，它们都没有值，这很正常。
调用后
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
我们可以看到我们从业务层传递的值（数组）存储在 SqlParameter[] commandParameters 中。这是代码，因此您无需向上滚动。
public static DataSet dsGetProductListsTest(String owner)
{
int ReturnValue;
//0 - for the RetunValue, owner is the string we passed
// from the UI (have the value of "a")
object[] objParams = { 0, owner };
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
ReturnValue = (int)objParams[0];
return ds;
}
这是证明它的调试
调用后
// Call the overload that takes an array of SqlParameters
//return ExecuteDataset(connectionString, CommandType.StoredProcedure,
// spName, commandParameters);
//Modify code - just store the dataset to dsReturn
dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure,
spName, commandParameters);
我们可以看到我们得到了 commandParameters 中的返回值
数组不具有与 CommandParameters 相同的值
到目前为止一切顺利。但是在 SqlHelper 中（深入数据层）的 CommandParameters 中拥有返回值对我们帮助不大。我们的目标是在业务层和 UI 层中获取返回值。为了做到这一点，我必须将 CommandParameters 数组中的值复制到我传入的数组中，并用返回值的更新索引 0。好吧，在这一点上，我必须编写一个执行此操作的小函数。
//Update the array - parameterValues from
// the new CommandParameters that should have the ReturnValue
UpdateParameterValues(commandParameters, parameterValues);
这是方法。我将其放在 sqlHelper.cs 中，紧接着 AssignParameterValues 方法（您可以在下载示例中找到完整的代码）。
private static void UpdateParameterValues(
SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}
// We must have the same number of values
// as we pave parameters to put them in
{
throw new ArgumentException("Parameter count does " +
"not match Parameter Value count.");
}
// Iterate through the SqlParameters, assigning the values
// from the corresponding position in the value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
//Update the Return Value
if (commandParameters[i].Direction == ParameterDirection.ReturnValue)
{
parameterValues[i] = commandParameters[i].Value;
}
if (commandParameters[i].Direction == ParameterDirection.InputOutput)
parameterValues[i] = commandParameters[i].Value;
}
}
需要指出的一点是，我总是将返回值放在数组的索引 0 和值 0 中。这是在我的数组中给出返回值的代码
//Update the Return Value
if (commandParameters[i].Direction == ParameterDirection.ReturnValue)
{
parameterValues[i] = commandParameters[i].Value;
}
对于输出参数的情况，我将它们放在输入参数之后数组的末尾。在此示例中，我没有使用任何输出参数。我稍后将在使用 ExecuteNonQuery 进行 Insert 和 Update 时展示它。
\\Copy Input and Output Param to the Array
if (commandParameters[i].Direction == ParameterDirection.InputOutput)
parameterValues[i] = commandParameters[i].Value;
让我们现在看一下调试器。在这里您可以看到 commandParameters 数组和 parameterValues 数组具有相同的值。最重要的是，还有返回值！
在 .NET 数组中是 ByRef 类型而不是 ByValue，这意味着我们的数组在业务层中得到了更新。现在，我将向您展示调用 SqlHelper 的业务层中的大图景。此方法位于我们的业务层中的 SqlProductProvider 类中。我将逐步证明我的数组得到了更新并且我拥有返回值。
public static DataSet dsGetProductListsTest(String owner)
{
int ReturnValue;
object[] objParams = { 0, owner };
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString,
"GetProjectLists", objParams);
ReturnValue = (int)objParams[0];
return ds;
}
在运行 SqlHelper.ExecuteDataset 之前，我们的 Array[0] = 0（这是我给索引 0 的默认值）。
运行后
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
在这里您可以看到我得到了我的 ReturnValueobjParams[0] = 1，并且我的数组从 ExecuteDataset（我们的数据层）更新了。
第二部分 - ExecuteNonQuery 示例 - 获取返回值和输出值
SqlHelper 对 ExecuteNonQuery 执行相同的步骤；只是使用此方法，我们不会返回 DataSet 或 DataReader。事实上，使用此方法获取返回值和输出值会更有意义。我们的存储过程执行一个非常简单的 Insert 并有一个名为 @pInsertStatus 的输出值，我们的返回值将给出新记录的 @@IDENTITY 值。本示例的目标是获取返回值和我们的新输出值。
Create PROCEDURE Products_SP_Insert
-- ****************************************************************************
-- PARAM
-- ****************************************************************************
@pProductName nvarchar(40),
@pSupplierID int,
@pCategoryID int,
@pQuantityPerUnit nvarchar(20),
@pUnitPrice money,
@pUnitsInStock smallint,
@pUnitsOnOrder smallint,
@pReorderLevel smallint,
@pDiscontinued bit,
@pInsertStatus varchar(50) output
AS
....
INSERT INTO
Products
(
ProductName ,
SupplierID ,
CategoryID ,
QuantityPerUnit ,
UnitPrice ,
UnitsInStock ,
UnitsOnOrder ,
ReorderLevel ,
Discontinued
)
VALUES
(
@pProductName ,
@pSupplierID ,
@pCategoryID ,
@pQuantityPerUnit ,
@pUnitPrice ,
@pUnitsInStock ,
@pUnitsOnOrder ,
@pReorderLevel ,
@pDiscontinued
)
SET @intError = @@Error
SET
-- Error validation
IF (@intError = 0) BEGIN
SET @pInsertStatus = 'Insert Successfully'
SET @intReturn = @@IDENTITY
END ELSE BEGIN
SET @pInsertStatus = 'Insert Faild'
SET @intReturn = 0
END
-- ****************************************************************************
-- RETURN
-- ****************************************************************************
SET NOCOUNT OFF
RETURN (@intReturn)
从 UI 中，我使用了这段代码。注意：所有输出参数都在数组的末尾，返回值在索引 0 处。
protected void Button3_Click(object sender, EventArgs e)
{
// In a real world project it would be good to pass these values
// by Class object (or typed dataset) and give it to the Bussniess layer
// and in the bussiness layer you can fill up the array,
//to make things simple I hard coded the values here in the UI
object[] objParams = { 0,"ProductTest",1, 1,
"48 - 6 oz jars",15.00,50,20,0,false,null};
sqlProductProvider.InsertProduct(objParams);
}
这是业务层中的代码。我使用了与之前 ExecuteDataSet 相同的重载方法。
public static int InsertProduct(object[] objParams)
{
int ReturnValue;
string OutPutValue;
ReturnValue = SqlHelper.ExecuteNonQuery(ConnectionString, "Products_SP_Insert", objParams);
ReturnValue = (int)objParams[0];
OutPutValue = (string)Params[10];
return ReturnValue;
}
这是 SqlHelper.ExecuteNonQuery 方法
internal static int ExecuteNonQuery(string connectionString,
string spName, params object[] parameterValues)
{
int intReturn;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0)
throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from
// the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters =
SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
//return ExecuteNonQuery(connectionString,
// CommandType.StoredProcedure, spName, commandParameters);
intReturn = ExecuteNonQuery(connectionString,
CommandType.StoredProcedure, spName, commandParameters);
//Update the array - parameterValues from the new CommandParameters
//That should have the ReturnValue (I add this Method)
UpdateParameterValues(commandParameters, parameterValues);
return intReturn;
}
else
{
// Otherwise we can just call the SP without params
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
让我们逐行查看调试器并查看更改。这是我们的数组的视图，其中索引 0 是我们的返回值，索引 10 是我们的输出值，目前它们是空的。
在这里我们调用 GetSpParameterSet。注意：我将最后一个参数设置为 true 以在 SqlHelperParameterCache.GetSpParameterSet 中获取返回值。
// Pull the parameters for this stored procedure
// from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(
connectionString, spName,true);
我们的调试显示
在这里我们调用 AssignParameterValues（将我们的数组链接到参数数组并复制值）
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
我这里只显示了索引 1，但它们都有值
调用 ExecuteNonQuery
//I changed this line to the line below
//return ExecuteNonQuery(connectionString,
// CommandType.StoredProcedure, spName, commandParameters);
intReturn = ExecuteNonQuery(connectionString, CommandType.StoredProcedure,
spName, commandParameters);
下面显示了索引 0 和索引 10（返回值和输出值）的调试。如您所见，我在 commandParameters 中有我的返回值和输出值，但仍然不在我的数组中。
调用 UpdateParameterValues
//Update the array - parameterValues from the new CommandParameters
// that should have the Return Value (I add this Method)
UpdateParameterValues(commandParameters, parameterValues);
我们的调试显示
我们的数组已更新，索引 0 中包含返回值，索引 10 中包含输出值。完成所有这些操作后，我们的业务层将拥有返回值和输出值。
ReturnValue = SqlHelper.ExecuteNonQuery(ConnectionString,
"Products_SP_Insert", objParams);
ReturnValue = (int)objParams[0];
OutPutValue = (string)Params[10];
并证明它
摘要
好了，我完成了！在这篇文章中，我向您展示了如何通过 3 到 4 行代码拥有一个非常干净的业务逻辑层，调用存储过程并获取返回值和输出值。您无需担心任何事情，只需按照正确的顺序将值传递给 SqlHelper。
注意：我无法获取 ExecuteDataReader 的返回值，因为它保持与 SQL Server 的开放连接。如果您确实需要从“RecordSet”获取返回值，那么请使用 ExecuteDataSet 并使用 DataTable 对象。我包含了 SQLHelper 类的完整源代码（带有一些小的修改）。
反馈
请随意对本文留下任何反馈。希望您喜欢它。