Query multiple results in Dapper asychronously

Question

I am trying to fix an issue with an existing code and not sure if this approach is correct or not as I have not worked with dapper before. I am trying to return a list of objects using a stored procedure.

The code below compiles but I am not sure if this is the correct way to return multiple rows or not. Is there anything that just returns to list rather than me looping through and constructing list and then returning?

public async Task<List<Event>> GetEventsAsync()
{
   await using SqlConnection db = new(this.settings.ConnectionString);
       
   List<Event> events = new List<Event>();

   var eventsMarkedPublished =  await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure);

     foreach (var pubEvents in eventsMarkedPublished)
     {
        events.Add(pubEvents);
     }

     return events;
 }

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 29, 2022 at 6:44

D-Shih · Accepted Answer · 2022-05-29 06:32:14Z

1

We can try to return List by ToList method directly.

return (await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure)).ToList();

or

var result = await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure);
return result.ToList();

answered May 29, 2022 at 6:32

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

snowflakes74 Over a year ago

thank you.. I did this and it seems to be cmpiling correctly : var result = await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure); return result.AsList<Event>();

Charlieface Over a year ago

result needs a using

Palle Due Over a year ago

@Charlieface: In the question there's a using on the first line. That should cover this solution as well.

Charlieface Over a year ago

I don't think that makes any difference, that's for the connection not the reader. What does make a difference is whether it's a buffered command, which in this case it is (it's the default). See source code github.com/DapperLib/Dapper/blob/… Still probably best practice to have a using, it's not like it costs

Collectives™ on Stack Overflow

Query multiple results in Dapper asychronously

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related