Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →
Ready to get started?
Download a free trial of the Excel Online Data Provider to get started:
Download NowLearn more:
Excel Online ADO.NET Provider
Rapidly create and deploy powerful .NET applications that integrate with live Excel Online Spreadsheet data!
LINQ to Excel Online Data
LINQ offers versatile querying capabilities within the .NET Framework (v3.0+), offering a straightforward method for programmatic data access through CData ADO.NET Data Providers. In this article, we demonstrate the use of LINQ to retrieve information from the Excel Online Data Provider.
This article illustrates using LINQ to access tables within the Excel Online via the CData ADO.NET Data Provider for Excel Online. To achieve this, we will use LINQ to Entity Framework, which facilitates the generation of connections and can be seamlessly employed with any CData ADO.NET Data Providers to access data through LINQ.
See the help documentation for a guide to setting up an EF 6 project to use the provider.
- In a new project in Visual Studio, right-click on the project and choose to add a new item. Add an ADO.NET Entity Data Model.
- Choose EF Designer from Database and click Next.
- Add a new Data Connection, and change your data source type to "CData Excel Online Data Source".
Enter your data source connection information.
You can connect to a workbook by providing authentication to Excel Online and then setting the following properties:
-
Workbook: Set this to the name or Id of the workbook.
If you want to view a list of information about the available workbooks, execute a query to the Workbooks view after you authenticate.
- UseSandbox: Set this to true if you are connecting to a workbook in a sandbox account. Otherwise, leave this blank to connect to a production account.
You use the OAuth authentication standard to authenticate to Excel Online. See the Getting Started section in the help documentation for a guide. Getting Started also guides you through executing SQL to worksheets and ranges.
-
- If saving your entity connection to App.Config, set an entity name. In this example we are setting ExcelOnlineEntities as our entity connection in App.Config.
- Enter a model name and select any tables or views you would like to include in the model.
Using the entity you created, you can now perform select , update, delete, and insert commands. For example:
ExcelOnlineEntities context = new ExcelOnlineEntities();
var test_xlsx_sheet1Query = from test_xlsx_sheet1 in context.Test_xlsx_Sheet1
select test_xlsx_sheet1;
foreach (var result in test_xlsx_sheet1Query) {
Console.WriteLine("{0} {1} ", result.Id, result.Id);
}
See "LINQ and Entity Framework" chapter in the help documentation for example queries of the supported LINQ.
Data Connectors
ETL/ ELT Solutions
Cloud & API Connectivity
OEM & Custom Drivers