Monday, May 28, 2007

Fast DataTable loading from IDataReader

I was using the Load method of DataTable to load data from IDataReader the other day, and while profiling the application I found out that this is my program bottleneck.

I've just wrote a quick & dirty function which does the same task much faster:

private static DataTable ReaderToDataTable(IDataReader reader)
{
int fieldCount = reader.FieldCount;
DataTable result = new DataTable("Query Result");
for (int i = 0; i < reader.FieldCount; i++)
{
DataColumn column = result.Columns.Add(reader.GetName(i));
column.DataType = reader.GetFieldType(i);
}

object[] row = new object[fieldCount];
while (reader.Read())
{
reader.GetValues(row);
result.Rows.Add(row);
}

return result;
}

2 comments:

K. B. McHugh said...

Sagi, loading a DataTable from an IDataReader list is something I am trying to learn to do; this snippet looks like an answer to a probelm I've encountered, but I don't understand how to use this sample.

Could you give an example of how it is used, please?

Sagi said...

Loading to a DataTable can be done without this function (using IDataAdapter), however, this function is faster.

A simple way to get IDataReader (I'm sketching here, didn't test this):
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select getdate()";
IDataReader dr = cmd.ExecuteReader();

Don't forget to close everything (maybe with 'using')