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

.net c# dapper dynamic

Question

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?

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

Those prints are

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

1
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