Implement a Generic Concurrency Action in Dataverse

Today, we will implement a generic concurrency solution that I think will fit as many scenarios as we can. With the help of Azure SQL Server + Dataverse Custom API, we can implement a simple architecture solution to handle the concurrency (only allowing the first operation to be successful while dropping the second request or more)! For example, I have the below requirement:

Sample Flow

In the above flow, you can see that User 01 and User 02 execute the same process at the same time. The requirement is only to allow whoever the first user hits the system. For this, we have lots of ways to solve this requirement. But for this demonstration, we can use Azure SQL where we can use the unique indexes to avoid duplication. If insert process to the table success, we can do the following operation that we want, else just show the failed result to the user:

Azure SQL Setup

Go to portal.azure.com > create SQL Database >fill up the necessary data (select/create Resource Group, SQL Server, setup the compute storage like what you need):

Create SQL Server

In the above setting, I choose to create SQL Server with Authentication method = “Use SQL authentication“. We will use the predefined password to connect to the SQL later on.

Wait until the resource is up > go to the SQL Database > select the Overview blade > Set server firewall > in here you can select Public network access to “Selected networks” and whitelist your IP Address (later we need to whitelist our Dataverse IP as well because we will call from the Plugin) > click Save.

Set the Firewall setting of the SQL Database


Again, in SQL Database > select Query editor (preview) blade > fill in your username + password and try to log in and you can execute the below script to create the table that we need it:

CREATE TABLE [dbo].[Dataverse](
	[Id] [uniqueidentifier] NOT NULL,
	[PrimaryKey] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_Dataverse] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Dataverse] ADD  CONSTRAINT [DF_Dataverse_Id]  DEFAULT (newid()) FOR [Id]
GO

Create Custom API

Next, you can create the Plugin project using Microsoft Power Platform CLI or any project you have. You can refer to this documentation about how to install and how to create the plugin using the command prompt.

Once the project is created, you can install the Newtonsoft NuGet package and make sure the .csproj is like below (Newtonsoft.Json don’t have PrivateAssets property):

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net462</TargetFramework>
    <PowerAppsTargetsPath>$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\PowerApps</PowerAppsTargetsPath>
    <SignAssembly>true</SignAssembly>
    <AssemblyOriginatorKeyFile>DemoPlugin.snk</AssemblyOriginatorKeyFile>
    <AssemblyVersion>1.0.0.0</AssemblyVersion>
    <FileVersion>1.0.0.0</FileVersion>
    <ProjectTypeGuids>{4C25E9B5-9FA6-436c-8E19-B395D2A65FAF};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
  </PropertyGroup>

  <Import Project="$(PowerAppsTargetsPath)\Microsoft.PowerApps.VisualStudio.Plugin.props" Condition="Exists('$(PowerAppsTargetsPath)\Microsoft.PowerApps.VisualStudio.Plugin.props')" />

  <ItemGroup>
    <PackageReference Include="Microsoft.CrmSdk.CoreAssemblies" Version="9.0.2.*" PrivateAssets="All" />
    <PackageReference Include="Microsoft.PowerApps.MSBuild.Plugin" Version="1.*" PrivateAssets="All" />
    <PackageReference Include="Microsoft.NETFramework.ReferenceAssemblies" Version="1.0.0" PrivateAssets="All" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
  </ItemGroup>

  <Import Project="$(PowerAppsTargetsPath)\Microsoft.PowerApps.VisualStudio.Plugin.targets" Condition="Exists('$(PowerAppsTargetsPath)\Microsoft.PowerApps.VisualStudio.Plugin.targets')" />
</Project>

And here is the code for the Custom API:

using Microsoft.Xrm.Sdk;
using System;
using System.Data.SqlClient;
using System.Threading;
using Newtonsoft.Json;

namespace DemoPlugin
{
    public class ConcurrencyApi : IPlugin
    {
        public class ConcurrencyApiInput
        {
            public string Key { get; set; }
            public string Action { get; set; }
            public Guid? Id { get; set; }
            public string LogicalName { get; set; }
        }

        private const string SqlConnectionString = "Server=tcp:demo-temmy.database.windows.net,1433;Initial Catalog=demo-temmy;Persist Security Info=False;User ID=temmy;Password=PBEv3pym6stXqUM;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        public const string InputParameter = "input";
        public const string OutputParameter = "output";
        
