Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core SqlDataReader Class with Examples
In this article, I will discuss the ADO.NET Core SqlDataReader Class with Examples. Please read our previous article discussing the ADO.NET Core SqlCommand Class.
What is ADO.NET Core SqlDataReader?
The SQLDataReader class in ADO.NET Core is a high-performance, forward-only, read-only data retrieval mechanism for efficiently fetching data from SQL Server databases. It enables sequential access to the rows returned from executing an SQL query or stored procedure.
Due to its forward-only and read-only nature, it reads one row at a time, minimizing memory usage and improving performance, especially when processing large volumes of data. So, it is an ideal choice for applications that need to efficiently process large amounts of data without loading it all into memory at once.
Key Points:
- Forward-only: You can only move forward through the result set using the Read method.
- Read-only: Data can be accessed but not modified directly through the SqlDataReader.
- Connected Architecture: It requires an open SqlConnection while reading data.
How Do We Create an Instance of the ADO.NET Core SqlDataReader Class?
You cannot create an instance of the SqlDataReader class directly using the new keyword. Instead, the SqlDataReader instance is returned by calling the ExecuteReader or ExecuteReaderAsync method of a SqlCommand object.
For example, after establishing a connection and preparing a SQL query, you create a SqlCommand object and call ExecuteReader() to retrieve the results. The ExecuteReader method runs your SQL query or stored procedure and returns a SqlDataReader that lets you iterate through the results. The syntax is given below:
The ExecuteReader method returns an instance of SqlDataReader.
Example to Understand ADO.NET Core SqlDataReader Class
Let us see an example to understand the ADO.NET Core SqlDataReader Class. We will use the following Customers and Orders table to understand this concept.
Please execute the following SQL Script in your SQL Server to create the OrderDB database and the Customers and Orders table and insert some dummy data into it.
CREATE DATABASE OrderDB;
GO
USE OrderDB;
GO
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100),
Email NVARCHAR(100),
Address NVARCHAR(255)
);
GO
CREATE TABLE Orders (
OrderID INT PRIMARY KEY IDENTITY(1,1),
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
GO
-- Insert dummy data into Customers
INSERT INTO Customers (Name, Email, Address) VALUES
('John Doe', 'john.doe@example.com', '1234 Elm Street'),
('Jane Smith', 'jane.smith@example.com', '5678 Oak Street'),
('Bob Johnson', 'bob.johnson@example.com', '9101 Pine Street');
-- Insert dummy data into Orders
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES
(1, '2023-01-10', 150.00),
(1, '2023-02-15', 200.00),
(2, '2023-03-20', 100.00),
(3, '2023-04-25', 250.00);
GO
How Do We Fetch Data with SqlDataReader?
Reading data from a SqlDataReader object in ADO.NET Core involves several steps. They are as follows:
- Establish a Connection: Create and open a connection to your SQL Server database using SqlConnection.
- Prepare the Command: Create a SqlCommand object with the SQL query you want to execute.
- Execute and Retrieve Data: To retrieve the SqlDataReader instance, call the ExecuteReader (or its async counterpart) method of the SqlCommand object.
- Read Rows: Use the Read method in a loop to iterate through each row. For each row, retrieve column values using the SqlDataReader’s methods (e.g., GetString, GetInt32) or the indexer.
- Close the SqlDataReader: After reading the data, close the SqlDataReader object.
- Close the SqlCommand: After reading the data, close the SqlCommand object
- Close the Connection: Close the SqlConnection object to free up the database resources.
Fetching Data from Customers Table using SqlDataReader
Please modify the Program class as follows. The following application fetches all rows from the Customers table using SqlDataReader. The following example code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Set your connection string to point to the OrderDB.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Use a try-catch block for proper exception handling.
try
{
// Establish the connection using the SqlConnection object.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the database connection.
connection.Open();
// Define the SQL query to select data from the Customers table.
string sqlQuery = "SELECT CustomerID, Name, Email, Address FROM Customers";
// Create a command using the SQL query and the open connection.
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
// Execute the command, which returns a SqlDataReader instance.
using (SqlDataReader reader = command.ExecuteReader())
{
// Check if the reader has rows before iterating.
if (reader.HasRows)
{
Console.WriteLine("Customer Details:");
// Iterate over the returned rows.
while (reader.Read())
{
// Reading each column's data using the reader indexer.
Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Address: {reader["Address"]}");
}
}
else
{
Console.WriteLine("No customer data found.");
}
} // The SqlDataReader is closed here.
} // The SqlCommand is closed here.
}// The connection is closed here.
}
catch (SqlException sqlEx)
{
// Log any SQL exceptions encountered during execution.
Console.WriteLine($"SQL error occurred: {sqlEx.Message}");
}
catch (Exception ex)
{
// Log any other exceptions encountered during execution.
Console.WriteLine($"Unexpected error occurred: {ex.Message}");
}
}
}
}
Code Explanation:
- Connection Setup: A SqlConnection is opened using a proper connection string for the OrderDB database.
- Command Execution: An SqlCommand is created with a SELECT statement that fetches customer details.
- Reader Iteration: The ExecuteReader() method returns a SqlDataReader. The code checks HasRows and iterates through each row with the Read() method.
- Output: Column values are accessed via the column name and printed to the console.
- Exception Handling: Specific SQL exceptions are caught separately from general exceptions.
- Resource Management: The using statements ensure that the connection, command, and reader are closed automatically, even if an error occurs.
When you run the above code, you will get the following output:
ADO.NET Core SqlDataReader Object Methods and Properties
The following are some of the key methods and properties of the SqlDataReader object that are crucial for effective data manipulation and retrieval.
Important Methods of ADO.NET Core SqlDataReader Object
- Read(): Advances the reader to the next record. Returns true if there are more rows.
- NextResult(): Moves to the next result set when executing a batch SQL command containing multiple SELECT statements.
- GetValue(int index): Retrieves the value of the column at the given index as an object.
- GetString(int index) / GetInt32(int index) / etc.: Retrieves a strongly typed value from a column.
- GetOrdinal(string columnName): Returns the index of the specified column, which is useful for accessing columns by name when performance is critical.
- GetDataTypeName(int index): Returns the data type of the specified column, as defined in the database.
- GetValues(object[] values): Populates an array with all column values for the current row.
- IsDBNull(int index): Checks whether a specified column in the current row contains a DBNull value.
- Close(): Closes the SqlDataReader object.
Important Properties of ADO.NET Core SqlDataReader Object
- HasRows: Indicates whether the reader contains any rows.
- FieldCount: Gets the number of columns in the current row.
- IsClosed: Indicates whether the reader is closed.
- RecordsAffected: Returns the number of rows changed, inserted, or deleted by the SQL statement (for SELECT operations, it returns -1).
Example to Understand the above Method and Properties:
For a better understanding, please modify the Program class as follows. The following example shows how to use the methods and properties of the SqlDataReader object. It reads data from the Customers table, checks various properties, and demonstrates how to retrieve column data using different techniques. The following example code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Connection string to the OrderDB Database.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// Create and open the connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the database connection.
connection.Open();
// Query to fetch customer details.
string sqlQuery = "SELECT CustomerID, Name, Email, Address FROM Customers";
// Create SqlCommand object
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
// Execute reader
using (SqlDataReader reader = command.ExecuteReader())
{
// Display whether the reader has rows.
// Check if any rows are present
Console.WriteLine($"HasRows: {reader.HasRows}");
// Check the number of columns
Console.WriteLine($"FieldCount: {reader.FieldCount}");
// If rows exist, read them
if (reader.HasRows)
{
int rowCount = 0;
// Iterate over each row.
while (reader.Read())
{
rowCount++;
Console.WriteLine($"\n--- Reading row {rowCount} ---");
// Loop through all the columns for the current row.
for (int i = 0; i < reader.FieldCount; i++)
{
// Retrieve column metadata and value.
string columnName = reader.GetName(i);
string dataType = reader.GetDataTypeName(i);
object value = reader.GetValue(i);
Console.WriteLine($"\tColumn Name: {columnName}, Data Type: {dataType}, Value: {value}");
}
// Demonstrate the use of GetOrdinal and IsDBNull.
int emailColumnIndex = reader.GetOrdinal("Email");
if (!reader.IsDBNull(emailColumnIndex))
{
string email = reader.GetString(emailColumnIndex);
Console.WriteLine($"Retrieved Email: {email}");
}
else
{
Console.WriteLine("Email column contains a null value.");
}
// Demonstrate the use of GetValues.
object[] values = new object[reader.FieldCount];
int numberOfValues = reader.GetValues(values);
Console.WriteLine($"Number of columns retrieved: {numberOfValues}");
// Accessing data via the indexer.
string? customerName = reader["Name"].ToString();
Console.WriteLine($"Customer Name from indexer: {customerName}");
}
}
// Explicitly close the reader (not required with using, shown for demonstration).
reader.Close();
Console.WriteLine($"\nReader Closed: {reader.IsClosed}");
// For SELECT queries, RecordsAffected returns -1.
Console.WriteLine($"Records Affected by the command: {command.ExecuteNonQuery()}");
}
}
}
}
catch (SqlException sqlEx)
{
Console.WriteLine($"SQL Exception: {sqlEx.Message}");
}
catch (Exception ex)
{
Console.WriteLine($"General Exception: {ex.Message}");
}
}
}
}
Code Explanation:
- Property Output: The code prints out HasRows and FieldCount to show the basic characteristics of the result.
- Column Loop: Inside each row, a loop retrieves metadata (using GetName and GetDataTypeName) and the value using GetValue.
- Demonstration: It further shows how to use GetOrdinal and IsDBNull for conditional reading and GetValues to fetch all column values.
- Cleanup & Additional Info: Finally, it explicitly closes the reader, displays the status (IsClosed), and uses ExecuteNonQuery() (which returns -1 for SELECT queries) to demonstrate command effects.
Output:
Understanding the Connection-Oriented Nature of SqlDataReader
The SqlDataReader is a strictly connection-oriented object. This means we should keep the connection open while reading data. The data is available as long as the connection with the database exists. If the connection is closed prematurely (for example, closing the connection before finishing the data read), then any further read attempts will result in a runtime error.
For a better understanding, please modify the Program class as follows. In the below code, once we execute the ExecuteReader method, we close the connection and then try to read the data from the reader. As the connection is closed, it will give a runtime error.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
Console.WriteLine("Connection opened successfully.");
// SQL query to fetch all customers.
string sql = "SELECT * FROM Customers";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
// Intentionally closing the connection before reading.
connection.Close();
Console.WriteLine("Connection closed before reading data.");
// This loop will throw an exception because the underlying connection is closed.
while (reader.Read())
{
Console.WriteLine($"CustomerID: {reader["CustomerID"]}, Name: {reader["Name"]}");
}
}
}
}
}
catch (Exception ex)
{
// Expected exception: "Invalid attempt to call Read when reader is closed."
Console.WriteLine($"Exception occurred: {ex.Message}");
}
}
}
}
Code Explanation:
Because the connection is closed immediately after executing ExecuteReader(), any subsequent call to reader.Read() fails with an error such as “Invalid attempt to call Read when reader is closed.” This example underlines that the connection must remain open while the SqlDataReader is in use.
Output:
Accessing Multiple Result Sets Using SqlDataReader
When executing a batch of SQL statements (for example, two separate SELECT statements), the SqlDataReader can retrieve multiple result sets. To move from one result set to the next, we need to use the NextResult() method.
Fetching Both Customers and Orders Data Using SqlDataReader
Let us fetch data from both the Customers and Orders tables using two SELECT statements and then display the data using SqlDataReader in a console application. This involves executing a batch of SQL commands (for example, two separate SELECT statements). The first retrieves all customers; the second retrieves all orders. For a better understanding, please modify the Program class as follows.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Define the connection string to the OrderDB database.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Batch query to fetch customers first, then orders.
string query = "SELECT * FROM Customers; SELECT * FROM Orders;";
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
// Process the first result set (Customers).
Console.WriteLine("Customers:");
while (reader.Read())
{
Console.WriteLine($"\tCustomerID: {reader["CustomerID"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Address: {reader["Address"]}");
}
// Move to the next result set (Orders).
if (reader.NextResult())
{
Console.WriteLine("\nOrders:");
while (reader.Read())
{
Console.WriteLine($"\tOrderID: {reader["OrderID"]}, CustomerID: {reader["CustomerID"]}, OrderDate: {reader["OrderDate"]}, TotalAmount: {reader["TotalAmount"]}");
}
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Code Explanation:
- Batch Query: The query contains two SELECT statements separated by a semicolon.
- Result Set Navigation: The code first iterates through the Customers result set. Then it calls reader.NextResult() to advance to the Orders result set and prints the order details.
Output:
How Do We Display the Customer and Its Related Order Information Parallelly?
Now, we require that instead of displaying the customer and order information separately, we need to display the customer information and its related order information in parallel. There are two common approaches:
Approach 1: Using Two Separate Queries and Matching in Code
Using Two Separate Queries and Matching in Code. This approach involves:
- Fetch and Store Customer Data: Execute the first SELECT statement to fetch all customer data and store it in a dictionary keyed by CustomerID.
- Fetch Orders and Match with Customers: Execute the second SELECT statement to fetch all orders and match each order with its customer from the dictionary
Let us understand this with an example. First, create the following Customer and Order models:
Customer.cs
Create a class file named Customer.cs and then copy and paste the following code:
namespace ADODOTNETCoreDemo
{
public class Customer
{
public int CustomerID { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Address { get; set; }
}
}
Order.cs
Create a class file named Order.cs and then copy and paste the following code:
namespace ADODOTNETCoreDemo
{
public class Order
{
public int OrderID { get; set; }
public int CustomerID { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
}
}
Modify Program Class:
Next, modify the Program class as follows. In the example below, we fetch Customers first, store them in a Dictionary<int, Customer>, then fetch Orders and match them to their corresponding customers.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Dictionary to store customer records, with CustomerID as key.
Dictionary<int, Customer> customers = new Dictionary<int, Customer>();
// Connection string to OrderDB Database
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the database connection.
connection.Open();
// Step 1: Fetch customer data and populate the dictionary.
string customerQuery = "SELECT * FROM Customers;";
using (SqlCommand customerCommand = new SqlCommand(customerQuery, connection))
{
using (SqlDataReader customerReader = customerCommand.ExecuteReader())
{
while (customerReader.Read())
{
// Create a Customer object from the row data.
var customer = new Customer
{
CustomerID = customerReader.GetInt32(customerReader.GetOrdinal("CustomerID")),
Name = customerReader.GetString(customerReader.GetOrdinal("Name")),
Email = customerReader.GetString(customerReader.GetOrdinal("Email")),
Address = customerReader.GetString(customerReader.GetOrdinal("Address")),
};
// Add the customer to the dictionary.
customers.Add(customer.CustomerID, customer);
}
}
}
Console.WriteLine("=== Customer and Related Orders ===");
// Step 2: Fetch orders and display the matching customer's information.
string orderQuery = "SELECT * FROM Orders;";
using (SqlCommand orderCommand = new SqlCommand(orderQuery, connection))
{
using (SqlDataReader orderReader = orderCommand.ExecuteReader())
{
while (orderReader.Read())
{
// Create an Order object from the row data.
var order = new Order
{
OrderID = orderReader.GetInt32(orderReader.GetOrdinal("OrderID")),
CustomerID = orderReader.GetInt32(orderReader.GetOrdinal("CustomerID")),
OrderDate = orderReader.GetDateTime(orderReader.GetOrdinal("OrderDate")),
TotalAmount = orderReader.GetDecimal(orderReader.GetOrdinal("TotalAmount"))
};
// Check if the corresponding customer exists.
if (customers.TryGetValue(order.CustomerID, out Customer? customer))
{
// Display customer info followed by order details.
Console.WriteLine($"CustomerID: {customer.CustomerID}, Name: {customer.Name}, Email: {customer.Email}, Address: {customer.Address}");
Console.WriteLine($"\tOrderID: {order.OrderID}, OrderDate: {order.OrderDate.ToShortDateString()}, TotalAmount: {order.TotalAmount}");
}
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Code Explanation:
- Customer Data Storage: Customers are read from the database and stored in a dictionary for fast look-up by CustomerID.
- Order Retrieval and Matching: Orders are read separately and for each order the associated customer is retrieved from the dictionary. This approach avoids joining data in SQL if you want to manipulate it programmatically.
Now, run the application, and you should get the following output:
Approach 2: Using a SQL JOIN
Alternatively, we can retrieve and display both customer and order data in a single query by joining the tables. Please note that customer information will be repeated for every order if a customer has multiple orders. For a better understanding, modify the Program class as follows.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// SQL query joins Customers and Orders.
string joinQuery = @"
SELECT c.CustomerID, c.Name, c.Email, c.Address,
o.OrderID, o.OrderDate, o.TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;";
using (SqlCommand command = new SqlCommand(joinQuery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine("=== Customer and Orders (Joined Data) ===");
while (reader.Read())
{
// Customer details (repeated per order).
Console.WriteLine($"CustomerID: {reader["CustomerID"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Address: {reader["Address"]}");
// Corresponding order details.
Console.WriteLine($"\tOrderID: {reader["OrderID"]}, OrderDate: {Convert.ToDateTime(reader["OrderDate"]).ToShortDateString()}, TotalAmount: {reader["TotalAmount"]}");
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Code Explanation:
- Join Query: The query joins the Customers and Orders tables based on CustomerID so that each row contains both customer and corresponding order information.
- Simplified Processing: Since both pieces of information come together, no additional in-code mapping is required. The result set is iterated in a single loop, with repeated customer information if multiple orders exist for one customer.
- Output: Each row displays both the customer and the associated order details.
Now, run the application, and you should get the following output:
Asynchronous Database Operations using SqlDataReader
Asynchronous operations in ADO.NET Core enable non-blocking data access by using the async/await pattern. This is especially useful in responsive applications because the thread can continue executing other tasks while waiting for the database response. The SqlDataReader provides several asynchronous methods that align with the async/await pattern, such as:
- ReadAsync(): Asynchronously reads the next row.
- NextResultAsync(): Asynchronously advances to the next result set.
Implementing Asynchronous Read Operations
The following is an asynchronous example illustrating how to read from two related tables (Customers and Orders) using the SqlDataReader in ADO.NET Core. This approach fetches both result sets in separate SQL Queries, correlates them in memory, and then displays each customer followed by all their associated orders. The following example code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static async Task Main(string[] args)
{
// Connection string to the OrderDatabase.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Dictionaries to store fetched data.
// customers: Key is CustomerID, value is the Customer object.
Dictionary<int, Customer> customers = new Dictionary<int, Customer>();
// ordersByCustomer: Key is CustomerID, value is a list of related Order objects.
Dictionary<int, List<Order>> ordersByCustomer = new Dictionary<int, List<Order>>();
try
{
// Establish the connection asynchronously.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection asynchronously.
await connection.OpenAsync();
// ***** Fetch Customer Data Asynchronously *****
string customerQuery = "SELECT CustomerID, Name, Email, Address FROM Customers";
// Create a command object for the customer query.
using (SqlCommand customerCommand = new SqlCommand(customerQuery, connection))
{
// Execute the customer query asynchronously, returning an SqlDataReader.
using (SqlDataReader customerReader = await customerCommand.ExecuteReaderAsync())
{
// Read each customer record asynchronously.
while (await customerReader.ReadAsync())
{
// Retrieve customer details by column index.
int customerId = customerReader.GetInt32(0); // Column 0: CustomerID.
string name = customerReader.GetString(1); // Column 1: Name.
string email = customerReader.GetString(2); // Column 2: Email.
string address = customerReader.GetString(3); // Column 3: Address.
// Create a Customer object with the retrieved data.
Customer customer = new Customer
{
CustomerID = customerId,
Name = name,
Email = email,
Address = address
};
// Add the customer to the dictionary.
customers[customerId] = customer;
}
}
}
// ***** Fetch Order Data Asynchronously *****
string orderQuery = "SELECT OrderID, CustomerID, OrderDate, TotalAmount FROM Orders";
// Create a command object for the order query.
using (SqlCommand orderCommand = new SqlCommand(orderQuery, connection))
{
// Execute the order query asynchronously, returning an SqlDataReader.
using (SqlDataReader orderReader = await orderCommand.ExecuteReaderAsync())
{
// Read each order record asynchronously.
while (await orderReader.ReadAsync())
{
// Retrieve order details by column index.
int orderId = orderReader.GetInt32(0); // Column 0: OrderID.
int customerId = orderReader.GetInt32(1); // Column 1: CustomerID.
DateTime orderDate = orderReader.GetDateTime(2); // Column 2: OrderDate.
decimal totalAmount = orderReader.GetDecimal(3); // Column 3: TotalAmount.
// Create an Order object with the retrieved data.
Order order = new Order
{
OrderID = orderId,
CustomerID = customerId,
OrderDate = orderDate,
TotalAmount = totalAmount
};
// If this is the first order for the customer, initialize a new list.
if (!ordersByCustomer.ContainsKey(customerId))
{
ordersByCustomer[customerId] = new List<Order>();
}
// Add the order to the customer's list.
ordersByCustomer[customerId].Add(order);
}
}
}
} // The connection is automatically closed here by the using block.
// ***** Display Customer Information with Associated Orders *****
Console.WriteLine("=== Customer Information with Associated Orders ===\n");
// Iterate through each customer in the dictionary.
foreach (var customerEntry in customers)
{
//Fetch the Value
Customer customer = customerEntry.Value;
// Print customer details.
Console.WriteLine($"CustomerID: {customer.CustomerID}, Name: {customer.Name}, Email: {customer.Email}, Address: {customer.Address}");
// If orders exist for this customer, print each order.
if (ordersByCustomer.TryGetValue(customer.CustomerID, out List<Order>? customerOrders))
{
foreach (var order in customerOrders)
{
Console.WriteLine($"\tOrderID: {order.OrderID}, OrderDate: {order.OrderDate.ToShortDateString()}, TotalAmount: {order.TotalAmount}");
}
}
else
{
// Inform if there are no orders for the customer.
Console.WriteLine("\tNo orders found for this customer.");
}
// Add an empty line for better readability.
Console.WriteLine();
}
}
// Catch SQL-specific exceptions.
catch (SqlException sqlEx)
{
Console.WriteLine($"SQL Error: {sqlEx.Message}");
}
// Catch any other general exceptions.
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
}
Output:
What Are the Benefits of Using SqlDataReader in an Application?
The primary benefits include:
- Performance & Efficiency: Since it is forward-only and does not cache data in memory, it is very fast and efficient for read-only operations.
- Minimal Memory Overhead: It loads only the current row into memory, making it ideal for processing large volumes of data.
- Simplicity: It provides a straightforward way to stream data from the database.
- Asynchronous Support: It comes with asynchronous methods (e.g., ExecuteReaderAsync, ReadAsync) that enable non-blocking operations, which is especially useful in scalable web applications.
The SqlDataReader class in ADO.NET Core is a high-performance, forward-only, read-only cursor used to retrieve data from the SQL Server. Because it reads one row at a time, it consumes very little memory compared to other data retrieval methods. Its design is ideal when you need to process large data sets sequentially.
In the next article, I will discuss the ADO.NET Core DataTable in detail. In this article, I explain the ADO.NET Core SqlDataReader Class with Examples. I want your feedback. Please post feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
Your tutorials are the best! Thank you.
nice