pass null parameter to SQL server query

Question

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.

    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();
...

Answer 1

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