Convert a DataReader to DataTable in ASP.NET
Posted by: Suprotim Agarwal ,
on 4/18/2008,
in
Category ASP.NET
Abstract: In this article, we will explore how to convert a datareader to a DataTable using two approaches. We will first see how to do the conversion using the Load() method of the DataTable. Alternatively, we will also explore converting the DataReader to DataTable manually.
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
End Sub
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.
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigious Microsoft MVP award for Fifteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.
Get in touch with him on Twitter @suprotimagarwal or at LinkedIn