Which would be quicker.
1) Looping a datareader and creating a custom rows and columns based populated datatable
2) Or creating a dataAdapter object and just (.Fill)ing a datatable.
Does the performance of a datareader still hold true upon dynamic creation of a datatable?
The DataAdapter uses a DataReader under the hood so your experience would likely be the same.
The benefit of the DataAdapter is you cut out a lot of code that would need maintenance.
This debate is a bit of a religious issue so definitely look around and decide what works best for your situation:
I cant speak to filling a datatable per se but using a datareader is the most efficient reading method.
Your option #1 would be slower. However, there's a better way to convert a datareader to a datatable than adding custom rows by hand:
DataTable dt = new DataTable();
using (SqlConnection conn = GetOpenSqlConnection())
using (SqlCommand cmd = new SqlCommand("SQL Query here", conn)
using (IDataReader rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
}
I can't comment on the difference between this and using .Fill().
It's nice to have DataReader when you need for example show progress of loading data. In DataSet you can't do something in a middle of loading data.
On the other hand DataSet is all-in-one object. So DataSet is much slower. DataReader can give you additional boost in places in your code where data operation is very slow. In these places change it from DataSet to DataReader. DataReader also takes less space in memory.
Oh course it takes more time to code good DataReader, but it's worth it. For example when you play with images or music taken from database.
The datareader is faster. And if you are using 2.0+ you probablt don't even have to use a datatable. You can use a generic list of your object.
Assuming you actually want all the data coming back from the database, the time taken at the database and on the network is almost certain to dwarf the time taken in-process to populate data structures in memory.
Yes, in some cases you might get a small saving by using DataReader - and in particular if you want to stream the data it may be useful - but if you do actually need it all, I'd stick with the simplest code. If you believe that the DataSet population is causing a significant performance problem, profile it and then try to improve it.
As with many questions like this the answer is: depends.
If you don't know the structure of your data up front and are creating TableAdapters on the fly, then the dynamic DataTable would be more efficient. There is a good deal of code generation involved in creating a TableAdapter.
However, if you know the structure of your data up front then the question becomes, How much functionality do I need?
If you need a full CRUD implementation then there are some efficiencies gained by using a TableAdapter rather than writing all that CRUD code yourself. Also, the TableAdapter implementation is OK (not great). If you need something more efficient then you may be better off using nHibernate or some other ORM.
If you don't need a full CRUD implementation (i.e., this is a read-only solution) and know your data structure up front, then you'll have to test the efficiency of a TableAdapter read-only implementation against a dynamically generated DataTable. If I were a betting man I'd put my money on the TableAdapter implementation since you bind data once and read it multiple times.
Thanks guys. That really helps. My colleague has coded a Data Access layer with a reader to datatable method - and as mentioned above it is both hard to maintain, and harder to read. Might try and convice to go with the dataAdapter, or at least test code execution times.