Posted on 12/23/2020 7:25:57 AM by Admin

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 so that cannot be inherited. It inherits DbDataReader class and implements IDisposable interface.

SqlDataReader Signature


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

SqlDataReader Properties

Property Description
Connection It is used to get the SqlConnection associated with the SqlDataReader.
Depth It is used to get a value that indicates the depth of nesting for the current row.
FieldCount It is used to get the number of columns in the current row.
HasRows It is used to get a value that indicates whether the SqlDataReader contains one or more rows..
IsClosed It is used to retrieve a boolean value that indicates whether the specified SqlDataReader instance has been closed.
Item[String] It is used to get the value of the specified column in its native format given the column name.
Item[Int32] It is used to get the value of the specified column in its native format given the column ordinal.
RecordsAffected It is used to get the number of rows changed, inserted or deleted by execution of the Transact-SQL statement.
VisibleFieldCount It is used to get the number of fields in the SqlDataReader that are not hidden.

Methods

Method Description
Close() It is used to closes the SqlDataReader object.
GetBoolean(Int32) It is used to get the value of the specified column as a Boolean.
GetByte(Int32) It is used to get the value of the specified column as a byte.
GetChar(Int32) It is used to get the value of the specified column as a single character.
GetDateTime(Int32) It is used to get the value of the specified column as a DateTime object.
GetDecimal(Int32) It is used to get the value of the specified column as a Decimal object.
GetDouble(Int32) It is used to get the value of the specified column as a double-precision floating point number.
GetFloat(Int32) It is used to get the value of the specified column as a single-precision floating point number.
GetName(Int32) It is used to get the name of the specified column.
GetSchemaTable() It is used to get a DataTable that describes the column metadata of the SqlDataReader.
GetValue(Int32) It is used to get the value of the specified column in its native format.
GetValues(Object[])/td> It is used to populate an array of objects with the column values of the current row.
NextResult() It is used to get the next result, when reading the results of SQL statements.
Read() It is used to read record from the SQL Server database.

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 Employee", connection);
      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["myConnection"].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 thru 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 thru 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.

Exported CSV file
Exported CSV file