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

User · Answer

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.

User · Answer

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

User · Answer

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

User · 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

User · Answer

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  amp  columns  that will require a lot of memory to hold

[c#] SqlDataAdapter vs SqlDataReader

Examples related to c#

Examples related to .net