When to use SqlDataAdapter? and When to use SqlDataReader ?
If you use a SqlDataAdapter to generate a DataSet or DataTable, note the following:
- You do not need to explicitly open or close the database connection. The SqlDataAdapter Fill method opens the database connection and then closes the connection before it returns. If the connection is already open, Fill leaves the connection open.
- If you require the connection for other purposes, consider opening it prior to calling the Fill method. You can thus avoid unnecessary open/close operations and gain a performance benefit.
- Although you can repeatedly use the same SqlCommand object to execute the same command multiple times, do not reuse the same SqlCommand object to execute different commands.
Use a SqlDataReader obtained by calling the ExecuteReader method of the SqlCommand object when:
- You are dealing with large volumes of data—too much to maintain in a single cache.
- You want to reduce the memory footprint of your application.
- You want to avoid the object creation overhead associated with the DataSet.
- You want to perform data binding with a control that supports a data source that implements IEnumerable.
- You wish to streamline and optimize your data access.
- You are reading rows containing binary large object (BLOB) columns. You can use the SqlDataReader to pull BLOB data in manageable chunks from the database, instead of pulling all of it at once.
