In this article I will explain with an example, how to return List from SqlDataReader in ASP.Net using C# and VB.Net.
The records from the Database will be read using SqlDataReader and then the data is copied into the Generic List collection of the Class objects, which is later used to populate the GridView in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
![Return List from SqlDataReader in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Return List from SqlDataReader in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The following HTML Markup consists of a GridView control which will be populated using a Generic List collection of the Property Class objects.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" ItemStyle-Width="80" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="120" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100" />
</Columns>
</asp:GridView>
Namespaces
You will need to the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Collections.Generic
Property Class
The following Class consists of three properties.
C#
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
VB.Net
Public Class Customer
Public Property CustomerId As Integer
Public Property Name As String
Public Property Country As String
End Class
Returning List from SqlDataReader in ASP.Net
Inside the GetCustomers method, the records from the Customers Table are fetched using SqlDataReader and then using WHILE Loop, the records are copied into the Generic List collection of Customer class objects.
Finally, the Generic List collection of Customer class objects is returned.
Inside the Page Load event handler, the GetCustomers method is called and is used to populate the GridView control.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
GridView1.DataSource = this.GetCustomers();
GridView1.DataBind();
}
}
private List<Customer> GetCustomers()
{
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers", con))
{
List<Customer> customers = new List<Customer>();
cmd.CommandType = CommandType.Text;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new Customer
{
CustomerId = Convert.ToInt32(sdr["CustomerId"]),
Name = sdr["Name"].ToString(),
Country = sdr["Country"].ToString()
});
}
}
con.Close();
return customers;
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
GridView1.DataSource = Me.GetCustomers
GridView1.DataBind()
End If
End Sub
Private Function GetCustomers() As List(Of Customer)
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT CustomerId, Name, Country FROM Customers", con)
Dim customers As New List(Of Customer)
cmd.CommandType = CommandType.Text
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(New Customer With {
.CustomerId = Convert.ToInt32(sdr("CustomerId")),
.Name = sdr("Name").ToString(),
.Country = sdr("Country").ToString()
})
End While
End Using
con.Close()
Return customers
End Using
End Using
End Function
Screenshot
![Return List from SqlDataReader in ASP.Net using C# and VB.Net]()
Demo
Downloads
