Integrate QuickBooks Online Data using ADO. NET

The ComponentOne 2020v3 release enhances the number of datasources supported by .NET Standard service library DataConnectors, which provides a set of connectors that can be used to connect to different types of data sources. You can now connect to six data sources, namely OData, Microsoft Dynamics 365 Sales, Salesforce Data, Kintone, QuickBooks Online, and Google Analytics, using an ADO.NET provider for each type.

These providers have the same ADO.NET architecture as the native .NET data providers for SQL Server and OLEDB. Hence, they ease the complexity of accessing data by letting you follow a similar set of classes available in the .NET architecture. These providers also have advantageous features such as authentication, caching, and SQL/LINQ query support.

To add up to these features, the DataConnector also includes an Entity Framework (EF) Core provider for each type of datasource, which makes this library useful even when working with Entity Framework Core.

This blog will help you understand how to use the ADO.NET provider for QuickBooks Online to connect to the QuickBooks Online data and even explain the basic features, including authentication, querying, and caching. To get started, you would need to install the C1.DataConnector and C1.AdoNet.QuickBooksOnline packages from NuGet:

You can explore all types of DataConnector and their features using Desktop DataConnector Explorer and Web-based DataConnector Explorer. You can also download and install the ComponentOne DataConnectors service component from here and find the installed samples at the following location: 'C:\Users\xxxx\Documents\ComponentOne Samples\ServiceComponents\DataConnector'.

Connecting to QuickBooks Online data

The first step towards working with the provider is to establish a connection to the QuickBooks Online data. The DataConnector service library provides you with the C1QuickBooksOnlineConnection class, just like the ADO.NET DataConnection class. It is used to establish a connection to the data by creating the connection object and passing the connection string as a parameter to the class constructor. The connection string can be predefined or generated using the C1QuickBooksOnlineConnectionStringBuilder class.

Here is a sample code snippet depicting how to generate a connection string and create a connection object:

//Configure Connection string 
C1QuickBooksOnlineConnectionStringBuilder builder = new C1QuickBooksOnlineConnectionStringBuilder 
{ 
  OAuthClientId = OAuthClientId, 
  OAuthClientSecret = OAuthClientSecret, 
  OAuthTokenEndpoint = OAuthTokenEndpoint, 
  OAuthAccessToken = OAuthAccessToken, 
  OAuthRefreshToken = OAuthRefreshToken, 
  CompanyId = CompanyId, //RealmId is equivalent to CompanyId 
  UseSandbox = true 
}; 

private void Form1_Load(object sender, EventArgs e) 
{ 
  //Setup Connection
  LoadAuthentication(); 

  using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(builder.ConnectionString)) 
  { 
    //Handle the OAuthTokenRefreshed event to update the connection string 
    //with new tokens after the tokens have been refreshed. 
    conn.OAuthTokenRefreshed += OAuthTokenRefreshed; 

    //Perform CRUD operations 

    } 
  } 

  private static void LoadAuthentication() 
  { 
    try 
    { 
      var arrAuth = File.ReadAllText(@"Authentication.txt").Split(';'); 
      if (arrAuth[0] == CompanyId) 
    { 
      OAuthAccessToken = arrAuth[1]; 
      OAuthRefreshToken = arrAuth[2]; 
     } 
   } 
      catch { } 
    } 

    private static void OAuthTokenRefreshed(object sender, EventArgs e) 
    { 
      var conn = sender as C1QuickBooksOnlineConnection; 
      var strAuthen = $"{conn.CompanyId};{conn.OAuthToken.AccessToken};{conn.OAuthToken.RefreshToken}";
      File.WriteAllText(@"Authentication.txt", strAuthen); 
    } 

Data Operations: Quering against the Data Source

The ADO.NET provider for QuickBooks OnlineData lets you query against the data source using standard SQL syntax. The provider supports most SQL functionalities, including join queries, functions, and summaries.

The basic CRUD operations, such as insertion, updating, and deleting data, can be performed either in the connected mode or disconnected mode by using the C1QuickBooksOnlineCommand and C1QuickBooksOnlineDataAdapter objects. The sample code below depicts the basic CRUD operations using the C1QuickBooksOnlineDataAdapter object:

