如何将Nullable Bit类型值传递给SQL Server存储过程？

I want to pass null value to where clause bit type variable to get all rows in a table. But also when I pass true or false where clause should work in order to that.

我想将null值传递给where子句位类型变量以获取表中的所有行。但是，当我传递true或false时，where子句应该工作为了那个。

Error:

错误：

Type of conditional expression cannot be determined because there is no implicit conversion between 'bool?' and 'System.DBNull'

无法确定条件表达式的类型，因为'bool？'之间没有隐式转换和'System.DBNull'

Stored procedure:

存储过程：

CREATE PROCEDURE [dbo].[SelectAllUIDs] 
    @enable bit
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        [UID], [fEnable] [Enable],
        [AddedDate] [Added Date]
    FROM 
        [VehicleService].[dbo].[NFCCard] 
    WHERE
        fEnable = @enable OR fEnable IS NOT NULL
    ORDER BY 
        NfcKy
END

C# code:

C＃代码：

 public DataSet SelectUid(bool? status)
 {
        DataSet ds = new DataSet();

        try
        {
            SqlCommand com = new SqlCommand("SelectAllUIDs", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@enable", SqlDbType.Bit)
                                   { Value = status != null ? status : DBNull.Value });

            SqlDataAdapter adp = new SqlDataAdapter(com);
            adp.Fill(ds);
        }
        catch (Exception ex)
        {
            ds = null;
            throw ex;
        }
        return ds;
    }

Calling:

呼叫：

DataSet ds = SelectUid(null); // should return all rows

DataSet ds = SelectUid(true); // should return fEnable = true rows

DataSet ds = SelectUid(false); // should return fEnable = false rows

1 个解决方案

#1

You can use the ?? operator to provide an alternative for when the status is null. Like @user2864740 commented, both sides have to be compatible types. This won't work:

你可以用??运算符为状态为空时提供替代。与@ user2864740评论一样，双方都必须是兼容类型。这不起作用：

var x = true ?? DBNull.Value;  // "no implicit conversion" error

The left hand value of ?? is a bool, but the right hand value is not. So the compiler will complain DBNull.Value cannot be "implicitly cast" to bool. To solve this, cast the left hand side to object:

左手值??是一个布尔，但右手价值不是。所以编译器会抱怨DBNull.Value不能“隐式地”转换为bool。要解决此问题，请将左侧投射到对象：

var x = (object) true ?? DBNull.Value;

Now ?? will evaluate to an object, which can contain both a bool and a DNull.Value. Applying that to your problem, you get:

现在？将评估一个对象，它可以包含bool和DNull.Value。将其应用于您的问题，您会得到：

com.Parameters.AddWithValue("@parname", (object) status ?? DBNull.Value);

#1