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

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

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

...then copy for the second.

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

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

Old question, but here's a fairly clean way to create a nullable parameter:

new SqlParameter("@note", (object) request.Body ?? DBNull.Value);

If request.Body has a value, then it's value is used. If it's null, then DbNull.Value is used.

I use a method to convert to DBNull if it is null

    // Converts to DBNull, if Null
    public static object ToDBNull(object value)
    {
        if (null != value)
            return value;
        return DBNull.Value;
    }

So when setting the parameter, just call the function

    sqlComm.Parameters.Add(new SqlParameter("@NoteNo", LibraryHelper.ToDBNull(NoteNo)));

This will ensure any nulls, get changed to DBNull.Value, else it will stay the same.

try this! syntax less lines and even more compact! don't forget to add the properties you want to add with this approach!

cmd.Parameters.Add(new SqlParameter{SqlValue=(object)username??DBNull.Value,ParameterName="user" }  );

    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

Let's say the name of the parameter is "Id" in your SQL stored procedure, and the C# function you're using to call the database stored procedure is name of type int?. Given that, following might solve your issue :

public void storedProcedureName(Nullable<int> id, string name)
{
	var idParameter = id.HasValue ?
				new SqlParameter("Id", id) :
				new SqlParameter { ParameterName = "Id", SqlDbType = SqlDbType.Int, Value = DBNull.Value };

    // to be continued...