一括処理

The ADO.NET Provider for Service Now allows the user to perform multiple operations with bulk data through C1ServiceNowDataAdapter. You can improve the process by executing many smaller batch requests. To control the size of each batch, you can set the C1ServiceNowDataAdapter's UpdateBatchSize property to a positive integer.

Steps to perform batch processing:

In C1ServiceNowCommand class objects, define the custom SQL statements.
Set the UpdatedRowSource property of the C1ServiceNowCommand object to "UpdateRowSource.None".
Assign the C1ServiceNowCommand class objects to the C1ServiceNowDataAdapter and add the parameters to the command.
Invoke the C1ServiceNowDataAdapter's Update method. Pass in a DataSet or DataTable containing your 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 example creates a batch that inserts data in bulk and retrieves the new data.

C#	コードのコピー
static void BulkInsert() { using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString)) { //DataTable を設定します。 C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident"); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); //Insert コマンドを作成します。 adapter.InsertCommand = new C1ServiceNowCommand(conn); adapter.InsertCommand.CommandText = "Insert into incident(caller_id, category, description, close_code) Values(@caller_id,@category,@description,@close_code)"; adapter.InsertCommand.Parameters.Add("@caller_id", "caller_id"); adapter.InsertCommand.Parameters.Add("@category", "category"); adapter.InsertCommand.Parameters.Add("@description", "description"); adapter.InsertCommand.Parameters.Add("@close_code", "close_code"); adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; //新しい行を挿入します。 DataRow incidentRow = dataTable.NewRow(); incidentRow["caller_id"] = "781ccaf9c0a8016400b98a06818d57d8"; incidentRow["category"] = "inquiry"; incidentRow["description"] = "I am unable to connect to the data server. It appears to be down."; incidentRow["close_code"] = "Solved (Permanently)"; dataTable.Rows.Add(incidentRow); //更新するバッチサイズを設定します。 adapter.UpdateBatchSize = 2; // データベースを更新します。 var i = adapter.Update(dataTable); } }

コードのコピー

static void BulkInsert()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //DataTable を設定します。
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        adapter.InsertCommand = new C1ServiceNowCommand(conn);
        adapter.InsertCommand.CommandText = "Insert into incident(caller_id, category, description, close_code) Values(@caller_id,@category,@description,@close_code)";
        adapter.InsertCommand.Parameters.Add("@caller_id", "caller_id");
        adapter.InsertCommand.Parameters.Add("@category", "category");
        adapter.InsertCommand.Parameters.Add("@description", "description");
        adapter.InsertCommand.Parameters.Add("@close_code", "close_code");

        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        //新しい行を挿入します。
        DataRow incidentRow = dataTable.NewRow();
        incidentRow["caller_id"] = "781ccaf9c0a8016400b98a06818d57d8";
        incidentRow["category"] = "inquiry";
        incidentRow["description"] = "I am unable to connect to the data server. It appears to be down.";
        incidentRow["close_code"] = "Solved (Permanently)";
        dataTable.Rows.Add(incidentRow);

        //更新するバッチサイズを設定します。

        adapter.UpdateBatchSize = 2;

        // データベースを更新します。
        var i = adapter.Update(dataTable);
    }
}

Bulk Update

A batch update additionally requires the primary key of each row to update. The following code example prepares a batch that updates data in bulk.

C#	コードのコピー
static void BulkUpdate() { using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString)) { //DataTable を設定します。 C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident"); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); //Update コマンドを作成します。 adapter.UpdateCommand = new C1ServiceNowCommand(conn); adapter.UpdateCommand.CommandText = "UPDATE incident SET close_code=@close_code where caller_id=@caller_id"; adapter.UpdateCommand.Parameters.Add("@caller_id", "caller_id"); adapter.UpdateCommand.Parameters.Add("@close_code", "close_code"); adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; //既存の行を更新します。 DataRow incidentRow1 = dataTable.Rows[0]; incidentRow1["close_code"] = "Solved Remotely (Permanently)"; //更新するバッチサイズを設定します。 adapter.UpdateBatchSize = 2; // データベースを更新します。 var i = adapter.Update(dataTable); } }

コードのコピー

static void BulkUpdate()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //DataTable を設定します。
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Update コマンドを作成します。
        adapter.UpdateCommand = new C1ServiceNowCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE incident SET close_code=@close_code where caller_id=@caller_id";
        adapter.UpdateCommand.Parameters.Add("@caller_id", "caller_id");
        adapter.UpdateCommand.Parameters.Add("@close_code", "close_code");

        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        //既存の行を更新します。
        DataRow incidentRow1 = dataTable.Rows[0];
        incidentRow1["close_code"] = "Solved Remotely (Permanently)";

        //更新するバッチサイズを設定します。
        adapter.UpdateBatchSize = 2;

        // データベースを更新します。
        var i = adapter.Update(dataTable);
    }
}

Bulk Delete

The following code example creates a batch that deletes data in bulk. The primary key for each row is required.

C#	コードのコピー
static void BulkDelete() { using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString)) { //DataTable を設定します。 C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident"); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); //Delete コマンドを作成します。 adapter.DeleteCommand = new C1ServiceNowCommand(conn); adapter.DeleteCommand.CommandText = "Delete from incident where caller_id = @caller_id"; adapter.DeleteCommand.Parameters.Add("@caller_id", "caller_id"); adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; //行を削除します。 DataRow incidentRow1 = dataTable.Rows[10]; incidentRow1.Delete(); //更新するバッチサイズを設定します。 adapter.UpdateBatchSize = 2; // データベースを更新します。 var i = adapter.Update(dataTable); } }

コードのコピー

static void BulkDelete()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //DataTable を設定します。
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Delete コマンドを作成します。
        adapter.DeleteCommand = new C1ServiceNowCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from incident where caller_id = @caller_id";
        adapter.DeleteCommand.Parameters.Add("@caller_id", "caller_id");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        //行を削除します。
        DataRow incidentRow1 = dataTable.Rows[10];
        incidentRow1.Delete();

        //更新するバッチサイズを設定します。
        adapter.UpdateBatchSize = 2;

        // データベースを更新します。
        var i = adapter.Update(dataTable);
    }
}