一括処理

The ADO.NET Provider for CSV facilitates the execution of multiple operations with bulk data through 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 the 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))
    {
        // DataTable を設定します。
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(con, "SELECT * FROM sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Insertコマンドを作成します。
        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)";
        // クエリパラメータを追加します。
        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;

        // 1 行を挿入します。
        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);
        // 別の行を挿入します。
        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);

        // バッチ サイズを設定し、データベースを更新します。
        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))
    {
        // DataTable を設定します。               
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(con, "SELECT * FROM sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Update クエリを作成します。
        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;

        // 更新操作を実行します。
        DataRow sampleCSVRow1 = dataTable.Rows[0];               
        sampleCSVRow1["year"] = 2020;
        sampleCSVRow1["id"] = 101;

        DataRow sampleCSVRow2 = dataTable.Rows[1];               
        sampleCSVRow2["year"] = 2021;
        sampleCSVRow2["id"] = 102;

        // バッチ サイズを設定し、データベースを更新します。
        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))
    {
        // DataTable を設定します。  
        C1CSVDataAdapter adapter = new C1CSVDataAdapter(con, "SELECT * FROM sampleCSV");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Delete コマンドを作成します。
        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;

        // 削除操作を実行します。
        DataRow sampleCSVRow1 = dataTable.Rows[0];
        sampleCSVRow1.Delete();
        DataRow sampleCSVRow2 = dataTable.Rows[1];             
        sampleCSVRow2.Delete();

        // バッチ サイズを設定し、データベースを更新します。
        adapter.UpdateBatchSize = 2;
        adapter.Update(dataTable);
        Console.WriteLine("Bulk delete successful! \n\n");
    }
}