Connecting C# to QuickBooks With Entity Framework Core

QuickBooks Online is a cloud-based financial management solution designed for invoicing, expense tracking, and financial reporting tasks. It is a popular solution for businesses that seek better accessibility to accounting data and real-time collaboration. However, reliable and efficient connectivity becomes essential for retrieving and modifying data when third-party or integrated applications use QuickBooks Online as a data source.

One effective solution is dotConnect for QuickBooks Online, a data provider with built-in support for Entity Framework Core. It addresses common connectivity challenges and simplifies data access. In this article, we'll demonstrate how to connect to QuickBooks Online using EF Core and dotConnect, build a C# project, and perform everyday data operations.

Why dotConnect for QuickBooks Online?

dotConnect for QuickBooks Online is a high-performance ADO.NET data provider that helps access and manage QuickBooks data through ADO.NET or using ORM.

The dotConnect product line uses the standard, familiar ADO.NET classes, thus allowing the users to get started quickly without studying any specifics or APIs of data sources. dotConnect grants easy and secure connections, advanced ORM support, broad compatibility with .NET, and full support for SQL queries.

Prerequisites

To get started, you will need the following prerequisites:

• Visual Studio 2022: The most comprehensive IDE for .NET and C++ developers on Windows. If you don't have it on your machine, download and install the free Community Edition.
• QuickBooks Online account: You will need credentials for an active QuickBooks account to which you will connect.
• dotConnect for QuickBooks Online: Our data provider will help you connect to your QuickBooks account. You can download it from our website or install it as a NuGet package in Visual Studio.

Download and activate dotConnect for QuickBooks Online

30-day free trial

You can download and install dotConnect for QuickBooks Online directly from our website. Alternatively, you can install the Devart.Data.QuickBooks NuGet package in Visual Studio.

No license key is required, and you can start exploring the product immediately.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

To activate a connection in your application, add the License Key to your connection string.

Create a C# project

Open Visual Studio and select Create a new project. Select the Console App (.NET Framework) template for C# and click Next.

Give the project a name and specify a path to the directory to store it. Click Create.

Install dotConnect for QuickBooks Online via the NuGet Package Manager. You can do it directly in Visual Studio. Right-click Solution Explorer > Manage NuGet Packages….

In the NuGet Package Manager, find Devart.Data.QuickBooks.EFCore and install it for your project.

Set up a QuickBooks workspace and create an app

Go to Intuit Developer, log in to your account, and create a new workspace if you don't have one.

Next, create a new app in your workspace.

Give this app the desired name.

Once the app is ready, you can obtain the connection credentials.

Get QuickBooks credentials

In your OAuth 2.0 Playground, select the necessary workspace and the app.

Next, you will see your Client ID and Client Secret. Click Get authorization code.

You will be prompted to approve the connection to your application. To do that, click Connect.

Click Get tokens.

Finally, get the refresh token.

For more detailed information, refer to Intuit's Get started page.

View QuickBooks data using Server Explorer

In the Server Explorer, right-click Data Connections and select Add Connection...

In the Choose Data Source window, select dotConnect for QuickBooks from the list of installed data providers. Click Continue.

In the Add Connection window, click Web login and enter the Company ID and Access Token.

Click Test Connection to verify the connection settings. Now you can connect to QuickBooks Online.

Expand the newly created data connection to view the database structure in the Server Explorer. Then expand the Tables node to see a list of tables in the database. Further expand any table to view its columns.

To fetch data from a table, right-click the table name and select Retrieve Data from Table. A query window opens with a SELECT statement to retrieve the necessary data portion from the table.

Create an EF Core model

After configuring the database, we can move on to the next stage and create an EF Core model. We can do this in two ways: with Scaffold-DbContext or via Entity Developer.

Create an EF Core model using Scaffold-DbContext

Check the EF Core Tools that you need to have installed. To install them globally, run the following command in the .NET command-line interface (CLI):

dotnet tool install --global dotnet-ef

Install Scaffold-DbContext using Package Manager Console. In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console and run the following command:

Install-Package Microsoft.EntityFrameworkCore.Tools

When the package is installed, you can use Scaffold-DbContext to generate DbContext and entity classes. Run the below command in the Package Manager Console, replacing values with your actual credentials:

Scaffold-DbContext "Sandbox=true;Company Id=**********;Refresh Token=**********;Client Id=**********;Client Secret=**********;License Key=**********;" -provider Devart.Data.QuickBooks.EFCore -OutputDir Models

