Dapper Multiple Results From single query

Question

Hi i am trying to get to grips with Dapper.

My situation is i want to pull two values from a query into two separate strings. Im not sure if i am going about this in the correct way, but this is what i am doing:

string sql = @"Select type, name 
              FROM ZipData 
              WHERE Zip = @zip";

using (var multi = conn.QueryMultiple(sql, new { zip = zip }))
{
   string result = multi.Read<string>().SingleOrDefault();         
}

And i am getting Cannot access a disposed object. Object name: 'GridReader'. when trying to read the second string.The thing is it gets the first value correctly and has both the fields in in the reader i am trying to get. Im sure im misusing the api.

What am i doing wrong here? Ive googled but can find a specific example.

Solution

You are mis-using QueryMultiple. That is defined for compound SQL statements that return multiple result sets. Something like:

SELECT Foo FROM MyTable;
SELECT Bar FROM MyOtherTable;

On the other hand, you are trying to get two different columns from a single result set, so you should just use the normal Query method:

var result = conn.Query(sql, new { zip = zip }).Single();
var type = result.type;
var name = result.name;

Query returns an enumerable (because generally a query can return multiple rows). It appears that you only want one row, however, so we invoke .Single at the end to just get that row. From there, the return type is dynamic so you can simply refer to the properies implied by the columns in your SELECT statement: type and name.

Comments

[gdp]

i guessed i was misusing it. Thankyou for your quick answer :) The dynamic return type is cool.

[webworm]

Wouldn't you have to use the dynamic keyword when defining result. Such as dynamic result = conn.Query(sql, new { zip = zip }).Single();

[Kirk Woll]

@webworm no, the signature of the method is public static IEnumerable<dynamic> Query(...), so each row is already declared as dynamic.