ADO.NET Provider for Google Sheets

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.

Install Entity Framework

Install Entity Framework or add references to the required assemblies for your chosen version of Entity Framework. See Using EF 6 for using Entity Framework 6. See Installed Assemblies for more information about all assemblies shipped with the provider.

Register the Provider

Add the connection string to App.Config or Web.config. The connectionStrings node is often located directly below the configSection node in the root configuration node.

<configuration>
  ...
  <connectionStrings>
    <add name="GoogleSheetsContext" connectionString="InitiateOAuth=GETANDREFRESH;Spreadsheet=NorthwindOrders" providerName="System.Data.CData.GoogleSheets" />
  </connectionStrings>
  ...
</configuration>

Create the Context Class

Start by creating the GoogleSheetsContext class. This is the base object that extends DbContext and exposes the DbSet properties that represent the tables in the data source. Next, override some of the default functionality of the DbContext class by overriding the OnModelCreating method. You can find a description of these properties in the code below:

C#

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;

class GoogleSheetsContext : DbContext {
  public GoogleSheetsContext() { }

  public DbSet<Spreadsheet1_Sheet1> Spreadsheet1_Sheet1 { set; get; }
  
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    // To remove the requests to the Migration History table
    Database.SetInitializer<GoogleSheetsContext>(null);
    // To remove the plural names
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();    
    //For versions of EF before 6.0, uncomment the following line to remove calls to EdmTable, a metadata table
    //modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
	//To remove the default schema "dbo" 
	modelBuilder.HasDefaultSchema("");
  }  
}

VB.NET

Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure
Imports System.Data.Entity.ModelConfiguration.Conventions

Class GoogleSheetsContext 
Inherits DbContext 

  Public Sub New()
  End Sub
  
  public property Spreadsheet1_Sheet1 As DbSet(Of Spreadsheet1_Sheet1)
  
 Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
    ' To remove the requests to the Migration History table
    Database.SetInitializer(of GoogleSheetsContext)(Nothing)
    ' To remove the plural names
    modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)
    ' For versions of EF before 6.0, uncomment the following line to remove calls to EdmTable, a metadata table
    ' modelBuilder.Conventions.Remove(Of IncludeMetadataConvention)()
	' To remove the default schema "dbo" 
	modelBuilder.HasDefaultSchema("")
 End Sub

End Class

Create the Table Models

Finally, 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.

C#

using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations.Schema; //EF 6 and later
//using System.ComponentModel.DataAnnotations //For versions of EF before 6


[System.ComponentModel.DataAnnotations.Schema.Table("Spreadsheet1_Sheet1")]
public class Spreadsheet1_Sheet1  {
  [System.ComponentModel.DataAnnotations.Key]
  public System.String Id { get; set; }
  public System.String Column1 { get; set; }
}

VB.NET

Imports System.Data.Entity.ModelConfiguration
Imports System.ComponentModel.DataAnnotations.Schema 'EF 6 and later
'Imports System.ComponentModel.DataAnnotations 'For versions of EF before 6


<System.ComponentModel.DataAnnotations.Schema.Table("Spreadsheet1_Sheet1")>
public class Spreadsheet1_Sheet1  
  <System.ComponentModel.DataAnnotations.Key>
  public property Id As System.String
  public property Column1 As System.String
End Class

Perform LINQ Commands in Your Code

You are now ready to start using LINQ in your code. Be sure to declare (c#)"using System.Linq"/(VB.Net)"Imports System.Linq" in your file.

C#

GoogleSheetsContext ents = new GoogleSheetsContext();
var Spreadsheet1_Sheet1Query = from Spreadsheet1_Sheet1 in ents.Spreadsheet1_Sheet1
                   orderby Spreadsheet1_Sheet1.Column1
                   select Spreadsheet1_Sheet1;

VB.Net

    Dim ents As GoogleSheetsContext = New GoogleSheetsContext()
    Dim Spreadsheet1_Sheet1Query = From Spreadsheet1_Sheet1 In ents.Spreadsheet1_Sheet1
	Order By Spreadsheet1_Sheet1.Column1
	Select Spreadsheet1_Sheet1

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