In this article I will explain with an example, how to use SqlDataReader in Windows Forms (WinForms) Application 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.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

Form Design

The following Form consists of:
DataGridView – For displaying data.
SqlDataReader Tutorial with example in C# and VB.Net
 
 

Namespaces

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

Property Class

The class consists of following properties.
C#
public class Customer
{
    public string CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}
 
VB.Net
Public Class Customer
    Public Property CustomerId As String
    Public Property Name As String
    Public Property Country As String
End Class
 
 

Implementing SqlDataReader in C# and VB.Net

Inside the Form Load event handler, first the connection string is read from App.Config file.
Note: For more details on how to read connection string from App.Config file please refer my article .Net 4.5: Read (Get) Connection String from App.Config file 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 is created.
The connection is opened and an object of SqlDataReader is created and the ExecuteReader function is executed.
Then, a WHILE loop is executed and fetched records are added to the Generic List collection of Customer class object.
Finally, the Generic List collection of Customer class object is assigned to the DataSource property of DataGridView and the DataGridView is populated.
Note: Here Generic List collection is used, but SqlDataReader can be directly used to populate DataSet or DataTable.
 
C#
private void Form1_Load(object sender, EventArgs e)
{
    string sql = "SELECT CustomerId, ContactName, Country FROM Customers";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    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 = sdr["CustomerId"].ToString(),
                        Name = sdr["ContactName"].ToString(),
                        Country = sdr["Country"].ToString()
                    });
                }
            }
            con.Close();
            dgvCustomers.DataSource = customers;
        }
    }
}
 
VB.Net
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    Dim sql As String = "SELECT CustomerId, ContactName, Country FROM Customers"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(sql, con)
            Dim customers As List(Of Customer) = New List(Of Customer)()
            con.Open()
            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
            con.Close()
            dgvCustomers.DataSource = customers
        End Using
    End Using
End Sub
 
 

Screenshot

SqlDataReader Tutorial with example in C# and VB.Net
 
 

Downloads

Download Code