# Batch Processing

## Content



The ADO.NET Provider for Service Now allows the user to perform multiple operations with bulk data through [C1ServiceNowDataAdapter](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.ServiceNow/C1.AdoNet.ServiceNow.C1ServiceNowDataAdapter.html). 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](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.CSV/C1.DataConnector.AdoNet.C1DataAdapterBase-1.UpdateBatchSize.html) property to a positive integer.

**Steps to perform batch processing**:

*   In [C1ServiceNowCommand](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.ServiceNow/C1.AdoNet.ServiceNow.C1ServiceNowCommand.html) class objects, define the custom SQL statements.
*   Set the [UpdatedRowSource](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.ServiceNow/C1.AdoNet.ServiceNow.C1ServiceNowCommand.UpdatedRowSource.html) 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.

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

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

```csharp
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);
    }
}
```