ADO.NET data table vs. data reader

Question

Datareader is more efficient that datatable if you need only show some data but not manipulate it. However , to get a datareader from dataaccess layer I should left the connection object open.I think this is also very big efficiency problem. Is there any approach to this to take the full advantage of datareader?

Answer 1

If you want to completely abstract the connections and ceremony of ADO.NET, the DataReader is a small challenge. I really don't like my data tool having an open connection on the loose, hoping the DataReader is disposed (assuming you used option CommandBehavior.CloseConnection). Also, when using a lot of DataReaders, it's difficult to pool connections, since you can't do anything with the connection until the previous DataReader is closed. They can't be passed around easily. Your Data-tool is not a true abstraction.

DataTables on the other hand are extremely flexible and can make for some very efficient, clear code. Linq-To-DataTable is great. Luckily the DataTable is actually pretty efficient. For non-huge result-sets it is almost as fast as the datareader. (it depends of course on exactly what you're doing.) More and more I am getting DataTables from my data-tool rather than Readers. It really makes life simple. I can continue to use the same open connection. There's no "state" in data-tool.

The code for getting a DataReader is very simple. So when I really do need a DataReader (not often), I just let my DAL hand me my connection and I get my DataReader myself.

Answer 2

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!

Answer 3

Let your data layer return objects, not datatables or data readers. I would suggest you populate your objects using a data reader.

Answer 4

What I usually do is open the reader with CommandBehavior.CloseConnection. Then I run through the reader and read the data into my own object model or list or whatever in memory with the data and then close the reader. It does much of the same stuff as a data table, but I just hate dealing with bloated and loosely-typed data stuctures.

Answer 5

When I researched this before, I believe I discovered that the performance difference between DataReader and DataTable was trivial, except for perhaps very large amounts of data. Since then, I have typically used DataTable as it's more full-featured, can be worked with disconnected, etc.

Answer 6

I never let a DataReader out into the wild (out of the DAL). Only a matter of time before you are leaving connections open somewhere. Also, I’m almost never dealing with so much data on a single call where passing around a DataTable or DataSet presents a problem.

We are using an object oriented language and the DAL can really take advantage of this. There should only be one line of code in your project that gets the connection string. Only one object that actually touches the database (calls ExecuteNonQuery, DA.Fill(), etc.)

This also lets you get pretty involved with logging exceptions, etc. because you are only doing it once. So in the one DAL base class that I use for all of my DAL object in all of my project I have the logic that if the DAL throws an exception then it is logged to a table in my database. This logging fails-over to a text file if the database logging fails.

So the code I see a lot looks like:
-   Start a try block
-   Make a SQLCommand
-   Get connection string.
-   Make Connection object
-   Open the connection
-   Get the data
-   Bind the data
-   Close the connection
-   Log error if exception

Since I encapsulate all of this, my code to get the data now looks like:

GridView1.DataSource = cProgram.DB.getMyData();

(or more likely a BAL object sits between the 2). The DB is a normal object (not static), but it only instantiated once per application.