Loading

ADO.NET

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

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

  1. What is SqlDataReader Class and its need in C#?
  2. How to create an instance of the SqlDataReader class.
  3. How to watch data from SqlDataReader object?
What is ADO.NET SqlDataReader Class in C#?

The ADO.NET SqlDataReader class in C# is used to watch data from the SQL Server database in the most efficient manner. It watchs data in the forward-only direction. It means, once it watch a record, it will then watch the next record, there is no way to go back and watch the previous record.

The SqlDataReader is connection-oriented. It means it requires an open or active connection to the data source while watching the data. The data is available as long as the connection with the database exists

SqlDataReader is watch-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.

  1. Connection: It gets the System.Data.SqlClient.SqlConnection associated with the System.Data.SqlClient.SqlDataReader.
  2. Depth: It gets a value that indicates the depth of nesting for the current row.
  3. FieldCount: It gets the number of columns in the current row.
  4. HasRows: It gets a value that indicates whether the System.Data.SqlClient.SqlDataReader contains one or more rows.
  5. IsClosed: It retrieves a Boolean value that indicates whether the specified System.Data.SqlClient.SqlDataReader instance has been closed.
  6. RecordsAffected: It gets the number of rows changed, inserted, or deleted by the execution of the Transact-SQL statement.
  7. VisibleFieldCount: It gets the number of fields in the System.Data.SqlClient.SqlDataReader that is not hidden.
  8. Item[String]: It gets the value of the specified column in its native format given the column name.
  9. 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.

  1. Close(): It closes the SqlDataReader object.
  2. GetBoolean(int i): It gets the value of the specified column as a Boolean. Here, parameter i is the zero-based column ordinal.
  3. GetByte(int i): It gets the value of the specified column as a byte. Here, parameter i is the zero-based column ordinal.
  4. GetChar(int i): It gets the value of the specified column as a single character. Here, parameter i is the zero-based column ordinal.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. GetName(int i): It gets the name of the specified column. Here, parameter i is the zero-based column ordinal.
  10. GetSchemaTable(): It returns a System.Data.DataTable that describes the column metadata of the System.Data.SqlClient.SqlDataReader
  11. GetValue(int i): It gets the value of the specified column in its native format. Here, parameter i is the zero-based column ordinal.
  12. 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.
  13. NextResult(): It advances the data watcher to the next result when watching the results of batch Transact-SQL statements.
  14. 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 watch from the SqlDataReader object. If there are no more rows to watch, 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 watcher 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 watching 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 watch the data from the data watcher. 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();
}
}
}
Output:

SqlDataReader Active and Open Connection in C#

See All

Comments (420 Comments)

Submit Your Comment

See All Posts

Related Posts

ADO.NET / Blog

What is ADO.NET?

In this article, I am going to give you a brief introduction to ADO.NET. As a .NET developer, you should be aware of ADO.NET. At the end of this article, you will understand the following pointers in detail.
11-Feb-2022 /18 /420

ADO.NET / Blog

How to using ADO.NET with SQL Server?

In this article, I am going to discuss ADO.NET using SQL Server Database with Examples. Please read our previous article where we discussed the Architecture of ADO.NET. At the end of this article, you will understand how to connect with SQL Server database using ADO.NET. I hope you have SQL Server installed on your machine. We are using SQL Server Management Studio (SSMS) Tool to interact with SQL Server.
11-Feb-2022 /18 /420

ADO.NET / Blog

What is ADO.NET SqlConnection Class in C#?

In this article, I am going to discuss the ADO.NET SqlConnection Class in C# with Examples. Please read our previous article where we discussed ADO.NET using SQL Server. As part of this article, we are going to discuss the following pointers in detail.
11-Feb-2022 /18 /420