Batch Processing

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

Steps to perform batch processing:

1. In C1ODataCommand class objects, define the custom SQL statements.
2. Set the UpdatedRowSource property of the C1ODataCommand object to "UpdateRowSource.None".
3. Assign the C1ODataCommand class objects to the C1ODataDataAdapter and add the parameters to the command.
4. Invoke the C1ODataDataAdapter's Update method, passing a DataSet or DataTable containing the 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 code example below creates a batch that inserts data in bulk:

using (var con = new C1ODataConnection(ODataServerConnectionString))
{
    // Populate Datatable
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);
    // Create Insert Command
    adapter.InsertCommand = new C1ODataCommand(conn);
    adapter.InsertCommand.CommandText =
        "INSERT INTO books (ISBN, Title, Author, Price)
        VALUES (@ISBN, @Title, @Author, @Price)";
    adapter.InsertCommand.Parameters.Add("@Title", "Title");
    adapter.InsertCommand.Parameters.Add("@ISBN", "ISBN");
    adapter.InsertCommand.Parameters.Add("@Author", "Author");
    adapter.InsertCommand.Parameters.Add("@Price", "Price");
    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    // Perform Insert operation
    DataRow bookRow1 = dataTable.NewRow();
    bookRow1["Title"] = "Tama 2007";
    bookRow1["ISBN"] = "306-0-355-48016-0";
    bookRow1["Author"] = "CK";
    bookRow1["Price"] = 200.20;
    dataTable.Rows.Add(bookRow1);
    DataRow bookRow2 = dataTable.NewRow();
    bookRow2["Title"] = "Teachers Guide 2007";
    bookRow2["ISBN"] = "306-0-355-48016-0";
    bookRow2["Author"] = "MR Mk";
    bookRow2["Price"] = 260.20;
    dataTable.Rows.Add(bookRow2);
    // Set batch size and update data source
    adapter.UpdateBatchSize = 2;
    adapter.Update(dataTable);
    Console.WriteLine("Bulk insert successful! \n\n");
}

Bulk Update

The code example below prepares a batch that updates data in bulk (the primary key for each row is required):

using (var conn = new C1ODataConnection(ODataServerConnectionString))
{
    // Populate Datatable
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);
    // Create Update Command
    adapter.UpdateCommand = new C1ODataCommand(conn);
    adapter.UpdateCommand.CommandText = "UPDATE Books SET Title = @Title WHERE id = @Id";
    adapter.UpdateCommand.Parameters.Add("@Title", "Title");
    adapter.UpdateCommand.Parameters.Add("@Id", "Id");
    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
    // Perform Update operation
    DataRow bookRow1 = dataTable.Rows[0];
    bookRow1["Title"] = "XCatq 2007";
    DataRow bookRow2 = dataTable.Rows[4];
    bookRow2["Title"] = "XMatqa 2020";
    // Set batch size and update data source
    adapter.UpdateBatchSize = 2;
    adapter.Update(dataTable);
    Console.WriteLine("Bulk update successful! \n\n");
}

Bulk Delete

The code example below creates a batch that deletes data in bulk (the primary key for each row is required):

using (var conn = new C1ODataConnection(ODataServerConnectionString))
{
    // Populate Datatable
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);
    // Create Delete Command
    adapter.DeleteCommand = new C1ODataCommand(conn);
    adapter.DeleteCommand.CommandText = "DELETE FROM Books WHERE id = @Id";
    adapter.DeleteCommand.Parameters.Add("@Id", "Id");
    adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
    // Perform Delete operation
    DataRow bookRow1 = dataTable.Rows[13];
    bookRow1.Delete();
    DataRow bookRow2 = dataTable.Rows[14];
    bookRow2.Delete();
    // Set batch size and update data source
    adapter.UpdateBatchSize = 2;
    adapter.Update(dataTable);
    Console.WriteLine("Bulk delete successful! \n\n");
}