c# - 存储过程中的动态查询不返回值作为 ASP.Net Core 2.2 中的 DataSet/DataTable

Question

我有一个存储过程，它返回一个动态表作为结果。来自 this answer 的 SQL 代码信用

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


CREATE PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
(
    @STATIC_COLUMN VARCHAR(255),
    @PIVOT_COLUMN VARCHAR(255),
    @VALUE_COLUMN VARCHAR(255),
    @TABLE VARCHAR(255),
    @AGGREGATE VARCHAR(20) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
            @SQLSTRING NVARCHAR(MAX),
            @PIVOT_SQL_STRING NVARCHAR(MAX),
            @TEMPVARCOLUMNS NVARCHAR(MAX),
            @TABLESQL NVARCHAR(MAX)

    IF ISNULL (@AGGREGATE, '') = '' 
    BEGIN
        SET @AGGREGATE = 'MAX'
    END

    SET @PIVOT_SQL_STRING ='SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                        FROM '+@TABLE+'
                        WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
                        FOR XML PATH(''''), TYPE)
                        .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                        from '+@TABLE+' ma
                        ORDER BY ' + @PIVOT_COLUMN + ''

    DECLARE @TAB AS TABLE(COL NVARCHAR(MAX) )

    INSERT INTO @TAB 
        EXEC SP_EXECUTESQL @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 

    SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)

    SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')

    SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')  
                INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

    SELECT * 
    FROM
        (SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a
                PIVOT
                (
                '+@AGGREGATE+'('+@VALUE_COLUMN+')
                FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
                ) piv

    SELECT * FROM @RETURN_TABLE'

    EXEC SP_EXECUTESQL @SQLSTRING
END

这是我在 ASP.net Core 2.2 中的 C# 代码

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand sqlComm = new SqlCommand("USP_DYNAMIC_PIVOT", conn);

    sqlComm.Parameters.Add(new SqlParameter("@STATIC_COLUMN", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "Date" });
    sqlComm.Parameters.Add(new SqlParameter("@PIVOT_COLUMN", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "category" });
    sqlComm.Parameters.Add(new SqlParameter("@VALUE_COLUMN", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "amount" });
    sqlComm.Parameters.Add(new SqlParameter("@TABLE", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "dbo.temp" });
    sqlComm.Parameters.Add(new SqlParameter("@AGGREGATE", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "sum" });

    sqlComm.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sqlComm;

    da.Fill(dt);
}

dt 为空。我已经尝试了一个跟踪器，并且在那里生成的 SQL 调用在运行时返回了正确的结果。跟踪器还显示该过程已完成该过程。有什么我想念的吗？

编辑:我已经添加了 Nuget 包 System.Data.Common 和 System.Data.SqlClient，如此处某些答案中所建议的，但没有任何变化。

编辑 2:我正在修改我的代码以获得一个完整的工作示例。这取自 this example但问题仍然存在。 tracer中的过程执行是

exec USP_DYNAMIC_PIVOT @STATIC_COLUMN='Date',@PIVOT_COLUMN='category',@VALUE_COLUMN='amount',@TABLE='dbo.temp',@AGGREGATE='sum'

这是正确的决定。

Answer 1

我(仍然)无法重现这一点。从跟踪事件可以看出，ADO.NET 不会在执行命令之前尝试确定结果集元数据。相反，它检查 SqlDataReader 中返回的元数据以创建 DataTable 架构:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp15
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "Server=.;Database=tempdb;integrated security=true";
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand sqlComm = new SqlCommand("USP_DYNAMIC_PIVOT", conn);
                sqlComm.Parameters.Add(new SqlParameter("@STATIC_COLUMN", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "Date" });
                sqlComm.Parameters.Add(new SqlParameter("@PIVOT_COLUMN", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "category" });
                sqlComm.Parameters.Add(new SqlParameter("@VALUE_COLUMN", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "amount" });
                sqlComm.Parameters.Add(new SqlParameter("@TABLE", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "dbo.temp" });
                sqlComm.Parameters.Add(new SqlParameter("@AGGREGATE", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = "sum" });

                sqlComm.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = sqlComm;

                da.Fill(dt);

                Console.WriteLine($"SqlClient: { typeof(SqlConnection).Assembly.FullName}");
                dt.TableName = "sp_test";
                dt.WriteXmlSchema(Console.Out);
                Console.WriteLine();
                Console.WriteLine($"rows {dt.Rows.Count}");
            }


        }
    }
}

输出

SqlClient: System.Data.SqlClient, Version=4.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
<?xml version="1.0" encoding="ibm437"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="sp_test" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="sp_test">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Date" type="xs:string" minOccurs="0" />
              <xs:element name="ABC" type="xs:string" minOccurs="0" />
              <xs:element name="DEF" type="xs:string" minOccurs="0" />
              <xs:element name="GHI" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>
rows 4