Using Dapper QueryAsync to Return a Single Object

Question

Unfortunately, our database is dated 90 years. Its legacy is so strong that we still use SP to do most CRUD operations. However, Dapper seems to fit very well, and we just started playing.

However, I am a little worried about how to handle a single row of data. In this case, I use QueryAsync to call the SP that passes the identifier. As you can see, the object returns outside the scope of the asynchronous call (*).

I will have problems? If so, does anyone know how to handle this? Should I use QuerySync instead?

public class SchemePolicyRepository : ISchemePolicyRepository { private readonly SqlConnection sql; protected SchemePolicyRepository(SqlConnection connection) { sql = connection; } ... public async Task<SchemePolicy> GetById(string id) { var schemePolicy = await sql.QueryAsync<SchemePolicy>("risk.iE_GetSchemePolicyById", new { Id = id }, commandType: CommandType.StoredProcedure); return schemePolicy != null ? schemePolicy.FirstOrDefault() : null; } ... } 

(*) The SchemePolicy object returned by FirstOfDefault () is not an async method.

Answer 1

First of all, I don’t think you need a null check , Dapper will return a null string for the request. Indicate that this is TRUE for SQL Server , but must be the same for any other DBMS. So this is

 return schemePolicy != null ? schemePolicy.FirstOrDefault() : null; 

can be simply written as

 return schemePolicy.FirstOrDefault(); 

Now, to solve the real problem, and you mentioned:

object returns outside of asynchronous call (*)

This is not true. If you write it anyway, you will ONLY receive your object after the request. Thus, two sets of codes give the same behavior:

 var schemePolicy = await sql.QueryAsync<SchemePolicy>("sp", {rest of code}); return schemePolicy.FirstOrDefault(); 

and

 var schemePolicy = sql.QueryAsync<SchemePolicy>("sp", {rest of code}); return schemePolicy.Result.FirstOrDefault(); 

Now the problem is that you are calling GetById to make sure that (1) the method will not block any other thread and (2) that you will receive the target ONLY when the request completes. Here is a snippet for a console application with which you can test it:

 static async void GetValue() { var repo = new SchemePolicyRepository(new DbManager()); // creates an open connection var result = await repo.GetById(); Console.WriteLine(result); } static void Main(string[] args) { GetValue(); Console.WriteLine("Query is running..."); Console.ReadKey(); } 

This test will show you that GetValue , which therefore calls the GetById method, does not block the rest of the code. In addition, nothing is returned from FirstOrDefault until the request is processed.

Here's the supporting code for the query in case someone wants to try and make sure the concept is valid (the code works with SQL Server 2008 and later):

 public async Task<int> GetById() { var sql = @" WAITFOR DELAY '00:00:05'; select 1 where 1=1"; var result = await {the_open_connection}.QueryAsync<int>(sql); return result.FirstOrDefault(); } 

Answer 2

Dapper currently supports QueryFirstOrDefaultAsync() , so you can write code like this:

 public async Task<SchemePolicy> GetById(string id) { return await sql.QueryFirstOrDefaultAsync<SchemePolicy>("risk.iE_GetSchemePolicyById", new { Id = id }, commandType: CommandType.StoredProcedure); }