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
This question is related to
c#
sql
sql-server
stored-procedures
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
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
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...
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.
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
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" } );
Source: Stackoverflow.com