CODEWITHSUNDEEP
c#sql
iceman
iceman

Reputation: 31

How to read dynamic sql columns into C#

I have an sql pivot query which results in dynamic sql columns. How do I read these values into a C# object?

I haven't had much success while I am able to read values from the datareader. I am unable to pack it into an object.

I need to use datareader and pass an object through the service layer to the UI.

sql code similar to below,

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME([MONTH]) 
                FROM #REVENUE
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SELECT @query = 
'SELECT * FROM
(SELECT     
 [MONTH], 
SALES
FROM #REVENUE)X
PIVOT 
(
  AVG(SALES)
  for [MONTH] in (' + @cols + ')
) P

EXEC SP_EXECUTESQL @query

Upvotes: 3

Views: 3688

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

If you select into a SQLDataReader, it has a GetName property which will return the column name. Something like:

    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(yourQuery, yourConnection)) {

        using (IDataReader reader = cmd.ExecuteReader) {
            for (int i = 0; i <= reader.FieldCount; i++) {
                var name = reader.GetName(i);
            }
        }
}

Upvotes: 2

Related Questions