Executing a stored procedure with Dapper doesn't return column headers if no rows are present

Question

I am executing a stored procedure with Dapper which returns dynamic pivot query, columns. I have noticed if there are no results Dapper does not even return the column headers. I print my results to excel and if there are no rows, thats fine, but I would still like to print out the column header.

So for instance if this being returned from the Sproc, just column headers

TSBNumber System1 System2 System3

The following obs, count is 0 but I still need to get the column headers. Is this possible or I would manually need to add column headers it to my datatable when there are no results? Or Should I add empty row in my SQL so dapper will get a count of 1 and print out?

   var obs = cnn.Query(sql: "spExportServiceTSB", param: p, commandType: CommandType.StoredProcedure);


  var p = new DynamicParameters();
            p.Add("@StartDate", StartDate);
            p.Add("@EndDate", EndDate);
            p.Add("@SelectedSystemIDs", SelectedSystemIDs);
            p.Add("@SelectedTsbIDs", SelectedTsbIDs);
            p.Add("@UserRoleID", UserRoleID);
            var obs = cnn.Query(sql: "spExportServiceTSB", param: p, commandType: CommandType.StoredProcedure);

            var dt = ToDataTable(obs);

            return ExportDatatableToHtml(dt);

Sproc

ALTER PROCEDURE  [dbo].[spExportServiceTSB]                            
(@StartDate datetime,                            
@EndDate datetime,                                   
@SelectedSystemIDs nvarchar (2000) = NULL,    
@SelectedTsbIDs nvarchar (2000) = NULL,   
@UserRoleID int
)                            
AS                            

DECLARE @PlatformID INT = NULL

IF(@SelectedSystemIDs = '')
BEGIN
SET @SelectedSystemIDs = NULL
END

IF(@SelectedTsbIDs = '')
BEGIN
SET @SelectedTsbIDs = '0'
END

IF(@UserRoleID = 1)    
 BEGIN     
  SET @PlatformID = 1     
  END  

IF(@UserRoleID = 2)  
BEGIN  
SET @PlatformID = 2  
END  

IF (@UserRoleID = 3)    
BEGIN    
SET @PlatformID = 12    
END   

IF(@UserRoleID = 4)
BEGIN 
SET @PlatformID = 3
END

IF(@UserRoleID = 5)
BEGIN 
SET @PlatformID = 4
END

IF(@UserRoleID = 6)
BEGIN 
SET @PlatformID = 0
END

DECLARE @PivotColumnHeaders NVARCHAR(MAX)

SELECT @PivotColumnHeaders =  
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System 
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))     
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))  

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       ClosedDate
     FROM ServiceEntry 
     INNER JOIN System 
       ON ServiceEntry.SystemID = System.ID
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND 
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       ( 
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )  
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''') 
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        --OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')

   ) AS PivotData
   PIVOT (
     MAX(ClosedDate)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     ) 
   ) AS PivotTable
' 



EXECUTE (@PivotTableSQL)
--print (@PivotTableSQL)

Answer 1

Indeed, Query doesn't, because it has no rows upon which to attach metadata. There is an ExecuteReader method, but at that point you aren't gaining much over ADO.NET, unless I add a method to turn an IDataReader into a series of dynamic rows or materilize into some type. I could add those things - it just takes work.