Id: ________
Description: _________________
(first) (previous) (next) (last) <- buttons
If anyone can give me an example will be appreciated, thanks for your time
You can either use a DataSet or a DataReader. DataSet gives you the ability to move back and forth within the data. This is ideal for grids. It also allows you to make changes and flush them back to the DB. Unfortunately DataSet is slow (compared to DataReader) and takes up more memory. Don't use it for large data sets. Once the data is loaded into the DB though the DB connection is dropped so you don't waste resources. You will use a DataAdapter to fill the DataSet. Refer to MSDN on how to do this.
DataReader is fast and slim for streaming through data. However it requires a consistent connection to the DB. It also is read-only and forward-only. Once you've read a row of data and moved on you can't go back. Until all the data is read the connection must remain open. For large data sets this is ideal but you don't want to keep a data reader open too long because it impacts scalability. Also be aware that (by default) you can have only one reader per connection so you don't want to open a bunch of readers at one time either. You get a reader back when you call ExecuteReader on a command. You can then enumerate the rows by calling Read. If the command returns multiple result sets (tables) then use MoveNext to move to the next result set.
It is a religious war as far as when to use which. Some people use only DataSet while others use only DataReader. My personal recommendation is to use DataSet for small data sizes where you want to be able to navigate through and/or update the data. If the data is large, you need to expose the data to a UI layer (in a n-tier app) or you need to do custom paging of data then switch to DataReader to stream the data in and (optionally) store it in custom business objects.
Michael Taylor - 5/17/07