In this article, we shall learn how to bind data using SqlDataReader and where to use SqlDataReader.
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
<asp:DropDownList ID="dropImages" runat="server" DataTextField="FileName"
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))
// 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 =
dropImages.DataSource = reader;
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
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.
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 !