How to retrieve records from SqlDataReader?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 9330 red flag

In this article, we shall learn 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. 

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)