Batch Processing

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 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.

static void BulkInsert()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate DataTable
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Insert command
        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;
        //Insert new row
        DataRow customerRow1 = dataTable.NewRow();
        customerRow1["Note"] = uniqueNote;
        customerRow1["Category"] = "Document";
        dataTable.Rows.Add(customerRow1);
        //Insert new row
        DataRow customerRow2 = dataTable.NewRow();
        customerRow2["Note"] = uniqueNote;
        customerRow2["Category"] = "Other";
        dataTable.Rows.Add(customerRow2);
        //Update database
        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.

static void BulkUpdate()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate DataTable
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables Where Category = 'Document'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Update command                
        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;
        //Update existing rows
        DataRow customerRow1 = dataTable.Rows[0];
        customerRow1["Category"] = "Signature";
        DataRow customerRow2 = dataTable.Rows[1];
        customerRow2["Category"] = "Signature";
        //Update database
        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.

static void BulkDelete()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate Datatable
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, $"Select * from Attachables Where Note = '{uniqueNote}'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Delete command
        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;
        //Delete a row
        DataRow customerRow1 = dataTable.Rows[0];
        customerRow1.Delete();
        //Delete a row
        DataRow customerRow2 = dataTable.Rows[1];
        customerRow2.Delete();
        //Update Database
        adapter.UpdateBatchSize = 2;
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Bulk Delete operation successful !!! \n \n");
        }
    }
}