---
title: "What is SqlDataReader?"  
description: "The SqlDataReader class in ADO.NET is used to fetch and read data from a SQL Server database efficiently and forward-only."  
author: "ICSM Computer"  
published: 2025-02-12  
updated: 2025-02-12  
canonical: https://www.mindstick.com/articles/338506/what-is-sqldatareader  
category: "ado.net"  
tags: ["c#", "ado.net"]  
reading_time: 4 minutes  

---

# What is SqlDataReader?

The `SqlDataReader` class in ADO.NET is used to **fetch and read data** from a SQL [Server database](https://www.mindstick.com/forum/155643/how-to-create-sql-server-database-in-google-cloud) **efficiently and forward-only**. It retrieves data row-by-row, making it **faster and memory-efficient** than `DataSet` or `DataTable`.

## Namespace:

```plaintext
using System.Data.SqlClient;
```

#### 1. How to Use `SqlDataReader`?

## Basic Example: Fetching Data from a Table

```cs
string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    string query = "SELECT Id, Username, Email FROM Users";

    using (SqlCommand cmd = new SqlCommand(query, conn))
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())  // Reads each row one by one
        {
            Console.WriteLine($"ID: {reader["Id"]}, Username: {reader["Username"]}, Email: {reader["Email"]}");
        }
    }
}
```

1. `ExecuteReader()`: Executes the SQL query and returns a `SqlDataReader` object.
2. `Read()`: Moves to the next row in the result set (returns `false` when no more rows exist).

#### 2. Accessing Data from `SqlDataReader`

## 2.1. Using Column Index

```cs
int id = reader.GetInt32(0); // Gets data from the first column
string username = reader.GetString(1); // Gets data from the second column
string email = reader.GetString(2);
```

- Faster than using column names.
- Harder to maintain if column order changes.

## 2.2. Using Column Name

```cs
int id = Convert.ToInt32(reader["Id"]);
string username = reader["Username"].ToString();
string email = reader["Email"].ToString();
```

- Easier to read and maintain.
- Slightly slower than using indexes.

#### 3. Handling NULL Values in `SqlDataReader`

If a column can have `NULL` values, you should **check for** `DBNull` before accessing it.

```cs
string email = reader.IsDBNull(reader.GetOrdinal("Email")) ? "No Email" : reader["Email"].ToString();
```

- `IsDBNull(int index)`: Checks if the column contains `NULL`.
- `GetOrdinal("ColumnName")`: Gets the index of the column dynamically.

#### 4. Reading a Single Row (`ExecuteReader()`)

If you only need a **single row**, use `Read()` **without a loop**:

```cs
using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 Username FROM Users", conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
    if (reader.Read()) // Only one row
    {
        Console.WriteLine("Username: " + reader["Username"]);
    }
}
```

Best for retrieving a **single** row.

#### 5. Checking if Data Exists (`HasRows`)

Before reading, you can check if any rows exist.

```cs
if (reader.HasRows)
{
    while (reader.Read())
    {
        Console.WriteLine("User: " + reader["Username"]);
    }
}
else
{
    Console.WriteLine("No users found.");
}
```

Avoids unnecessary looping when the result set is empty.

#### 6. Using `SqlDataReader` with Stored Procedures

You can also use `SqlDataReader` to execute a **[stored procedure](https://www.mindstick.com/forum/12886/stored-procedure-error-transaction-count-mismatch)**.

## Example: Fetching Data from a Stored Procedure

```cs
using (SqlCommand cmd = new SqlCommand("GetUserById", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@UserId", 1);

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Username: {reader["Username"]}, Email: {reader["Email"]}");
        }
    }
}
```

- `CommandType.StoredProcedure`: Tells ADO.NET that we're calling a stored procedure.
- **Pass parameters** using `cmd.Parameters.AddWithValue()`.

#### 7. Closing and Disposing `SqlDataReader` Properly

Always **close the reader** after use to free up [database connections](https://www.mindstick.com/forum/160212/explain-dependency-injection-for-database-connections-in-dot-net-core-api).

Best Practice: Use `using` Statements (Auto-Close `SqlDataReader`)

```cs
using (SqlCommand cmd = new SqlCommand(query, conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine(reader["Username"]);
    }
} // Automatically closes reader and command here
```

Manually Close Reader if Not Using `using`

```cs
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["Username"]);
}
reader.Close(); // Close the reader after use
```

#### 8. Asynchronous Execution with `SqlDataReader`

For [better performance](https://answers.mindstick.com/qa/111685/how-do-i-optimize-my-code-for-better-performance) in **ASP.NET applications**, use `async` queries.

```cs
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn))
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())
    {
        Console.WriteLine(reader["Username"]);
    }
}
```

- **Does not block the main thread** (better for [web applications](https://www.mindstick.com/blog/11464/improve-your-understanding-of-web-applications)).
- **Improves performance** for large queries.

#### 9. Using `NextResult()` for Multiple Result Sets

If your query returns **multiple result sets**, use `NextResult()`.

```cs
using (SqlCommand cmd = new SqlCommand("EXEC GetUsersAndOrders", conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
    // First Result Set: Users
    while (reader.Read())
    {
        Console.WriteLine("User: " + reader["Username"]);
    }

    // Move to the next result set
    if (reader.NextResult())
    {
        // Second Result Set: Orders
        while (reader.Read())
        {
            Console.WriteLine("Order ID: " + reader["OrderId"]);
        }
    }
}
```

Best for **[stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) that return [multiple tables](https://www.mindstick.com/forum/160909/how-to-join-multiple-tables-and-retrieve-specific-columns-in-sql-server)**.

#### 10. Best Practices for Using `SqlDataReader`

- **Always use** `using` **statements** to automatically close the reader.
- **Use** `HasRows` before reading to check if data exists.
- **Use** `NextResult()` when [handling multiple](https://www.mindstick.com/interview/34064/handling-multiple-exceptions) result sets.
- **Use** `IsDBNull()` to handle `NULL` values safely.
- **Use** `async` **methods** (`ExecuteReaderAsync()`, `ReadAsync()`) for non-blocking execution.

## Conclusion

`SqlDataReader` is a **fast, memory-efficient** way to read **forward-only** [data from SQL](https://www.mindstick.com/forum/12885/how-to-retrieving-data-from-sql-server-and-adding-it-to-arraylist) Server. It’s ideal for large data retrieval scenarios where you don’t need full in-memory storage like `DataTable`.

---

Original Source: https://www.mindstick.com/articles/338506/what-is-sqldatareader

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.