DataConnector
ADO.NET provider for Dynamics 365 Sales / Batch Processing
In This Topic
    Batch Processing
    In This Topic

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

    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {
      conn.Open();
      //Populate Data
      C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
      DataTable dataTable = new DataTable();
      adapter.Fill(dataTable);
      //Create Insert Command
      adapter.InsertCommand = new C1D365SCommand(conn);
      adapter.InsertCommand.CommandText = "Insert INTO accounts (accountid, name) VALUES (@accountid,@name)";
      adapter.InsertCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));
      adapter.InsertCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
      adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
      //Perform Insert operation
      DataRow bookRow1 = dataTable.NewRow();
      bookRow1["accountid"] = Guid.NewGuid();
      bookRow1["name"] = "Rajesh";
      dataTable.Rows.Add(bookRow1);
      DataRow bookRow2 = dataTable.NewRow();
      bookRow2["accountid"] = Guid.NewGuid();
      bookRow2["name"] = "Robert";
      dataTable.Rows.Add(bookRow2);
      //Update database
      adapter.UpdateBatchSize = 2;
      adapter.Update(dataTable);
      Console.WriteLine("Bulk Insert Operation 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.

    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {
       conn.Open();
       //Populate Datatable
       C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
       //Create Update command
       adapter.UpdateCommand = new C1D365SCommand(conn, "UPDATE accounts SET name = @upname " + "WHERE accountid = @accountid");
       adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@upname", DbType.String, "name"));
       adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));
       adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
       //Perform Update operation
       DataRow bookRow1 = dataTable.Rows[0];
       bookRow1["name"] = "Julia";
       DataRow bookRow2 = dataTable.Rows[6];
       bookRow2["name"] = "Nick";
       //Update database
       adapter.UpdateBatchSize = 2;
       adapter.Update(dataTable);
       Console.WriteLine("Bulk Update Operation 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.

    using (C1D365SConnection conn = new C1D365SConnection(connstr))
    {
       conn.Open();
       //Populate data
       C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
       //Create Delete Command              
       adapter.DeleteCommand = new C1D365SCommand(conn);
       adapter.DeleteCommand.CommandText = "Delete From accounts where name = @name";
       adapter.DeleteCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
       //Perform Delete operation
       DataRow bookRow1 = dataTable.Rows[3];
       bookRow1.Delete();
       DataRow bookRow2 = dataTable.Rows[5];
       bookRow2.Delete();
       //Update database
       adapter.UpdateBatchSize = 2;
       adapter.Update(dataTable);
       Console.WriteLine("Bulk Delete Operation Successful !!! \n \n");
    }