DataReaders vs DataSets

[Hack]

Before posting this, I did some Googling and found some articles stating that DataSet should NOT be used for large resultset. It seems the DataSets are "in memory" while the DataReader is a direct readonly, forward only connection to the data source.

On the other hand, it appears that a "problem" with the DataReader is that as long as you are using it, you are using a connection. I don't see the issue with this, but it would seem that others feel this is not good. Finally, it appears as though DataReaders are recommended for smaller result sets.

So, I have some questions.

1. Which do you use and why? Do you use both as the circumstance dictates?

2. If the DataSet is not supposed to be used for large result sets and the DataReader is recommended for smaller result sets, what the heck are you supposed to use for result sets that you KNOW are going to be large?

3. I don't know about you folks, but most of the time I have no idea how many records are going to be returned from a SELECT query. As the database grows, and my application is used, the number of records returned could also grow. Consequently, how the heck are you supposed to know before hand what to use?

[GaryMazzone]

Personnally I use datareaders for just about every thing except populating a grid of any type. I just use the reader to get data (most of the time a single row). I don't use any of direct edits in a grid connected to the dataset I fill the grid then dispose of the dataset.

[sevenhalo]

DataReaders. .Net is very very OOP purist friendly. The dataset (unless it's typed) is a generic housing container for any kind of data. Naturally, that comes with overhead. With a DataReader, I can take the data, place it into my objects I created and be done with the reader. I'm not stuck maintaining a recordset anymore and quite frankly - that is one of the things I love about .Net the most.

[Hack]

I'm not stuck maintaining a recordset anymore

I'm not quite sure what you mean by maintain. Whenever I created a recordset, I used, then closed it and set it to nothing. Whats to maintain?

I'm doing the same thing with the reader (which, just based on two posts, I think I'll stick with). I create, use it, and close it. I see the way I'm using the reader to be nearly identical to the way I used recordsets.

[sevenhalo]

Forward only recordsets - yes, but i meant to compare the recordset to a dataset, not the datareader.

[Hack]

Forward only recordsets - yes, but i meant to compare the recordset to a dataset, not the datareader.

Ohhhh...yeah, I don't have much experience with datasets, but from what I've read, I can understand that.

[Shaggy Hiker]

The datareader, being read-Only Forward-Only, is a fast way to get data, but it also has some drawbacks. Since it is Read-Only, you can't very well use it to update data directly, though you can use the data in it to build an INSERT or UPDATE SQL statement. The dataset allows much more flexibility, because you can have multiple tables, plus the relationship between them, you can change them, and you can write all the changes back to the database. There are certainly advantages to that, but the cost is somewhat slower performance.

You can also make your dataset disconnected, so if you are drawing data from a server with many users, you can basically take your data and go play with it without bugging the server. I don't do much with that, since all my programs are single user, but that's the theory.

[Hack]

You can also make your dataset disconnected, so if you are drawing data from a server with many users, you can basically take your data and go play with it without bugging the server. I don't do much with that, since all my programs are single user, but that's the theory.

This sounds interesting. Does anyone have an example of this?

[GaryMazzone]

hack the example I sent you does this. The class returns a dataset to the calling sub. Then closes the dataset in the function and closes the connection. This is done in the Finally portion of the Try..Catch constraint.

[Hack]

Ah....when I saw that you had posted I had a feeling you were going to say that there was an example in the stuff you sent.

Actually, Friday afternoon my wife and I went back up to Lake Superior and hung out until yesterday evening so I haven't had a chance to look at it yet, but I will.

Thanks.

[sevenhalo]

Welcome to VBF

Don't forget to mark your thread resolved under Thread Tools at the top.

[nemaroller]

3. I don't know about you folks, but most of the time I have no idea how many records are going to be returned from a SELECT query. As the database grows, and my application is used, the number of records returned could also grow. Consequently, how the heck are you supposed to know before hand what to use?

Where I work we have at the minimum 300,000+ records in one table.

The proc will never return that many for any one query on that table since it limits the results to 1/1000 of the full result set or roughly 300 records for performance and resource reasons.

Any data that needs to be mined or queried on the full results is done at the database and piped down in small parts as needed, via either recursive calls or offset indexes, etc.

We exclusively only use datareader for reads which of course just populates business objects via a generic object filler class.

For updates, a call(s) to sql proc(s) are made - the same thing the DataSet.Update method does.

[techgnome]

Haven't read any of the other reponses yet... will get to that in a sec...

1. Which do you use and why? Do you use both as the circumstance dictates?

It depends on my needs. If I need the data once in a highspeed manner and don't need to modify the data, like for filling a combo, then I'll use a reader. It' clean simple and economic.
But for some of my heavier lifting where I am doing a lot of processing on the data returned, I've been returning it in datatables. In the future (such as on the next round of my current project) I'll be exchanging the datatables for specialized generic collections. At that point, all of the data reading will be via readers, or one-shot stored procedures (with output parameters).
The reason that some people don't like DataReaders is primarily they don't understand them. When a reader is opened, it obtains an exclusive lock on the connection, preventing anything else from also using that connection. And the connection pooling manner of .NET just makes it worse.

2. If the DataSet is not supposed to be used for large result sets and the DataReader is recommended for smaller result sets, what the heck are you supposed to use for result sets that you KNOW are going to be large?

Depends on what's a large dataset and what you plan to do with it. As long as you aren't selecting everything from a 10 table join, and are careful in the records returned (use those filters), there shouldn't be an issue.

3. I don't know about you folks, but most of the time I have no idea how many records are going to be returned from a SELECT query. As the database grows, and my application is used, the number of records returned could also grow. Consequently, how the heck are you supposed to know before hand what to use?

Do your best to be selective. It might mean a paradigm change and adding a few controls to allow the users to filter the data they can view. Or re-organizing the screens to allow paginating. But it also goes back to what you plan to do with the data and what it is for.

-tg

[GlenW]

DataSets are useful if you have a few lists to fill. One procedure can populate the tables in the DataSet then you fill your lists from the appropriate table.