Populate DataTable using DataReader in VB.NET

From this post, we will be talking about the way of populating dataTables using the DataReader in VB.NET. In this example, we’ll be using the SQLDataReader which is available in the SQLClient library.

What is a DataReader?

DataReader is an object that is used to fetch the data from any data source in a high-performance manner. It always stays connected with the data source, and it is highly optimized. In the .NET framework, there are 3 data providers available.

• SQLDataReader
• ODBCDataReader
• OLEDbDataReader

Create a new Project

To begin with, create a new Visual Studio Windows Forms Application and name it with your project name. Next, we have to create our database to load the data to the dataTable. For that, you can use any database instance you like. As mentioned earlier, we are using the SQLDataReader for this example. To make sure that synchronize with the database, we will use SQL Server instance to store our data.

Create the database

After creating the project, I have created a new table in my Company database called Customers which has several columns of data. For the demonstration purposes, I have added 15 records to it which contains the personal information of the clients that visit our company.

Import Namespaces

Next, we need to create our method to populate the dataTable. Before that, we need to import three libraries to our project.

Imports System.Data
Imports System.Data.SqlClient

SQLClient library act as the data provider in our application. Now, let’s create our method to populate the dataTable from the DataReader.

Protected Sub DataReaderToDataTable()
     Dim connection As String = "Your connection string"

     Using conn As New SqlConnection(connection)
     Using command As New SqlCommand("SELECT customer_num,  
                                     fname,lname,company,address1,address2,city,state,
                                     zipcode,phone FROM Customer") 'querying the database

     command.Connection = conn
     conn.Open()

    'using the SQLDataReader  to excute the command
     Using dataReader As SqlDataReader = command.ExecuteReader()     

    'new DataTable is created with customer information
     Dim customerTable As New DataTable("Customer")      
     
     customerTable.Load(dataReader) 'Loading DataReader into the DataTable
     End Using
     conn.Close()
     End Using
    End Using
End Sub

In here, we use SQLDataReader to execute the command we created. Depending on the data provider we use, the way of executing the command differ. CustomerTable is the name of our dataTable instance. Hence, it is used to store the data from the Customer table. Finally, we need to load all the data into the dataTable using the DataReader Load() function. To execute this method, we can call this method inside the FormLoad method or even we can code the method body inside the FormLoad method with creating a new method. But it is always recommended to create new methods for each activity which is practical programming ethics.

Now you can load a DataGrid with the Reader we just created as shown below.

Result

Related Articles