A SqlDataReader lets you read a query result row by row. This lesson shows how to create it, advance through the result, handle database nulls, and close the reader and its connection.
- Obtain the reader from a command.
- Read each row and select the right typed accessor.
- Dispose the reader even when processing fails.
Updated technical examples: 4 October 2026. Target: .NET 10, SDK 10.0.401, Microsoft.Data.SqlClient 7.1.1. Examples compile; database-independent checks run locally. No SQL Server was available during verification, so the SQL queries and server-backed output have not been executed.
Forward-only reading
A data reader is a forward-only stream of results rather than a disconnected editable collection. Call Read or ReadAsync to advance; copy any values you need to retain before advancing again. You cannot seek backward to a previous row.
Streaming can avoid materializing an entire result into an application collection, but it is not automatically the fastest choice for every workload. Your query, network, row processing, repeated access, and caching needs affect the result. Microsoft’s DataReader guide explains its behavior. Lesson 05 covers disconnected data.
Set up the current provider
The original lesson used System.Data.SqlClient. The updated example uses Microsoft.Data.SqlClient, Microsoft’s SQL Server provider. Its namespace guide describes migration considerations. Installing a package changes the provider; it does not create SQL Server or install the Northwind database.
dotnet new console --framework net10.0
dotnet add package Microsoft.Data.SqlClient --version 7.1.1Use a disposable test database containing dbo.Customers with CompanyName as a required string, ContactName as a nullable string, and City as nullable nvarchar(15). This matches the columns used by the original Northwind examples. Supply the connection string through CSHARP_STATION_SQL in your local environment. Keep credentials out of source control; use authentication and certificate settings appropriate to your server. The examples do not disable certificate validation.
Create the reader with ExecuteReader
Open the connection, then call ExecuteReader or await ExecuteReaderAsync on a configured SqlCommand. The returned reader initially has no current row. Its first successful read positions it on the first result row; false means that this result set has ended.
Keep the connection available while the reader consumes data. The example’s scopes close the reader, dispose the command, and then dispose the connection. It does not rely on multiple active result sets or share a reader across concurrent operations.
Listing 1: Using the SqlDataReader
Replace Program.cs with this complete example, which retains the original customer/contact/city task:
using System;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
class ReaderDemo
{
public static string DescribeRow(DbDataReader reader, int companyOrdinal,
int contactOrdinal, int cityOrdinal)
{
string company = reader.GetString(companyOrdinal);
string? contact = reader.IsDBNull(contactOrdinal) ? null : reader.GetString(contactOrdinal);
string? city = reader.IsDBNull(cityOrdinal) ? null : reader.GetString(cityOrdinal);
return $"{company} | {contact ?? "(null)"} | {city ?? "(null)"}";
}
static async Task<int> Main()
{
string? connectionString = Environment.GetEnvironmentVariable("CSHARP_STATION_SQL");
if (string.IsNullOrWhiteSpace(connectionString))
{
Console.Error.WriteLine("Set CSHARP_STATION_SQL to your test database connection string.");
return 2;
}
using var timeout = new CancellationTokenSource(TimeSpan.FromSeconds(30));
using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(
"SELECT CompanyName, ContactName, City FROM dbo.Customers ORDER BY CompanyName;",
connection);
await connection.OpenAsync(timeout.Token);
using SqlDataReader reader = await command.ExecuteReaderAsync(timeout.Token);
int companyOrdinal = reader.GetOrdinal("CompanyName");
int contactOrdinal = reader.GetOrdinal("ContactName");
int cityOrdinal = reader.GetOrdinal("City");
while (await reader.ReadAsync(timeout.Token))
{
Console.WriteLine(DescribeRow(reader, companyOrdinal, contactOrdinal, cityOrdinal));
}
return 0;
}
}Configure your test connection string and run dotnet run. Each row uses the format CompanyName | ContactName | City. The row values depend on your database. With no connection string, the locally verified program prints the configuration message and returns 2 before connecting.
Check null before reading a typed value
GetOrdinal resolves a selected column name once, outside the loop. GetString reads a string value; it does not convert an arbitrary database type to text. If the schema uses a different type, choose the matching accessor or an explicit conversion. See the SQL Server/.NET mapping table.
ContactName and City may be SQL NULL. The helper uses IsDBNull before GetString and retains null as a nullable C# string until formatting. The display marker (null) is a presentation decision; it does not change the database. The required CompanyName assumption must agree with your schema. Microsoft’s IsDBNull reference documents the null check.
Lifetime, asynchronous calls, and large values
using provides deterministic disposal on normal completion and exception paths. Disposal may involve synchronous cleanup even when the earlier I/O calls are asynchronous. Keep the reader’s scope short; holding it open while performing unrelated work also holds its underlying connection in use.
Await each reader operation before starting another on the same reader. Cancellation is cooperative and may arrive during connection opening, query execution, or row retrieval; handle and record failures at the application boundary. A command timeout and a cancellation token serve related but distinct purposes.
The sample reads ordinary string columns. Very large binary or text values may require CommandBehavior.SequentialAccess and stream-based accessors, with ordered column consumption. Multiple result sets require NextResult or NextResultAsync to advance; any unread rows in the current set are skipped. Those scenarios need their own fixtures and are not demonstrated by this example. See the SqlClient asynchronous API guide.
What was verified
The complete SqlClient program compiled with the pinned package, and its configuration guard ran. The row-formatting helper was exercised with a DataTableReader for populated, nullable, and empty-string rows, including an empty result and disposal after a processing exception. These in-memory checks test the mapping logic; they are not a substitute for a SqlDataReader integration test.
No database query was run during this update. Before depending on the example in an application, test it against your server with zero, one, and multiple rows; nullable columns; cancellation and permission failures; and the intended encryption/authentication settings. Verify resource cleanup under those conditions.
Continue to Lesson 05: Working with Disconnected Data – The DataSet and SqlDataAdapter. For user-supplied filters, use Lesson 06’s command parameters.