It generates the ModelContext file and the Models folder that contains the table entity classes, which you will see in the Solution Explorer. Therefore, the DbContext classes are successfully created.

Create an EF Core model via Entity Developer

Another approach to creating EF Core models is with the help of Entity Developer - a powerful ORM designer with both the model-first and database-first approaches. It provides excellent visualization capacities and integrates flawlessly with Visual Studio.

Right-click the Solution Explorer and select Add > New Item.

Go to Installed > C# Items > Data, select Devart EF Core Model, and click Add.

In the Create Model Wizard, select Database First and click Next.

Provide the connection details for QuickBooks Online and click Next.

Select Generate From Database and click Next.

Choose the database objects you want to scaffold. You can select them all, but since we've been using the Customer and Invoice tables, let's choose them.

After that, you need to define the naming conventions for the property names in the data source object and click Next. It is recommended that you use the default settings.

In the Model properties window, configure the necessary settings.

Choose Code Generation Templates for the model. We will use the default settings in this tutorial. Click Next.

Your model is ready now. Click Finish to open the created model.

The DbContext class is successfully created as well.

Now, we can proceed to performing data-related operations in our application.

Read data from QuickBooks

This tutorial assumes that the Customer table with data exists in your QuickBooks Online account. Adjust the field names and data types as necessary to match your actual data model, and use the code below to retrieve data from QuickBooks Online into your application.

