The SqlDataReader class in ADO.NET is used to fetch and read data from a SQL Server database efficiently and forward-only. It retrieves data row-by-row, making it faster and memory-efficient than DataSet or DataTable.

Namespace:

using System.Data.SqlClient;

1. How to Use SqlDataReader?

Basic Example: Fetching Data from a Table

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

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

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.

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:

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.

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.

Example: Fetching Data from a Stored Procedure

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.

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

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

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 in ASP.NET applications, use async queries.

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).
• Improves performance for large queries.

9. Using NextResult() for Multiple Result Sets

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

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 that return multiple tables.

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 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 Server. It’s ideal for large data retrieval scenarios where you don’t need full in-memory storage like DataTable.