What are the differences between using SqlDataAdapter vs SqlDataReader for getting data from a DB?
I am specifically looking into their Pros and Cons as well as their speed and memory performances.
Thanks
Answer
DataReader:
- Holds the connection open until you are finished (don't forget to close it!).
- 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). For some query types this can also be a very big deal.
DataAdapter/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.
For more info, see the official Microsoft documentation.