How to read column names from a dynamic variable index with Dapper?

.net c# dapper dynamic


I have a sproc that, depending on the input parameter used, produces a varied number of columns with various column names. (And also if there are more rows than 1)

Sproc, for instance, returns 3 columns if the input is 3 and 5 columns if the input is 5. (I'm unsure of the potential input parameter that may be used.)

I'm integrating my sproc into a C# application using dapper, and the return type I'm using is dynamic, as seen below.

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

Could you please explain to me how to read the columns from a dynamic variable?

Using returnValue[0].col1, returnValue[0].col2 works, but I'm unsure of how many columns to query.

Is it possible to access columns likereturnValue[0]["c1"] or is there a more straightforward method I can use to loop over all the columns depending on the input parameter?

6/28/2017 9:44:28 AM

Accepted Answer

the outcome of a callconnection.Query() may be thrown to anIEnumerable<IDictionary<string, object>> This will let you to obtain the outcomes using an index and column name key, or iterate the results. For instance:

IEnumerable<IDictionary<string, object>> results;

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

using (SqlConnection connection = new SqlConnection(connectionString))

    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

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

Those prints are

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

6/28/2017 4:33:21 PM

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow