Batch Processing

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 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.

static void BulkInsert()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //Populate DataTable
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Insert command
        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;
        //Insert new row
        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);
        //Set batch size to be updated
        adapter.UpdateBatchSize = 2;
        //Update database
        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.

static void BulkUpdate()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //Populate DataTable
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Update command                
        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;
        //Update existing row
        DataRow incidentRow1 = dataTable.Rows[0];
        incidentRow1["close_code"] = "Solved Remotely (Permanently)";
        //Set batch size to be updated
        adapter.UpdateBatchSize = 2;
        //Update database
        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.

static void BulkDelete()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //Populate Datatable
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Delete command
        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;
        //Delete a row
        DataRow incidentRow1 = dataTable.Rows[10];
        incidentRow1.Delete();
        //Set batch size to be updated
        adapter.UpdateBatchSize = 2;
        //Update Database
        var i = adapter.Update(dataTable);
    }
}