//READ QUERY
using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) 
{ 
  //Populate DataTable 
  C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select Id, MetaData_CreateTime, MetaData_LastUpdatedTime, Category, Note from Attachables"); 

  //Fill DataTable
  DataTable dataTable = new DataTable(); 
  adapter.Fill(dataTable); 

  //Return DataTable 
  return dataTable; 
} 

//INSERT QUERY
using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) 
{ 
  //Populate DataTable 
  C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select Id, MetaData_CreateTime, MetaData_LastUpdatedTime, Category, Note from Attachables"); 
  DataTable dataTable = new DataTable(); 
  adapter.Fill(dataTable); 

  //Create Insert command 
  adapter.InsertCommand = new C1QuickBooksOnlineCommand(conn); 
  adapter.InsertCommand.CommandText = "Insert into Attachables(Id, MetaData_CreateTime, MetaData_LastUpdatedTime, Note, Category) Values(@Note, @Category)"; 
  adapter.InsertCommand.Parameters.Add("@Note", "Note"); 
  adapter.InsertCommand.Parameters.Add("@Category", "Category"); 

  adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; 

  //Insert new row 
  DataRow customerRow = dataTable.NewRow(); 
  customerRow["Note"] = uniqueNote; 
  customerRow["Category"] = "Document"; 
  dataTable.Rows.Add(customerRow); 

  //Update database 
  adapter.Update(dataTable); 
} 

//UPDATE QUERY 
using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) 
{
  //Populate DataTable 
  C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select Id, MetaData_CreateTime, MetaData_LastUpdatedTime, Category, Note from Attachables Where Category = 'Document'");
  DataTable dataTable = new DataTable(); 
  adapter.Fill(dataTable); 

  //Create Update command 
  adapter.UpdateCommand = new C1QuickBooksOnlineCommand(conn); 
  adapter.UpdateCommand.CommandText = "UPDATE Attachables SET Category = @Category WHERE Id = @Id"; 
  adapter.UpdateCommand.Parameters.Add("@Category", "Category"); 
  adapter.UpdateCommand.Parameters.Add("@Id", "Id"); 

  adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; 

  //Update existing row 
  DataRow customerRow = dataTable.Rows[0]; 
  customerRow["Category"] = "Signature"; 

  //Update database 
  adapter.Update(dataTable); 
} 

//DELETE QUERY 
using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) 
{ 
  //Populate Datatable 
  C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, $"Select Id, MetaData_CreateTime, MetaData_LastUpdatedTime, Category, Note from Attachables Where Note = '{uniqueNote}'"); 
  DataTable dataTable = new DataTable(); 
  adapter.Fill(dataTable); 

  //Create Delete command
  adapter.DeleteCommand = new C1QuickBooksOnlineCommand(conn); 
  adapter.DeleteCommand.CommandText = "Delete From Attachables Where Id = @Id"; 
  adapter.DeleteCommand.Parameters.Add("@Id", "Id"); 
  adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; 

  //Delete a row 
  DataRow customerRow = dataTable.Rows[0]; 
  customerRow.Delete(); 

  //Update Database 
  adapter.Update(dataTable); 
} 

Here is a quick example for fetching the data from QuickBooks Online using C1 QuickBooksOnline ADO.NET Provider and binding it to FlexGrid in a WinForms application.

Here is the sample code snippet implementing the same, assuming FlexGrid control has already been added to the Windows Form using designer:

  private void Form1_Load(object sender, EventArgs e) 
  { 
    LoadAuthentication(); 

    //Fetch Data 
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) 
    { 
      conn.OAuthTokenRefreshed += OAuthTokenRefreshed; 

      //Populate DataTable 
      C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select Id, MetaData_CreateTime, MetaData_LastUpdatedTime, Category, Note from Attachables"); 

      //Fill DataTable 
      DataTable dataTable = new DataTable();
      adapter.Fill(dataTable); 

      //Bind fetched data to FlexGrid 
      c1FlexGrid1.DataSource = dataTable; 

      //AutoSize columns to display data 
      c1FlexGrid1.AutoSizeCols(); 
   } 
} 

Here is a glimpse of FlexGrid after loading the fetched data:

