DataConnector
一括処理
ADO.NET provider for OData > 一括処理

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 the examples of different operations with bulk data.

Bulk Insert

The code example below creates a batch that inserts data in bulk:

C#
コードのコピー 
using (var con = new C1ODataConnection(ODataServerConnectionString))
{
    // データテーブルにデータを入力します。
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

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

    // 挿入操作を実行します。
    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);

    // バッチ サイズを設定し、データ ソースを更新します。
    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):

C#
コードのコピー 
using (var conn = new C1ODataConnection(ODataServerConnectionString))
{
    // データテーブルにデータを入力します。
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

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

    // 更新操作を実行します。
    DataRow bookRow1 = dataTable.Rows[0];
    bookRow1["Title"] = "XCatq 2007";
    DataRow bookRow2 = dataTable.Rows[4];
    bookRow2["Title"] = "XMatqa 2020";

    // バッチ サイズを設定し、データソースを更新します。
    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):

C#
コードのコピー 
using (var conn = new C1ODataConnection(ODataServerConnectionString))
{
    // データテーブルにデータを入力します。
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

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

    // 削除操作を実行します。
    DataRow bookRow1 = dataTable.Rows[13];
    bookRow1.Delete();
    DataRow bookRow2 = dataTable.Rows[14];
    bookRow2.Delete();

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

 

 

 

Copyright © MESCIUS, Inc. All rights reserved.