Sql Query with Dapper returns an empty result, but the request result from profiler is not empty

Question

I have the SQL Query made with dapper, and its return an empty result. But if i run the SQL Profiler and execute request that i see there the result would be not empty.

Here is my code

    public class Parameters
    {
        public DateTime? SinceDate { get; set; }
        public DateTime? UntilDate { get; set; }
        public byte NetworkId { get; set; } = 7;
    }

    var parameters = new Parameters()
    {
        SinceDate = startDate,
        UntilDate = endDate,
        NetworkId = networkId,
    };

    DynamicParameters requestParameters = new DynamicParameters();
    requestParameters .Add("@sinceDateFilter", parameters.SinceDate, DbType.DateTime);
    requestParameters .Add("@untilDateFilter", parameters.UntilDate, DbType.DateTime);
    requestParameters .Add("@networkId", parameters.networkId, DbType.Byte);

   using (var secondConn = new SqlConnection(ConnectionString))
   {
       var query = secondConn.Query<ReportRow>(
       "[dbo].[GET_Report] @sinceDateFilter, @untilDateFilter, @NetworkId",
       requestParameters,
       commandTimeout: 500
       );
       Groups = query.ToList();
   }


    public class ReportRow
    {
        public string Network { get; set; }
        public ulong Clicks { get; set; }
        public decimal Profit { get; set; }
    }

In the debugger i see that my "Groups" is empty, but if i run the sql profiler i see the request made by this code is

exec sp_executesql N'[dbo].[GET_Report] @sinceDateFilter, @untilDateFilter, @networkId',N'@sinceDateFilter datetime,@untilDateFilter datetime, @networkId tinyint',@sinceDateFilter='2019-04-01 00:00:00',@untilDateFilter='2019-04-02 00:00:00',@networkId=7

And if i execute this from management studio i see the table with data:

+----------+--------+--------+
| Network  | Clicks | Profit |
+----------+--------+--------+
| Network1 |  38835 | 0.45   |
| Network2 |  38835 | 0.35   |
+----------+--------+--------+

This is hard to investigate in isolation, because dapper *doesn't try to do anything clever here* - it just adds the parameters and executes it; the fact that you're using `[dbo].` prefix rules out one common set of causes; but; how many grids does this SP provide? exactly one? always? or...? as a side note: you don't really need `DynamicParameters` here. The first thing I'd try here is manual ADO.NET - perhaps just `ExecuteScalar` - if `ExecuteScalar` with the same command/args doesn't return a value (most likely: `"Network1"`) - it is something odd in the query — Marc Gravell, Apr 01 '19 at 13:45
i.e. `using (var cmd = conn.CreateCommand(); cmd.CommandText = "[dbo].[GET_Report] @sinceDateFilter, @untilDateFilter, @NetworkId"; cmd.Parameters.AddWithValue("@sinceDateFilter", parameters.SinceDate); cmd.Parameters.AddWithValue("@untilDateFilter", parameters.UntilDate); cmd.Parameters.AddWithValue("@networkId", parameters.networkId); var val = cmd.ExecuteScalar(); } ` - and look at `val` — Marc Gravell, Apr 01 '19 at 13:48

score 0 · Accepted Answer · edited Apr 26 '19 at 16:14

0

If you want to execute by stored procedure.

You can try to set commandType parameter.

Setting with CommandType.StoredProcedure

using (var secondConn = new SqlConnection(ConnectionString))
{
   var query = secondConn.Query<ReportRow>(
   "[dbo].[GET_Report]",
   requestParameters,
   commandTimeout: 500, 
   commandType: CommandType.StoredProcedure
   );
   Groups = query.ToList();
}

edited Apr 26 '19 at 16:14

marc_s

732,580
175
1,330
1,459

answered Apr 01 '19 at 13:40

D-Shih

44,943
6
31
51

2

when using `CommandType.StoredProcedure` - it would be just `"[dbo].[GET_Report]"` - the parameters are taken from `requestParameters` – Marc Gravell Apr 01 '19 at 13:41

Sql Query with Dapper returns an empty result, but the request result from profiler is not empty

1 Answers1