Important alert: (current site time 8/28/2014 6:54:34 PM EDT)
 

VB icon

Fill DataTable from DataReader

Email
Submitted on: 11/24/2003 11:20:03 AM
By: Scott Stamps 
Level: Intermediate
User Rating: By 1 Users
Compatibility: VB.NET, ASP.NET
Views: 20576
 
     Because DataAdapters / DataSets carry excessive overhead, MS recommends using the DataReader object whenever possible for optimal perfomance. The down side of this is that filling a DataTable from a DataReader is much more time consuming to code than using the DataAdapter.Fill command. Because of this, and the fact that I couldn't find any examples of filling a DataTable directly from a DataReader, I wrote this function.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
				
//**************************************
// Name: Fill DataTable from DataReader
// Description:Because DataAdapters / DataSets carry excessive overhead, MS recommends using the DataReader object whenever possible for optimal perfomance. 
The down side of this is that filling a DataTable from a DataReader is much more time consuming to code than using the DataAdapter.Fill command.
Because of this, and the fact that I couldn't find any examples of filling a DataTable directly from a DataReader, I wrote this function.
// By: Scott Stamps
//
// Inputs:DataReader
//
// Returns:DataTable
//
// Assumes:Simply pass the function a DataReader object and it will return a DataTable object.
//
// Side Effects:None Known
//
//This code is copyrighted and has// limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1818&lngWId=10//for details.//**************************************

Public Function FillDataTable(ByRef dataReader As IDataReader)
Dim i As Integer
Dim intNumCols As Integer
Dim dataTable As New DataTable
Dim dtCols As New DataTable
Dim drow As DataRow
'
'Insert datareader schema into datatable(dtCols)
dtCols = dataReader.GetSchemaTable()
intNumCols = dtCols.Rows.Count - 1
'
'Loop thru dtCols, inserting columns into dataTable
For i = 0 To intNumCols
 dataTable.Columns.Add(dtCols.Rows(i)("ColumnName"), dtCols.Rows(i)("DataType"))
Next
'
 'Iterate thru datareader, adding rows to datatable
 While dataReader.Read
drow = dataTable.NewRow
'Iterate thru columns datatable
For i = 0 To intNumCols
 drow(i) = dataReader(dtCols.Rows(i)("ColumnName"))
Next
dataTable.Rows.Add(drow)
 End While
'
dataReader.Close()
dtCols.Rows.Clear()
'
Return dataTable
'
 End Function


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

2/3/2004 11:40:16 AM

C# Version:
private DataTable LlenarDataTable(SqlDataReader dtrDataReader)
{
DataTable dtDataTable = new DataTable();
DataRow drow;

DataTable dtCols = dtrDataReader.GetSchemaTable();
int intNumCols = dtCols.Rows.Count - 1;

for(int i = 0; i <= intNumCols;i++)
{
dtDataTable.Columns.Add(dtCols.Rows[i][
(If this comment was disrespectful, please report it.)

 
2/3/2004 11:41:54 AM

C# Version:
private DataTable LlenarDataTable(SqlDataReader dtrDataReader)
{
DataTable dtDataTable = new DataTable();
DataRow drow;

DataTable dtCols = dtrDataReader.GetSchemaTable();
int intNumCols = dtCols.Rows.Count - 1;

for(int i = 0; i <= intNumCols;i++)
{
dtDataTable.Columns.Add(dtCols.Rows[i]["ColumnName"].ToString(), dtCols.Rows[i]["DataType"].GetType());
}

while (dtrDataReader.Read())
{
drow = dtDataTable.NewRow();

for(int i = 0; i <= intNumCols;i++)
{
drow[i] = dtrDataReader[i];
}
dtDataTable.Rows.Add(drow);
}

return dtDataTable;
}
(If this comment was disrespectful, please report it.)

 
8/24/2004 8:55:35 AM

The dataadapter's fill method supports the filling of a dataset from a datareader and a datatable from a datareader.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlr fsystemdatacommondbdataadapterclassfilltopic4.asp

(If this comment was disrespectful, please report it.)

 
8/24/2004 11:00:51 AMScott Stamps

Yes, but the point is that data adapters / data sets carry excessive overhead for a simple, forward, read-only cursor. Of course the data adapter's .fill method would be easier but, in this case, it's less efficient.
(If this comment was disrespectful, please report it.)

 
8/26/2004 6:31:18 AM

Have you tested the performance?
Go to:
http://www.eggheadcafe.com/articles/20030205.asp

The author has compared the method similar to yours to the fill method to an "enhanced" fill method. There is no real performance improvement between the regular fill and your approach.
(If this comment was disrespectful, please report it.)

 
8/26/2004 10:41:47 AMScott Stamps

Interesting article. I used to use the data set / data adapter all over the place until I attended DevConn last October. All of the MS guys preached about the DataReader being the fastest, best performing method of reading in data. After reading the article on eggheadcafe, I think MS may need to rethink billing the data reader as the end-all performance king. Now I will test for myself. Thanks for the input.
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.