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

Question

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

User · Accepted Answer

SqlParameters 1    new SqlParameter  Date1   SqlDbType SqlDateTime   SqlParameters 1  Value   DBNull Value  SqlParameters 1  Direction   ParameterDirection Input       then copy for the second

User · Answer

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

User · Answer

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

User · Answer

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 lt int gt  id  string name        var idParameter   id HasValue                   new SqlParameter  Id   id                    new SqlParameter   ParameterName    Id   SqlDbType   SqlDbType Int  Value   DBNull Value            to be continued

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to c#

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures