CODEWITHSUNDEEP
c#.netdynamicdapper
G_S
G_S

Reputation: 7110

Read column names based on index from a dynamic variable

I have an sproc which returns variable number of columns with different column names based on input parameter given to it. (and also number of rows >= 1)

For example, if input is 3, sproc returns 3 columns, and if input is 5, it returns 5. (Not sure on the input parameter that might be given)

I am using dapper to integrate my sproc in C# application and i am using dynamic as return type for it as below. 

dynamic returnValue = connection.Query<dynamic>("sproc and inputs")

Can someone guide me on how to read the columns from dynamic variable?

Using returnValue[0].col1, returnValue[0].col2 works but i am not sure on the number of columns to query on.

Is there a way to read columns like returnValue[0]["c1"] or any simpler way so that i can loop over to get all the columns based on input parameter?

Upvotes: 1

Views: 1782

Answers (1)

petelids
petelids

Reputation: 12815

The result of calling connection.Query() can be cast to an IEnumerable<IDictionary<string, object>> which will allow you to iterate the results or access them using an index and column name key. For example:

IEnumerable<IDictionary<string, object>> results;

string sql = @"select 'a' as col1, 'b' as col2, 'c' as col3, 'd' as col4
               union
               select 'w' as col1, 'x' as col2, 'y' as col3, 'z' as col4";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    results = connection.Query(sql) as IEnumerable<IDictionary<string, object>>;
}

//we need to use ElementAt as we have an Enumerable but we could ToList it if required
Console.WriteLine(results.ElementAt(0)["col1"]);

//we can iterate the rows
foreach (var row in results)
{
    //then iterate the columns and get a KeyValuePair for each column
    foreach (var col in row)
    {
        Console.WriteLine("{0} {1}", col.Key, col.Value);
    }
}

Which prints:

a
col1 a
col2 b
col3 c
col4 d
col1 w
col2 x
col3 y
col4 z

Upvotes: 2

Related Questions