ADO.NET Provider for Excel

Build 20.0.7655

Querying with the DataReader

The CData ADO.NET Provider for Excel implements two ADO.NET interfaces you can use to retrieve data from 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 = "Excel File=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 = "Excel File=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

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