如何将空值传递到SqlCommand的参数中

Question

在任何人评论之前，这个问题已经在其他问题中得到了解答，我知道这一点。但是尽管我已经查看了以下答案:

• 将null分配给SqlParameter
• 将null参数发送到Sql Server
• 分配NULL值给SqlParameter的最佳方法

甚至我自己的问题：

• SQLCommand.CreateParameter.Value = DBNull.Value未返回结果

但我仍然无法使我的查询返回带有空参数的值。

我已经尝试简化我的代码，以便可以在此处查看。

我也尝试过这个：

 int? i = null;

 SqlConnection connection = new SqlConnection(Properties.Settings.Default.connstring.ToString());

 SqlCommand cmd = new SqlCommand();
 cmd.Connection = connection;
 cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk";
 cmd.Parameters.AddWithValue("@parent_pk", i ?? Convert.DBNull);

 cmd.Connection.Open();

 var dataReader = cmd.ExecuteReader();
 var dataTable = new DataTable();
 dataTable.Load(dataReader);

 cmd.Connection.Close();

我已经尝试过各种变化，只是这样做

cmd.Parameters.AddWithValue("@parent_pk", DBNull.Value);

并且我已经尝试使用查询

cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk or @parent_pk Is Null";

我尝试明确将参数声明为可为空

cmd.Parameters.AddWithValue("@parent_pk", i ?? Convert.DBNull).IsNullable = true;

由于某种原因，我认为我已经把它做好了，这也是我接受答案的原因，但我错了，它只是无论什么值都返回给我。

我知道命令对象正在连接并返回数据，因为如果我输入有效值（比如27），它会返回记录...我也知道有记录的值为空...但无论我如何设置，当我尝试传递空值作为参数时，我总是得不到任何返回。

任何能帮助我弄清楚我在这里做错了什么的人，我将非常感激。

- Bastyon

---

当您使用SQL Profiler查看实际发送到SQL Server的内容时，传递的是什么值而不是Null？ - PhillipH

你为什么不使用存储过程而是在Visual Studio中编写纯查询语句呢？我也赞同@PhillipH提出的SQL Profiler的想法。 - techspider

如果我理解正确，你所需要的是："parent_pk = @parent_pk 或者 (@parent_pk 为空并且 parent_pk 也为空)" - Evk

EVK，你的解决方案完美地运行了。 - Bastyon

Answer 1

由于我在评论中提供的解决方案可行，因此我将在此发布。基本上，问题已经如先前所述（甚至在你之前的问题中已经有答案），就是你需要使用 IS NULL 来比较 SQL 中的 null 值。由于你可能有两种情况（你的参数为 null 或不为 null），你必须测试这两种情况，像这样：

where parent_pk = @parent_pk or (@parent_pk IS NULL and parent_pk IS NULL)

- Evk

---

我在想为什么我以前不知道这个。有没有其他的方法可以替代这种做法？ - Kr15

@Kr15 你可以使用 "set ansi_nulls off" 来改变等式运算符如何处理空值，但我不建议这样做。根据 ISO sql 标准，两个空值之间的比较是未定义的 (null = null 不是真也不是假)，而且这样做有原因存在，所以最好还是接受这一点吧 :) - Evk

哦，好的，谢谢。但是也许你能解释一下为什么 null == null？不应该被认为是 true 吗？ - Kr15

我发现我的数据库实际上是使用SET ANSI_NULLS OFF和SET ANSI_NULL_DEFAULT OFF进行配置的。我想知道，如果我设置"... parms[0].Value = DBNull.Value;"（其中parms [0]是“new SqlParameter（"@name"，SqlDbType.VarChar，50）;”），然后让我的Sql语句类似于：“.. WHERE name = @name;”，为什么它不起作用？这是因为生成适当的SQL语句对框架来说太“困难”了（因为应该在那里生成“... WHERE @name IS（NOT）NULL;”）吗？ - Kr15

很难说为什么 ANSI 空值在你的情况下不起作用。至于 SQL，框架与其无关。如果你在纯 SQL 中执行相同的操作（使用参数），结果也是一样的。 - Evk

关于为什么，请尝试阅读此问题及其所有（不仅仅是已接受的）答案。https://dev59.com/onI-5IYBdhLWcg3wc4Cw。评论太短无法解释，但最终您会意识到原因。 - Evk

Answer 2

问题在于你选择的SQL查询语句：

``` cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk"; ```

如果 @parent_pk 的值为 NULL，它将返回空值，因为即使在 SQL Server 中 parent_pk 为 null，NULL = NULL 返回 false。但是，NULL is NULL 返回 true。

cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk or @parent_pk Is Null";

如果你传递一个 NULL 给 @parent_pk，那么这个查询会返回所有结果，因为在每一行的计算中，@parent_pk 都没有改变，所以条件 @parent_pk is NULL 会被认为是真。

如果要实现你想要的功能，需要进行以下操作：

string nullCommandText = "Select * from view_nests where parent_pk IS @parent_pk";
string commandText = "Select * from view_nests where parent_pk = @parent_pk";
cmd.CommandText = i == null? nullCommandText : commandText;
cmd.Parameters.AddWithValue("@parent_pk", i ?? Convert.DBNull);

还有两个评论：

1. 不应使用*，而是列出您想要的列。
2. 如果必须执行此类逻辑，请在数据库端创建存储过程并使用该存储过程。

- Peter4499