Getting Started

The ADO.NET provider for QuickBooks Online provides a wide range of features that enable connectivity to QuickBooks Online from .Net applications. The documentation will help you understand the C1.AdoNet.QuickbooksOnline namespace, which includes all the available classes that can be used to connect and retrieve data from QuickBooks Online.

DataConnectors are mostly used in combination with other ComponentOne components, such as DataEngine and FlexPivot. The procedure below describes how to use the DataConnector in a console application within Visual Studio.

How to create a new Console App

The ADO.NET provider for QuickBooks Online can be used in any application. In this guide, a console application is created:

1. Open Visual Studio.
2. Select Create a new project from the Get Started pane.
3. In the Create a new project window, select Console Application and click Next, as in the screenshot below:
4. In the Configure your new project window, write your project name, choose a location to save your project, and click Create.

How to add the NuGet packages

1. From the Project menu, select Manage NuGet Packages. The NuGet Package Manager appears.
2. In the NuGet Package Manager, click the Package source drop-down and select nuget.org.
3. In the left pane of the Browse tab select C1.AdoNet.QuickBooksOnline.
4. In the right pane of the Browse tab, click Install to add the reference to the package.
   

How to use ADO.Net provider for QuickBooks Online to retrieve data

Follow the steps provided below to learn and implement data retrieval using ADO.NET provider for QuickBooks Online.

1. Create a reference to the QuickBooks Online service URL and a platform for unlinked servers and services to allow authenticated access to the data sources without sharing private credentials using OAuth Authorization.

   static string CompanyId = "*******";
   static string OAuthClientId = @"*****";
   static string OAuthClientSecret = @"******";
   static string OAuthAccessToken = @"*******";
   static string OAuthRefreshToken = @"*******";
   static string MinorVersion = "**";
   static string connectionString => $"Company Id={CompanyId};Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};"         + "OAuth Access Token={OAuthAccessToken};OAuth Refresh Token={OAuthRefreshToken}; Minor Version={MinorVersion}";      
   //Set values for OAuthAccessToken and OaAuthRefreshToken
   private static void LoadAuthentication()
   {
       try
       {
           var arrAuth = File.ReadAllText(@"Authentication.txt").Split(';');
           if (arrAuth[0] == CompanyId)
           {
               OAuthAccessToken = arrAuth[1];
               OAuthRefreshToken = arrAuth[2];
           }
       }
       catch { }
   }
   //Handle OAuthTokenRefreshed event to fetch the new access token
   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);
   }
   

2. In the next step, C1QuickBooksOnlineDataAdapter is used to retrieve the data. C1QuickBooksOnlineDataAdapter objects retrieve a single result set of all the data that matches a query. For more details see Creating Connection. C1QuickBooksOnlineConnection implements the ADO.NET DbConnection, similar to the standard ADO.NET connection object. Once the connection is established, the adapter's Fill method is used to retrieve the data from the source as shown in the following code.
   

   //Setup Connection
   using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
   {
       conn.OAuthTokenRefreshed += OAuthTokenRefreshed;
       //Command to fetch data from DataTable
       C1QuickBooksOnlineCommand comm = new C1QuickBooksOnlineCommand(conn, "Select * from Customers");
       C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(comm);
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
       //Display fetched data
       foreach (DataRow row in dataTable.Rows)
       {
           Console.WriteLine("{0}\t{1}\t{2}", row["CompanyName"], row["DisplayName"], row["Active"]);
       }
       Console.WriteLine("Connection created and read operation completed !!!");
   }