具有空值的SQL服务器查询

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

在我的c#代码中，我必须执行这样的SQL查询:

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)

当field1在c#中为空，在数据库中为空时，此查询无效。(我必须使用与NULL不同的语法)

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

我如何在不使用if的情况下纠正这个错误(实际上，我有10个字段…)?

4 个解决方案

#1

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:

默认情况下，SQL server不允许您将值与null进行比较。所有的比较都是假的，即使是逻辑上相反的。换句话说，你可以:

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

其中字段= 1，字段<> 1。如果字段为空，则两者逻辑上都解析为false。

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

在任何情况下，您都需要在查询中对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)

#2

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.

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

#3

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

我要做的第一件事就是删除select *。糟糕了!

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)

#4

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

您可以使用if语句的短变体。我认为没有if语句你无法解决你的问题。例子:

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"

还可以使用“@ParameterName”来参数化查询。

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

Regards

问候

#1