Introduction


When we need to retrive mutliple records from databsae, we have following options
1. DataSet or DataTable - use SqlDataAdapter to generate it
2. SqlDataReader - use it to provide read only, forward only data stream
3. XmlReader - use it to provide a read only, forward only data stream of XML data.


The choice between SqlDataReader and DataSet/DataTable is essentially one of the performance vs functionality issue. We should use SqlDataReader to gain optimum performance while DataSet/DataTable gain additional functionality and flexibility.


Use DataSet

We should use a DataSet populated by SqlDataAdapter when

1. We require a disconnected memory-resident cache of data so that we can pass to different component or layers
2. We are working wtih data retrived from multiple data sources.
3. We want to update some of all retrieved rows using batch updated provided by SqlDataAdapter
4. We want to perform data binding against a control that requires a data source that spport IList such as GridView, Repeater etc.

Whenever we are using SqlDataAdapter to generate DataSet or DataTable, following points should be considered.

1. We don't need to explicitly open or close the database connection as SqlAdapter Fill method opens the database connection and then closes it before it returns. If the connection is already open (before using it into SqlDataAdapter) then it leaves it as it is.
2. If we require the connection object to use it further then better to open the connection before calling Fill method of SqlDataAdapter to avoid reopening it again.


Use SqlDataReader


We should use SqlDataReader by calling ExecuteReader method of SqlCommand object when

1. We are dealing with large volumes of data - too much to maintain in single cache.
2. We want to reduce the memory occupied by the application (using DataSet consumes memory)
3. We want to perform data binding with a control that supports a data source that implements IEnumerable.

When we are using SqlDataReader, following points should be considered.

1. The underlying connection to the database remains open and cannot be used for any other purpose while the data reader is active. So we should call conn.Close() method as soon as possible.
2. There can be only one reader per connection.
3. Better to use CommandBehavior.CloseConnection (dCmd.ExecuteReader(CommandBehavior.CloseConnection)) to close the connection. This indicates that the connection should be closed when the SqlDataReader is closed.
4. When accessing data by using the reader, use the typed accessor methods like GetInt32, GetString as they reduce the amound ot type convertion required when reading the column data.
5. To avoid unnecessary data pulling from the datbase, use Cancel method of Command object (dCmd.Cancel()) before closing the reader. (lets say you have 100 records into the reader and after looping through 10 records you think that you got the data you needed then before closing the reader call Cancel method of the command object) This ensures that the results are discarded on the server and are not pulled unnecessarily to the client.


Use XmlReader

Use an XmlReader obtained by calling the ExecuteXmlReader method of SqlCommand object when
1. We want to process the retrieved data as XML but we don't want to incur the overhead of creating DataSet.

When we are using XmlReader following points should be considered
1. The connection must remain open while we read the data from XmlReader. There is no support of CommandBehavior.CloseConnection as in case of SqlDataReader so we should close the connection explicitly.


Conclusion

We generally do these activities on almost all projects, a little attention on what to use when increases the performance of the overall project and increases the scalability.