DataConnector
ADO.NET provider for Salesforce / Batch Processing
In This Topic
    Batch Processing
    In This Topic

    The ADO.NET Provider for Salesforce allows the user to perform multiple operations with bulk data through C1SalesforceDataAdapter. You can improve the process by executing many smaller batch requests. To control the size of each batch, you can set the C1SalesforceDataAdapter'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 are the examples of different operations with bulk data.

    Bulk Insert

    The below code example creates a batch that inserts data in bulk and retrieves the new data.

    C#
    Copy Code
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate Datatable
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    
        //Create Insert Query
        adapter.InsertCommand = new C1SalesforceCommand(conn);
        adapter.InsertCommand.CommandText = "Insert into [Order] (AccountId,Status,BillingState,BillingCountry,EffectiveDate) values (@AccountId,@Status,@BillingState,@BillingCountry,@EffectiveDate)";
        adapter.InsertCommand.Parameters.Add("@AccountId", "AccountId");//Query Parametres
        adapter.InsertCommand.Parameters.Add("@Status", "Status");
        adapter.InsertCommand.Parameters.Add("@BillingState", "BillingState");
        adapter.InsertCommand.Parameters.Add("@BillingCountry", "BillingCountry");
        adapter.InsertCommand.Parameters.Add("@EffectiveDate", "EffectiveDate");
        
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    
        //Perform insert operation
        DataRow orderRow1 = dataTable.NewRow();
        orderRow1["AccountId"] = "0012w00000Ak4iLAAR";
        orderRow1["Status"] = "Draft";
        orderRow1["BillingState"] = "Delhi";
        orderRow1["BillingCountry"] = "India";
        orderRow1["EffectiveDate"] = "23-01-1992";
        
        dataTable.Rows.Add(orderRow1);
    
        
        DataRow orderRow2 = dataTable.NewRow();
        orderRow2["AccountId"] = "0012w00000Ak4iLAAT";
        orderRow2["Status"] = "Draft";
        orderRow2["BillingState"] = "Mumbai";
        orderRow2["BillingCountry"] = "India";
        orderRow2["EffectiveDate"] = "13-02-2000";
        dataTable.Rows.Add(orderRow2);
    
        //Set batch size to be updated
        adapter.UpdateBatchSize = 2;
    
        //Update database
        adapter.Update(dataTable);
    
        Console.WriteLine("Bulk insert successful !!! \n \n");
    }
    

    Bulk Update

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

    C#
    Copy Code
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate Datatable                
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    
        //Create Update Query
        adapter.UpdateCommand = new C1SalesforceCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE [Order] SET BillingState=@BillingState where AccountId=@AccountId";//Update Query
        adapter.UpdateCommand.Parameters.Add("@BillingState", "BillingState");
        adapter.UpdateCommand.Parameters.Add("@AccountId", "AccountId");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
    
        //Perform Update operation
        DataRow orderRow1 = dataTable.Rows[0];
        orderRow1["AccountId"] = "0012w00000Ak4iLAAR";
        DataRow orderRow2 = dataTable.Rows[4];
        orderRow2["BillingState"] = "Chennai";
    
        //Set batch size to be updated
        adapter.UpdateBatchSize = 2;
    
        //Update database
        adapter.Update(dataTable);
    
        Console.WriteLine("Bulk update successful !!! \n \n");
    }
    

    Bulk Delete

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

    C#
    Copy Code
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate Datatable   
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    
        //Create Delete command
        adapter.DeleteCommand = new C1SalesforceCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from [Order]  where AccountId=@AccountId";
        adapter.DeleteCommand.Parameters.Add("@AccountId", "AccountId");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
    
        //Perform delete operation
        DataRow orderRow1 = dataTable.Rows[03];
        orderRow1.Delete();
    
        DataRow orderRow2 = dataTable.Rows[04];
        orderRow2.Delete();
    
        //Set batch size to be updated
        adapter.UpdateBatchSize = 2;
    
        //Update database
        adapter.Update(dataTable);
    
        Console.WriteLine("Bulk delete successful !!! \n \n");
    }