Practice Now
Async Programming
SqlDataAdapter
C# ADO.NET
Database Operations
.NET Development

SqlDataAdapter.Fill - Asynchronous approach

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
DatabasesConcurrencyC# & .NET

Introduction

SqlDataAdapter.Fill() is a synchronous method in ADO.NET that populates a DataTable or DataSet with query results. Unlike SqlCommand, SqlDataAdapter does not have a native async FillAsync() method. To avoid blocking threads (especially in ASP.NET or WPF applications), you need alternative approaches to perform the equivalent operation asynchronously.

The Problem: Synchronous Fill Blocks the Thread

csharp
1using System.Data;
2using System.Data.SqlClient;
3
4// This blocks the calling thread until the query completes
5var adapter = new SqlDataAdapter("SELECT * FROM Orders", connectionString);
6var dataTable = new DataTable();
7adapter.Fill(dataTable);  // synchronous — no async version exists

In an ASP.NET request handler or WPF UI thread, this blocks the thread and reduces scalability or freezes the UI.

Approach 1: Use SqlDataReader with Async Methods

The recommended approach is to use SqlCommand.ExecuteReaderAsync() and manually load the data:

csharp
1public static async Task<DataTable> FillAsync(string query, string connectionString)
2{
3    var dataTable = new DataTable();
4
5    using var connection = new SqlConnection(connectionString);
6    using var command = new SqlCommand(query, connection);
7
8    await connection.OpenAsync();
9    using var reader = await command.ExecuteReaderAsync();
10    dataTable.Load(reader);
11
12    return dataTable;
13}
14
15// Usage
16var orders = await FillAsync("SELECT * FROM Orders", connectionString);

DataTable.Load() is synchronous but runs against an already-fetched reader. The network I/O (the slow part) is handled asynchronously by ExecuteReaderAsync().

Approach 2: Fully Async Row-by-Row Loading

For complete async control, read rows individually:

csharp
1public static async Task<DataTable> FillAsyncComplete(string query, string connectionString)
2{
3    var dataTable = new DataTable();
4
5    using var connection = new SqlConnection(connectionString);
6    using var command = new SqlCommand(query, connectionString);
7
8    await connection.OpenAsync();
9    using var reader = await command.ExecuteReaderAsync();
10
11    // Load schema
12    dataTable.Load(reader);
13    // Reset reader for data
14    // Alternative: build schema manually from reader.GetSchemaTable()
15
16    return dataTable;
17}

Or build the DataTable column by column:

csharp
1public static async Task<DataTable> FillAsyncManual(string query, string connectionString)
2{
3    var dataTable = new DataTable();
4
5    await using var connection = new SqlConnection(connectionString);
6    await using var command = new SqlCommand(query, connection);
7
8    await connection.OpenAsync();
9    await using var reader = await command.ExecuteReaderAsync();
10
11    // Build columns from schema
12    for (int i = 0; i < reader.FieldCount; i++)
13    {
14        dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
15    }
16
17    // Read rows asynchronously
18    while (await reader.ReadAsync())
19    {
20        var row = dataTable.NewRow();
21        for (int i = 0; i < reader.FieldCount; i++)
22        {
23            row[i] = await reader.IsDBNullAsync(i) ? DBNull.Value : reader.GetValue(i);
24        }
25        dataTable.Rows.Add(row);
26    }
27
28    return dataTable;
29}

Wrapping the synchronous call in Task.Run offloads to a thread pool thread but does not provide true async I/O:

csharp
1// Not recommended — uses a thread pool thread for blocking I/O
2var dataTable = await Task.Run(() =>
3{
4    var adapter = new SqlDataAdapter("SELECT * FROM Orders", connectionString);
5    var dt = new DataTable();
6    adapter.Fill(dt);
7    return dt;
8});

This is acceptable in desktop applications (WPF/WinForms) to unblock the UI thread, but is wasteful in ASP.NET where thread pool threads are precious.

Approach 4: Extension Method

Create a reusable async extension:

csharp
1public static class SqlDataAdapterExtensions
2{
3    public static async Task<int> FillAsync(this SqlDataAdapter adapter, DataTable dataTable)
4    {
5        using var reader = await adapter.SelectCommand.ExecuteReaderAsync();
6        dataTable.Load(reader);
7        return dataTable.Rows.Count;
8    }
9}
10
11// Usage
12var adapter = new SqlDataAdapter("SELECT * FROM Orders", connectionString);
13adapter.SelectCommand.Connection = new SqlConnection(connectionString);
14await adapter.SelectCommand.Connection.OpenAsync();
15
16var dataTable = new DataTable();
17await adapter.FillAsync(dataTable);

With Parameters

csharp
1public static async Task<DataTable> QueryAsync(
2    string query,
3    string connectionString,
4    params SqlParameter[] parameters)
5{
6    var dataTable = new DataTable();
7
8    await using var connection = new SqlConnection(connectionString);
9    await using var command = new SqlCommand(query, connection);
10
11    command.Parameters.AddRange(parameters);
12
13    await connection.OpenAsync();
14    await using var reader = await command.ExecuteReaderAsync();
15    dataTable.Load(reader);
16
17    return dataTable;
18}
19
20// Usage
21var orders = await QueryAsync(
22    "SELECT * FROM Orders WHERE CustomerId = @id",
23    connectionString,
24    new SqlParameter("@id", customerId)
25);

Dapper Alternative

Consider using Dapper for simpler async database access:

csharp
1using Dapper;
2
3await using var connection = new SqlConnection(connectionString);
4var orders = (await connection.QueryAsync<Order>(
5    "SELECT * FROM Orders WHERE CustomerId = @Id",
6    new { Id = customerId }
7)).ToList();

Dapper is fully async and maps results directly to objects, eliminating the need for DataTable in most cases.

Common Pitfalls

  • Exception Handling: Asynchronous operations require conscious exception management; ensure proper try-catch blocks are incorporated, especially dealing with SqlException.
  • Resource Management: Always utilize using or await using statements to manage IDisposable resources efficiently to avoid memory leaks and connection pool exhaustion.
  • Configuration and Performance: Opt for configurations that support appropriate concurrent connections from the client side and manage connection pooling effectively.
  • Task.Run in ASP.NET: Using Task.Run to wrap Fill() in ASP.NET wastes a thread pool thread on blocking I/O. Use ExecuteReaderAsync for true async I/O.
  • DataTable.Load thread safety: DataTable is not thread-safe. Do not share a DataTable across multiple async operations without synchronization.
  • Connection lifetime: Keep connections open only as long as needed. Open with OpenAsync, read data, then let using dispose the connection.

Summary

  • SqlDataAdapter.Fill() has no native async version
  • Use SqlCommand.ExecuteReaderAsync() + DataTable.Load(reader) for async filling
  • Avoid Task.Run wrappers in ASP.NET — they waste thread pool threads
  • Create extension methods for reusable async fill patterns
  • Consider Dapper for simpler async database access without DataTable
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.