sql-server - 如果没有行，使用 Dapper 执行存储过程不会返回列标题

Question

我正在使用 Dapper 执行一个存储过程，它返回动态数据透视查询、列。我注意到如果没有结果，Dapper 甚至不会返回列标题。我将结果打印到 excel，如果没有行，那很好，但我仍然想打印出列标题。

例如，如果这是从 Sproc 返回的，则只有列标题

TSB编号 System1 System2 System3

下面的 obs，count 是 0 但我仍然需要获取列标题。这是可能的，还是我需要在没有结果时手动将列标题添加到我的数据表中？或者我应该在我的 SQL 中添加空行以便 dapper 将计数为 1 并打印出来吗？

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

存储过程

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

事实上，Query 没有，因为它没有要附加元数据的行。有一个 ExecuteReader 方法，但此时您并没有比 ADO.NET 获得太多，除非我添加一个方法将 IDataReader 转换为一系列 dynamic 行或 materilize 为某种类型。我可以添加这些东西 - 它只是需要工作。