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().
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.
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.
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.