Fastest way to populate datatable using ADO.Net classes

Alright folks who knows the panic of using SqlDataAdapter class to fill the Datatable in case if you required only a single table to fill from database. This post is to show you there’s an alternate way to get a DataTable and that’s alternate way is returning DataReader from the command object after executing the command.

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

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

you can get the schema of the Table 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 creating a new DataTable using the data we received from the schema and then start populating it by using the data in the DataReader. See 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 SqlDataAdapter class instead.

Let me go though a small demo over this. Let 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 lets 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 Custom : 129.0074 Time Elapsed in Adapter: 14.0008

So I’m sure you must be impressed with performance here. Moreover you can make it more fast 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.