How to pass a null variable to a SQL stored procedure from C # .net code

Question

I call the SQL stored procedure from a C # .net code snippet:

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

where the sqlParameters variable sqlParameters 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 pass two more parameters to this Stored Proc (both are of type SqlDateTime ), which in this case will be NULL.

Thank,

AT

Answer 1

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

... then copy for the second.

Answer 2

Use DBNull.Value . Even better, the stored procedure parameters have a default value of NULL. Or use the Nullable<DateTime> parameter if the parameter is sometimes a valid DateTime object

Answer 3

You can pass DBNull.Value to the parameter. Value Property:

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

Just set the DateTime for the two parameters, obviously - just show how to use the value of the DBNull.Value property here.

Mark

Answer 4

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; } 

Therefore, when setting up a parameter, just call the function

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

This will provide any zeros, change to DBNull.Value, otherwise it will remain the same.

Answer 5

An old question, but here's a pretty clean way to create a parameter with a reset option:

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

If request.Body has a value, then this value is used. If it is zero, then DbNull.Value is used.

Answer 6

  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 

Answer 7

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

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

Answer 8

Let's say the parameter name is "Id" in your SQL stored procedure, and the C # function that you use to call the database stored procedure is an int? type name int? . Given that the following problem may solve your problem:

 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...