65.9K
CodeProject 正在变化。 阅读更多。
Home

Input、Output 和 Input/Output SqlParameter 行为详解

Bob Crowley
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.84/5 (13投票s)

2016 年 11 月 2 日

CPOL

12分钟阅读

viewsIcon

36524

在本文中，我将解释 ADO.NET SqlParameter 的一些有时令人惊讶的行为，特别是 ParameterDirection、null 值和 DBNull.Value 对参数如何发送到数据库的影响。

在本文中，我将解释 ADO.NET SqlParameter 的一些有时令人惊讶的行为，特别是 ParameterDirection、null 值和 DBNull.Value 对参数如何发送到数据库的影响。

随附的 VisualStudio 2015 解决方案包含数据库项目以及下面概述的每个场景的单元测试。

我在这里专门关注定义了参数的 MSSQL 存储过程以及用于调用存储过程的 CommandType.StoredProcedure 类型的 ADO.NET SqlCommand。

跳转到

存储过程中没有默认值的输入参数

存储过程中有默认值的输入参数

存储过程中没有默认值的输出参数

存储过程中有默认值的输出参数

存储过程中没有默认值的输入输出参数

存储过程中有默认值的输入输出参数

下表描述了客户端 ADO.NET SqlParameter 属性的各种组合以及 SQL 存储过程中的相应参数。

Query Trace 行是 SQL Server Profiler 捕获的发送到数据库的查询。

所有 SqlCommand 都使用 ExecuteScalar() 执行。 有关示例的确切执行，请参阅随附的 GitHub 项目。

存储过程中没有默认值的输入参数

以下 4 个示例使用了此过程

CREATE PROCEDURE [dbo].[usp_Input_No_Default]
  @param1 varchar(250)
AS
  SELECT @param1;
RETURN 0;
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);
par.Direction = ParameterDirection.Input;
par.Value = “Foo”;
cmd.Parameters.Add(par);
查询跟踪 exec usp_Input_No_Default @param1=’Foo’
结果 没有惊喜，'Foo' 被发送到数据库，标量结果值为 “Foo”。

标量值：“Foo”
par.Value：“Foo”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);
par.Direction = ParameterDirection.Input;
par.Value = DBNull.Value;
cmd.Parameters.Add(par);
查询跟踪 exec usp_Input_No_Default @param1=NULL
结果 这次，一个 null 值参数被发送到数据库。标量结果值不是 null，而是 DBNull.Value。

标量值：DBNull.Value
par.Value：DBNull.Value
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);
par.Direction = ParameterDirection.Input;
par.Value = null;
cmd.Parameters.Add(par);
查询跟踪 exec usp_Input_No_Default @param1=default
结果 SqlException：过程或函数 ‘usp_Input_No_Default’ 期望参数 ‘@param1’，但未提供该参数。

由于未提供值，ADO.NET 应用 “default”。由于过程未定义默认值，因此会发生错误。必须有一个值或一个默认值，或者两者都有。
SqlParameter 未向 SqlCommand 添加参数。
查询跟踪 exec usp_Input_No_Default
结果 SqlException：过程或函数 ‘usp_Input_No_Default’ 期望参数 ‘@param1’，但未提供该参数。

我们收到与为参数提供 no Value 相同的异常，但 SQL 跟踪显示过程在没有任何参数的情况下被调用，甚至没有 “default” 值。

存储过程中有默认值的输入参数

以下 4 个示例使用了此过程

CREATE PROCEDURE [dbo].[usp_Input_Has_Default]
  @param1 varchar(50) = 'I''m a default value'
AS
  SELECT @param1;
RETURN 0;
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);
par.Direction = ParameterDirection.Input;
par.Value = “Foo”;
cmd.Parameters.Add(par);
查询跟踪 exec usp_Input_Has_Default @param1=’Foo’
结果 再次，没有惊喜，'Foo' 被发送到数据库，标量结果值为 “Foo”。过程定义了默认值，但由于提供了值，因此未使用它。

标量值：“Foo”
par.Value：“Foo”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);
par.Direction = ParameterDirection.Input;
par.Value = DBNull.Value;
cmd.Parameters.Add(par);
查询跟踪 exec usp_Input_Has_Default @param1=NULL
结果 再次，一个 null 值参数被发送到数据库。标量结果值不是 null，而是 DBNull.Value。由于数据库中的 null 是有效的，因此过程会满足请求，而不使用默认值。

