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, Dec 07 '15 at 05:18
able to read data but unable to proceed. as its dynamic i am stuck at the entity creation. — iceman, Dec 07 '15 at 05:20
Have a look at this http://stackoverflow.com/questions/31656938/how-to-convert-a-data-reader-to-dynamic-query-results — J-D, Dec 07 '15 at 05:56
haven't been able to create object. since the columns are dynamic. columns could be MAR, APR, MAY, JUN, JUL, AUG or JUN, JUL, AUG,SEP,OCT,NOV — iceman, Dec 07 '15 at 06:12
@iceman : you may need to have class with x+1 number of fields, 1 field is for Header and remaining X fields are to store your data. — Sateesh Pagolu, Dec 07 '15 at 06:19

Allan S. Hansen · Answer 1 · 2015-12-07T07:20:22.730

1

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 07 '15 at 07:20

answered Dec 07 '15 at 07:12

Allan S. Hansen

4,013
23
25

but what about the object / entity? – iceman Dec 07 '15 at 08:12
I'd properly just do a key/value store wrapped object - only other way I know (but haven't tried myself) would be ExpandoObject - https://msdn.microsoft.com/en-us/library/system.dynamic.expandoobject%28v=vs.100%29.aspx - but it seems more trouble than it's worth. – Allan S. Hansen Dec 07 '15 at 09:58

How to read dynamic sql columns into C#

1 Answers1