Dynamic query in a stored procedure not returning value as DataSet/DataTable in ASP.Net Core 2.2

Question

I have a stored procedure which returns me a dynamic table as a result. SQL code credit from this answer

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

This is my C# code in ASP.net Core 2.2

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

The dt is null. I have tried a tracer and the SQL call generated there returns correct results when run. The tracer also shows that the procedure has completed the process. Is there anything I am missing?

EDIT: I have added the Nuget packages System.Data.Common and System.Data.SqlClient as suggested in some answer here but there is no change.

EDIT 2 : I am modifying my code to have a complete working example. This is taken from this example but the issue remains. The procedure execution in tracer is

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

which is the right call.

I suggest against `SET FMTONLY`, it's been deprecated: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-2017 — Thom A, Feb 26 '19 at 18:17
why don't you try DataSet instead DataTable and convert dataset to datatable later? — Prabin Yovan, Feb 26 '19 at 18:23

David Browne - Microsoft · Answer 1 · 2019-02-26T19:37:35.780

I (still) can't repro this. And as you can see from tracing the activity, ADO.NET does not try to determine the resultset metadata before executing the command. Instead it examines the metadata returned in the SqlDataReader to create the DataTable schema:

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


        }
    }
}

outputs

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

The SQL procedure in my code does not quite give you the output. It is just an approximation of my actual SP which is returning rows. I will edit the code to something simpler but which works. — TheFallenOne, Feb 26 '19 at 18:51
I am not sure what further details you need. I have the table structure,data, procedure code and procedure execution statement in my question which all produce results. I am not able to retrieve the produced results in ASP.Net Core. So if you can point out what exactly the details you need, I can look to update my question accordingly. — TheFallenOne, Feb 26 '19 at 19:37
I posted a complete working example .NET Core Console application. Any real problem should repro there. Rereading your question, you mentioned that `dt` is null. If you use DataAdapter.Fill(dt) you must new-up a DataTable first, as in my example. — David Browne - Microsoft, Feb 26 '19 at 19:39
I have defined the new dt, it was missing in this code. I am using ASP.Net Core 2.2. with entity framework. So I am not sure if there is a difference between how the code is interpreted by Core .2 to console application and so you don't see the issue. — TheFallenOne, Feb 26 '19 at 19:46
There is no difference in the ASP.NET environment that would account for this. If you post the body of that `main()` into a ASP.NET controller, it will work. — David Browne - Microsoft, Feb 26 '19 at 19:53

Dynamic query in a stored procedure not returning value as DataSet/DataTable in ASP.Net Core 2.2

1 Answers1