ASP.NET ExecuteReader
The ExecuteReader() method in the SqlCommand Object is used to send SQL statements to the Connection Object and retrieve query results from the data source. When this method is executed, it creates an instance of the SqlDataReader Object, specific to the SqlClient namespace.
SqlDataReader Object
The SqlDataReader Object is designed to provide a stream-based, forward-only, and read-only retrieval of query results from the data source. It allows for efficient and sequential access to the data without the ability to modify or update the data contained within it.
VB.Net
Dim reader As SqlDataReader = cmd.ExecuteReader
C#
SqlDataReader reader = cmd.ExecuteReader();
Once the SqlDataReader Object is created, it allows for traversing through the result set row by row, accessing the columns and values of each row. This provides a way to process and work with the query results in a read-only manner.
The SqlDataReader Object is particularly useful when dealing with large result sets, as it allows for efficient retrieval and processing of data on a row-by-row basis without the need to load the entire result set into memory.
The following ASP.NET program execute sql statement and using ExecuteReader mthod.
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 au_lname,au_fname from authors";
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));
}
connection.Close();
}
catch (Exception ex)
{
Label1.Text = "Error in ExecuteReader " + 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 au_lname,au_fname from authors"
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
connection.Close()
Catch ex As Exception
Label1.Text = "Error in ExecuteReader " & ex.ToString
End Try
End Sub
End Class
Conclusion
The ExecuteReader() method in the SqlCommand Object is responsible for executing SQL statements, retrieving query results, and creating a SqlDataReader Object. The SqlDataReader Object enables stream-based, forward-only, and read-only access to the query results. It cannot be directly instantiated but is created by calling the ExecuteReader() method of a Command Object, such as a SqlCommand Object.