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.
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.
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.