Batch Processing

The ADO.NET Provider for Kintone facilitates the execution of multiple operations with bulk data through C1KintoneDataAdapter. The process can be improved by executing many smaller batch requests. The size of each batch can be controlled by setting the C1KintoneDataAdapter's UpdateBatchSize property to a positive integer.

Steps to perform batch processing:

1. In C1KintoneCommand class objects, define the custom SQL statements.
2. Set the UpdatedRowSource property of the C1KintoneCommand object to "UpdateRowSource.None".
3. Assign the C1KintoneCommand class objects to the C1KintoneDataAdapter and add the parameters to the command.
4. Invoke the C1KintoneDataAdapter'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 example creates a batch that inserts data in bulk and retrieves the new data.

static void BulkInsert()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate Datatable
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Insert Query
        adapter.InsertCommand = new C1KintoneCommand(conn);
        adapter.InsertCommand.CommandText = "INSERT INTO Products(ProductName, UnitPrice) VALUES(@ProductName, @UnitPrice)";
        adapter.InsertCommand.Parameters.Add("@ProductName", "ProductName");//Query Parametres
        adapter.InsertCommand.Parameters.Add("@UnitPrice", "UnitPrice");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        //Perform insert operation
        DataRow productRow1 = dataTable.NewRow();
        productRow1["ProductName"] = "Product 1";
        productRow1["UnitPrice"] = 200;
        dataTable.Rows.Add(productRow1);
        DataRow productRow2 = dataTable.NewRow();
        productRow2["ProductName"] = "Product 2";
        productRow2["UnitPrice"] = 300;
        dataTable.Rows.Add(productRow2);
        //Update database
        adapter.Update(dataTable);
        Console.WriteLine("Bulk insert 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()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate Datatable               
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Update Query
        adapter.UpdateCommand = new C1KintoneCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE Products SET UnitPrice = @UnitPrice WHERE ProductName = @ProductName";//Update Query
        adapter.UpdateCommand.Parameters.Add("@UnitPrice", "UnitPrice");
        adapter.UpdateCommand.Parameters.Add("@ProductName", "ProductName");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        //Perform Update operation
        DataRow productRow1 = dataTable.Rows[0];
        productRow1["UnitPrice"] = 1000;
        productRow1["ProductName"] = "Product 1";
        DataRow productRow2 = dataTable.Rows[1]; 
        productRow2["UnitPrice"] = 2000;
        productRow2["ProductName"] = "Product 2";
        DataRow productRow3 = dataTable.Rows[2];
        productRow3["UnitPrice"] = 3000;
        productRow3["ProductName"] = "Product 3";
        //Update database
        adapter.Update(dataTable);
        Console.WriteLine("Bulk update 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 (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate Datatable  
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Delete command
        adapter.DeleteCommand = new C1KintoneCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from Products where Name = @Name";
        adapter.DeleteCommand.Parameters.Add("@Name", "Name");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
        //Perform delete operation
        DataRow productRow1 = dataTable.Rows[13];
        productRow1.Delete();
        DataRow productRow2 = dataTable.Rows[14];
        productRow2.Delete();
        //Update database
        adapter.Update(dataTable);
        Console.WriteLine("Bulk delete successful !!! \n \n");
    } 
}