带有空值的 SQL Server 查询

发布于 2024-09-26 14:49:37 字数 294 浏览 2 评论 0原文

在我的 C# 代码中，我必须执行如下 SQL 查询：

context.ExecuteStoreQuery("SELECT * FROM MyTable WHERE Field0 = {0} AND 
    Field1 = {1}", field0, field1)

当 C# 中的 field1 = null 且数据库中的 NULL 时，此查询不起作用。（我必须对 IS NULL 使用不同的语法）

如何在不使用 if 的情况下纠正这个问题（实际上，我有 10 个字段......）？

原文

In my C# code, I have to do an SQL Query like this :

context.ExecuteStoreQuery("SELECT * FROM MyTable WHERE Field0 = {0} AND 
    Field1 = {1}", field0, field1)

When field1 = null in c# and NULL in database this query doesn't work. (I have to use a different syntax with IS NULL)

How can I correct this without make an if (in reality, I have 10 fields...) ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

疧_╮線 2024-10-03 14:49:37

默认情况下，SQL Server 不允许您将值与null 进行比较。所有比较都解析为 false，即使是逻辑上相反的比较。换句话说，您可以执行以下操作：

where field = 1 和 where field <> 1..如果 field 为 null，则两者在逻辑上都解析为 false。

无论如何，您需要在查询中显式检查 null：

context.ExecuteStoreQuery(@"SELECT * FROM MyTable WHERE 
    (Field0 = {0} or (Field0 is null and {0} is null))  AND 
    (Field1 = {1} or (Field1 is null and {0} is null))", field0, field1)

By default, SQL server does not allow you to compare a value to null. All comparisons resolve to false, even those that are logically opposite. In other words, you can do:

where field = 1 and where field <> 1. If field is null, both logically resolve to false.

In any case, you need an explicit check for null in your query:

context.ExecuteStoreQuery(@"SELECT * FROM MyTable WHERE 
    (Field0 = {0} or (Field0 is null and {0} is null))  AND 
    (Field1 = {1} or (Field1 is null and {0} is null))", field0, field1)

回复收藏 0 原文

留一抹残留的笑 2024-10-03 14:49:37

public string appendCondition(String sqlQuery, String field, Object value)
{ 
 string resultQuery = sqlQuery + " " + value == null ? " IS NULL " : "=" + value.ToString();
 return resultQuery;
}

希望您可以添加简单的逻辑来自己添加“WHERE”或“AND”。

public string appendCondition(String sqlQuery, String field, Object value)
{ 
 string resultQuery = sqlQuery + " " + value == null ? " IS NULL " : "=" + value.ToString();
 return resultQuery;
}

Hope you can add simple logic to add "WHERE" or "AND" by yourself.

回复收藏 0 原文

驱逐舰岛风号 2024-10-03 14:49:37

好吧，我要做的第一件事就是删除 select *.坏的！

我要做的第二件事是将其设为存储过程。

    create procedure dbo.MyTableSelect
    @field0 int,
    @field1 int=null
as
begin

    select
        *
    from MyTable
    where Field0=@field0
        and (@field1 is null or Field1=@field1)



end

然后你可以将你的代码更改为这样

context.ExecuteStoreQuery("exec dbo.MyTableSelect @field0={0}, @field1 = {1}", field0, field1)

well, the first thing i would do is remove the select *. BAD!

the second thing i would do is make this a stored procedure.

    create procedure dbo.MyTableSelect
    @field0 int,
    @field1 int=null
as
begin

    select
        *
    from MyTable
    where Field0=@field0
        and (@field1 is null or Field1=@field1)



end

you then can change your code to this

context.ExecuteStoreQuery("exec dbo.MyTableSelect @field0={0}, @field1 = {1}", field0, field1)

回复收藏 0 原文

莳間冲淡了誓言ζ 2024-10-03 14:49:37

您可以使用 if 语句的简短变体。
我认为如果没有 if 语句你就无法解决你的问题。
示例：

String.Format("SELECT * FROM MyTable WHERE Field0 {0} ", value==null ? "IS NULL" : String.Format("= {0}", value))

还可以使用“@ParameterName”对查询进行参数

context.ExecuteStoreQuery<ProductionUnit>(
  String.Format("SELECT * FROM MyTable WHERE Field0 {0} @Parameter1",
  value==null ? "IS", "="), new SqlParameter("@Parameter1", value));

化

you can use the short variant of the if statement.
I don't think you can handle your problem without an if statement.
Example:

String.Format("SELECT * FROM MyTable WHERE Field0 {0} ", value==null ? "IS NULL" : String.Format("= {0}", value))

It is also possible to parameterize the query by using "@ParameterName"

context.ExecuteStoreQuery<ProductionUnit>(
  String.Format("SELECT * FROM MyTable WHERE Field0 {0} @Parameter1",
  value==null ? "IS", "="), new SqlParameter("@Parameter1", value));

Regards

回复收藏 0 原文

~没有更多了~