Convert a DataReader to DataTable in ASP.NET
A DataReader is a read-only forward-only way of reading data. It is quiet fast when compared to fetching data using a DataSet. Infact internally, a DataSet uses a DataReader to populate itself. However at times, we need the best of both worlds. A dataset/datatable is extremely handy when it comes to binding it to a control like a GridView. So to make use of both the DataReader and DataTable in the same solution, we can fetch the data using a DataReader and then convert it to a DataTable and bind it to the control. In this article, we will explore how to do the conversion using two approaches; the first one, a direct method by using the DataTable.Load() and the second one, by manually converting a DataReader to a DataTable.
Step 1: Create a new ASP.NET application. Drag and drop two GridView controls to the page. We will fetch data from a DataReader into a DataTable and bind the DataTable to the GridView’s. Before moving ahead, add a web.config file to the project and add the following element.
<connectionStrings>
<addname="NorthwindConn"connectionString="Data Source=(local); Initial Catalog=Northwind; Integrated Security=true;"/>
</connectionStrings>
Step 2: Let us first see how to convert a DataReader to a DataTable using the easy way out. DataTable in ADO.NET 2.0 contains a Load() method which enables the DataTable to be filled using a IDataReader. This method is quiet handy when you need to quickly create a DataTable, without using a DataAdapter!! Let us see how.
C#
private void ConvertDateReadertoTableUsingLoad()
{
SqlConnection conn = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
conn = new SqlConnection(connString);
string query = "SELECT * FROM Customers";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable();
dt.Load(dr);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (SqlException ex)
{
// handle error
}
catch (Exception ex)
{
// handle error
}
finally
{
conn.Close();
}
}
VB.NET
Private Sub ConvertDateReadertoTableUsingLoad()
Dim conn As SqlConnection = Nothing
Try
Dim connString As String = ConfigurationManager.ConnectionStrings("NorthwindConn").ConnectionString
conn = New SqlConnection(connString)
Dim query As String = "SELECT * FROM Customers"
Dim cmd As SqlCommand = New SqlCommand(query, conn)
conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim dt As DataTable = New DataTable()
dt.Load(dr)
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As SqlException
' handle error
Catch ex As Exception
' handle error
Finally
conn.Close()
End Try
End Sub
Note 1: If there is some existing data in the DataTable, the data coming from the DataReader is merged with the existing rows.
Note 2: If you need a DataReader back from a DataTable, use the DataTable.CreateDataReader() method.
Step 3: The method shown in the Step 2 was the easy way out. However, if for some reason(if you are not using ADO.NET 2.0), you would want to convert a DataReader to a DataTable ‘manually’, here’s the code. In the code below, a DataTable schema is created first using the GetSchemaTable() method of DataReader. The GetSchemaTable() returns a DataTable describing the column metadata of the IDataReader. Once done, we loop through the rows of the schema table and create a DataColumn object and set its properties. This DataColumn is also added to the List<> collection. We then read rows from the DataReader and populate the DataTable.
C#
private void ConvertDataReaderToTableManually()
{
SqlConnection conn = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
conn = new SqlConnection(connString);
string query = "SELECT * FROM Customers";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dtSchema = dr.GetSchemaTable();
DataTable dt = new DataTable();
// You can also use an ArrayList instead of List<>
List<DataColumn> listCols = new List<DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = System.Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dt.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (dr.Read())
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = dr[i];
}
dt.Rows.Add(dataRow);
}
GridView2.DataSource = dt;
GridView2.DataBind();
}
catch (SqlException ex)
{
// handle error
}
catch (Exception ex)
{
// handle error
}
finally
{
conn.Close();
}
}
VB.NET
Private Sub ConvertDataReaderToTableManually()
Dim conn As SqlConnection = Nothing
Try
Dim connString As String = ConfigurationManager.ConnectionStrings("NorthwindConn").ConnectionString
conn = New SqlConnection(connString)
Dim query As String = "SELECT * FROM Customers"
Dim cmd As SqlCommand = New SqlCommand(query, conn)
conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim dtSchema As DataTable = dr.GetSchemaTable()
Dim dt As DataTable = New DataTable()
' You can also use an ArrayList instead of List<>
Dim listCols As List(Of DataColumn) = New List(Of DataColumn)()
If Not dtSchema Is Nothing Then
For Each drow As DataRow In dtSchema.Rows
Dim columnName As String = System.Convert.ToString(drow("ColumnName"))
Dim column As DataColumn = New DataColumn(columnName, CType(drow("DataType"), Type))
column.Unique = CBool(drow("IsUnique"))
column.AllowDBNull = CBool(drow("AllowDBNull"))
column.AutoIncrement = CBool(drow("IsAutoIncrement"))
listCols.Add(column)
dt.Columns.Add(column)
Next drow
End If
' Read rows from DataReader and populate the DataTable
Do While dr.Read()
Dim dataRow As DataRow = dt.NewRow()
For i As Integer = 0 To listCols.Count - 1
dataRow((CType(listCols(i), DataColumn))) = dr(i)
Next i
dt.Rows.Add(dataRow)
Loop
GridView2.DataSource = dt
GridView2.DataBind()
Catch ex As SqlException
' handle error
Catch ex As Exception
' handle error
Finally
conn.Close()
End Try
Step 4: Call the two methods on the PageLoad()
C#
protected void Page_Load(object sender, EventArgs e)
{
ConvertDateReadertoTableUsingLoad();
ConvertDataReaderToTableManually();
}
VB.NET
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
ConvertDateReadertoTableUsingLoad()
ConvertDataReaderToTableManually()
End Sub
The DataTable.Load(IDataReader) is extremely handy when you want to quickly bind the data coming from a DataReader to a control like the GridView. The DataTable.Load() method has three overloads. We have explored one of them. I would encourage you to explore the other two over here.
I hope this article was useful and I thank you for viewing it.