ADO.NET: Retrieve Data using OLE DB

The OleDbDataReader is created by calling the ExecuteReader method of the OleDbCommand, not through direct use of the constructor. While the OleDbDataReader is in use, the associated OleDbConnection is busy serving the OleDbDataReader. While in this state, no other operations can be performed on the OleDbConnection other than closing it. This is the case until the Close method of the OleDbDataReader is called.

VB adodtreader.aspx [Run Sample] | [View Source]

OleDbDataReader provides a means of reading a forward-only stream of data records from an OleDb data source. For more interactive operations, such as scrolling, filtering, navigating, remoting, etc., use the DataSet.

The example creates an OleDbConnection to the Northwind database using the OLE DB .NET Data Provider. The OleDbCommand selecting items from the employee table is then executed using the OleDbCommand ExecuteReader method. The results of this command are passed to the OleDbDataReader.

Dim myDataReader as OleDbDataReader
Dim myOleDbConnection as OleDbConnection
Dim myOleDbCommand as OleDbCommand

myOleDbConnection = new OleDbConnection("server=(local)\VSdotNET;Trusted_Connection=yes;
    database=northwind;provider=sqloledb")
myOleDbCommand = new OleDbCommand("SELECT EmployeeID, LastName,
    FirstName, Title, ReportsTo FROM Employees", myOleDbConnection)
...
myOleDbConnection.Open()
myDataReader = myOleDbCommand.ExecuteReader()

do while (myDataReader.Read())
    ...
    if (myDataReader.IsDBNull(4)) then
      Console.Write("N/A" + Chr(10))
    else
      Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
    end if
loop

Finally, the example closes the OleDbDataReader, then the OleDbConnection.

' Always call Close when done reading.
myDataReader.Close()

' Close the connection when done with it.
myOleDbConnection.Close()

Summary

1. An OleDbDataReader is for reading a forward-only stream of data records from a data source fast.
2. Remember to close the OleDbDataReader and then the OleDbConnection.
3. Remember if the OleDbDataReader is in use, the associated OleDbConnection is busy serving the OleDbDataReader and while in this state, no other operations can be performed on the OleDbConnection other than closing it.