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

Using the Microsoft SharePoint ExcelDataReader

The Microsoft SharePoint ExcelDataReader retrieves data faster than the Microsoft SharePoint ExcelDataAdapter because it can retrieve data in pages. As you read data from the Microsoft SharePoint 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 Account table:

C#

string connectionString = "Url=https://myorg.sharepoint.com;[email protected];Password=password;File=Book1.xlsx;";

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

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

VB.NET

Dim connectionString As String = "Url=https://myorg.sharepoint.com;[email protected];Password=password;File=Book1.xlsx;"

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

  Dim rdr As Microsoft SharePoint ExcelDataReader = cmd.ExecuteReader()

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