ASP.NET DataReader

The DataReader Object in ADO.NET provides a stream-based, forward-only, and read-only retrieval of query results from data sources. It is specifically designed for efficiently accessing and processing large result sets without the need to load the entire set into memory. It is important to note that the DataReader Object is read-only and does not support data modification or updating operations.

DataReader Object

To create a DataReader Object, you need to first instantiate a Command Object, such as a SqlCommand Object, and then call the ExecuteReader() method of the Command Object. This method executes the SQL command or query and returns a DataReader Object containing the retrieved rows from the data source.

Dim reader As SqlDataReader = cmd.ExecuteReader
SqlDataReader reader = cmd.ExecuteReader();

Once the DataReader Object is created, it should be open and positioned prior to the first record in order to start reading from it. The Read() method of the DataReader Object is used to sequentially read the rows from the DataReader. Each call to the Read() method moves the DataReader to the next valid row, if one exists, allowing you to access the columns and values of that row.

The following ASP.NET program execute sql statement and read the data using SqlDataReader.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns=""> <head id="Head1" runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" /> <br /> <asp:ListBox ID="ListBox1" runat="server"></asp:ListBox> <br /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </div> </form> </body> </html>
Full Source | C#
using System; using System.Data ; using System.Data.SqlClient ; using System.Configuration; public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString(); SqlConnection connection = new SqlConnection(connectionString); string sql = "select pub_id,pub_name from publishers"; try { connection.Open(); SqlCommand cmd = new SqlCommand(sql, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ListBox1.Items.Add(reader.GetValue(0) + " - " + reader.GetValue(1)); } reader.Close(); connection.Close(); } catch (Exception ex) { Label1.Text = "Error in SqlDataReader " + ex.ToString(); } } }
Full Source | VB.NET
Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connectionString As String Dim connection As SqlConnection connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString connection = New SqlConnection(connectionString) Dim sql As String = "select pub_id,pub_name from publishers" Try connection.Open() Dim cmd As New SqlCommand(sql, connection) Dim reader As SqlDataReader = cmd.ExecuteReader While reader.Read() ListBox1.Items.Add(reader.Item(0) & " - " & reader.Item(1)) End While reader.Close() connection.Close() Catch ex As Exception Label1.Text = "Error in SqlDataReader " & ex.ToString End Try End Sub End Class


The DataReader Object in ADO.NET offers a stream-based, forward-only, and read-only approach to retrieve query results from data sources. It is created by calling the ExecuteReader() method of a Command Object, and the Read() method is used to sequentially read rows. Properly managing the lifecycle of the DataReader Object, including opening, positioning, reading, and closing it, is essential for efficient data access and resource utilization.