using(var context = new QbModel()) {
  try {
    var customers = context.Customers
      .Select(c => new {
        DisplayName = c.DisplayName, // Use DisplayName instead of FullyQualifiedName
          CompanyName = c.CompanyName,
          PrimaryEmailAddrAddress = c.PrimaryEmailAddrAddress
      })
      .Take(10) // Limit the results to 10 customers
      .ToList();

    foreach(var customer in customers) {
      Console.WriteLine($"Display Name: {customer.DisplayName}");
      Console.WriteLine($"Company Name: {customer.CompanyName}");
      Console.WriteLine($"Email: {customer.PrimaryEmailAddrAddress}");
      Console.WriteLine(new string('-', 20));
    }
  } catch (Exception ex) {
    Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

You can see the data from the DisplayName, CompanyName, and PrimaryEmailAddrAddress fields retrieved and displayed for each customer successfully.

Insert new records into QuickBooks

In most applications, a common scenario involves inserting records into a database and displaying the results. The example below demonstrates how to add records and retrieve the updated data.

using(var context = new QbModel()) {
  try {
    // Check connection status
    var canConnect = context.Database.CanConnect();
    if (canConnect) {
      Console.WriteLine("Connection to QuickBooks Online was successful.");
    } else {
      Console.WriteLine("Failed to connect to QuickBooks Online.");
      return;
    }

    // Insert 5 new Customer records
    var newCustomers = new [] {
      new Customer {
        DisplayName = "Customer 1", CompanyName = "Company A", PrimaryEmailAddrAddress = "[email protected]"
      },
      new Customer {
        DisplayName = "Customer 2", CompanyName = "Company B", PrimaryEmailAddrAddress = "[email protected]"
      },
      new Customer {
        DisplayName = "Customer 3", CompanyName = "Company C", PrimaryEmailAddrAddress = "[email protected]"
      },
      new Customer {
        DisplayName = "Customer 4", CompanyName = "Company D", PrimaryEmailAddrAddress = "[email protected]"
      },
      new Customer {
        DisplayName = "Customer 5", CompanyName = "Company E", PrimaryEmailAddrAddress = "[email protected]"
      }
    };

    context.Customers.AddRange(newCustomers);
    context.SaveChanges();

    Console.WriteLine("5 new customer records inserted successfully.");

    // Retrieve and display the inserted records
    var insertedCustomers = context.Customers
      .Where(c => newCustomers.Select(nc => nc.DisplayName).Contains(c.DisplayName))
      .Select(c => new {
        DisplayName = c.DisplayName,
          CompanyName = c.CompanyName,
          PrimaryEmailAddrAddress = c.PrimaryEmailAddrAddress
      })
      .ToList();

    foreach(var customer in insertedCustomers) {
      Console.WriteLine($"Display Name: {customer.DisplayName}");
      Console.WriteLine($"Company Name: {customer.CompanyName}");
      Console.WriteLine($"Email: {customer.PrimaryEmailAddrAddress}");
      Console.WriteLine(new string('-', 20));
    }
  } catch (Exception ex) {
    Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

This way, we have created and added five new customer records to the Customers DbSet using the AddRange method. The SaveChanges method is called to push these changes to the database.

We can see the new data in the application:

Update QuickBooks data

Let's update the existing records with DisplayName values Customer 1, Customer 2, and so on. We need to modify the DisplayName and CompanyName fields.

using(var context = new QbModel()) {
  try {
    // Check connection status
    var canConnect = context.Database.CanConnect();
    if (canConnect) {
      Console.WriteLine("Connection to QuickBooks Online was successful.");
    } else {
      Console.WriteLine("Failed to connect to QuickBooks Online.");
      return;
    }

    // Update the specific records
    var customersToUpdate = context.Customers
      .Where(c => new [] {
          "Customer 1",
          "Customer 2",
          "Customer 3",
          "Customer 4",
          "Customer 5"
        }
        .Contains(c.DisplayName))
      .ToList();

    foreach(var customer in customersToUpdate) {
      customer.DisplayName = $"{customer.DisplayName} Updated";
      customer.CompanyName = $"{customer.CompanyName} Updated";
    }

    context.SaveChanges();
    Console.WriteLine("Customer records updated successfully.");

    // Retrieve and display the updated records
    var updatedCustomers = context.Customers
      .Where(c => new [] {
          "Customer 1 Updated",
          "Customer 2 Updated",
          "Customer 3 Updated",
          "Customer 4 Updated",
          "Customer 5 Updated"
        }
        .Contains(c.DisplayName))
      .Select(c => new {
        DisplayName = c.DisplayName,
          CompanyName = c.CompanyName,
          PrimaryEmailAddrAddress = c.PrimaryEmailAddrAddress
      })
      .ToList();

    foreach(var customer in updatedCustomers) {
      Console.WriteLine($"Display Name: {customer.DisplayName}");
      Console.WriteLine($"Company Name: {customer.CompanyName}");
      Console.WriteLine($"Email: {customer.PrimaryEmailAddrAddress}");
      Console.WriteLine(new string('-', 20));
    }
  } catch (Exception ex) {
    Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

We filter the records by using the Updated value in the DisplayName and CompanyName fields. Call the SaveChanges method to save the updates in the database.

Delete data from QuickBooks

Assume we need to delete the last added records from the database. For that, we use the RemoveRange method.

Modify the Program.cs as shown below:

using(var context = new QbModel()) {
  try {
    // Check connection status
    var canConnect = context.Database.CanConnect();
    if (canConnect) {
      Console.WriteLine("Connection to QuickBooks Online was successful.");
    } else {
      Console.WriteLine("Failed to connect to QuickBooks Online.");
      return;
    }

    // Find and delete the specific records
    var customersToDelete = context.Customers
      .Where(c => new [] {
          "Customer 1 Updated",
          "Customer 2 Updated",
          "Customer 3 Updated",
          "Customer 4 Updated",
          "Customer 5 Updated"
        }
        .Contains(c.DisplayName))
      .ToList();

    context.Customers.RemoveRange(customersToDelete);
    context.SaveChanges();

    Console.WriteLine("Customer records deleted successfully.");

    // Optionally, verify deletion by attempting to retrieve the deleted records
    var deletedCustomers = context.Customers
      .Where(c => new [] {
          "Customer 1 Updated",
          "Customer 2 Updated",
          "Customer 3 Updated",
          "Customer 4 Updated",
          "Customer 5 Updated"
        }
        .Contains(c.DisplayName))
      .ToList();

    if (!deletedCustomers.Any()) {
      Console.WriteLine("Verified: The specified customer records have been deleted.");
    } else {
      Console.WriteLine("Some records were not deleted.");
    }
  } catch (Exception ex) {
    Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

When we execute this code, it deletes specified customer records and verifies the results.

Conclusion

Using QuickBooks Online as a data source for C# applications is a popular choice. However, the effectiveness of any application depends on reliable data connectivity, ensuring correct connections and easy access to retrieve, update, and manage data stored in that source.

dotConnect for QuickBooks Online provides a direct connection to this cloud service without additional clients or libraries. It enables you to develop C# applications using ADO.NET and EF Core, delivering fast, stable, and smooth data access.

You can try dotConnect for QuickBooks Online with a fully functional 30-day trial, letting you test its features in real-world scenarios under full workload. To help you get started as quickly and easily as possible, we suggest you refer to the documentation, which is replete with useful tips and walkthroughs.