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 !