ADO.NET: Retrieve Data using OLE DB

This sample illustrates how to read data from an OLE DB provider using the OleDbDataReader class. This class provides a way of reading a forward-only stream of data records from a data source. If you want to work with SQLServer 7.0 or higher, you should refer to the topic Retrieve Data from SQLServer.

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.

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. OleDbDataReader myDataReader = null; OleDbConnection myOleDbConnection = new OleDbConnection("server=(local)\SQLExpress;Integrated Security=SSPI; database=northwind;provider=SQLNCLI"); OleDbCommand myOleDbCommand = new OleDbCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", myOleDbConnection); ... myOleDbConnection.Open(); myDataReader = myOleDbCommand.ExecuteReader(); Dim myDataReader as OleDbDataReader Dim myOleDbConnection as OleDbConnection Dim myOleDbCommand as OleDbCommand myOleDbConnection = new OleDbConnection("server=(local)\SQLExpress;Integrated Security=SSPI; database=northwind;provider=SQLNCLI") myOleDbCommand = new OleDbCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", myOleDbConnection) ... myOleDbConnection.Open() myDataReader = myOleDbCommand.ExecuteReader() OleDbDataReader * myDataReader; OleDbConnection * myOleDbConnection = new OleDbConnection(S"server=(local)\\SQLExpress;Trusted_Connection=yes;database=northwind;provider=SQLNCLI"); OleDbCommand * myOleDbCommand = new OleDbCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", myOleDbConnection); ... myOleDbConnection->Open(); myDataReader = myOleDbCommand->ExecuteReader(); The example reads through the data using the OleDbDataReader Read method and writing the data elements out to the console. while (myDataReader.Read()) { ... if (myDataReader.IsDBNull(4)) Console.Write("N/A\n"); else Console.Write(myDataReader.GetInt32(4) + "\n"); } 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 while (myDataReader->Read()) { ... if (myDataReader->IsDBNull(4)) Console::Write("N/A\n"); else Console::Write(String::Concat(myDataReader->GetInt32(4).ToString(),"\n")); }

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(); ' Always call Close when done reading. myDataReader.Close() ' Close the connection when done with it. myOleDbConnection.Close() // 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.