SqlDataReader

In simple words#

Learning objectives#

• Loop through rows with Read/ReadAsync
• Read columns with typed getters
• Use GetOrdinal for clear, fast column access
• Map rows into C# records

Explanation#

A reader is forward-only and read-only: you move from the first row to the last, once.

Typed getters (GetInt32, GetString, GetDecimal, GetDateTime) avoid boxing and conversions. GetFieldValue<T> works for any type.

Look up column positions once with GetOrdinal("Name") before the loop: clearer than magic numbers and faster than reader["Name"] on every row.

Check IsDBNull before reading nullable columns.

Code example: Mapping rows to records#

1public record Product(int Id, string Name, decimal Price, int Stock, string? Description);
2

3static async Task<List<Product>> GetProductsAsync(string cs)
4{
5    await using var conn = new SqlConnection(cs);
6    await conn.OpenAsync();
7    await using var cmd = new SqlCommand("SELECT Id, Name, Price, Stock, Description FROM Products ORDER BY Name", conn);
8    await using var r = await cmd.ExecuteReaderAsync();
9

10    int id = r.GetOrdinal("Id"), name = r.GetOrdinal("Name"), price = r.GetOrdinal("Price"),
11        stock = r.GetOrdinal("Stock"), desc = r.GetOrdinal("Description");
12

13    var list = new List<Product>();
14    while (await r.ReadAsync())
15    {
16        list.Add(new Product(
17            r.GetInt32(id), r.GetString(name), r.GetDecimal(price), r.GetInt32(stock),
18            r.IsDBNull(desc) ? null : r.GetString(desc)));
19    }
20    return list;
21}
22

23foreach (var p in await GetProductsAsync(cs)) Console.WriteLine(
quot;{p.Name,-10} ₹{p.Price,9} stock {p.Stock}");

Keyboard   ₹  2200.00 stock 40
Laptop     ₹ 55000.00 stock 8
Mouse      ₹  1400.00 stock 120

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. GetOrdinal finds each column’s position once.
2. ReadAsync moves to the next row without blocking a thread.
3. Typed getters read values directly.
4. Description can be NULL, so IsDBNull decides between null and the string.
5. The method returns a list — the reader and connection are closed before it returns.

Real-world use case#

Common mistakes#

Best practices#

• Use GetOrdinal once, outside the loop
• Use typed getters
• Map to records or DTOs inside the method
• Select only the columns you use

Remember this#

Practice exercise#

1await using var cmd = new SqlCommand("SELECT Id, Name FROM Products WHERE Price > @min", conn);
2cmd.Parameters.Add("@min", SqlDbType.Decimal).Value = min;
3await using var r = await cmd.ExecuteReaderAsync();
4var result = new List<(int, string)>();
5while (await r.ReadAsync()) result.Add((r.GetInt32(0), r.GetString(1)));

Mini quiz#

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

Related topics#

My notes#

Saved in this browser.