Getting Started

In This Topic

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:

Open Visual Studio.
Select Create a new project from the Get Started pane.
In the Create a new project window, select Console Application and click Next, as in the screenshot below:
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

From the Project menu, select Manage NuGet Packages. The NuGet Package Manager appears.
In the NuGet Package Manager, click the Package source drop-down and select nuget.org.
In the left pane of the Browse tab select C1.AdoNet.QuickBooksOnline.
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.

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.

C#	Copy Code
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); }

Copy Code

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);
}

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.

C#	Copy 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 !!!"); }

Copy 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 !!!");
}