How to bind data using SqlDataReader and where to use SqlDataReader?

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

In this article, we shall learn how to bind data using SqlDataReader and where to use 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 demonstrate binding data using SqlDataReader and scenario where we need to use SqlDataReader to bind the data, I have created a sample page.

Below is the DropDownList code on my ASPX Page

ASPX PAGE

<asp:DropDownList ID="dropImages" runat="server" DataTextField="FileName"

DataValueField="AutoId" />

CODE BEHIND

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

 

 

protected void Page_Load(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))

{

dropImages.DataSource = reader;

dropImages.DataBind();

}

}

}

}

In the above code snippet, you can see that we have a DropDownList on the .aspx page and from the code behind; we have used SqlDataReader to specify the DataSource of the DropDownList. As the amount of data to be fetched from the database is less so we have used SqlDataReader.

Notice that ExecuteDataReader method has CommandBehavior (CommandBehavior.CloseConnection) as parameter that instructs the SqlDataReader that as soon as it is closed, close the database connection as well.

Important point to note here is that SqlDataReader works in the connected mode, it means that during the data binding operation the database connection must be alive as against the DataTable where you can fetch the data from the database using the SqlDataAdapter.Fill method and close the database connection and then perform your operation or bind the data.

SqlDataReader is good when you have to deal with less amount of data or loading large amount of data in a not so busy application. This is because it blocks the database connection; however it is faster than DataTable or DataSet.

In N-tier architecture you should not return SqlDataReader object from your BAL or DAL methods instead you can return the DataTable or DataTableReader

Now you may ask question that if we should not return SqlDataReader from BAL or DAL then where to use SqlDataReader. This is frequently used to fetch the data from the database, iterate through its records and build a collection object to return to the BAL/UI or get a single record from the database and set its values to the Business objects etc.

OUTPUT

Note: The way we used SqlDataReader, in the same way we can use reader for other database provider as well, like OledbDataReader, MySqlDataReader tec

Hope this article was useful, do write your comment or suggestions.

Thanks for reading !

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)