Batch Update

The ADO.NET provider for QuickBooks Online lets you group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable and hence supports batch processing. The batch updates enhance the overall performance of data access and data update operation by preventing multiple requests to the server.

The UpdateBatchSize property must be set to an appropriate value to make sure that the commands are grouped in the batches of the specified size and sent to the server for processing.

Here is a sample code snippet depicting how to set the UpdateBatchSize property and perform batch update:

  using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) 
  { 
    //Populate DataTable 
    C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables Where Category = 'Document'"); 
    DataTable dataTable = new DataTable(); 
    adapter.Fill(dataTable); 

    //Create Update command 
    adapter.UpdateCommand = new C1QuickBooksOnlineCommand(conn); 
    adapter.UpdateCommand.CommandText = "UPDATE Attachables SET Category = @Category WHERE Id = @Id"; 
    adapter.UpdateCommand.Parameters.Add("@Category", "Category"); 
    adapter.UpdateCommand.Parameters.Add("@Id", "Id"); 

    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; 

    //Update existing rows 
    DataRow customerRow1 = dataTable.Rows[0]; 
    customerRow1["Category"] = "Signature"; 
    DataRow customerRow2 = dataTable.Rows[1]; 
    customerRow2["Category"] = "Signature"; 

    //Update database adapter.UpdateBatchSize = 2;
    adapter.Update(dataTable); 

    //Return DataTable 
    return dataTable; 
  } 

Caching

The performance of the ADO.NET provider for QuickBooks Online can further be enhanced by accessing the data from the cache when the user performs similar operations repeatedly. To support this type of functionality, the DataConnector library provides in-built caching support. We would need to configure the connection object to set the cache properties and enable cache for a connection.

The UseCache property must be set to true, while you can customize the default cache location by setting the CacheLocation property. The CacheTolerance property is used to set the cache tolerance time in seconds, with the default value set to 600 seconds.

Here is a sample code snippet that depicts how to enable caching by setting all the cache properties in the connection object:

  //Configure Connection string 
  C1QuickBooksOnlineConnectionStringBuilder builder = new C1QuickBooksOnlineConnectionStringBuilder 
  { 
    OAuthClientId = OAuthClientId, 
    OAuthClientSecret = OAuthClientSecret, 
    OAuthTokenEndpoint = OAuthTokenEndpoint, 
    OAuthAccessToken = OAuthAccessToken, 
    OAuthRefreshToken = OAuthRefreshToken, 
    CompanyId = CompanyId, //RealmId is equivalent to CompanyId 
    UseSandbox = true 
    UseCache = true; 
    CacheTolerance = 500; 
    CacheLocation = @"C:\temp\c1cache.db"; 
  }; 

  using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(builder.ConnectionString)) 
  { 
    conn.OAuthTokenRefreshed += OAuthTokenRefreshed; 

    Console.WriteLine("Start Time " + DateTime.Now); 
    conn.Open(); 
    C1QuickBooksOnlineCommand cmd = new C1QuickBooksOnlineCommand(conn, "Select * from Attachables'"); 
    var rdr = cmd.ExecuteReader(); 
    while (rdr.Read()) 
    { 
      Console.WriteLine("Read and cached the row with Id " + rdr["Id"]); 
    } 
            Console.WriteLine("End Time " + DateTime.Now); 
  } 

Incremental Caching

The ADO.NET provider for QuickBooks Online provides another caching strategy known as Incremental Caching, which updates the cache data periodically to add new records from the source entities without refreshing the complete cache data.

This technique relies on a unique field and a timestamp field which records the last time the record was updated, or a new record was inserted. Hence, any table which needs to implement incremental caching must have a Timestamp column, QuickBooks Online has a default timestamp column, "MetaData_LastUpdatedTime". The "IncrementalUpdate" and "IncrementalUpdateColumn" properties of C1QuickBooksOnlineConnectionStringBuilder class must be set to enable Incremental caching. For more details, refer to the documentation topic for Incremental caching.

The steps above have demonstrated how easy it is to connect to a new type of data source if you have a common interface based on a known data access technology such as ADO.NET. So, let's get our hands on something new by using something old.

For more detailed information, kindly refer to the DataConnector documentation.