[c#] SqlDataAdapter vs SqlDataReader

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

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.Open();
    using(var rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
            //...
        }
    }
}

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.)

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.

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.