DataConnector | ComponentOne
ADO.NET provider for QuickBooks Online / Entity Framework
In This Topic
    Entity Framework
    In This Topic

    The ADO.NET Provider for QuickBooks Online supports Entity Framework which requires C1.EntityFrameworkCore.QuickBooksOnline package to be installed. In this section, we have used the model first approach to build an Entity Framework model that maps data tables to classes for simpler access to QuickBooks Online.

    Note: The Scaffolding feature supports the user to create a model and dbcontext when you create a model in the Entity Framework for all dataconnectors.

    The following code defines the Attachables class to map the datatable.

    C#
    Copy Code 
    public partial class Attachables
{
    public string Id { get; set; }
    public string SyncToken { get; set; }
    public DateTime? MetaDataCreateTime { get; set; }
    public DateTime? MetaDataLastUpdatedTime { get; set; }
    public string FileName { get; set; }
    public int? Size { get; set; }
    public string ContentType { get; set; }
    public string Category { get; set; }
    public double? Latitude { get; set; }
    public double? Longitude { get; set; }
    public string Note { get; set; }
    public string PlaceName { get; set; }
    public string Tag { get; set; }
    public string AttachableRef { get; set; }
    public string FileAccessUri { get; set; }
    public string TempDownloadUri { get; set; }
}

    The next code example defines the QuickBooksOnlineContext class to access the Attachables datatable and establish a connection to QuickBooks Online. This is done by overriding the OnConfiguring method, which invokes the UseQuickBooksOnline method of the DbContextOptionsBuilder object to configure the context and establish a connection to the QuickBooks Online service.

    C#
    Copy Code 
    public partial class QuickBooksOnlineContext : DbContext
{
    public QuickBooksOnlineContext()
    {
        Database.AutoTransactionsEnabled = false;
    }

    public QuickBooksOnlineContext(DbContextOptions<QuickBooksOnlineContext> options)
        : base(options)
    {
        Database.AutoTransactionsEnabled = false;
    }

    public virtual DbSet<Attachables> Attachables { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseQuickBooksOnline("Company Id = *******; OAuth Client
            Id=*************; OAuth Client Secret=***********; OAuth Refresh Token =
             ************; OAuth Access Token = **********; Use SandBox=true;");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Attachables>(entity =>
        {
            entity.Property(e => e.MetaDataCreateTime)
                .HasColumnName("MetaData_CreateTime")
                .ValueGeneratedOnAddOrUpdate();

            entity.Property(e => e.MetaDataLastUpdatedTime)
                .HasColumnName("MetaData_LastUpdatedTime")
                .ValueGeneratedOnAddOrUpdate();

            entity.Property(e => e.SyncToken).ValueGeneratedOnAddOrUpdate();
        });
    }
}

    You can now use the LINQ queries to perform different data operations to the mapped classes as demonstrated in the code below.

    Note: For LINQ queries, the code must have declared "using System.Linq".
    INSERT
    C#
    Copy Code 
    public static void Insert(QuickBooksOnlineContext context)
{
    Console.WriteLine("\nQuery Insert...");
    Attachables account = new Attachables
    {
        Note = "Test Note " + DateTime.Now.Ticks.ToString(),
        Category = "Other"
    };

    context.Attachables.Add(account);

    int result = context.SaveChanges();
    Console.WriteLine("Number row insert: " + result);
}
    SELECT
    C#
    Copy Code 
    public static void Select(QuickBooksOnlineContext context)
{
    Console.WriteLine("Query all Attachables...");
    var records =
        from p in context.Attachables
        select p;

    foreach (var attachable in records)
    {
        Console.WriteLine("{0} - {1} - {2} - {3}",
                        attachable.FileName, attachable.Note, attachable.Category, attachable.ContentType);
    }
}
    UPDATE
    C#
    Copy Code 
    public static void Update(QuickBooksOnlineContext context)
{
    Console.WriteLine("\nQuery Update...");
    var attachables = context.Attachables.Where(x => x.Id == "5000000000000504413");

    foreach (var attachable in attachables)
    {
        attachable.Note = "Test Note " + DateTime.Now.Ticks.ToString();
        attachable.Category = "Signature";
    }

    int result = context.SaveChanges();

    Console.WriteLine("Number row update: " + result);
}
    DELETE
    C#
    Copy Code 
    public static void Delete(QuickBooksOnlineContext context)
{
    Console.WriteLine("\nQuery Delete...");
    var attachables = context.Attachables.Where(x => x.Id == "5000000000000504413");

    foreach (var attachable in attachables)
    {
        context.Attachables.Remove(attachable);
    }

    int result = context.SaveChanges();

    Console.WriteLine("Number row delete: " + result);
}

     

    View Tables

    QuickBooks Online has some tables which do not have a primary key. For example, the ExchangeRates table. EFCore considers such tables as the View table where you can only perform the select operation.

    C#
    Copy Code 
    public partial class ExchangeRates
{
    public string SourceCurrencyCode { get; set; }
    public string TargetCurrencyCode { get; set; }
    public double Rate { get; set; }
    public DateTime? AsOfDate { get; set; }
    public DateTime? MetaDataLastUpdatedTime { get; set; }
}

    Currently, EFCore 2.1 Scaffolding doesn’t support view tables, therefore you have to manually add the View Table to DbContext as shown in the following code snippet.

    C#
    Copy Code 
    public virtual DbQuery<ExchangeRates> ExchangeRates { get; set; }

     

    C#
    Copy Code 
    modelBuilder.Query<ExchangeRates>(entity =>
{
    entity.Property(e => e.MetaDataLastUpdatedTime).HasColumnName("MetaData_LastUpdatedTime");
});