DataConnector
一括処理
ADO.NET provider for Salesforce > 一括処理

The ADO.NET Provider for Salesforce facilitates the execution of multiple operations with bulk data through C1SalesforceDataAdapter class. The process can be improved by executing many smaller batch requests. The size of each batch can be controlled by setting the UpdateBatchSize property to a positive integer.

Steps to perform batch processing:

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

C#
コードのコピー 
static void BulkInsert()
{
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        // データテーブルにデータを入力します。
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //挿入クエリを作成します。
        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;

        //挿入操作を実行します。
        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);

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

        //データベースを更新します。
        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 following code prepares a batch that updates data in bulk.

C#
コードのコピー 
static void BulkUpdate()
{
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        // データテーブルにデータを入力します。                
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //更新クエリを作成します。
        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;

        //更新操作を実行します。
        DataRow orderRow1 = dataTable.Rows[0];
        orderRow1["AccountId"] = "0012w00000Ak4iLAAR";
        DataRow orderRow2 = dataTable.Rows[4];
        orderRow2["BillingState"] = "Chennai";

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

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

        Console.WriteLine("Bulk update successful !!! \n \n");
    }
}

Bulk Delete

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

C#
コードのコピー 
static void BulkDelete()
{
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        // データテーブルにデータを入力します。   
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Delete コマンドを作成します。
        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;

        //削除操作を実行します。
        DataRow orderRow1 = dataTable.Rows[03];
        orderRow1.Delete();

        DataRow orderRow2 = dataTable.Rows[04];
        orderRow2.Delete();

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

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

        Console.WriteLine("Bulk delete successful !!! \n \n");
    }
}

 

 

Copyright © MESCIUS, Inc. All rights reserved.