# Batch Processing

## Content



The ADO.NET Provider for CSV facilitates the execution of multiple operations with bulk data through the [C1CSVDataAdapter](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.CSV/C1.AdoNet.CSV.C1CSVDataAdapter.html) class. The process can be improved by executing many smaller batch requests. The size of each batch can be controlled by setting the **UpdateBatchSize** property to a positive integer.

**Steps to perform batch processing**:

1.  In [C1CSVCommand](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.CSV/C1.AdoNet.CSV.C1CSVCommand.html) class objects, define the custom SQL statements.
2.  Set the [UpdatedRowSource](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.CSV/C1.AdoNet.CSV.C1CSVCommand.UpdatedRowSource.html) property of the **C1CSVCommand** object to "UpdateRowSource.None".
3.  Assign the **C1CSVCommand** class objects to the **C1CSVDataAdapter** and add the parameters to the command.
4.  Invoke the C1CSVDataAdapter's **Update** method. Pass in 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** creates a batch that inserts data in bulk.

**csharp**

```csharp
static void BulkInsert()
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    using (C1CSVConnection con = new C1CSVConnection(csvConnectionString))
    {
        // Populate Datatable
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(con, "SELECT * FROM sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Create Insert command
        adapter.InsertCommand = new C1CSVCommand(con);
        adapter.InsertCommand.CommandText = "INSERT INTO sampleCSV
                (id,year,industry_code,industry_name,rme_size_grp,variable,value,unit) VALUES
                (@id,@year, @industry_code, @industry_name, @rme_size_grp, @variable, @value, @unit)";
        // Add query parameters
        adapter.InsertCommand.Parameters.Add("@id", "id");
        adapter.InsertCommand.Parameters.Add("@year", "year");
        adapter.InsertCommand.Parameters.Add("@industry_code", "industry_code");
        adapter.InsertCommand.Parameters.Add("@industry_name", "industry_name");
        adapter.InsertCommand.Parameters.Add("@rme_size_grp", "rme_size_grp");
        adapter.InsertCommand.Parameters.Add("@variable", "variable");
        adapter.InsertCommand.Parameters.Add("@value", "value");
        adapter.InsertCommand.Parameters.Add("@unit", "unit");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        // Insert one row
        DataRow sampleCSVRow1 = dataTable.NewRow();
        sampleCSVRow1["id"] = 101;
        sampleCSVRow1["year"] = 2021;
        sampleCSVRow1["industry_code"] = "A";
        sampleCSVRow1["industry_name"] = "Agriculture, Forestry and Fishing";
        sampleCSVRow1["rme_size_grp"] = "c_6-9";
        sampleCSVRow1["variable"] = "Total assets";
        sampleCSVRow1["value"] = "45";
        sampleCSVRow1["unit"] = "DOLLARS(millions)";
        dataTable.Rows.Add(sampleCSVRow1);
        // Insert another row
        DataRow sampleCSVRow2 = dataTable.NewRow();
        sampleCSVRow2["id"] = 102;
        sampleCSVRow2["year"] = 2021;
        sampleCSVRow2["industry_code"] = "B";
        sampleCSVRow2["industry_name"] = "Mining";
        sampleCSVRow2["rme_size_grp"] = "a_0";
        sampleCSVRow2["variable"] = "Total income";
        sampleCSVRow2["value"] = "10";
        sampleCSVRow2["unit"] = "DOLLARS(millions)";
        dataTable.Rows.Add(sampleCSVRow2);
        // Set batch size and update database
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        Console.WriteLine("Bulk insert successful! \n\n");
    }
}
```

### Bulk Update

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

**csharp**

```csharp
static void BulkUpdate()
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    using (C1CSVConnection con = new C1CSVConnection(csvConnectionString))
    {
        // Populate Datatable               
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(con, "SELECT * FROM sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Create Update query
        adapter.UpdateCommand = new C1CSVCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE sampleCSV SET year=@year WHERE id=@Id";
        adapter.UpdateCommand.Parameters.Add("@year", "year");
        adapter.UpdateCommand.Parameters.Add("@Id", "id");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        // Perform Update operation
        DataRow sampleCSVRow1 = dataTable.Rows[0];               
        sampleCSVRow1["year"] = 2020;
        sampleCSVRow1["id"] = 101;
        DataRow sampleCSVRow2 = dataTable.Rows[1];               
        sampleCSVRow2["year"] = 2021;
        sampleCSVRow2["id"] = 102;
        // Set batch size and update database
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        Console.WriteLine("Bulk update successful! \n\n");
    }
}
```

### Bulk Delete

The **following** **code** creates a batch that deletes data in bulk (the primary key for each row is required).

**csharp**

```csharp
static void Bulk_Delete()
{
    string csvConnectionString = string.Format(@"Uri='sampleCSV.csv';Trim Values=true");
    using (C1CSVConnection con = new C1CSVConnection(csvConnectionString))
    {
        // Populate Datatable  
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(con, "SELECT * FROM sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Create Delete command
        adapter.DeleteCommand = new C1CSVCommand(conn);
        adapter.DeleteCommand.CommandText = "DELETE FROM sampleCSV WHERE id=@Id";
        adapter.DeleteCommand.Parameters.Add("@Id", "80");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
        // Perform delete operation
        DataRow sampleCSVRow1 = dataTable.Rows[0];
        sampleCSVRow1.Delete();
        DataRow sampleCSVRow2 = dataTable.Rows[1];             
        sampleCSVRow2.Delete();
        // Set batch size and update database
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        Console.WriteLine("Bulk delete successful! \n\n");
    }
}
```