In this article I will explain how to convert / copy DataReader (SqlDataReader) data to DataTable and DataSet using C# and VB.Net.
The DataTable and DataSet will be populated with records from the DataReader using Load method of the DataTable.
Database
I have made use of the following table Customers with the schema as follows.
![Convert / Copy DataReader to DataTable and DataSet C# and VB.Net]()
I have already inserted few records in the table.
![Convert / Copy DataReader to DataTable and DataSet C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Converting DataReader to DataTable using C# and VB.Net
The records from the Customers table are fetched using SqlDataReader. Finally a new DataTable is created and the DataReader records are loaded into the DataTable using its Load method.
C#
protected void ConvertDataReaderToDataTable(object sender, EventArgs e)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers"))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
//Create a new DataTable.
DataTable dtCustomers = new DataTable("Customers");
//Load DataReader into the DataTable.
dtCustomers.Load(sdr);
}
con.Close();
}
}
}
VB.Net
Protected Sub ConvertDataReaderToDataTable(sender As Object, e As EventArgs)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT CustomerId, Name, Country FROM Customers")
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
'Create a new DataTable.
Dim dtCustomers As New DataTable("Customers")
'Load DataReader into the DataTable.
dtCustomers.Load(sdr)
End Using
con.Close()
End Using
End Using
End Sub
The following screenshot displays the DataTable with records copied from the DataReader.
![Convert / Copy DataReader to DataTable and DataSet C# and VB.Net]()
Converting DataReader to DataSet using C# and VB.Net
The records from the Customers table are fetched using SqlDataReader. Then a new DataSet is created and a DataTable is added to the DataSet.
Finally the DataReader records are loaded into the DataTable of the DataSet using its Load method.
C#
protected void ConvertDataReaderToDataSet(object sender, EventArgs e)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers"))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
//Create a new DataSet.
DataSet dsCustomers = new DataSet();
dsCustomers.Tables.Add("Customers");
//Load DataReader into the DataTable.
dsCustomers.Tables[0].Load(sdr);
}
con.Close();
}
}
}
VB.Net
Protected Sub ConvertDataReaderToDataSet(sender As Object, e As EventArgs)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT CustomerId, Name, Country FROM Customers")
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
'Create a new DataSet.
Dim dsCustomers As New DataSet()
dsCustomers.Tables.Add("Customers")
'Load DataReader into the DataTable.
dsCustomers.Tables(0).Load(sdr)
End Using
con.Close()
End Using
End Using
End Sub
The following screenshot displays the DataTable of the DataSet with records copied from the DataReader.
![Convert / Copy DataReader to DataTable and DataSet C# and VB.Net]()
Downloads
Download Code
