Using DataTables instead DataReaders: a good pratice? (C#)

Question

0.00/5 (No votes)

See more:

Hey, all!
What do you think about working only with DataTables instead DataReaders on my DAO classes?

I thought to change the classic:

C#

public IList<client> ReadAll()  
{  
    String selectString = "SELECT * FROM CLIENTS";  
    IList<client> clients = new List<client>();  
    using (var selectCommand = new OracleCommand(connection, selectString))  
    {  
        using (OracleDataReader selectReader = selectCommand.ExecuteReader())  
        {   
           if (selectReader.HasRows)  
           {  
               while (selectReader.Read())  
               {  
                   clientes.add( GetDomainObject(selectReader) );  
               }  
               selectReader.close();  
           }  
        }
    }  
    return clients;
}  
</client></client></client>

for this way to get data from database to my collection:

C#

public IList<client> ReadAll()  
{  
    String selectString = "SELECT * FROM CLIENTS";  
    IList<client> clients = new List<client>();  
    using (var adapter = new OracleDataApdater(connection, selectString))  
    {  
        DataTable datatableClients = new DataTable("CLIENTS");  
        adapter.Fill(datatableClients);  
        foreach (DataRow datarowClient in datatableClients.Rows)  
        {  
            clients.add( GetDomainObject(datarowClient) );  
        }  
    }  
    return clients;
}  
</client></client></client>

Do you see any advantage in do this change on my DAO classes?
Thanks all!

Posted 3-Aug-11 16:37pm

NicolasFernandes

Add a Solution

4 solutions

Solution 2

So true Wonde. +5.

Datareader works in connected Architecture whereas Dataset, Datatable with disconnected Architecture.
Connected Architecture hold a connection until reader closed. and fetched updated records from database.
But use of readers for large nunmbers of records is not good coding practics which may slow down the performance.

If you are using Datareader then you need to free up memory after use by using Close() Dispose() and make it NULL
Other wise OUT_OF_MEMORY_EXCEPTION could occours here is ref[^].

Posted 3-Aug-11 18:56pm

koolprasad2003

Solution 3

The Reader allows you to filter (or otherwise process) the rows you want to add to your collection, without loading the whole lot into memory. If what you're writing is a function to get the whole lot into memory (as here), then there is little difference between these two approaches, and as Wonde mentions there are marginal benefits to the DataTable approach. In most applications you do just load all the result rows into memory and then process them – it's only if your query will return a ridiculous number of rows (millions) that you would want to process them linearly without pre-loading them all.

Posted 4-Aug-11 2:30am

BobJanova

Solution 4

Can I trust that "using" directive will free my DataTable from memory after I work with it?
E.g.:

C#

IList<String> myCollection = new List<String>();
(using DataTable datatableSelect = new DataTable("MYTABLE"))
{
   foreach (DataRow datarowSelect in datatableSelect.Rows)
   {
       // process, process and process...
   }
} //<--- here, I want to free this DataTable from memory! Can I trust?
return myCollection;

Posted 4-Aug-11 3:35am

NicolasFernandes

Comments

BobJanova 4-Aug-11 11:05am

Yes. using calls Dispose at the end of the scope.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wonde Tadesse · Accepted Answer · 2011-08-03T17:15:00

Solution 1

Both give the desired out put. Except DataReader relatively faster but has cost due to it works with exclusive database connection. If you want work with disconnected scenario, then Datatable or Dataset is much preferable. For more look this MSDN forum DataTable Vs DataReader[^]

Posted 3-Aug-11 17:15pm

Wonde Tadesse

Updated 4-Aug-11 1:58am

v2

Comments

NicolasFernandes 4-Aug-11 9:32am

Thanks for this vision about the scenario, Wonde!

Wonde Tadesse 6-Dec-11 11:13am

You 're welcome