What is ADO.NET SqlDataReader in C#?. The Complete ADO.NET Developer Course 2024

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.

What is ADO.NET SqlDataReader Class?

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.

How to create instance of SqlDataReader class?

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#.

Example to understand the SqlDataReader Object in ADO.NET

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



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.
" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

ADO.NET SqlDataReader

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, Name Index position is 0, 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.
" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

SqlDataReader in C#

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 we 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.
" + e.Message);
            }
            Console.ReadKey();
        }
    }
}

What is ADO.NET SqlDataReader in C#?. The Complete ADO.NET Developer Course 2023 [Videos].

What is ADO.NET SqlDataReader Class in C#?

SqlDataReader Class Signature:

ADO.NET SqlDataReader Class Properties in C#:

ADO.NET SqlDataReader Class Methods in C#:

How to create an instance of the ADO.NET SqlDataReader class in C#?

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

Example: Using SqlDataReader in C#

Output:

Example: SqlDataReader in C#

Output:

Example: SqlDataReader Active and Open Connection in C#

Output:

Comments (420 Comments)

Submit Your Comment

Related Posts

What is ADO.NET?

How to using ADO.NET with SQL Server?

What is ADO.NET SqlConnection Class in C#?

What is Bitcoin and Cryptocurrency Technologies?

What is LINQ Extension Methods in C#?

What is Asynchronous Programming with Entity Framework?

The Pros of Selling on Amazon

Instagram SEO Tactics to Rank Higher in 2022

What is Differences between IEnumerable and IQueryable in C#?

ASP.NET Web API Test Questions and Answers

ASP.NET MVC Test Questions and Answers

How to Sell on Amazon International Marketplaces?