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 ADO.NET Core SqlCommand Class. As part of this article, we will discuss the following pointers in detail.
- What is ADO.NET Core SqlDataReader Class?
- How Do We Create an Instance of the ADO.NET Core SqlDataReader Class?
- Example to Understand ADO.NET Core SqlDataReader Class.
- ADO.NET Core SqlDataReader Object Important Methods and Properties.
- ADO.NET Core SqlDataReader Active and Open Connection.
- How Do We Access Multiple Result Sets Using ADO.NET Core SqlDataReader?
- How Do We Display Multiple Result Set Data Parallely?
- Asynchronous Database Operations using ADO.NET Core SqlDataReader Object.
- Implementing Asynchronous Read Operations using SqlDataReader.
What is ADO.NET Core SqlDataReader Class?
The SqlDataReader class in ADO.NET Core reads a forward-only stream of rows from a SQL Server database. It is part of the Microsoft.Data.SqlClient namespace. The SqlDataReader class is efficient and optimized for performance, making it ideal for retrieving large volumes of data where you need to process each row sequentially. This class is used with the SqlCommand object to execute SQL queries and retrieve results. The following are the key features of the SqlDataReader class:
- Forward-Only Stream: The SqlDataReader provides a way to read rows in a forward-only manner, meaning you can only move from the first row to the last row, one row at a time. You can not go back to previous rows.
- Read-Only: This class is read-only, meaning you cannot modify the data it retrieves or update data in the database.
- Read Method: The Read() method advances the SqlDataReader to the next record. It returns true if there are more rows; otherwise, it returns false.
- Data Access Methods: The SqlDataReader provides a variety of methods to access the data, such as GetString(), GetInt32(), GetBoolean(), and so on, which allow you to retrieve the value of a column in its native type.
- Column Access: Columns can be accessed by their ordinal position (index) or by their name.
- Performance: SqlDataReader is designed for high performance when retrieving large volumes of data because the data is not loaded into memory all at once; instead, it’s accessed as the reader moves forward through the stream.
- Connection Requirement: While a SqlDataReader is open, the associated SqlConnection remains busy serving the SqlDataReader. Therefore, you cannot execute any commands that require an open connection elsewhere until you close the SqlDataReader.
- Close Method: It’s essential to call the Close() method on the SqlDataReader when you’re done with it to free up the database connection for other operations.
How Do We Create an Instance of the ADO.NET Core SqlDataReader Class?
You cannot create the instance of SqlDataReader using the new keyword. Then, the question is how we get or create the instance of the SqlDataReader class in ADO.NET Core. To create the instance of the SqlDataReader class, you need to call the ExecuteReader method of the SqlCommand object, which will return an instance of the SqlDataReader class, as shown in the image below.
Example to Understand ADO.NET Core SqlDataReader Class
Let’s understand the ADO.NET Core SQLDataReader Object with an example. We will use the following Employee table to illustrate this concept.
Please execute the following SQL script in your SQL Server to create the EmployeeDB database and the Employees table and insert some dummy data into it.
CREATE DATABASE EmployeeDB;
GO
USE EmployeeDB;
GO
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(100),
Department NVARCHAR(50)
);
GO
INSERT INTO Employees (FirstName, LastName, Email, Department)
VALUES
('John', 'Doe', 'john.doe@example.com', 'IT'),
('Jane', 'Doe', 'jane.doe@example.com', 'HR'),
('Jim', 'Beam', 'jim.beam@example.com', 'Finance');
GO
Example to Fetch Data with SqlDataReader Object
Reading data from a SqlDataReader object in ADO.NET Core involves executing an SQL command and using the SqlDataReader to access the data a query returns. The process typically includes the following steps:
- Establish a Connection: Use SqlConnection to establish a connection to the database.
- Execute the Command: Create a SqlCommand object to define the SQL query or stored procedure you want to execute.
- Create the SqlDataReader instance: Execute the SqlCommand object’s ExecuteReader method to create an SqlDataReader.
- Read the Data: Use the Read method of SqlDataReader to iterate through the rows the query returns.
- Access Column Values: Access column values within each row using the SqlDataReader’s methods, like GetString, GetInt32, etc.
- Close the SqlDataReader object: After reading the data, close the SqlDataReader object.
- Close the Connection objecr: Close the SqlConnection object to free up the database resources.
Now, modify the Program class as follows. The following code is self-explained, so please go through the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
//The connectionString contains information needed to establish a connection to the database,
//including the server name, database name, and authentication method.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
//Creating an instance of SqlConnection. It's instantiated with the connection string.
//Using block will ensures that the SQLConnection is disposed of correctly once it goes out of scope,
//which closes the connection to the database.
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Opening the Connection
connection.Open();
//A string containing the SQL query to be executed.
//This query selects all columns from the Employees table.
string sql = "SELECT EmployeeID, FirstName, LastName, Email, Department FROM Employees";
//Represents an SQL statement that can be executed against an SQL Server database.
//It is initialized with the SQL query and the connection object.
SqlCommand command = new SqlCommand(sql, connection);
//ExecuteReader Executes the SqlCommand and returns an SqlDataReader object to read the data returned by the query.
//SqlDataReader read a forward-only stream of rows from a SQL Server database
using (SqlDataReader reader = command.ExecuteReader())
{
//Advances the SqlDataReader to the next record. It returns true if there are more rows; otherwise, it is false.
while (reader.Read())
{
//Accessed Data by column name
Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Department: {reader["Department"]}");
}
}
}
}
}
}
When you run the above code, you will get the following output:
ADO.NET Core SqlDataReader Object Important Methods and Properties
The following are some of the methods and properties of the SqlDataReader object that are crucial for effective data manipulation and retrieval.
Methods of ADO.NET Core SqlDataReader Object
- Read(): Advances the SqlDataReader to the next record. It returns true if there are more rows; otherwise, it is false. This method is used in a loop to process each row in the result set.
- Close(): Closes the SqlDataReader object. It’s usually called automatically when exiting a using block.
- GetDataTypeName(int i): Gets the name of the data type of the specified column.
- GetOrdinal(string name): Returns the zero-based column ordinal given the column’s name.
- GetValue(int i): Retrieves the specified column’s value in its native format.
- IsDBNull(int i): Checks if the specified column contains DBNull, i.e., Checks whether the column contains non-existent or missing values.
Properties of ADO.NET Core SqlDataReader Object
- FieldCount: Gets the number of columns in the current row.
- HasRows: Indicates if the SqlDataReader has one or more rows. This can be used to avoid calling Read() when there are no rows.
- IsClosed: Indicates whether the SqlDataReader is closed.
- Item[string name] and Item[int i]: Provide indexers with the ability to retrieve data from a column specified by either the column’s name or its ordinal number in the result set.
- RecordsAffected: Provides the number of rows changed, inserted, or deleted by executing the SQL statement. It is not applicable for SELECT statements. For SELECT statements, it returns -1.
Example to Understand Method and Properties of SqlDataReader Object:
Let us see an example to understand the important method and properties of the ADO.NET Core SqlDataReader object. 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)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
int count = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "SELECT EmployeeID, FirstName, LastName, Email FROM Employees";
SqlCommand command = new SqlCommand(sql, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine($"HasRows: {reader.HasRows}");
Console.WriteLine($"FieldCount: {reader.FieldCount}");
if (reader.HasRows)
{
while (reader.Read())
{
count = count + 1;
Console.WriteLine($"\nReading {count} Row:");
for (int i = 0; i < reader.FieldCount; i++)
{
string dataType = reader.GetDataTypeName(i);
string columnName = reader.GetName(i);
object value = reader.GetValue(i);
Console.WriteLine($"\tColumn Name: {columnName}, Data Type: {dataType}, Value: {value}");
}
// Demonstrating GetOrdinal and IsDBNull
int emailColumnIndex = reader.GetOrdinal("Email");
if (!reader.IsDBNull(emailColumnIndex))
{
string email = reader.GetString(emailColumnIndex);
Console.WriteLine($"Email: {email}");
}
else
{
Console.WriteLine("Email column is DBNull.");
}
// Demonstrating GetValues
object[] values = new object[reader.FieldCount];
int numberOfValues = reader.GetValues(values);
Console.WriteLine($"{numberOfValues} values have been read.");
// Accessing data through Item property
string firstName = reader["FirstName"].ToString();
Console.WriteLine($"First Name: {firstName}");
// IsClosed, and RecordsAffected demonstration will be after the reader is closed to avoid disrupting the read loop.
}
}
// Demonstrating Close method explicitly, though it's not necessary with using statement
reader.Close();
Console.WriteLine($"\nIsClosed: {reader.IsClosed}");
// RecordsAffected is more relevant for update/delete operations
Console.WriteLine($"RecordsAffected: {command.ExecuteNonQuery()}"); // Will return -1 for SELECT operations
}
}
}
}
}
Output:
Example to Understand ADO.NET Core SqlDataReader Active and Open Connection
The SqlDataReader in ADO.NET Core is Connection-Oriented. This means an open or active connection to the data source must be maintained while reading the data. The data is available as long as the connection with the database exists. In the example below, 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=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "SELECT * FROM Employees";
SqlCommand command = new SqlCommand(sql, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
//Closing the Connection
connection.Close();
//The following Statement should throw Runtime Exception
while (reader.Read())
{
Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Department: {reader["Department"]}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
}
}
}
Output: Exception Occurred: Invalid attempt to call Read when reader is closed.
How Do We Access Multiple Result Sets Using ADO.NET Core SqlDataReader?
Accessing multiple result sets with ADO.NET Core using the SqlDataReader involves executing an SQL command that returns more than one result sets. This functionality is helpful in scenarios where you need to execute a stored procedure or a batch SQL statement that returns multiple data tables.
Accessing multiple result sets using SqlDataReader in ADO.NET Core involves executing a command that returns multiple result sets and then iterating over those result sets using the SqlDataReader.NextResult() method. This method advances the data reader to the next result set if one exists. Let us understand this with an example. 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 OrderDatabase database and the Customers and Orders table and insert some dummy data into it.
CREATE DATABASE OrderDatabase;
GO
USE OrderDatabase;
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
Fetching Both Customers and Orders Data Using SqlDataReader
Fetching data from both the Customers and Orders tables using two SELECT statements with a single command object in ADO.NET Core and then displaying the data using SqlDataReader in a console application involves executing a batch SQL command. This command contains both SELECT statements, and then we must use the SqlDataReader.NextResult() method to move between the result sets. The following code is self-explained, so please go through the comment lines for a better understanding:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDatabase;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
//Establishing a Database Connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection to the database
connection.Open();
// Preparing the SQL Command
// Create the command with both SELECT statements
string query = "SELECT * FROM Customers; SELECT * FROM Orders;";
//Create the Command Object
using (SqlCommand command = new SqlCommand(query, connection))
{
//Execute the Command using ExecuteReader which will return an instance of SqlDataReader
//SqlDataReader will have both result set
using (SqlDataReader reader = command.ExecuteReader())
{
// First result set (Customers), i.e., First Select Statement Result
Console.WriteLine("Customers:");
//The Read method on the SqlDataReader is called in a loop to read each row in the first result set (Customers).
while (reader.Read())
{
//For each row, it accesses column values (like CustomerID, Name, etc.) and prints them to the console.
Console.WriteLine($"\tID: {reader["CustomerID"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Address: {reader["Address"]}");
}
// Move to the next result set (Orders), i.e., Select Select Statement Result if any
// The reader.NextResult() is called to advance the reader to the next result set (Orders), if available.
if (reader.NextResult())
{
//Process is same to read the data from the second result set
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}");
}
}
}
}
So, when you run the application code, you will get the following output:
How Do We Display the Customer and its Related Order Information Paralley?
Now, what is our requirement? Instead of displaying the Customer and Order information separately, we need to display the Customer and its related Order Information parallelly. Let us proceed and see how we can implement this by modifying our previous example.
To display customer information and their related orders in parallel, we need to modify the logic to first read all customers into a suitable data structure (like a list or dictionary) and then iterate over the orders, matching them with their respective customers. This approach involves two main steps:
- Fetch and Store Customer Data: Execute the first SELECT statement to fetch all customer data and store it in a dictionary or a list.
- Fetch Orders and Match with Customers: Execute the second SELECT statement to fetch all orders. For each order, use the stored customer data to display it along with its corresponding customer information.
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; }
}
}
Next, modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
Dictionary<int, Customer> customers = new Dictionary<int, Customer>();
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDatabase;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Fetch customers
string customerQuery = "SELECT * FROM Customers;";
using (SqlCommand customerCommand = new SqlCommand(customerQuery, connection))
{
using (SqlDataReader customerReader = customerCommand.ExecuteReader())
{
while (customerReader.Read())
{
var customer = new Customer
{
CustomerID = (int)customerReader["CustomerID"],
Name = customerReader["Name"].ToString(),
Email = customerReader["Email"].ToString(),
Address = customerReader["Address"].ToString()
};
customers.Add(customer.CustomerID, customer);
}
}
}
Console.WriteLine("Customer and Orders:");
// Fetch orders and match with customers
string orderQuery = "SELECT * FROM Orders;";
using (SqlCommand orderCommand = new SqlCommand(orderQuery, connection))
{
using (SqlDataReader orderReader = orderCommand.ExecuteReader())
{
while (orderReader.Read())
{
var order = new Order
{
OrderID = (int)orderReader["OrderID"],
CustomerID = (int)orderReader["CustomerID"],
OrderDate = (DateTime)orderReader["OrderDate"],
TotalAmount = (decimal)orderReader["TotalAmount"]
};
// Match order with customer
if (customers.TryGetValue(order.CustomerID, out Customer customer))
{
Console.WriteLine($"CustomerID: {customer.CustomerID}, Name: {customer.Name}, Email: {customer.Email}, Address: {customer.Address}");
Console.WriteLine($"\tOrderID: {order.OrderID}, OrderDate: {order.OrderDate}, TotalAmount: {order.TotalAmount}");
}
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Code Explanation:
- Customer Data Storage: The code begins by querying the Customers table and storing each customer record in a Dictionary<int, Customer> customers. This makes it efficient to retrieve customer information by ID later.
- Order Processing: After loading customers, it fetches orders from the Orders table. Each order uses the CustomerID to find the corresponding customer in the dictionary.
- Displaying Information: For each order, it displays the related customer’s data along with order details.
Now, run the application, and you should get the following output:
Another Approach:
Given the sequential nature of the SqlDataReader over two distinct result sets, a more efficient approach would be to modify the SQL query to join the customers and orders tables, fetching related data in a single result set. This method simplifies the logic needed to display customer and order information together. 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=OrderDatabase;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection to the database
connection.Open();
// Modify the query to join Customers and Orders tables
string query = @"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;";
//Create the Command Object
using (SqlCommand command = new SqlCommand(query, connection))
{
//SqlDataReader will have both result set
using (SqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine("Customer and Orders:");
while (reader.Read())
{
// Assuming you are okay with repeating customer info for each order
Console.WriteLine($"CustomerID: {reader["CustomerID"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Address: {reader["Address"]}");
Console.WriteLine($"\t OrderID: {reader["OrderID"]}, OrderDate: {reader["OrderDate"]}, TotalAmount: {reader["TotalAmount"]}");
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Now, run the application, and you should get the following output:
Asynchronous Database Operations using ADO.NET Core SqlDataReader Object
The Asynchronous database operations in ADO.NET Core allow for efficient data retrieval without blocking the executing thread. Asynchronous operations let your application continue with other work that doesn’t depend on the database query result, improving overall application throughput.
The SqlDataReader provides several asynchronous methods that implement the async/await pattern in C#. These methods enable non-blocking database reads, making your application more responsive and scalable.
- ReadAsync(): Asynchronously reads the next row from the result set.
- NextResultAsync(): Asynchronously advances to the next result when reading the results of batch SQL statements.
- GetFieldValueAsync<T>(): Asynchronously gets the specified column’s value as a type T.
- IsDBNullAsync(): Asynchronously checks if the column contains non-existent or missing values.
Implementing Asynchronous Read Operations using SqlDataReader
To implement asynchronous read operations using SqlDataReader, please modify the Program class as follows. The following example code initiates an asynchronous database connection, executes a command to obtain a SqlDataReader, and then asynchronously reads the data.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static async Task Main(string[] args)
{
try
{
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Define the SQL query to be executed
string query = "SELECT EmployeeID, FirstName, LastName, Email, Department FROM Employees";
// Create and open a connection asynchronously
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Open the Connection
await connection.OpenAsync();
// Create a SqlCommand to execute the SQL query
using (SqlCommand command = new SqlCommand(query, connection))
{
// Execute the command and get a SqlDataReader asynchronously
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
// Read the data asynchronously
while (await reader.ReadAsync())
{
// Assuming Column1 is of type int and Column2 is of type string
int column1 = reader.GetInt32(0); //EmployeeID - Type INT
string column2 = reader.GetString(1); //FirstName - Type String
string column3 = reader.GetString(2); //LastName - Type String
string column4 = reader.GetString(3); //Email - Type String
string column5 = reader.GetString(4); //Department - Type String
Console.WriteLine($"EmployeeID: {column1}, FirstName: {column2}, LastName: {column3}, Email: {column4}, Department: {column5}");
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Output:
In the next article, I will discuss the ADO.NET Core SqlDataAdapter Class in detail. In this article, I explain the ADO.NET Core SqlDataReader Class with Examples. I would like 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