Batch Processing

In This Topic

Bulk Insert
Bulk Update
Bulk Delete

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 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))
    {
        // Populate Datatable
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(con, "SELECT * FROM books");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Create Insert command
        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;

        // Insert one row
        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);
        // Insert another row
        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);

        // Update database
        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))
    {
        // Populate Datatable               
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "SELECT * FROM Books");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Create Update query
        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;

        // Perform Update operation
        DataRow bookRow1 = dataTable.Rows[0];               
        bookRow1["genre"] = "romance";
        DataRow bookRow2 = dataTable.Rows[2];               
        bookRow2["genre"] = "novel";
                
                // Update database
        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))
    {
        // Populate Datatable  
        C1JsonDataAdapter adapter = new C1JsonDataAdapter(conn, "SELECT * FROM Books");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        // Create Delete command
        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;

        // Perform delete operation
        DataRow bookRow1 = dataTable.Rows[0];
        bookRow1.Delete();
        DataRow bookRow2 = dataTable.Rows[1];             
        bookRow2.Delete();

                // Update database
        adapter.Update(dataTable);
        Console.WriteLine("Bulk delete successful! \n\n");
    }
}