Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Advanced

Async Data Access

Async database calls let your web server handle many more requests by not blocking threads while SQL Server works.

Intermediate 5 min Lesson 19 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:SqlDataReaderC#: Async and Await
0/32 in ADO.NET
On this page · 5 min · Intermediate

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Intermediate
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

A waiter takes your order to the kitchen and serves other tables while it cooks, instead of standing at the kitchen door. Async database calls free the thread while SQL Server works.

OpenAsync / ExecuteReaderAsync
Async versions of the calls.
await
Wait without blocking a thread.
CancellationToken
Stop the query if the user leaves.

Learning objectives#

  • Use the Async versions of ADO.NET methods
  • Pass CancellationTokens
  • Use await using for disposal
  • Avoid sync-over-async deadlocks

Explanation#

Each method has an async counterpart: OpenAsync, ExecuteNonQueryAsync, ExecuteScalarAsync, ExecuteReaderAsync, ReadAsync, NextResultAsync.

While the query runs, the thread returns to the pool to serve other requests. A single query is not faster, but the server handles more at once.

Pass a CancellationToken (for example HttpContext.RequestAborted, which ASP.NET Core binds automatically) so a cancelled request also cancels its query.

Never block on async code with .Result or .Wait() — it wastes threads and can deadlock in older frameworks.

Code example: An async, cancellable API endpoint#

C#ProductsEndpoint.cs Illustrative
app.MapGet("/api/products/top", async (int count, IConfiguration config, CancellationToken ct) =>
{
    var list = new List<object>();
    await using var conn = new SqlConnection(config.GetConnectionString("Shop"));
    await conn.OpenAsync(ct);


    await using var cmd = new SqlCommand("SELECT TOP (@n) Id, Name, Price FROM Products ORDER BY Price DESC", conn);
    cmd.Parameters.Add("@n", SqlDbType.Int).Value = Math.Clamp(count, 1, 50);


    await using var r = await cmd.ExecuteReaderAsync(ct);
    while (await r.ReadAsync(ct))
        list.Add(new { Id = r.GetInt32(0), Name = r.GetString(1), Price = r.GetDecimal(2) });


    return Results.Ok(list);
});
Expected output
GET /api/products/top?count=2
[{"id":1,"name":"Laptop","price":55000.00},{"id":3,"name":"Keyboard","price":2200.00}]

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. ASP.NET Core injects a CancellationToken that fires if the client disconnects.
  2. Every database call is awaited and receives the token.
  3. The count is clamped so users cannot request millions of rows.
  4. await using disposes asynchronously.

Real-world use case#

An API under heavy load keeps responding because threads are not stuck waiting on slow reports.

Common mistakes#

.Result on async methods

Blocking defeats the purpose of async and can exhaust the thread pool.

Mixing async ExecuteReaderAsync with sync Read()

Use ReadAsync in the loop too, or large results block threads.

Best practices#

  • Async all the way in web apps
  • Pass CancellationToken
  • Use await using
  • Limit result sizes

Remember this#

Async = more requests per server, not a faster query.

Token in, query cancelled when the user leaves.

No .Result, no .Wait().

Practice exercise#

Your task: Add a 5-second timeout to the query using a CancellationTokenSource linked to the request token.

Show solution
C# Illustrative
using var cts = CancellationTokenSource.CreateLinkedTokenSource(ct);
cts.CancelAfter(TimeSpan.FromSeconds(5));
await using var r = await cmd.ExecuteReaderAsync(cts.Token);

Mini quiz#

Async Data Access quizTrue or false Question 1 of 1

Using ExecuteReaderAsync makes an individual query run faster on SQL Server.

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.