        public void Execute(IServiceProvider serviceProvider)
        {
            var pluginExecutionContext = (IPluginExecutionContext)
                serviceProvider.GetService(typeof(IPluginExecutionContext));
          

            var input = pluginExecutionContext.InputParameters[InputParameter].ToString();
            if (string.IsNullOrEmpty(input)) throw new ArgumentNullException(InputParameter);

            var data = JsonConvert.DeserializeObject<ConcurrencyApiInput>(input);

            using (var connection = new SqlConnection(SqlConnectionString))
            {
                var blockId = Guid.Empty;
                try
                {
                    connection.Open();

                    // Insert Block Id
                    using (var command = new SqlCommand(
                               "INSERT INTO Dataverse (PrimaryKey) OUTPUT Inserted.ID VALUES (@PrimaryKey)",
                               connection))
                    {
                        command.Parameters.AddWithValue("@PrimaryKey", data.Key);
                        blockId = (Guid)command.ExecuteScalar();
                    }

                    // To simulate long operation
                    Thread.Sleep(5000);

                    // Execute Your logic here
                    DataverseLogic(serviceProvider, data);

                }
                finally
                {
                    if (blockId != Guid.Empty)
                    {
                        // Delete Block Id
                        using (var command = new SqlCommand("DELETE FROM Dataverse WHERE Id=@Id", connection))
                        {
                            command.Parameters.AddWithValue("@Id", blockId);
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }

            pluginExecutionContext.OutputParameters[OutputParameter] = "Success";
        }

        private void DataverseLogic(IServiceProvider serviceProvider, ConcurrencyApiInput concurrencyApiInput)
        {
            var pluginExecutionContext = (IPluginExecutionContext)
                serviceProvider.GetService(typeof(IPluginExecutionContext));

            var serviceFactory =
                (IOrganizationServiceFactory)serviceProvider.GetService(
                    typeof(IOrganizationServiceFactory));
            var service = serviceFactory
                .CreateOrganizationService(pluginExecutionContext.UserId);

            if (concurrencyApiInput.Action != "Demo") return;

            var updateEntity = new Entity(concurrencyApiInput.LogicalName, concurrencyApiInput.Id.GetValueOrDefault());
            updateEntity["jobtitle"] = "Blogger";

            service.Update(updateEntity);
        }
    }
}

From the code above, you can see that the logic is pretty simple. We only need to create a connection to the Azure SQL Server. Then, we just need to insert it into the Dataverse table that has a unique index setup before. So if there’s the same key inserted, it will fail. If no error, we can continue the operation that we want (which here I put Thread.Sleep(5000) to make the system busy). For this demonstration, I just update the Contact > Job Title of the Contact to “Blogger“. The last step is to remove the data that we inserted. So the design here is to make the table nearly empty in every operation (Create and Delete) so the table performance will be fast.

Once the code is ready, you can build the project and you can register a new Plugin Package:

Register the Plugin Package

Then you can create a new Custom API like the below (I’m using Custom API Manager by David RivardXrmToolbox):

Create Custom API

Once this is created, we can continue to the last step!

Demonstration

I created a new Ribbon button in Contact Entity that will call below javascript:

var ribbon = ribbon || {};

var concurrencyRequest = function(key, action, id, logicalName) {
  this.input = JSON.stringify({Key: key, Action: action, Id: id, LogicalName: logicalName});
};

concurrencyRequest.prototype.getMetadata = function(logicalName, Id) {
  return {
    boundParameter: null,
    operationType: 0, // Action
    operationName: "tmy_concurrencyapi",
    parameterTypes: {
      input: {
        typeName: "Edm.String",
        structuralProperty: 1
      }
    }
  };
};

(function () {
  this.executeCustomButton = function(logicalName, id) {
    Xrm.Utility.showProgressIndicator("Execute Custom API..");

    var req = new concurrencyRequest(`Demo${logicalName}${id}`, 'Demo', id, logicalName);
    var confirmOptions = { height: 200, width: 450 };
    Xrm.WebApi.execute(req).then(async success => {
      Xrm.Utility.closeProgressIndicator();
      var result = await success.json();
      Xrm.Navigation.openConfirmDialog({text: result.output, title: "Success Execute Custom API"}, confirmOptions);
    }).catch(error => {
      Xrm.Utility.closeProgressIndicator();
      Xrm.Navigation.openConfirmDialog({text: "An error occured. Please retry the request later.", title: "Error Execute Custom API"}, confirmOptions);
    })
  };
}).apply(ribbon);

If you have different Input parameters, you can refer to this blog post for the sample which I feel will help all of you to create the correct request object in Javascript! From the above code, you can see that the function accepts 2 parameters which are the logical name and the entity Id. Then we can execute the custom API with a key that we construct with a combination of the “Demo”+LogicalName+Id string and show the result to the User.

For the first-time execution, you will get the below error which asks you to whitelist the IP Address of the Dataverse to the SQL Server’s firewall (refer to the above step):

Need to whitelist the Dataverse IP

And finally here is the demo of all the works today:

Demo Time!

Happy CRM-ing!

Advertisement

One thought on “Implement a Generic Concurrency Action in Dataverse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.