DataConnector | ComponentOne
ADO.NET provider for ServiceNow / Batch Processing
In This Topic
    Batch Processing
    In This Topic

    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:

    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.

    C#
    Copy Code 
    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.

    C#
    Copy Code 
    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.

    C#
    Copy Code 
    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);
    }
}