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.
VB.Net
Dim reader As SqlDataReader = cmd.ExecuteReader
C#
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.
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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
Conclusion
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.