Dataverse Updates with ExecuteMultipleRequest
Recently, in a WhatsApp group I’m part of, someone reached out with a challenge: "With pagination, I want to update 20,511 records using Power Automate, but I am not able to do it more than 5,000. Anyone has any idea how to implement?" It’s a classic problem when working with Microsoft Dataverse—Power Automate, while great for many tasks, hits a wall with its pagination limits. In this case, the 5,000-record cap was stopping them cold, even with pagination tweaks.
Power Automate’s low-code simplicity is fantastic for small-to-medium workflows, but for bulk updates like this, it’s not the best fit. The pagination limit forces you into processing records in tiny batches, slowing things down and complicating the flow. For 20,511 records, that’s a lot of hoops to jump through—and a lot of time wasted.
My advice? Ditch Power Automate for this job and use a C# console application with ExecuteMultipleRequest. This Dataverse SDK method lets you bundle up to 1,000 operations into a single request, slashing the number of API calls and turbocharging performance. It’s a better choice because:
- No Pagination Hassles: Unlike Power Automate,
ExecuteMultipleRequestdoesn’t choke on large datasets—you can process all 20,511 records without breaking them into 5,000-record chunks. - Faster Execution: Batching operations reduces server round trips, making it way quicker for big updates.
- More Control: A console app lets you customize error handling, logging, and logic—things Power Automate struggles to match.
In this post, I’ll show you how to build that console app step-by-step, complete with code snippets and tips. Let’s get those records updated efficiently!
What You’ll Need
Before we start coding, gather these essentials:
- Dataverse Environment: Access to a Dataverse instance with a table (e.g.,
account) containing records to update. - Azure AD App Registration: A registered app with client ID, client secret, and tenant ID, plus Dataverse permissions.
- .NET Development Setup: Visual Studio or VS Code with .NET Core 3.1 or later.
- NuGet Packages:
Microsoft.CrmSdk.CoreAssembliesMicrosoft.CrmSdk.XrmTooling.CoreAssemblyMicrosoft.IdentityModel.Clients.ActiveDirectory
Step-by-Step: Building the Console App
Step 1: Create the Console Project
Fire up your terminal and create a new C# console app, then add the necessary NuGet packages.
dotnet new console -n DataverseBatchUpdater
cd DataverseBatchUpdater
dotnet add package Microsoft.CrmSdk.CoreAssemblies --version 9.0.2.34
dotnet add package Microsoft.CrmSdk.XrmTooling.CoreAssembly --version 9.1.0.68
dotnet add package Microsoft.IdentityModel.Clients.ActiveDirectory --version 5.3.0
Step 2: Set Up Authentication
To connect to Dataverse, configure authentication using your Azure AD app credentials. Open Program.cs and add these constants, replacing placeholders with your values.
private static readonly string dataverseUrl = "https://yourorg.crm.dynamics.com";
private static readonly string clientId = "your-client-id";
private static readonly string clientSecret = "your-client-secret";
private static readonly string tenantId = "your-tenant-id";
private static readonly string authority = $"https://login.microsoftonline.com/{tenantId}";
We’ll use these to authenticate via the Dataverse SDK’s CrmServiceClient.
Step 3: Authenticate with Dataverse
Add a method to establish a connection to Dataverse using the client credentials flow.
private static async Task GetCrmServiceClient()
{
var authContext = new AuthenticationContext(authority);
var clientCred = new ClientCredential(clientId, clientSecret);
var authResult = await authContext.AcquireTokenAsync(dataverseUrl, clientCred);
var connectionString = $"AuthType=ClientSecret;Url={dataverseUrl};ClientId={clientId};ClientSecret={clientSecret};RequireNewInstance=true";
var serviceClient = new CrmServiceClient(connectionString);
if (serviceClient.IsReady)
{
return serviceClient;
}
else
{
throw new Exception($"Connection failed: {serviceClient.LastCrmError}");
}
}
Step 4: Prepare Records for Update
To use ExecuteMultipleRequest, we need a list of records to update. For this example, we’ll fetch accounts and modify their name field. Here’s how to query records (we’ll assume you have a pre-populated list or fetch them separately).
// Example: Fetch records (replace with your data source)
var accounts = new List();
for (int i = 1; i <= 20511; i++)
{
var account = new Entity("account") { Id = Guid.NewGuid() };
account["name"] = $"Account_{i}";
accounts.Add(account);
}
In practice, you’d retrieve these using RetrieveMultiple, but we’ll focus on the update logic here.
Step 5: Implement ExecuteMultipleRequest
Now, the star of the show: ExecuteMultipleRequest. We’ll process records in batches of 1,000 (the max allowed per request).
private static async Task UpdateRecordsAsync(IOrganizationService service, List accounts)
{
int batchSize = 1000;
int totalUpdated = 0;
for (int i = 0; i < accounts.Count; i += batchSize)
{
var requestCollection = new ExecuteMultipleRequest
{
Settings = new ExecuteMultipleSettings
{
ContinueOnError = true, // Keep going if one update fails
ReturnResponses = true // Get feedback on each operation
},
Requests = new OrganizationRequestCollection()
};
int endIndex = Math.Min(i + batchSize, accounts.Count);
for (int j = i; j < endIndex; j++)
{
var entity = accounts[j];
entity["name"] = $"{entity.GetAttributeValue("name")}_Updated";
var updateRequest = new UpdateRequest { Target = entity };
requestCollection.Requests.Add(updateRequest);
}
var response = (ExecuteMultipleResponse)service.Execute(requestCollection);
totalUpdated += requestCollection.Requests.Count;
foreach (var resp in response.Responses)
{
if (resp.Fault != null)
{
Console.WriteLine($"Error at index {i + resp.RequestIndex}: {resp.Fault.Message}");
}
}
Console.WriteLine($"Updated {requestCollection.Requests.Count} records. Total: {totalUpdated}");
await Task.Delay(500); // Throttling prevention
}
Console.WriteLine($"Finished! Total records updated: {totalUpdated}");
}
Tip 1: Batch Size Matters
Stick to 1,000 records per batch—Dataverse’s upper limit for ExecuteMultipleRequest. Smaller batches work but slow you down unnecessarily.
Step 6: Tie It All Together
Update Main to connect and run the update.
static async Task Main(string[] args)
{
try
{
var service = await GetCrmServiceClient();
Console.WriteLine("Connected to Dataverse!");
// Replace with your record-fetching logic
var accounts = new List();
for (int i = 1; i <= 20511; i++)
{
var account = new Entity("account") { Id = Guid.NewGuid() };
account["name"] = $"Account_{i}";
accounts.Add(account);
}
await UpdateRecordsAsync(service, accounts);
}
catch (Exception ex)
{
Console.WriteLine($"Oops! {ex.Message}");
}
}
Step 7: Run and Verify
Build and run your app, then check Dataverse to confirm the updates.
dotnet run
Look for console output like “Updated 1000 records” and verify in Dataverse that account names now end with "_Updated".
The Full Code
Here’s the complete C# console app. Replace placeholders with your Dataverse and Azure AD details.
using Microsoft.Crm.Sdk.Messages;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Tooling.Connector;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace DataverseBatchUpdater
{
class Program
{
private static readonly string dataverseUrl = "https://yourorg.crm.dynamics.com";
private static readonly string clientId = "your-client-id";
private static readonly string clientSecret = "your-client-secret";
private static readonly string tenantId = "your-tenant-id";
private static readonly string authority = $"https://login.microsoftonline.com/{tenantId}";
static async Task Main(string[] args)
{
try
{
var service = await GetCrmServiceClient();
Console.WriteLine("Connected to Dataverse!");
// Simulated record list (replace with your fetch logic)
var accounts = new List();
for (int i = 1; i <= 20511; i++)
{
var account = new Entity("account") { Id = Guid.NewGuid() };
account["name"] = $"Account_{i}";
accounts.Add(account);
}
await UpdateRecordsAsync(service, accounts);
}
catch (Exception ex)
{
Console.WriteLine($"Oops! {ex.Message}");
}
}
private static async Task GetCrmServiceClient()
{
var authContext = new AuthenticationContext(authority);
var clientCred = new ClientCredential(clientId, clientSecret);
var authResult = await authContext.AcquireTokenAsync(dataverseUrl, clientCred);
var connectionString = $"AuthType=ClientSecret;Url={dataverseUrl};ClientId={clientId};ClientSecret={clientSecret};RequireNewInstance=true";
var serviceClient = new CrmServiceClient(connectionString);
if (serviceClient.IsReady)
{
return serviceClient;
}
else
{
throw new Exception($"Connection failed: {serviceClient.LastCrmError}");
}
}
private static async Task UpdateRecordsAsync(IOrganizationService service, List accounts)
{
int batchSize = 1000;
int totalUpdated = 0;
for (int i = 0; i < accounts.Count; i += batchSize)
{
var requestCollection = new ExecuteMultipleRequest
{
Settings = new ExecuteMultipleSettings
{
ContinueOnError = true,
ReturnResponses = true
},
Requests = new OrganizationRequestCollection()
};
int endIndex = Math.Min(i + batchSize, accounts.Count);
for (int j = i; j < endIndex; j++)
{
var entity = accounts[j];
entitytrong["name"] = $"{entity.GetAttributeValue("name")}_Updated";
var updateRequest = new UpdateRequest { Target = entity };
requestCollection.Requests.Add(updateRequest);
}
var response = (ExecuteMultipleResponse)service.Execute(requestCollection);
totalUpdated += requestCollection.Requests.Count;
foreach (var resp in response.Responses)
{
if (resp.Fault != null)
{
Console.WriteLine($"Error at index {i + resp.RequestIndex}: {resp.Fault.Message}");
}
}
Console.WriteLine($"Updated {requestCollection.Requests.Count} records. Total: {totalUpdated}");
await Task.Delay(500); // Avoid throttling
}
Console.WriteLine($"Finished! Total records updated: {totalUpdated}");
}
}
}
Pro Tips for Success
Tip 1: Maximize Batch Efficiency
Use the full 1,000-record capacity of ExecuteMultipleRequest to minimize requests and speed up execution.
Tip 2: Dodge Throttling Limits
Add a small delay (e.g., await Task.Delay(500)) between batches to avoid hitting Dataverse’s throttling limits.
Tip 3: Robust Error Handling
Log errors from response.Responses to a file or table for post-run analysis, ensuring no issues slip through.
Wrapping Up
With ExecuteMultipleRequest, updating large datasets in Dataverse becomes a breeze. This C# console app showcases how to harness batch processing for efficiency and reliability. Try it out, tweak it to your needs, and watch your Dataverse updates soar!
No comments:
Post a Comment