Reading Data with DataReader Objects

Datasets are great when you need really rich control over your data—updates, rollbacks, reading and writing schemas, and so on. But when all you need to do is display some data quickly and efficiently, the DataSet object (and the DataAdapter used to fill it) has a fair amount of overhead that you might not want to incur. Enter the DataReader object.

A DataReader object provides the equivalent of a forward-only, read-only cursor on your data. It is both faster and more lightweight than a dataset object, making it ideal when you need to retrieve a set of rows and iterate over them just once (to display them, for example). Unlike datasets, datareaders can also be data-bound to directly, without the need for a DataView.

SqlDataReader

SqlDataReader is the class to use when accessing data from a SQL Server database. You create this class by calling the ExecuteReader method on a SqlCommand object.

Dim mySqlDR As SqlDataReader = mySqlCmd.ExecuteReader()

To access the rows in a SqlDataReader instance, call the Read method of the instance, usually in a loop:

While mySqlDR.Read() Response.Write(mySqlDR.Item(0)) End While

Tip

If you want a simple way to check if a datareader contains data before performing any operations on it, you can check the HasRows property (new for version 1.1 of the Framework), which returns a True if the datareader contains one or more rows of data.

Once you’re finished with SqlDataReader, you should always call its Close method, as well as calling the Close method on the associated Connection object: mySqlDR.Close().

Note

To avoid having to explicitly close the connection associated with the Command object used to create either a SqlDataReader or anOleDbDataReader, pass the CommandBehavior.CloseConnection argument to the ExecuteReader method of the Command:

mySqlDR = mySqlCmd.ExecuteReader(CommandBehavior.CloseConnection)

The connection associated with the Command object will be closed automatically when the Close method of the datareader is called. This makes it all the more important to always remember to call Close on your DataReader objects!

OleDbDataReader

Creating and using an OleDbDataReader object is essentially the same as for the SqlDataReader, with one notable exception. The OleDbDataReader can handle hierarchical recordsets retrieved using the MSDataShape OLE DB Provider (also known as the Data Shaping Service for OLE DB). When you create anOleDbDataReader based on an OleDbCommand that returns a hierarchical recordset, the OLE DB chapter is returned as a column in the OleDbDataReader. The value of the column is an OleDbDataReader representing the child records. See http://msdn.microsoft.com/library/en-us/wp/htm/wpmdac_tf_shaping_service.asp for more information on the Data Shaping Service for OLE DB.