ADO.NET Provider for Excel Services

Build 20.0.7587

Querying with the DataReader

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

Using the ExcelServicesDataReader

The ExcelServicesDataReader retrieves data faster than the ExcelServicesDataAdapter because it can retrieve data in pages. As you read data from the ExcelServicesDataReader, 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;User=admin@myorg.onmicrosoft.com;Password=password;File=Book1.xlsx;";

using (ExcelServicesConnection connection = new ExcelServicesConnection(connectionString)) {
  ExcelServicesCommand cmd = new ExcelServicesCommand("SELECT * FROM Account", connection);
  
  ExcelServicesDataReader 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;User=admin@myorg.onmicrosoft.com;Password=password;File=Book1.xlsx;"

Using connection As New ExcelServicesConnection(connectionString)
  Dim cmd As New ExcelServicesCommand("SELECT * FROM Account", connection)

  Dim rdr As ExcelServicesDataReader = cmd.ExecuteReader()

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

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587