はじめに

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 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 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#	コードのコピー
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}"; //OAuthAccessToken と OaAuthRefreshToken の値を設定します。 private static void LoadAuthentication() { try { var arrAuth = File.ReadAllText(@"Authentication.txt").Split(';'); if (arrAuth[0] == CompanyId) { OAuthAccessToken = arrAuth[1]; OAuthRefreshToken = arrAuth[2]; } } catch { } } //OAuthTokenRefreshed イベントを処理して、新しいアクセス用のトークンを取得します。 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); }

コードのコピー

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

//OAuthAccessToken と OaAuthRefreshToken の値を設定します。
private static void LoadAuthentication()
{
    try
    {
        var arrAuth = File.ReadAllText(@"Authentication.txt").Split(';');
        if (arrAuth[0] == CompanyId)
        {
            OAuthAccessToken = arrAuth[1];
            OAuthRefreshToken = arrAuth[2];
        }
    }
    catch { }
}
        
//OAuthTokenRefreshed イベントを処理して、新しいアクセス用のトークンを取得します。
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 standard ADO.NET connection object. Once the connection is established, adapter's Fill method is used to retrieve the data from the source as shown in the following code.

C#	コードのコピー
//接続を設定します。 using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) { conn.OAuthTokenRefreshed += OAuthTokenRefreshed; //DataTable からデータを取得するコマンド。 C1QuickBooksOnlineCommand comm = new C1QuickBooksOnlineCommand(conn, "Select * from Customers"); C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(comm); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); //取得したデータを表示します。 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 !!!"); }

コードのコピー

//接続を設定します。
using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
{
    conn.OAuthTokenRefreshed += OAuthTokenRefreshed;

    //DataTable からデータを取得するコマンド。
    C1QuickBooksOnlineCommand comm = new C1QuickBooksOnlineCommand(conn, "Select * from Customers");

    C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(comm);
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);

    //取得したデータを表示します。
    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 !!!");
}