Back to: ADO.NET Tutorial For Beginners and Professionals
ADO.NET SqlDataReader Class in C# with Examples
I will discuss the ADO.NET SqlDataReader Class in C# with Examples in this article. Please read our previous article, where we discussed ADO.NET SqlCommand Class. At the end of this article, we will discuss the following pointers in detail.
- What is the SqlDataReader Class and its need in C#?
- How to create an instance of the SqlDataReader class.
- How to read data from the SqlDataReader object?
What is ADO.NET SqlDataReader Class in C#?
The ADO.NET SqlDataReader class in C# is used to read data from the SQL Server database in the most efficient manner. It reads data in the forward-only direction. It means once it reads a record, it will then read the next record; there is no way to go back and read the previous record.
- SqlDataReader is Connection-Oriented. It means it requires an open or active connection to the data source while reading the data. The data is available as long as the connection with the database exists.
- SqlDataReader is Read-Only. It means it is also not possible to change the data using SqlDataReader. You also need to open and close the connection explicitly.
- Forward-Only: The SqlDataReader works forwardly, meaning it can only read data in one direction – from the first to the last.
SqlDataReader Class Signature:
If you look at the following image, you will see that this class is inherited from the DbDataReader class and implements the IDisposable interface.
ADO.NET SqlDataReader Class Properties in C#:
The SqlDataReader class provides the following properties.
- Connection: It gets the System.Data.SqlClient.SqlConnection associated with the System.Data.SqlClient.SqlDataReader.
- Depth: It gets a value that indicates the depth of nesting for the current row.
- FieldCount: It gets the number of columns in the current row.
- HasRows: It gets a value that indicates whether the System.Data.SqlClient.SqlDataReader contains one or more rows.
- IsClosed: It retrieves a Boolean value that indicates whether the specified System.Data.SqlClient.SqlDataReader instance has been closed.
- RecordsAffected: It gets the number of rows changed, inserted, or deleted by executing the Transact-SQL statement.
- VisibleFieldCount: It gets the number of fields in the System.Data.SqlClient.SqlDataReader that is not hidden.
- Item[String]: It gets the specified column’s value in its native format, given the column name.
- Item[Int32]: It gets the specified column’s value in its native format given the column ordinal.
ADO.NET SqlDataReader Class Methods in C#:
The SqlDataReader class provides the following methods.
- Close(): It closes the SqlDataReader object.
- GetBoolean(int i): It gets the specified column’s value as a Boolean. Here, parameter i is the zero-based column ordinal.
- GetByte(int i): It gets the specified column’s value as a byte. Here, parameter i is the zero-based column ordinal.
- GetChar(int i): It gets the specified column’s value as a single character. Here, parameter i is the zero-based column ordinal.
- GetDateTime(int i): It gets the value of the specified column as a System.DateTime object. Here, parameter i is the zero-based column ordinal.
- GetDecimal(int i): It gets the value of the specified column as a System.Decimal object. Here, parameter i is the zero-based column ordinal.
- GetDouble(int i): It gets the specified column’s value as a double-precision floating-point number. Here, parameter i is the zero-based column ordinal.
- GetFloat(int i): It gets the specified column’s value as a single-precision floating-point number. Here, parameter i is the zero-based column ordinal.
- GetName(int i): It gets the name of the specified column. Here, parameter i is the zero-based column ordinal.
- GetSchemaTable(): It returns a System.Data.DataTable that describes the column metadata of the System.Data.SqlClient.SqlDataReader
- GetValue(int i): It gets the specified column’s value in its native format. Here, parameter i is the zero-based column ordinal.
- GetValues(object[] values): It Populates an array of objects with the column values of the current row. Here, the parameter values are an array of System.Object into which to copy the attribute columns.
- NextResult(): It advances the data reader to the next result when reading the results of batch Transact-SQL statements.
- Read(): It Advances the System.Data.SqlClient.SqlDataReader to the next record, returns true if there are more rows; otherwise, it is false.
How do you create an instance of the ADO.NET SqlDataReader class in C#?
You can not create the instance of SqlDataReader using the new keyword. Then, the question is how we get or create the instance of the SqlDataReader class. To create the instance of the SqlDataReader class, what you need to do is 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 the C# SqlDataReader Object in ADO.NET:
We will use the following student table to understand the SqlDataReader object in C#.
Please use the SQL script below to create a database called StudentDB and a table called Student with the required sample data.
CREATE DATABASE StudentDB; GO USE StudentDB; GO CREATE TABLE Student( Id INT PRIMARY KEY, Name VARCHAR(100), Email VARCHAR(50), Mobile VARCHAR(50) ) GO INSERT INTO Student VALUES (101, 'Anurag', 'Anurag@dotnettutorial.net', '1234567890') INSERT INTO Student VALUES (102, 'Priyanka', 'Priyanka@dotnettutorial.net', '2233445566') INSERT INTO Student VALUES (103, 'Preety', 'Preety@dotnettutorial.net', '6655443322') INSERT INTO Student VALUES (104, 'Sambit', 'Sambit@dotnettutorial.net', '9876543210') GO
Example to Understand ADO.NET SqlDataReader in C#
We need to fetch all the data from the student table and need to display it in the console using SqlDataReader. The following code exactly does the same thing. In the below example, we use the Read() method of the SqlDataReader object to loop through the items of the SqlDataReader object. The Read method returns true as long as there are rows to read from the SqlDataReader object. If there are no more rows to read, this method will return false. In the example below, we are retrieving the data using the string key names, nothing but the column names returned by the select clause.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConString))
{
// Creating the command object
SqlCommand cmd = new SqlCommand("select Name, Email, Mobile from student", connection);
// Opening Connection
connection.Open();
// Executing the SQL query
SqlDataReader sdr = cmd.ExecuteReader();
//Looping through each record
while (sdr.Read())
{
Console.WriteLine(sdr["Name"] + ", " + sdr["Email"] + ", " + sdr["Mobile"]);
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
Console.ReadKey();
}
}
}
Output:
Note: The DataReader object increases the performance of the application as well as reduces the system overheads, and the reason for this is one row at a time is stored in memory.
Example to Understand SqlDataReader in C#
In the example below, we access the data from the SqlDataReader object using the index number. Here, the index is starting from 0. So, the Name Index position is 0, the Email Index Position is 1, and the Mobile Index Position is 2. So, you can retrieve the data from the data reader by either using the string key or the Integer index position.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConString = @"data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConString))
{
// Creating the command object
SqlCommand cmd = new SqlCommand("select Name, Email, Mobile from student", connection);
// Opening Connection
connection.Open();
// Executing the SQL query
SqlDataReader sdr = cmd.ExecuteReader();
//Looping through each record
while (sdr.Read())
{
Console.WriteLine(sdr[0] + ", " + sdr[1] + ", " + sdr[2]);
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
Console.ReadKey();
}
}
}
Output:
Example to Understand SqlDataReader Active and Open Connection in C#
The SqlDataReader is connection-oriented. It means it requires an open or active connection to the data source 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 System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConString = @"data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConString))
{
// Creating the command object
SqlCommand cmd = new SqlCommand("select Name, Email, Mobile from student", connection);
// Opening Connection
connection.Open();
// Executing the SQL query
SqlDataReader sdr = cmd.ExecuteReader();
// Closing the Connection
connection.Close();
//Reading Data from Reader will give runtime error as the connection is closed
while (sdr.Read())
{
Console.WriteLine(sdr[0] + ", " + sdr[1] + ", " + sdr[2]);
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e.Message);
}
Console.ReadKey();
}
}
}
Output:
How to Access Multiple Result Sets Using DataReader in C#?
As of now, we have discussed how to retrieve the result set using the Read method of the Data Reader object. Now, you will understand how to access multiple result sets using a data reader. For this demo, we will use the following Customers and Orders tables to understand the ADO.NET DataSet.
Please use the below SQL Script to create a database and tables and populate the Customers and Orders tables with the required sample data in the SQL Server database.
CREATE DATABASE ShoppingCartDB; GO USE ShoppingCartDB; GO CREATE TABLE Customers( ID INT PRIMARY KEY, Name VARCHAR(100), Mobile VARCHAR(50) ) GO INSERT INTO Customers VALUES (101, 'Anurag', '1234567890') INSERT INTO Customers VALUES (102, 'Priyanka', '2233445566') INSERT INTO Customers VALUES (103, 'Preety', '6655443322') GO CREATE TABLE Orders( ID INT PRIMARY KEY, CustomerId INT, Amount INT ) GO INSERT INTO Orders VALUES (10011, 103, 20000) INSERT INTO Orders VALUES (10012, 101, 30000) INSERT INTO Orders VALUES (10013, 102, 25000) GO
Example to Understand NextResult Method of DataReader Object:
When we want to access the second result set, we need to call the NextResult method on the data reader object. The NextResult method returns true if there is any new result set. For a better understanding, please have a look at the following example. The following example code is self-explained, so please go through the comment lines.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=ShoppingCartDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConString))
{
// Creating the command object
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers; SELECT * FROM Orders", connection);
// Opening Connection
connection.Open();
// Executing the SQL query
SqlDataReader reader = cmd.ExecuteReader();
//Looping through First Result Set
Console.WriteLine("First Result Set:");
while (reader.Read())
{
Console.WriteLine(reader[0] + ", " + reader[1] + ", " + reader[2]);
}
//To retrieve the second result set from SqlDataReader object, use the NextResult().
//The NextResult() method returns true and advances to the next result-set.
while (reader.NextResult())
{
Console.WriteLine("\nSecond Result Set:");
//Looping through each record
while (reader.Read())
{
Console.WriteLine(reader[0] + ", " + reader[1] + ", " + reader[2]);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:
When to Use ADO.NET SqlDataReader Class in C#?
Here are some situations where you might consider using SqlDataReader:
- High-Performance Data Reading: Use SqlDataReader when performance is critical. It is optimized for fast, efficient data reading and faster than other methods like DataTable or DataSet because it does not load all data into memory at once.
- Read-Only Data Access: If you only need to read data from the database and don’t need to perform any updates, inserts, or deletes, SqlDataReader is a suitable choice.
- Large Volume Data Processing: For processing large volumes of data, SqlDataReader is ideal because it reads data row-by-row, which means it uses less memory and can handle large datasets more efficiently.
- Sequential Data Access: When you need to access data sequentially, SqlDataReader is the way to go. It’s a forward-only reader, meaning you can only read data in the order it is received from the database.
- Data Binding in Web Applications: In web applications, SqlDataReader can be used for simple data binding scenarios where the data is displayed in data-bound controls and no manipulation or updates are required.
- Real-Time Data Display: For scenarios where data needs to be fetched and displayed in real-time, like in dashboards or reporting tools, SqlDataReader provides an efficient way to retrieve and present data quickly.
- Minimizing Memory Footprint: If you want to minimize the memory footprint of your application, SqlDataReader is a good choice as it does not store the entire dataset in memory.
- Streaming Data: SqlDataReader is suitable for streaming data scenarios, where you process data as it’s read without waiting for the entire set of data to be loaded.
Best Practices
- Using Statement: Always use a using statement for SqlDataReader and SqlConnection to ensure that resources are properly released.
- Exception Handling: Implement proper exception handling, especially for database operations, which can fail for various reasons.
- Performance Considerations: Since SqlDataReader is connected and forward-only, it’s very efficient for large data reads. However, ensure the database connection is not kept open longer than necessary.
- Read-only Access: Remember that SqlDataReader is read-only and forward-only. If you need more functionality, like editing data or moving back and forth, consider other ADO.NET objects like DataTable or DataSet.
In the next article, I will discuss the ADO.NET SqlDataAdapter class in detail. In this article, I try to explain the ADO.NET SqlDataReader Class in C# with examples. I hope this C# SqlDataReader Class article will help you with your needs. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET SqlDataReader 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.
