How to retrieve records from SqlDataReader?

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In order to retrieve records from the database using SqlDataReader and getting their values by iterating through
them, we can use this approach.

Below is the Button code on my ASPX Page

ASPX Page

<p><asp:Button ID="btnRead" runat="server" Text="Read data from SqlDataReader"
OnClick="ReadDataFromDataReader" /></p>

CODE BEHIND

 
    string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
 
/// <summary>
    /// Reads the data from data reader.
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event
data.</param>
    protected void ReadDataFromDataReader(object sender, EventArgs e)
    {
        // get the connection
        using (SqlConnection conn = new SqlConnection(_connStr))
        {
             // write the sql statement to execute
            string sql = "SELECT AutoId, FileName FROM Files Order By FileName ASC";
            // instantiate the command object to fire
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                conn.Open();
                // If you want to control the database connection behavior, simply call
the ExecuteReader method without any parameter
               // using (SqlDataReader reader = cmd.ExecuteReader()
               // if you want to close the connection as soon as reader is closed, pass
the CommandBehavior, the way I have passed below
                using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                  while (reader.Read())
                  {
                     // 1st way - not better way
                     //Response.Write("<p>" + reader["AutoId"] + ". " +
reader["FileName"] + "</p>");
 
                       // 2nd way - considered to be faster and better
                       Response.Write("<p>" +
reader.GetInt32(reader.GetOrdinal("AutoId")) + ". " +
reader.GetString(reader.GetOrdinal("FileName")) + "</p>");
                    }
               }
          }
       }
   }

In the above code snippet, on the click of the button, “ReadDataFromDataReader” method from code behind executes. In this method, we have retrieved the data by calling the ExecuteReader method of the command object. Now to iterate through all records, we need to run a while loop till the record exists into the SqlDataReader. Inside the loop, we have shown two ways of retrieving the data from the reader and the 2nd way is considered to be faster and better (reader object exposes several methods according to the type of data we need to retrieve, like we have used GetInt32 and GetString method to retrieve Integer and String types of data respectively from the reader).

The GetOrdinal method gives the position of the column from the left for the field whose name is passed as parameter.

Hope this article was useful, thanks for reading.

Stay tuned for more article in this series and if you are looking for other articles on ADO.NET, do not forget to check my earlier articles.