Link to home
Start Free TrialLog in
Avatar of Brian
BrianFlag for United States of America

asked on

DataReader vs. DataTable


I have the following code listed below and would like to know which method would be better to use, either a DataTable or DataReader. The content that this code retrieves will hardly change but it's data is tied to other data for reasons that I cannot make it static within the site.

This code below is called in 20 different pages. The same code in each 20 pages called each time a user visits each of those 20 pages.


    protected void Page_Load(object sender, EventArgs e)

    protected void Retrieve_NavLinks()
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
            SqlCommand cmdRetrieveNavLinks = new SqlCommand();
            cmdRetrieveNavLinks.CommandText = "PicksPhoto_RetrieveNavLinks";
            cmdRetrieveNavLinks.CommandType = CommandType.StoredProcedure;
            cmdRetrieveNavLinks.Connection = conn;


            SqlDataReader rdr = cmdRetrieveNavLinks.ExecuteReader();

            rpRetrieveNavLinks.DataSource = rdr;


ALTER PROCEDURE [dbo].[PicksPhoto_RetrieveNavLinks]


SELECT navm.mnav_id, navm.onav_id, navm.mnav_linkname, navm.mnav_url
FROM [App_NavMain] AS navm
INNER JOIN [App_NavOrder] navo
ON navm.onav_id = navo.onav_id
WHERE navm.mnav_id > 1
ORDER BY onav_id ASC

Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

A DataReader maintains a connection to the database for the entire time you are reading the data, and the data is delivered as you ask for it. A DataTable pulls all the data from the database into the DataTable construct and then lets you do all sorts of things to it.

Generally a DataTable will be slower and more memory intensive.

In your situation could you consider caching the data for a period of time to avoid having to pull it every page load?
Avatar of Dmitry G
OK, I'm not quite sure about other complications for your app but I'd probably (especially for ASP) I'd choose Datareader.

The main and very important advantage - performance. I believe for on-line apps it's crucial.

It's easier to work with tables, definitely.

Probably, volue of data is important. If it's small - DataTable would be OK and fast enough.

Also. datareaders have other drawback. Please read the article:
Avatar of Brian


Could you show me example how I would cache the code I have above? Is there anything I have to enable for caching?

I guess I'm a little confused, I was hoping of a solution that would somehow prevent 20 hits to my DB for the same data as in the code above.
Well your question said "would be better to use, either a DataTable or DataReader" - which has been answered.

It sounds like you also want to know how to improve the performance of the site. Firstly it depends how often and under what circumstances the data changes. If having considered that you determine that you can afford to cache the data for a period then you need to consider it the data the same for all users in which case look at caching ( if it differs from user to user then consider using a session variable (

Avatar of Brian


The data will hardly ever change. All users need to see the same data. I plan on expecting thousands of hits per day.
OK, this definitely needs caching to avoid DB traffic, I'd say. Not quite sure how this is done in ASP correctly - should be on Google.
Avatar of systan
Flag of Philippines image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The best way to optimized your web performance is the way you handle your configurations,  and the code structures you wrote if is clean and simple.