Fastest way to populate datatable from database using IDataReader

Alright folks! Who knows the panic of using a SqlDataAdapter class to fill the Datatable. If you require only a single table to be filled from the database, then this post is to show you there's an alternate way to get a DataTable. That alternate way is returning a DataReader from the command object after executing the command.

Well I always thought of a DataReader when it comes to performance but I can't forget to keep the data in memory and also keep in mind that the DataReader works in connected mode. So I came up with a mixed approach; reading the fetched the data from a DataReader and then put the data into a DataTable. The next thing was to make it reusable so finally I did some juggling with the code and came up with the code below that can convert your DataReader object to a DataTable. 

I always use IDataReader to receive the object returned from the Command.ExecuteReader() method.

You can get the schema of the Table a by method that is available through the IDataReader interface GetSchemaTable(). I'll use this method to get the schema of the table that IDataReader is holding. Then I create a new DataTable using the data we received from the schema and start populating it by using the data in the DataReader. See the below snippet; I've put all the ideas together and created this method GetDataTableFromDataReader().

public DataTable GetDataTableFromDataReader(IDataReader dataReader)
{
    DataTable schemaTable = dataReader.GetSchemaTable();
    DataTable resultTable = new DataTable();

    foreach (DataRow dataRow in schemaTable.Rows)
    {
        DataColumn dataColumn = new DataColumn();
        dataColumn.ColumnName = dataRow["ColumnName"].ToString();
        dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
        dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
        dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
        dataColumn.Unique = (bool)dataRow["IsUnique"];

        resultTable.Columns.Add(dataColumn);
    }

    while (dataReader.Read())
    {
        DataRow dataRow = resultTable.NewRow();
        for (int i = 0; i < resultTable.Columns.Count - 1; i++)
        {
            dataRow[i] = dataReader[i];
        }
        resultTable.Rows.Add(dataRow);
    }

    return resultTable;
}

Now you can use this method to fetch the data in the DataReader and then get the DataTable from it. Its really fast I'm telling you! Yeah people can ask me about the complexity of creating another table's schema and looping through the records, but this method is still worth using  over the SqlDataAdapter class.

Let me go though a small demo over this. Let's create a DB connection and use this method and the Adapter method to get the Performance records.

public class DAL
{
    static SqlConnection connection;
    public DAL()
    {
        connection = new SqlConnection();
        connection.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["myConnectionString"];
    }

    public DataTable GetDataTableByAdapter(string query)
    {
        connection.Open();
        DateTime start = DateTime.Now;
        DataTable dt = new DataTable();
        new SqlDataAdapter(query, connection).Fill(dt);
        TimeSpan ts = DateTime.Now.Subtract(start);
        System.Diagnostics.Trace.Write("Time Elapsed in Adapter: "+ts.TotalMilliseconds);
        connection.Close();
        return dt;
    }

    public DataTable GetDataTableFast(string query)
    {
        connection.Open();
        DateTime start = DateTime.Now;
        IDataReader rdr = new SqlCommand(query, connection).ExecuteReader();
        DataTable resultTable = GetDataTableFromDataReader(rdr);
        TimeSpan ts = DateTime.Now.Subtract(start);
        System.Diagnostics.Trace.Write("Time Elapsed in Custom : " + ts.TotalMilliseconds);
        connection.Close();
        return resultTable;
    }
}

Now lets call this method in a console application and then we'll go through the Trace where the code will be writing the elapsed time in individual operation.

static void Main(string[] args)
        {
            DAL dal = Singleton<DAL>.Instance;
            string query = "Select * from Production.Product";
            
            DataTable dt1 = dal.GetDataTableFast(query);
            DataTable dt = dal.GetDataTableByAdapter(query);
            System.Console.Read();
        }

Now Run the code and let's have a look at figures. I ran this program 2 times just to make sure counts are correct

First Run - Time Elapsed in Adapter: 93.0053 Time Elapsed in Custom : 13.0008

Second Run - Time Elapsed in Adapter: 129.0074 Time Elapsed in Custom: 14.0008

So I'm sure you must be impressed with the performance here. Moreover you can make it faster by using the Parallel.For() and Parallel.Foreach() loop if you are working on .NET 4.0 and have a multicore processor.

Get Smarter. Get Faster!! Nobody wants to wait.