The CData ADO.NET Provider for Microsoft Excel implements two ADO.NET interfaces you can use to retrieve data from Microsoft Excel: ExcelDataAdapter and ExcelDataReader objects. Whereas ExcelDataAdapter objects retrieve a single result set of all the data that matches a query, ExcelDataReader objects fetch data in subset increments as needed.

Using the ExcelDataReader

The ExcelDataReader retrieves data faster than the ExcelDataAdapter because it can retrieve data in pages. As you read data from the ExcelDataReader, it periodically requests the next page of results from the data source, if required. This causes results to be returned at a faster rate. The following example selects all the columns from the Sheet table:

C#

string connectionString = "URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;";

using (ExcelConnection connection = new ExcelConnection(connectionString)) {
  ExcelCommand cmd = new ExcelCommand("SELECT * FROM Sheet", connection);
  
  ExcelDataReader rdr = cmd.ExecuteReader();

  while (rdr.Read()) {
    Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["RowId"], rdr["LastName"]));
  }
}

VB.NET

Dim connectionString As String = "URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;"

Using connection As New ExcelConnection(connectionString)
  Dim cmd As New ExcelCommand("SELECT * FROM Sheet", connection)

  Dim rdr As ExcelDataReader = cmd.ExecuteReader()

  While rdr.Read()
    Console.WriteLine([String].Format(vbTab & "{0} --> " & vbTab & vbTab & "{1}", rdr("RowId"), rdr("LastName")))
  End While
End Using