In this article I will explain with an example, how to loop through the records in DataReader using C# and VB.Net.
What is SqlDataReader?
The SqlDataReader class is used to read data from the SQL Server database.
The SqlDataReader uses connection-oriented architecture i.e. it requires an open connection to the Data Source while reading the data.
The data is available as long as the connection with the database exists. You need to open and close the connection explicitly.
It reads data in the forward-only direction. Once a record is read, then the next record will be read.
There is no way to go back and read the previous record.
The SqlDataReader class has some properties.
1. Connection - Gets the SqlConnection associated with the SqlDataReader.
2. HasRows - Gets a value that indicates whether the SqlDataReader contains one or more rows.
3. IsClosed - Retrieves a Boolean value that indicates whether the specified SqlDataReader instance has been closed.
4. RecordsAffected - Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.
5. FieldCount - Gets the number of columns in the current row.
Note: For more details about SqlDataReader, please refer MSDN.
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Form Design
You need to add a DataGridView control to the Windows Form.
How to Loop Through DataReader in C# and VB.Net
You will need to import the following namespaces.
using System.Collections.Generic;
using System.Data.SqlClient;
Imports System.Collections.Generic
Imports System.Data.SqlClient
Property Class
The following Class consists of three properties.
public class Customer
    public string CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
Public Class Customer
    Public Property CustomerId As String
    Public Property Name As String
    Public Property Country As String
End Class
Populate DataTable using SqlDataReader in C# and VB.Net
Inside the Form Load event handler, the records are fetched from the Customers Table 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 used to populate the DataGridView.
Note: Here List Collection is used, but SqlDataReader can be directly used to populate or DataSet or DataTable can be used.
private void Form1_Load(object sender, EventArgs e)
    string constring = @"Data Source=.\SQL2019;Initial Catalog=Northwind;User id=sa;password=pass@123";
    using (SqlConnection con = new SqlConnection(constring))
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con))
            List<Customer> customers = new List<Customer>();
            using (SqlDataReader sdr = cmd.ExecuteReader())
                while (sdr.Read())
                    customers.Add(new Customer
                        CustomerId = sdr["CustomerId"].ToString(),
                        Name = sdr["ContactName"].ToString(),
                        Country = sdr["Country"].ToString()
            dataGridView1.DataSource = customers;
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim constring As String = "Data Source=.\SQL2019;Initial Catalog=Northwind;User id=sa;password=pass@123"
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con)
            Dim customers As List(Of Customer) = New List(Of Customer)()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    customers.Add(New Customer With {
                        .CustomerId = sdr("CustomerId").ToString(),
                        .Name = sdr("ContactName").ToString(),
                        .Country = sdr("Country").ToString()
                End While
            End Using
            dataGridView1.DataSource = customers
        End Using
    End Using
End Sub
How to Loop Through DataReader in C# and VB.Net