将null参数传递给SQL Server查询

How can I pass a null parameter to a SQL server query. I have a simple table with a nullable int column. If I pass a .NET null value, I get a sql error. If I pass DBNull.Value, no row matches the filter. Is there a simple way to do this without using ISNULL.

如何将null参数传递给SQL Server查询。我有一个带有可空int列的简单表。如果我传递.NET null值,我会收到sql错误。如果我传递DBNull.Value,则没有行匹配过滤器。有没有使用ISNULL这样做的简单方法。

    OleDbConnection connection = new OleDbConnection();
    connection.ConnectionString = ...;
    connection.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = connection;
    cmd.CommandText = "select * from myTable where myColumn = ?";
    OleDbParameter parameter = cmd.Parameters.Add(null, OleDbType.Integer);
    parameter.DbType = System.Data.DbType.Int32 ;
    parameter.IsNullable = true;
    parameter.Value = DBNull.Value; // no row returned
    parameter.Value = null; // sql error
    var reader = cmd.ExecuteReader();
...

3 个解决方案

#1

Since NULL does not match anything (even NULL = NULL is false), you have no choice other than using the IS NULL statement.

由于NULL与任何内容都不匹配(即使NULL = NULL为false),除了使用IS NULL语句之外别无选择。

#2

In SQL, null acts a bit differently to other values - you can't just evaluate things as being = null, as this doesn't really work, you need to use "myColumn is null" instead.

在SQL中,null对其他值的作用有点不同 - 您不能仅仅将事物评估为= null,因为这实际上不起作用,您需要使用“myColumn is null”。

In your case, when you need to match either a value or a null, you might need to use a case statement in your where clause.

在您的情况下,当您需要匹配值或null时,您可能需要在where子句中使用case语句。

A bit of reading: wikipedia

一点阅读:*

#3

As ocdecio mention, NULL is not equal to itself. But you do have another option. If you are worried about NULL in this scenario, you can just put an empty string in your parameter value and write the query itself like this:

作为ocdecio提到,NULL不等于它自己。但你确实有另一种选择。如果您在这种情况下担心NULL,您可以在参数值中放入一个空字符串,并像这样编写查询本身:

select * from myTable where COALESCE(myColumn,'') = ?

#1