Passing Null Date Parameter from C# to SQL returns data with Zero in Count Column

Question

I am trying to pass a null Date parameter to SQL stored procedure from C#. If I run the stored procedure directly, I get values in the total column:

However, if I call the stored procedure from my C# code and pass null Date parameter, the Total column for all rows returns Zero (0):

I set the date parameters in the stored procedure if the date parameter is null, so it returns the correct records just no totals.

Here is my stored procedure code:

DECLARE @StartDate date= null, @EndDate date = null

If(@StartDate IS NULL) SET @StartDate = getDate()-30;
If(@EndDate is NULL) SET @EndDate = getDate();

WITH
    L0 AS ( SELECT 1 AS c 
            FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
                        (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
    L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
    L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
    Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
              FROM L2 ),
    Date_Range_T (d_range) AS (
      SELECT TOP(DATEDIFF(day, @StartDate, @EndDate) + 1)
          DATEADD(day, rownum - 1, @StartDate) AS d_range
      FROM Nums
    )
SELECT CONVERT(VARCHAR, d_range, 101) AS CreateDate, COUNT(R.Id) AS Total 
FROM Date_Range_T 
LEFT JOIN tbl_Support_Requests R ON Cast(R.CreatedDate as date) = d_range
LEFT JOIN dbo.tbl_Employee_Companies ec on ec.Id = R.ReporterId
GROUP BY d_range
ORDER BY d_range ASC
OPTION (MAXRECURSION 366);

Here is my C# method:

        public List<ChartData> GetSupportTicketMetrics_ByDateRange(string startDate, string endDate)
    {
        DateTime? StartDate = null;
        DateTime? EndDate = null;
        if (startDate != "")
        {
            StartDate = Convert.ToDateTime(startDate);
        }
        if (endDate != "")
        {
            EndDate = Convert.ToDateTime(endDate);
        }
        var list = new List<ChartData>();
        var sql = "exec stp_GetSupportTicketMetrics_ByDateRange {0}, {1};";
        try
        {
            using (var sdb = new SafetyDataContext())
            {
                list = sdb.ExecuteQueryNullSafe<ChartData>(sql,  StartDate, EndDate).ToList();
            }
        }
        catch (Exception e)
        {
            Elmah.ErrorSignal.FromCurrentContext().Raise(e);
            string msg = e.Message;
        }
        return list;
    }

Here is ExecuteQueryNullSafe:

internal static IEnumerable<TResult> ExecuteQueryNullSafe<TResult>(this System.Data.Linq.DataContext context,
      string command, params object[] parameters)
    {
        var list = new List<object>();
        var listVals = new List<bool>();

        for (int x = 0; x < parameters.Count(); x++)
        {
            if (parameters[x] == null || parameters[x] is System.DBNull)
            {
                command = command.Replace("{" + x + "}", "NULL");
                listVals.Add(false);
            }
            else
            {
                list.Add(parameters[x]);
                listVals.Add(true);
            }
        }

        int nextId = 0;
        for (int i = 0; i < listVals.Count; i++)
        {
            var isUsed = listVals[i];
            if (!isUsed)
                continue;
            if (nextId != i)
                command = command.Replace("{" + i.ToString() + "}", "{" + nextId.ToString() + "}");
            nextId++;
        }

        return context.ExecuteQuery<TResult>(command, list.ToArray());
    }

Now if I actually supply the exact same dates to the method, the list returned has values in the Total column. I can't figure out why the query runs as it in SQL but doesn't supply the values when called from C# code.

NULL in C# and NULL in DB are 2 different things. You need to pass DBNULL if the value is NULL in C#. See this: https://stackoverflow.com/questions/1207404/how-to-pass-a-null-variable-to-a-sql-stored-procedure-from-c-net-code — Brad, Mar 04 '21 at 16:47
@Brad, I'm sorry, but how do I set the DateTime? StartDate to DBNull? This DateTime? StartDate = DBNull.Value does not work.; — Rani Radcliff, Mar 04 '21 at 16:54
You don't set StartDate to DBNull.Value, you pass DBNull.Value to the stored proc. Something like `StartDate.HasValue ? StartDate: DBNull.Value`, and same for EndDate. — insane_developer, Mar 04 '21 at 17:03
@insane_developer - thanks, but the coalesce gives me the error "Type of conditional expression cannot be determined because there is no implicit conversion between System.DateTime? and System.DBNull" — Rani Radcliff, Mar 04 '21 at 17:10
Thanks to both of you but passing DBNULL " list = sdb.ExecuteQueryNullSafe(sql, DBNull.Value, DBNull.Value).ToList();" returns the same results with same issue, so DBNull does not seem to be the issue. — Rani Radcliff, Mar 04 '21 at 17:14
@RaniRadcliff that tells me `ExecuteQueryNullSafe` is expecting `DateTime?` as a parameter, where maybe it could be `object`. Did you try that? — insane_developer, Mar 04 '21 at 17:15
This should help https://stackoverflow.com/questions/3501239/dealing-with-dbnull-value — Dan Rayson, Mar 04 '21 at 18:03
Nice to see my code in action from your previous question https://stackoverflow.com/questions/66324479/calculate-the-number-of-records-for-each-date-between-2-dates by the way you don't need `option(maxrecursion` because there is no recursion. Preferably don't run sprocs like this from EF, you can add the procedure to your data model directly https://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx — Charlieface, Mar 04 '21 at 18:53
@Charlieface they are not using entity framework models in this project that is why I am calling sprocs directly. I actually like this much better. I can make changes to sprocs without rebuilding the model each time. — Rani Radcliff, Mar 04 '21 at 19:04
If you are not using a model then why are you `ExecuteQueryNullSafe` (whatever that does), just construct an `SqlCommand` with `CommandType.StoredProcedure` then the query text is just `stp_GetSupportTicketMetrics_ByDateRange` (no `exec` or parameters) then you pass the parameters through with `command.Parameters.Add(new SqlParameter...` — Charlieface, Mar 04 '21 at 19:10

score 1 · Answer 1 · answered Mar 04 '21 at 17:25

You can try to use SqlParameter's. It could look like this:

int rowsAffected;
string sql = "EXEC stp_GetSupportTicketMetrics_ByDateRange @StartDate, @EndDate";

List<SqlParameter> parms = new List<SqlParameter>
{ 
    // Create parameters    
    new SqlParameter { ParameterName = "@StartDate", Value = StartDate },
    new SqlParameter { ParameterName = "@EndDate",   Value = EndDate }  
};

rowsAffected = _db.Database.ExecuteSqlRaw(sql, parms.ToArray());

Passing Null Date Parameter from C# to SQL returns data with Zero in Count Column

1 Answers1