DataConnector
一括処理
ADO.NET provider for QuickBooks Online > 一括処理

 The ADO.NET Provider for QuickBooks Online facilitates the execution of multiple operations with bulk data through C1QuickBooksOnlineDataAdapter. You can improve the process by executing many smaller batch requests. To control the size of each batch, you can set the C1QuickBooksOnlineDataAdapter's UpdateBatchSize property to a positive integer.

Steps to perform batch processing:

  1. In C1QuickBooksOnlineCommand class objects, define the custom SQL statements.
  2. Set the UpdatedRowSource and UpdateBatchSize property of the C1QuickBooksOnlineCommand object to "UpdateRowSource.None".
  3. Add the parameters to the command and assign the C1QuickBooksOnlineCommand class objects to the C1QuickBooksOnlineDataAdapter.
  4. Invoke the C1QuickBooksOnlineDataAdapter's Update method. Pass in a DataSet or DataTable containing your changes.

The provider translates all SQL queries in the batch into a single request. Below are the examples of different operations with bulk data.

Bulk Insert

The following code creates a batch that inserts data in bulk and retrieves the new data.

C#
コードのコピー 
static void BulkInsert()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //DataTable を設定します。
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        adapter.InsertCommand = new C1QuickBooksOnlineCommand(conn);
        adapter.InsertCommand.CommandText = "Insert into Attachables(Note, Category) Values(@Note, @Category)";
        adapter.InsertCommand.Parameters.Add("@Note", "Note");
        adapter.InsertCommand.Parameters.Add("@Category", "Category");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        //新しい行を挿入します。
        DataRow customerRow1 = dataTable.NewRow();
        customerRow1["Note"] = uniqueNote;
        customerRow1["Category"] = "Document";
        dataTable.Rows.Add(customerRow1);

        //新しい行を挿入します。
        DataRow customerRow2 = dataTable.NewRow();
        customerRow2["Note"] = uniqueNote;
        customerRow2["Category"] = "Other";
        dataTable.Rows.Add(customerRow2);

        //データベースを更新します。
        adapter.UpdateBatchSize = 2;
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Bulk Insert operation successful !!! \n \n");
        }
    }
}

Bulk Update

A batch update additionally requires the primary key of each row to update. The following code example prepares a batch that updates data in bulk.

C#
コードのコピー 
static void BulkUpdate()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //DataTable を設定します。
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables Where Category = 'Document'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Update コマンドを作成します。                
        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;

        // 既存の行を更新します。s
        DataRow customerRow1 = dataTable.Rows[0];
        customerRow1["Category"] = "Signature";

        DataRow customerRow2 = dataTable.Rows[1];
        customerRow2["Category"] = "Signature";

        //データベースを更新します。
        adapter.UpdateBatchSize = 2;
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Bulk Update operation successful !!! \n \n");
        }
    }
}

Bulk Delete

The following code example creates a batch that deletes data in bulk. The primary key for each row is required.

C#
コードのコピー 
static void BulkDelete()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        // データテーブルにデータを入力します。
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, $"Select * from Attachables Where Note = '{uniqueNote}'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Delete コマンドを作成します。
        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;

        //行を削除します。
        DataRow customerRow1 = dataTable.Rows[0];
        customerRow1.Delete();

        //行を削除します。
        DataRow customerRow2 = dataTable.Rows[1];
        customerRow2.Delete();

        //データベースを更新します。
        adapter.UpdateBatchSize = 2;
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Bulk Delete operation successful !!! \n \n");
        }
    }
}

 

 

Copyright © MESCIUS, Inc. All rights reserved.