How to pass a null variable to a SQL Stored Procedure from C#.net code

Question

Hi,

Im calling a SQL stored procedure from a piece of C#.net code:

SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters);

where the sqlParameters variable is defined as:

        SqlParameter[] sqlParameters = new SqlParameter[SQL_NUMBER_PARAMETERS];

        Log.Logger.Debug(string.Format("Running proc: {0} ", STORED_PROC_NAME));

        SqlParameters[0] = new SqlParameter("fieldID", SqlDbType.BigInt );
        SqlParameters[0].Value = fieldID;
        SqlParameters[0].Direction = ParameterDirection.Input;

I need to now pass in another two parameters to this Stored Proc, (both are of type SqlDateTime), which are going to NULL in this case.

Thanks,

IN

Answer 1

You can pass the DBNull.Value into the parameter's .Value property:

    SqlParameters[0] = new SqlParameter("LedgerID", SqlDbType.BigInt );
    SqlParameters[0].Value = DBNull.Value;

Just adjust for your two DateTime parameters, obviously - just showing how to use the DBNull.Value property value here.

Marc

Answer 2

SqlParameters[1] = new SqlParameter("Date1", SqlDbType.SqlDateTime);
SqlParameters[1].Value = DBNull.Value;
SqlParameters[1].Direction = ParameterDirection.Input;

...then copy for the second.

Answer 3

Use DBNull.Value Better still, make your stored procedure parameters have defaults of NULL. Or use a Nullable<DateTime> parameter if the parameter will sometimes be a valid DateTime object

Answer 4

    SQLParam = cmd.Parameters.Add("@RetailerID", SqlDbType.Int, 4)
    If p_RetailerID.Length = 0 Or p_RetailerID = "0" Then
        SQLParam.Value = DBNull.Value
    Else
        SQLParam.Value = p_RetailerID
    End If