标量值：DBNull.Value
par.Value：DBNull.Value
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);
par.Direction = ParameterDirection.Input;
par.Value = null;
cmd.Parameters.Add(par);
查询跟踪 exec usp_Input_Has_Default @param1=default
结果 由于未提供值，ADO.NET 应用 “default”，并且由于过程这次有一个默认值，因此结果是

标量值：“I’m a default value”
par.Value：null
SqlParameter 未向 SqlCommand 添加参数。
查询跟踪 exec usp_Input_Has_Default
结果 即使不提供参数，过程也会应用默认值，我们收到的标量值是

标量值：“I’m a default value”
par.Value：不适用。我们没有向命令添加参数，因此无法访问它。如果尝试这样做，将会抛出异常。

存储过程中没有默认值的输出参数

以下 4 个示例使用了此过程。请注意 3 点

  1. 参数定义为 OUTPUT
  2. 我们在设置它之前 SELECT 该参数（与所有前面的示例完全相同），以便观察返回的标量值以及执行后的参数 Value。
  3. 我们在设置它之后，将参数设置为与我们传入的值不同的值，以便观察行为。
CREATE PROCEDURE [dbo].[usp_Output_No_Default]
    @param1 VARCHAR(50) OUTPUT
AS
    SELECT @param1;
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.Output;
par.Value = “Foo”;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 这一个可能会让你感到惊讶。跟踪显示 @p1 = NULL，即使我们给它的值是 “Foo”。

标量值：DBNull.Value
par.Value：“changed by procedure”

par.Value 为什么是这样的，应该很清楚了，OUTPUT 参数可以在过程执行期间更改其值，并且在命令执行后会反映到客户端代码中。但是为什么标量值是 DBNull 呢？这是因为参数定义为仅 Output，任何对其的值都会被完全忽略。因此，当过程 SELECT 它时，它是 null，而不是 “Foo”。
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.Output;
par.Value = DBNull.Value;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 结果与我们设置参数为 “Foo” 时完全相同。这同样是因为 Output 参数上的值会被忽略。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.Output;
par.Value = null;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 结果与我们设置参数为 “Foo” 时完全相同。这同样是因为 Output 参数上的值会被忽略。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter 这次没有添加参数。
查询跟踪 exec usp_Output_No_Default
结果 SqlException：过程或函数 ‘usp_Output_No_Default’ 期望参数 ‘@param1’，但未提供该参数。

虽然我们已经看到尝试为输入参数设置值是徒劳的，但您仍然必须将参数添加到命令中，以便存储过程可以使用它（但前提是您不提供默认值，如下所示）。

存储过程中有默认值的输出参数

以下 4 个示例使用了此过程。请注意 4 点

  1. 参数定义为 OUTPUT
  2. 参数还具有默认值 “I’m a default value”
  3. 我们在设置它之前 SELECT 该参数（与所有前面的示例完全相同），以便观察返回的标量值以及执行后的参数 Value。
  4. 我们在设置它之后，将参数设置为与我们传入的值不同的值，以便观察行为。
CREATE PROCEDURE [dbo].[usp_Output_Has_Default]
    @param1 varchar(250) = 'I''m a default value' OUTPUT
AS
    SELECT @param1
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.Output;
par.Value = “Foo”;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_Has_Default @param1=@p1 output
select @p1
结果 行为与使用没有默认值的 Output 参数完全相同。您的客户端值将被忽略。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.Output;
par.Value = DBNull.Value;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 结果与我们设置参数为 “Foo” 时完全相同。这同样是因为 Output 参数上的值将被忽略。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.Output;
par.Value = null;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 结果与我们设置参数为 “Foo” 时完全相同。这同样是因为 Output 参数上的值将被忽略。

但是，为什么带有默认值的输入参数在传递 null 时会使用默认值，而输出参数则不会？对我来说，原因是个谜，但原因本身就在查询跟踪中。带有默认值的 null 值输入变为 “@param=default”，而 null 值的 Output 变为 “@p1=NULL”。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter 这次没有添加参数。
查询跟踪 exec usp_Output_No_Default
结果 这里我们甚至没有向 SqlCommand 添加参数，但我们收到了过程参数默认值的标量值。正如我们上面所见，传递 null 值参数不会触发使用过程的默认值，但完全省略参数会。太疯狂了。正如我们所演示的，Output 参数上的默认值仅在您根本不将参数发送到过程时才起作用。

标量值：“I’m a default value”
par.Value：不适用，未向 SqlCommand 添加参数，因此执行命令后我们无法访问其值。是的，这基本上会使仅具有输出参数的过程的执行目的失效。

存储过程中没有默认值的输入输出参数

以下 4 个示例将再次使用我们之前看到的 usp_Output_No_Default

