一括処理

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

Steps to perform batch processing:

In C1JsonCommand class objects, define the custom SQL statements.
Set the UpdatedRowSource property of the C1JsonCommand object to "UpdateRowSource.None".
Assign the C1JsonCommand class objects to the C1JsonDataAdapter and add the parameters to the command.
Invoke the C1JsonDataAdapter'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 InsertBulk()
{
    string documentConnectionString = $"Data Model=Document;Uri='json_bookstore.json';Json Path='$.bookstore.books'";
    
    using (C1JsonConnection con = new C1JsonConnection(documentConnectionString))
    {
        // データテーブルにデータを入力します。
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(con, "SELECT * FROM books");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Insert コマンドを作成します。
        adapter.InsertCommand = new C1JsonCommand(con);
        adapter.InsertCommand.CommandText = "INSERT INTO books(_id, genre, publicationdate, ISBN, title, [author.first-name], [author.last-name], price) " +
            "VALUES(@id, @genre, @publicationdate, @ISBN, @title, @fname, @lname, @price)";
        // Add query parameters
        adapter.InsertCommand.Parameters.Add("@id", "_id");
        adapter.InsertCommand.Parameters.Add("@genre", "genre");
        adapter.InsertCommand.Parameters.Add("@publicationdate", "publicationdate");
        adapter.InsertCommand.Parameters.Add("@ISBN", "ISBN");
        adapter.InsertCommand.Parameters.Add("@title", "title");
        adapter.InsertCommand.Parameters.Add("@price", "price");
        adapter.InsertCommand.Parameters.Add("@fname", "author.first-name");
        adapter.InsertCommand.Parameters.Add("@lname", "author.last-name");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        // 行を1つ挿入します。
        DataRow bookRow1 = dataTable.NewRow();
        bookRow1["_id"] = "22";
        bookRow1["genre"] = "novel";
        bookRow1["publicationdate"] = "2021-11-17";
        bookRow1["ISBN"] = "0-201-63361-2";
        bookRow1["title"] = "Lorem Ipsum";
        bookRow1["price"] = 11.99;
        bookRow1["author.first-name"] = "fname test 1";
        bookRow1["author.last-name"] = "lname Test 1";
        dataTable.Rows.Add(bookRow1);
        // 別の行を挿入します。
        DataRow bookRow2 = dataTable.NewRow();
        bookRow2["_id"] = "23";
        bookRow2["genre"] = "roman";
        bookRow2["publicationdate"] = "2018-12-07";
        bookRow2["ISBN"] = "0-885-76699-4";
        bookRow2["title"] = "Dolores Ipsum";
        bookRow2["price"] = 10.65;
        bookRow2["author.first-name"] = "fname test 2";
        bookRow2["author.last-name"] = "lname Test 2";
        dataTable.Rows.Add(bookRow2);

        // データベースを更新します。
        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 Update_Bulk()
{
    using (C1JsonConnection conn = new C1JsonConnection(documentConnectionString))
    {
        // データテーブルにデータを入力します。               
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "SELECT * FROM Books");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // 更新クエリを作成します。
        adapter.UpdateCommand = new C1JsonCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE Books SET genre=@genre WHERE _id=@Id";
        adapter.UpdateCommand.Parameters.Add("@genre", "romance");
        adapter.UpdateCommand.Parameters.Add("@Id", "678");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // 更新操作を実行します。
        DataRow bookRow1 = dataTable.Rows[0];               
        bookRow1["genre"] = "romance";
        DataRow bookRow2 = dataTable.Rows[2];               
        bookRow2["genre"] = "novel";
                
                // データベースを更新します。
        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 Delete_Bulk()
{
    using (C1JsonConnection conn = new C1JsonConnection(documentConnectionString))
    {
        // データテーブルにデータを入力します。  
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "SELECT * FROM Books");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Delete コマンドを作成します。
        adapter.DeleteCommand = new C1JsonCommand(conn);
        adapter.DeleteCommand.CommandText = "DELETE FROM Books WHERE id=@Id";
        adapter.DeleteCommand.Parameters.Add("@Id", "123");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

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

        // データベースを更新します。
        adapter.Update(dataTable);
        Console.WriteLine("Bulk delete successful! \n\n");
    }
}