DataTable versus SQLDataReader benefits on Grid

I was told in a previous post to populate a grid with DataTable instead of SQLDataReader.

I just ran a test of 30,000 records in the result set, but a pagesize of 100, and the page load was typically identical in both cases. I ran the test about 10 times for each scenario and they fluctuated within 3 seconds of each other, sometimes winning, sometimes losing. But nothing that would seem to be a clear winner ever.

What are the benefits again?
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
Reread my answer in your previous post.

DataReader is usually best if you simply display the data.

DataAdapter is usually best if you need to send back corrected data to the database after modifications.
0
CodeCruiserCommented:
>DataReader is usually best if you simply display the data.

There is another aspect though. Datareader reads one row at a time. Datatable is populated in one go.
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I did tests of 230,000 records and there was no noticeable difference in speed between the two. That being said, I don't understand the importance of read one row at a time, versus populated all at once.

I do notice if I wrap a timer around the DataTable creation it takes 5 seconds, whereas the SQLDataReader takes less than one second. But the page load is identical for both, so the time is made up elsewhere.

I'm so confused. :(

What am I losing by using SQLDataReader, what am I specifically gaining by using DataTable? I don't do inserts, updates, deletes within my grids.

thanks.
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
And also does a SQLDataReader require it be closed? Maybe that's the big issue. I'm not closing it, but you said a DataTable doesn't need to be closed.
0
CodeCruiserCommented:
Yes reader needs closing as it occupies the connection while its open.

You may not be seeing difference in speed with one user but the difference in speed as well as available connections limit on SQL Server would become apparent once you try to scale your application.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
>>You may not be seeing difference in speed with one user but the difference in speed as well as available connections limit on SQL Server would become apparent once you try to scale your application.

aha! That's exactly my problem! thanks!
0
CodeCruiserCommented:
Glad to help :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.