Batch Processing

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

Steps to perform batch processing:

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

static void InsertBulk()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        // Populate Data
        var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10");
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Create Insert Command
        adapter.InsertCommand = new C1D365SCommand(conn);
        adapter.InsertCommand.CommandText = "INSERT INTO accounts (accountid, name) VALUES (@accountid,@name)";
        adapter.InsertCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));
        adapter.InsertCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        // Perform Insert operation
        DataRow AccountRow1 = dataTable.NewRow();
        AccountRow1["accountid"] = Guid.NewGuid();
        AccountRow1["name"] = "Rajesh";
        dataTable.Rows.Add(AccountRow1);
        DataRow AccountRow2 = dataTable.NewRow();
        AccountRow2["accountid"] = Guid.NewGuid();
        AccountRow2["name"] = "Robert";
        dataTable.Rows.Add(AccountRow2);
        // Set batch size and update data source
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        Console.WriteLine("Bulk insert successful! \n\n");
    }
}

Bulk Update

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

static void UpdateBulk()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        // Populate Datatable
        var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10");
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Create Update Command
        adapter.UpdateCommand = new C1D365SCommand(conn, "UPDATE accounts SET name = @upname WHERE accountid = @accountid");
        adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@upname", DbType.String, "name"));
        adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        // Perform Update operation
        DataRow AccountRow1 = dataTable.Rows[0];
        AccountRow1["name"] = "Julia";
        DataRow AccountRow2 = dataTable.Rows[6];
        AccountRow2["name"] = "Nick";
        // Set batch size and update data source
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        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 DeleteBulk()
{
    using (var conn = new C1D365SConnection(connectionString))
    {
        conn.Open();
        // Populate Datatable
        var adapter = new C1D365SDataAdapter(conn, "SELECT * FROM accounts LIMIT 10");
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Create Delete Command
        adapter.DeleteCommand = new C1D365SCommand(conn);
        adapter.DeleteCommand.CommandText = "DELETE FROM accounts WHERE name = @name";
        adapter.DeleteCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
        // Perform Delete operation
        DataRow AccountRow1 = dataTable.Rows[3];
        AccountRow1.Delete();
        DataRow AccountRow2 = dataTable.Rows[5];
        AccountRow2.Delete();
        // Set batch size and update data source
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        Console.WriteLine("Bulk delete successful! \n\n");
    }
}