ADO.NET Provider for Microsoft Excel Online

Build 24.0.9060

Code-First Approach

An alternative to introspecting the model from the provider is to handwrite your model classes. This is the code-first approach to Entity Framework, which gives you greater control over the exact data model you use in your application.

Create the Context Class

This is the base object that extends DbContext and exposes the DbSet properties that represent the tables in the data source. Override some of the default functionality of the DbContext class by overriding the OnConfiguring method.

using Microsoft.EntityFrameworkCore;

public class ExcelOnlineContext : DbContext
{
	public DbSet<MyTable> MyTable { get; set; }

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		if (!optionsBuilder.IsConfigured)
		{
			optionsBuilder.UseExcelOnline("InitiateOAuth=GETANDREFRESH;");
		}
	}
}

Create the Table Models

Define a class for each table that was defined in the DbSet properties of the context class. The table classes should have a list of properties that correspond to each field of that table. A corresponding map class must be defined to configure attributes for each property in the table class.

public class MyTable
{
	public string KeyColumn { get; set; }
	public string MyColumn { get; set; }
}

Retrieving Records

You can retrieve records from a table as follows:

class Program
	{
		static void Main(string[] args)
		{
			ExcelOnlineContext context = new ExcelOnlineContext();var Query = from MyTable in context.MyTableselect MyTable;
			foreach (var result in Query)
			{
				Console.WriteLine("{0}", result.KeyColumn);
				Console.WriteLine("{0}", result.MyColumn);
			}
			try
			{
				context.SaveChanges();
			}
			catch (Exception e)
			{
				Console.WriteLine(e);
			}
			Console.ReadLine();
		}
	}

Inserting Records

You can insert records into existing tables as follows:

class Program
	{
		static void Main(string[] args)
		{
			ExcelOnlineContext context = new ExcelOnlineContext();
			MyTable newRecord = new MyTable()
			{
				MyColumn1 = "Value1", MyColumn2 = "Value2"
			};
			context.Test_xlsx_Sheet1.Add(newRecord);
			try
			{
				context.SaveChanges();
				Console.WriteLine(newRecord.Id);
			}
			catch (Exception e)
			{
				Console.WriteLine(e);
			}
			Console.ReadLine();
		}
	}

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060