Integrating WinForms DataGrids with QuickBooks Online Using Entity Framework Core

QuickBooks Online is a popular cloud-based Accounting Software that exposes REST APIs’ for external access to its data. Users can consume these API's in various applications ranging from dashboards, reporting to analytical apps.

Entity Framework is an established data access standard provided by Microsoft. Most developers are well acquainted with this technology and find it easier to access any data source. Keeping this in mind, ComponentOne has released a DataConnector for QuickBooks Online that allows developers to use Entity Framework Core and LINQ to access and perform data operations over QuickBooks Online.

FlexGrid is a popular WinForms DataGrid that provides great features to display, editing and analyze data. This blog will understand how to use Entity Framework Core to connect, perform retrievals, update, and insert operations over QuickBooks and FlexGrid.

Authentication

There is an authentication prerequisite for connecting QuickBooks Online REST API’s. QuickBooks Online supports OAuth for authenticating external applications. The documentation discusses the requirements and how to get them. Essentially we would need the following information:

• OAuthClientId
• OAuthClientSecret
• OAuthTokenEndpoint
• OAuthAccessToken
• OAuthRefreshToken
• CompanyId

Setup Project

Create a WinForms application targeting .NET Framework 4.6.2 or later. Alternatively, you can create a .NET Core 2.1 or later application. Add the following NuGet packages to the project:

• C1.EntityFrameworkCore.QuickBooksOnline
• Microsoft.EntityFrameworkCore.Tools (version 2.1.0)

Scaffolding Data Model

The Entity Framework Core support for QuickBooks Online includes scaffolding support to generate DbContext and Model classes. The command to generate the classes is similar to the one supported by Entity Framework Core.

1. Go to View -> Other Windows -> Package Manager Console from Visual Studio.
2. Run the scaffolding command line with syntax in the following format to generate a single table from the data source.

Scaffold-DbContext "<ConnectionString>" C1.EntityFrameworkCore.QuickBooksOnline -OutputDir "Models" -Context "QBOContext" -Tables Invoices

Setup FlexGrid and Data Operations

1. Add a FlexGrid control on the form, set the following properties:
   • AllowAddNew:true
   • Name:fg
2. Add a button on the form, set its text property to save.
3. In the constructor of the form, initialize the QBOContext.

QBOContext context;

public Form1()

{

InitializeComponent();

Load += Form1_Load;

context = new QBOContext();

//Get connection

     var conn = context.Database.GetDbConnection() as C1QuickBooksOnlineConnection;

//Attach event

    conn.OAuthTokenRefreshed += Conn_OAuthTokenRefreshed;
}

In the Form load event, add the below code to bind FlexGrid to the Invoice's data, also it is required to save the refresh token so that it can be used later:

 //Execute queries, bind to grid    

context.Invoices.Load();

this.fg.DataSource = context.Invoices.Local.ToBindingList();

     //Detach event

     conn.OAuthTokenRefreshed -= Conn_OAuthTokenRefreshed;

Here is the method to save the refresh token

private static void Conn_OAuthTokenRefreshed(object sender, EventArgs e)

{

    //Save new token here        

    var conn = sender as C1QuickBooksOnlineConnection;

    var strAuthen = $"{conn.CompanyId};{conn.OAuthToken.AccessToken};{conn.OAuthToken.RefreshToken}";

    File.WriteAllText(@"Authentication.txt", strAuthen); 

}

The above code also takes care of change tracking. It tracks update, insert changes performed by the user.

To push the changes back to QuickBooksOnline, we need to call the SaveChanges function of QBOContext. We can call it on form closing or in the Save button we just added:

private void btnSave_Click(object sender, EventArgs e)

{

try

{

context.SaveChanges();

}

catch (Exception ex)

{

MessageBox.Show("There was a problem saving the changes");

//log the error

}

}

The application is ready, run the app and perform all data-specific changes like updating a record and inserting a row using New Row.

Improve Performance with Intelligent Caching

The QuickBooks Online DataConnector comes with intelligent caching that supports full or incremental refresh of data. This helps increase the performance of the application where network roundtrips are saved when the same dataset is queried in a short period of time or when the app requires that only new or updated records be fetched.

To view the QuickBooks Online data connector in action, check out the online demo that shows integration with OLAP control:

DataConnectorExplorer

Do more with QuickBooks Online DataConnector

• Integrate QuickBooks Online data with C1DataCollection to provide filtering, grouping, data virtualization to your data-aware controls.
• Import data from QuickBooks Online to C1DataEngine to analyze massive amounts of data in fractions of a second.
• Connect QuickBooks Online data to pivot grids and charts, such as FlexPivot, to analyze data visually.
• Create business reports using FlexReport and QuickBooks Online for essential business tasks.
• Sync Salesforce CRM Accounts data with QuickBooks Online Customers data using Salesforce and QuickBooks Online data connectors.