ADO.NET SqlDataReader Class

If a .NET application (Web, Desktop, Console etc.) has to retrieve data, then the application needs to
    1. Connect to the Database
    2. Prepare an SQL Command
    3. Execute the Command
    4. Retrieve the results and display in the application

This is the normal flow which is used by any .Net application, not only to retrieve data but also to insert, update or Delete data. we have already covered the first two steps in previous sections Sql Connection ,read here and Sql Command , read here. we also learnt that to execute command we either use ExecuteReader(), ExecuteScalar() or ExecuteNonQuery() method of Command class depending on whether we are retrieving multiple columns, single column (scalar value) or performing insert,update and delete operation respectively.

when we want to retrieve multiple values then we use ExecuteReader() method of command object, this method returns a SqlDataReader object because When the ExecuteReader method in the SqlCommand Object executes , it actually instantiate a SqlClient.SqlDataReader Object.

SqlDataReader class is used to read data from SQL Server database. It reads data in forward-only fashion from a SQL Server database. It reads data in the most efficient manner possible. It is read-only and forward only, meaning once you read a record and go to the next record, there is no way to go back to the previous record. It is also not possible to change the data using SqlDataReader. SqlDataReader is connection oriented, meaning it requires an active connection to the data source, while reading data. The forward-only nature of SqlDataReader is what makes it an efficient choice to read data.

It is sealed class and cannot be inherited. It inherits DbDataReader class and implements IDisposable interface.

SqlDataReader Signature

  public class SqlDataReader : System.Data.Common.DbDataReader, IDisposable  

SqlDataReader Properties

Methods

we cannot create an instance of SqlDataReader using the new operator as shown below. If we try to do so, we will get a compilation error stating - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.

SqlDataReader rd = new SqlDataReader(); // Not Valid

The SqlCommand object's ExecuteReader() method creates and returns an instance of SqlDataReader.

SqlCommand command = new SqlCommand("Select * from table", con);
SqlDataReader reader = command.ExecuteReader();

Another important point to keep in mind is that, SqlDataReader is connection oriented and the connection needs to be opened explicitly, by calling the Open() method on the connection object, before calling the ExecuteReader() method of the command object, just like the SqlConnection object, SqlDataReader is wrapped in an using block. This will ensure that the SqlDataReader is closed in a timely fashion, and that we don't run out of available connections to the database.

string cs = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
    connection.Open();
    SqlCommand cmd = new SqlCommand("Select * from Employee", con);
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        GridView1.DataSource = rdr;
        GridView1.DataBind();
    }
}

Please note that, finally block, can also be used to close the SqlConnection and SqlDataReader objects.

If for some reason, you want to loop through each row in the SqlDataReader object, then use the Read() method, which returns true as long as there are rows to read. If there are no more rows to read, then this method will return false.

In the following example, we loop through each row in the SqlDataReader to write values in a text file in CSV format.

Export to CSV from SqlDataReader in C#

private void ExportToCsv()
        {
            using (SqlConnection con = new SqlConnection("Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"))
            {
                SqlCommand cmd = new SqlCommand("select * from Employee",con);
                con.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                using (StreamWriter writer = new StreamWriter("D:\\ExportFolder\\file.txt"))
                {
                    while (reader.Read())
                    {
                        // Using Name and Phone as example columns.
                        writer.WriteLine("Name: {0}, Salary : {1}",
                          reader["Name"], reader["Salary"]);
                    }
                }

            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportToCsv();
        }

The aspx page looks like shown below

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Home.aspx.cs" Inherits="AdoNet.Home" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
   

        <asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>
 <form id="form1" runat="server">
    <div>

I have used Test database in the above example. Execute the following SQL script using SQL Server Management Studio. The script creates:-
  1. Test Database.
  2. Employee table under Test Database. and
&emsp 3. Populate it with some data

 Create Database Test
 Go
 Use Test
 Go
 CREATE TABLE [dbo].[Employee] (
    [Id]     INT          IDENTITY (1, 1) NOT NULL,
    [Name]   VARCHAR (50) NULL,
    [Salary] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC)
 );
 Go
 SET IDENTITY_INSERT [dbo].[Employee] ON
 INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (1, N'sachin', 100000)
 INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (2, N'arjun', 23000)
 INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (3, N'vikash', 30000)
 INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (4, N'pocha', 43000)
 INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (5, N'Nivedita', 24000)
 SET IDENTITY_INSERT [dbo].[Employee] OFF
 Go

When you run the project ,you will get a text file with below results.