How to read dynamic sql columns into C#

Question

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

Are all of the columns in the resultset named? If you use a function like COUNT(*) or possibly your pivot, the columns may not have a name. Ensure all the columns have a name which matches what you code expects. — Turophile
– Turophile, Commented Dec 7, 2015 at 5:18
able to read data but unable to proceed. as its dynamic i am stuck at the entity creation. — iceman
– iceman, Commented Dec 7, 2015 at 5:20

Allan S. Hansen · Accepted Answer · 2015-12-07 07:20:22Z

2

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

edited Dec 7, 2015 at 7:20

answered Dec 7, 2015 at 7:12

Allan S. Hansen

4,09126 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iceman Over a year ago

but what about the object / entity?

Allan S. Hansen Over a year ago

I'd properly just do a key/value store wrapped object - only other way I know (but haven't tried myself) would be ExpandoObject - msdn.microsoft.com/en-us/library/… - but it seems more trouble than it's worth.

Collectives™ on Stack Overflow

How to read dynamic sql columns into C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related