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.