In this article I will explain with an example, how to return list from SqlDataReader in ASP.Net with C# and VB.Net.
 
 
 

Database

I have made use of the following table Customers with the schema as follow.
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.
            Download SQL file
 
 

HTML Markup

The HTML Markup consists of following control:
GridView – For displaying data.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false"> 
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
 
 

Namespaces

You will need to import the follow namespaces.
C#
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
 
 

Property Class

The Customer class consists of following 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
 
 

Binding (Populating) GridView using Property Class in ASP.Net

Inside the Page_Load event handler, the GetCustomers method (explained later) is called which returns the Generic List collection of Customer class (Property class) that is assigned to the DataSource property of the GridView.
 

GetCustomers

Inside this method, first the connection is read from Web.Config file.
Note: For more details on how to read connection string from Web.Config file, please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Then, an object of SqlCommand class is created and the SELECT query is passed to it as parameter and the Generic List collection of Customer class (Property class) is created.
The connection is opened and an object of SqlDataReader is created and records are fetched from the database using the ExecuteReader function is executed.
Note: For more details on how SqlDataReader, please refer my article SqlDataReader Tutorial with example in C# and VB.Net.
 
Finally, the fetched records are stored into a Generic List collection of Customer class (Property class) which is returned.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        gvCustomers.DataSource = this.GetCustomers();
        gvCustomers.DataBind();
    }
}
 
private List<Customer> GetCustomers()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string sql = "SELECT CustomerId, Name, Country FROM Customers";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            List<Customer> customers = new List<Customer>();
            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
        gvCustomers.DataSource = Me.GetCustomers
        gvCustomers.DataBind()
    End If
End Sub
 
Private Function GetCustomers() As List(Of Customer)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim sql As String = "SELECT CustomerId, Name, Country FROM Customers"
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(sql, con)
            Dim customers As New List(Of Customer)
            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

View Demo
 
 

Downloads

Download Code