[Webinar] Streamline your web hosting managementRegister Today


Which one is better? from datatable or datareader?

Posted on 2008-02-04
Medium Priority
Last Modified: 2008-03-06
Hi experts,

I need an array result from database.
I wonder which one is better? from datatable or datareader?

if I get it from datareader, I need 3 steps to get an array result.
datareader --> ArrayList --> Array

from datatable only 2 steps to get an array result.
datatable --> array.

really appreciate if your reply can include the reason. :)

Thank you.
Question by:aciang
  • 3
  • 2
  • 2
  • +1
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20812935
large results, processing row by row -> datareader
small results -> datatable

do you really need an array, btw?

Author Comment

ID: 20812952
yes I need to copy the result to an array object.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20812956
why? sorry to sound like a plague...
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 22

Expert Comment

ID: 20813843
i agree with angelIII and in addition to that... the datareader is a lot more light weight than a datatable b/c you don't deal with table schema like you would with  a datatable.
LVL 22

Expert Comment

ID: 20816812
Are you building an array of atomic objects such as strings or ints or dates, or is it an array of business objects, such that any row returned in the result set from the DB contains more than one column?


Author Comment

ID: 20821250
For Example I have 3 tables.

Table 1: Marriage
Table 2: Kids
Table 3: Pets

-      Husband name
-      Wife name
-      Year of marriage
-      Etc..
-      Name
-      Sex: female
-      Age:
-      Height
-      weight
-      Sex
-      Age
-      biteOrNot

with above tables I make a class like attached
the array is for the Kids[] and Pets[]
public class Family
        private Marriage _marrige;
        private Kids[] _kids;
        private Pets[] _pets;
        public Family()
        public Marriage marriage
            get { return _family; }
            set { _family = value; }
        public Kids[] KidList
            get { return _kids; }
            set { _kids = value; }
        public Pets[] PetList
            get { return _pets; }
            set { _pets = value; }

Open in new window

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 450 total points
ID: 20821361
I would fill 1 dataset with all those data, and make relations between the data tables:

LVL 22

Accepted Solution

JimBrandley earned 1050 total points
ID: 20825235
I would say it depends on what you need to do with them. If you need to hang on to the data for some period of time before creating objects and lists of objects, angelll's suggestion of a DataSet with relations is pretty efficient.

However, If you are going to need to construct the objects right away as you retrieve the data, I do it somewhat differently. For a single row, such as I expect your Marriage object to be, I use:
public void SelectSingleRow(string sql, List<IDbDataParameter>, inParameters, ref Object[] results)
                     reader = mICommand.ExecuteReader(CommandBehavior.SingleRow);
      if (reader.Read())

The caller knows which columns were selected and in which order they they appear in the output. According to David Sceppa at MS, that's the most efficient way to load a subset of a single row.

For Pets and Kids, I use a DataAdapter to fill a DataTable. Once again , that that's the advice I got from MS.


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question