ADO.NET SqlDataReader in C# with Example

Back to: ADO.NET Tutorial For Beginners and Professionals

ADO.NET SqlDataReader in C# with Examples

In this article, I am going to discuss the ADO.NET SqlDataReader in C# with Examples. Please read our previous article where we discussed ADO.NET SqlCommand Class. At the end of this article, we are going to discuss the following pointers in detail.

What is SqlDataReader Class and its need in C#?
How to create an instance of the SqlDataReader class.
How to read data from 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 read a record, it will then read the next record, there is no way to go back and read the previous record.

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.

SqlDataReader Class Signature:

If you look at the following image, you will see that this class is inherited from 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 the execution of 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 value of the specified column in its native format given the column name.
Item[Int32]: It gets the value of the specified column 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 value of the specified column as a Boolean. Here, parameter i is the zero-based column ordinal.
GetByte(int i): It gets the value of the specified column as a byte. Here, parameter i is the zero-based column ordinal.
GetChar(int i): It gets the value of the specified column 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 value of the specified column as a double-precision floating-point number. Here, parameter i is the zero-based column ordinal.
GetFloat(int i): It gets the value of the specified column 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 value of the specified column 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 is 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 and returns true if there are more rows; otherwise false.

How to 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 SqlDataReader class. In order to create the instance of SqlDataReader class, what you need to do is, call the ExecuteReader method of the SqlCommand object which will return an instance of SqlDataReader class as shown in the below image.

Example to understand the C# SqlDataReader Object in ADO.NET:

We are going to use the following student table to understand the SqlDataReader object in C#.

Please use the below SQL script to create a database called StudentDB, 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: Using 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, then this method will simply return false. Here, in the below example, we are retrieving the data by 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: SqlDataReader in C#

In the below example, we are accessing the data from the SqlDataReader object by 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 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: 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 below example, once we execute the ExecuteReader method, then we close the connection and then try to read the data from the data reader. As the connection is closed, so 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 you can access multiple result sets using a data reader. For this demo, we are going to 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, then 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:

In the next article, I am going to discuss the ADO.NET SqlDataAdapter class in detail. Here, in this article, I try to explain the ADO.NET SqlDataReader class in C# with examples. I hope this C# SqlDataReader Object article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this ADO.NET SqlDataReader article.