CREATE PROCEDURE [dbo].[usp_Output_No_Default]
    @param1 varchar(250)
AS
    SELECT @param1;
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.InputOutput;
par.Value = “Foo”;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=’Foo’
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 由于此参数既是输入又是输出，因此该过程接受 “Foo” 的初始值，然后对其进行修改，该修改会反映在客户端的 par.Value 中。

标量值：“Foo”
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.InputOutput;
par.Value = DBNull.Value;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 同样，我们的输入值被接受，并且参数在过程中被修改，如前所述。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.InputOutput;
par.Value = null;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=default
exec usp_Output_No_Default @param1=@p1 output
select @p1
结果 抛出异常：“System.Data.SqlClient.SqlException” 在 System.Data.dll

附加信息：过程或函数 ‘usp_Output_No_Default’ 期望参数 ‘@param1’，但未提供该参数。

这很有趣，因为在跟踪中我们可以看到参数被发送为 “@p1=default”，但当它到达时，没有默认值可用，并且发生了异常。这与仅输入参数设置为 null 的行为相同。

您看，存储过程没有严格的 OUTPUT 参数。OUTPUT 隐式地既是 INPUT 又是 OUTPUT。因此，如果您不为没有默认值的参数提供输入值，您的命令将失败。
SqlParameter 未添加
查询跟踪 exec usp_Output_No_Default
结果 类型为 ‘System.Data.SqlClient.SqlException’ 的异常发生在 System.Data.dll 中，但未在用户代码中处理

附加信息：过程或函数 ‘usp_Output_No_Default’ 期望参数 ‘@param1’，但未提供该参数。

结果与 null 值的 InputOutput 参数相同。null 输入等同于根本不添加参数。

存储过程中有默认值的输入输出参数

以下 4 个示例将再次使用我们之前看到的 usp_Output_Has_Default

CREATE PROCEDURE [dbo].[usp_Output_Has_Default]
    @param1 varchar(250) = 'I''m a default value' OUTPUT
AS
    SELECT @param1
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.InputOutput;
par.Value = “Foo”;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=’Foo’
exec usp_Output_Has_Default @param1=@p1 output
select @p1
结果 由于此参数既是输入又是输出，因此该过程接受 “Foo” 的初始值，忽略默认值，然后对其进行修改，该修改会反映在客户端的 par.Value 中。

标量值：“Foo”
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.InputOutput;
par.Value = DBNull.Value;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=NULL
exec usp_Output_Has_Default @param1=@p1 output
select @p1
结果 同样，我们的输入值被接受，并且参数在过程中被修改，如前所述。

标量值：DBNull.Value
par.Value：“changed by procedure”
SqlParameter SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);
par.Direction = ParameterDirection.InputOutput;
par.Value = null;
cmd.Parameters.Add(par);
查询跟踪 declare @p1 varchar(250)
set @p1=default
exec usp_Output_Has_Default @param1=@p1 output
select @p1
结果 这次没有抛出异常，因为在过程中定义了默认值，并且它被用于代替缺乏值的参数。

标量值：I’m a default value
par.Value：“changed by procedure”
SqlParameter 未添加
查询跟踪 exec usp_Output_No_Default
结果 同样，没有异常。但是，由于我们没有客户端 SqlParameter，因此我们无法访问过程设置的值。

标量值：I’m a default value
par.Value：不适用

始终要记住的点是

  • 如果 SqlParameter 定义为 Input 或 InputOutput，并且具有 null 值，则过程必须定义默认值。
  • 如果存储过程定义了一个带有默认值的 OUTPUT 参数，那么该默认值仅在您的 SqlCommand 完全不包含该参数时才会被使用。
  • 仅 Output 参数的客户端 Value 会被丢弃，因此在执行命令之前设置值没有意义。
  • 存储过程没有像 SqlParameters 那样的 InputOutput 定义。过程中的所有 OUTPUT 参数都可以既是输入又是输出。
  • 如果您正在检查过程的元数据（例如使用 sys.parameters），则无法确定参数是否应在 ADO.NET 命令中定义为 InputOutput。例如，如果您正在构建一个在运行时动态创建 SqlParameters 的数据层，这一点就会发挥作用。如果参数将在客户端有一个值，那么您必须选择 Input/Output，但这无法通过元数据确定。
  • SqlParameter 同时具有 Value 和 SqlValue 属性。有什么区别？差别不大，正如您从引用源所见；当您设置 SqlValue 时，它只是设置 Value
    public object SqlValue {
     get { //omitted for brevity  }
     set { Value = value; }

© . All rights reserved.