ADO.NET data table vs. data reader

Question

The DataReader is more efficient than a DataTable if you only need to show data but not manipulate it. However, to get a DataReader from the data access layer should I leave the connection object open? I think this is also a very big efficiency problem. So is there another approach to this in order to take full advantage of the DataReader?

Answer 1

Yes, the data reader is definitely the most efficient - but you do not want to keep a connection open for a long period of time!

• use the DataReader to read your data into an entity object; open the connection, read the data, close the connection
• do whatever you need to do with your business object
• store the changes back, e.g. by using an ad-hoc SQL query, a stored procedure, or whatever else you want; again: open the connection, write back the changes, close the connection

This is probably the most efficient you can get - it's a bit of work, some boring code, and all, but it's about as fast as it can be.

If you're more interested in developer productivity than raw speed, why not use some kind of an ORM to do all this boring, annoying mapping back and forth? Saves you a lot of coding and messy stuff to maintain!