In this article I will explain with an example, how to use SqlDataReader Read command in 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.
Form Design
You need to add a DataGridView control to the Windows Form.
![SqlDataReader Read Command Example in C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.Collections.Generic;
using System.Data.SqlClient;
VB.Net
Imports System.Collections.Generic
Imports System.Data.SqlClient
Property Class
The following Class consists of three 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
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.
C#
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>();
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();
dataGridView1.DataSource = customers;
}
}
}
VB.Net
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)()
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()
dataGridView1.DataSource = customers
End Using
End Using
End Sub
Screenshot
![SqlDataReader Read Command Example in C# and VB.Net]()
Downloads
