SqlDataAdapter vs SqlDataReader

Question

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

Unharness

Answer 1

DataReader:

• Needs the connection held 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, or even binding to a grid where later updates will be separate tasks, as in most web scenarios.

For more info, see the official Microsoft documentation.

Nocuous

DataSet is an in-memory datastore, whereas datareader is just a medium to retrieve the data. On a lighter note, you can run Linq queries on DataSet, but not on a datareader. – Reflexion

Actually, with a little bit of extra code you certainly can run linq queries (or, at least one query) on a datareader. Just use an iterator block to yield return the DataReader cast as an IDataRecord inside your while (reader.Read()) loop. – Nocuous

This answer is misleading. If you wrap your SqlConnection and SqlDataReader objects with "using" statements (as you should anyway, since they are IDisposable), the connection will be closed automatically. And you can use a DataSet with a SqlDataReader: just call DataSet.Load(SqlDataReader). – Debar

While you are iterating a reader, you have to open another reader during this iteration then you would need two connections. So the number of connections will depend upon the no. of open readers at given time – Alluring

@Debar Don't be too quick to trust using statements to close things for you. They call the object's Dispose() method, not it's Close() method, and I have run into at least one case where the Dispose did not actually close the object for me. It is always best to include an explicit call to the close method inside the using block. – Cletuscleve

@Cletuscleve the MSDN docs are usually pretty clear about Close() vs. Dispose(). In the case of SqlConnection, for example, the docs say that Close() and Dispose() are functionally equivalent. I don't have any objection to calling Close(), but calls to Dispose() should be there as well for all IDisposables--and the cleanest way to do that is with a using statement. In cases where you know Dispose() doesn't call Close(), then you should call Close() within a finally block if you can, not within the using block (so it still gets called if there's an exception). – Debar

Answer 2

The answer to that can be quite broad.

Essentially, the major difference for me that usually influences my decisions on which to use is that with a SQLDataReader, you are "streaming" data from the database. With a SQLDataAdapter, you are extracting the data from the database into an object that can itself be queried further, as well as performing CRUD operations on.

Obviously with a stream of data SQLDataReader is MUCH faster, but you can only process one record at a time. With a SQLDataAdapter, you have a complete collection of the matching rows to your query from the database to work with/pass through your code.

WARNING: If you are using a SQLDataReader, ALWAYS, ALWAYS, ALWAYS make sure that you write proper code to close the connection since you are keeping the connection open with the SQLDataReader. Failure to do this, or proper error handling to close the connection in case of an error in processing the results will CRIPPLE your application with connection leaks.

Pardon my VB, but this is the minimum amount of code you should have when using a SqlDataReader:

Using cn As New SqlConnection("..."), _
      cmd As New SqlCommand("...", cn)

    cn.Open()
    Using rdr As SqlDataReader = cmd.ExecuteReader()
        While rdr.Read()
            ''# ...
        End While
    End Using
End Using     

equivalent C#:

using (var cn = new SqlConnection("..."))
using (var cmd = new SqlCommand("...", cn))
{
    cn.Open();
    using(var rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
            //...
        }
    }
}
   

Scrouge

If your goal is to get data using a select query on db, and only access this data at different rows, go to rpevious row, etc, then you can use the SQLDatareader and load it into a datatable using dtable.Load(rdr). Then browse up and down within this datatable. You can use this method instead of DataAdapter... – Franklynfrankness

Answer 3

A SqlDataAdapter is typically used to fill a DataSet or DataTable and so you will have access to the data after your connection has been closed (disconnected access).

The SqlDataReader is a fast forward-only and connected cursor which tends to be generally quicker than filling a DataSet/DataTable.

Furthermore, with a SqlDataReader, you deal with your data one record at a time, and don't hold any data in memory. Obviously with a DataTable or DataSet, you do have a memory allocation overhead.

If you don't need to keep your data in memory, so for rendering stuff only, go for the SqlDataReader. If you want to deal with your data in a disconnected fashion choose the DataAdapter to fill either a DataSet or DataTable.

Frug

Answer 4

Use an SqlDataAdapter when wanting to populate an in-memory DataSet/DataTable from the database. You then have the flexibility to close/dispose off the connection, pass the datatable/set around in memory. You could then manipulate the data and persist it back into the DB using the data adapter, in conjunction with InsertCommand/UpdateCommand.

Use an SqlDataReader when wanting fast, low-memory footprint data access without the need for flexibility for e.g. passing the data around your business logic. This is more optimal for quick, low-memory usage retrieval of large data volumes as it doesn't load all the data into memory all in one go - with the SqlDataAdapter approach, the DataSet/DataTable would be filled with all the data so if there's a lot of rows & columns, that will require a lot of memory to hold.

Gish

Answer 5

The Fill function uses a DataReader internally. If your consideration is "Which one is more efficient?", then using a DataReader in a tight loop that populates a collection record-by-record, is likely to be the same load on the system as using DataAdapter.Fill.

(System.Data.dll, System.Data.Common.DbDataAdapter, FillInternal.)

Upcountry