VietMX Staff asked 3 years ago
SqlDataReader:
- Holds the connection open until you are finished (don’t forget to close it or use using).
- Can typically only be iterated over once
- Is not as useful for updating back to the database
On the other hand, it:
- Only has one record in memory at a time rather than an entire result set (this can be huge)
- Is about as fast as you can get for that one iteration
- Allows you start processing results sooner (once the first record is available)
SqlDataAdapter/DataSet:
- Lets you close the connection as soon it’s done loading data, and may even close it for you automatically
- All of the results are available in memory
- You can iterate over it as many times as you need, or even look up a specific record by index
- Has some built-in faculties for updating back to the database
At the cost of:
- Much higher memory use
- You wait until all the data is loaded before using any of it
So really it depends on what you’re doing, but I tend to prefer a DataReader until I need something that’s only supported by a dataset. SqlDataReader is perfect for the common data access case of binding to a read-only grid.
