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

Using the ExcelOnlineDataReader

The ExcelOnlineDataReader retrieves data faster than the ExcelOnlineDataAdapter because it can retrieve data in pages. As you read data from the ExcelOnlineDataReader, 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 Test_xlsx_Sheet1 table:

C#

string connectionString = "InitiateOAuth=GETANDREFRESH;";

using (ExcelOnlineConnection connection = new ExcelOnlineConnection(connectionString)) {
  ExcelOnlineCommand cmd = new ExcelOnlineCommand("SELECT * FROM Test_xlsx_Sheet1", connection);
  
  ExcelOnlineDataReader rdr = cmd.ExecuteReader();

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

VB.NET

Dim connectionString As String = "InitiateOAuth=GETANDREFRESH;"

Using connection As New ExcelOnlineConnection(connectionString)
  Dim cmd As New ExcelOnlineCommand("SELECT * FROM Test_xlsx_Sheet1", connection)

  Dim rdr As ExcelOnlineDataReader = cmd.ExecuteReader()

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