Batch Processing

In This Topic

Bulk Insert
Bulk Update
Bulk Delete

The ADO.NET Provider for CSV facilitates the execution of multiple operations with bulk data through the C1CSVDataAdapter 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:

In C1CSVCommand class objects, define the custom SQL statements.
Set the UpdatedRowSource property of the C1CSVCommand object to "UpdateRowSource.None".
Assign the C1CSVCommand class objects to the C1CSVDataAdapter and add the parameters to the command.
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.

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

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

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");
    }
}