Dynamics CRM: Create Custom Autonumber Plugin Using Azure SQL Sequence

In this blog post, I reviewed the Auto Number attribute that already can handle the common scenario. There is a way to give a simple conditional Auto Number from Nishant Rana that you can read here. But what if we want to implement a more complex scenario to generate the Auto Number part for instance we want to generate Auto Number based on Type? For example, if Order Type A, generate A001. If Order Type B needs to generate B001? The possible solution is to create our own code. So today we will try to create auto number code using Azure SQL Sequence (Sequence can ensure every call will get unique value).

Create Azure SQL Database

You can go to your portal.azure.com > SQL databases > hit + Create button > fill the Subscription, Resource Group, fill the Database name, create Server.

For creating SQL Database Server you can fill in Server name, Location, and set Authentication settings (to be used on the next step).

Create SQL Database Server

Back to the Azure SQL Database, set the “Want to use SQL elastic pool?” as no. Select the Compute + storage (I chose Basic which is the cheapest solution). Set the Backup storage redundancy as Locally-redundant backup storage. Then you can click Review + create. Click next and create the resource.

Create SQL Database

Once you created the Azure SQL Database resource, you can go to the Connection string blade to get the information. Save this information for the next step.

Create SQL Sequence

On yoOn portal.azure.com, go to your SQL Database that you created > click Query editor (preview) On portal.azure.com, go to your SQL Database that you created > click Query editor (preview) > login using your username + password. If you got the below error, then just click the Whitelist link:

Add whitelist

You need to install SSMS and just fill in the server information (that you get from the previous step) and fill in Login + Password:

Once you connected, you can go to your database > Programmability > right-click on Sequences > hit New Sequence > then you need to fill in Sequence name, Sequence Schema, Start value, and Increment by. Below picture is my settings (you can create more sequence here):

Create Sequence

Create Sequence WebApi

Create WebApi project (using .NET Core 3.1), then installed below NuGet Packages (from .csproj):

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.3" />
  </ItemGroup>
</Project>

Create a new controller name Sequence (SequenceController), and here is the code:

using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace SequenceApi.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class SequenceController : ControllerBase
    {
        public class Input
        {
            public string SequenceName { get; set; }
            public string Prefix { get; set; }
            public string Suffix { get; set; }
            public int Length { get; set; } = 6;
        }
        [HttpPost]
        public async Task<string> Post([FromBody] Input input)
        {
            var connectionString = "Server=tcp:[sequence-database].database.windows.net,1433;Initial Catalog=[db];Persist Security Info=False;User ID=[username];Password=[password];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                var sqlCommand = new SqlCommand($"SELECT NEXT VALUE FOR dbo.[{input.SequenceName}]")
                {
                    Connection = sqlConnection
                };
                var result = await sqlCommand.ExecuteScalarAsync();
                sqlConnection.Close();
                return $"{input.Prefix}{result.ToString().PadLeft(input.Length, '0')}{input.Suffix}";
            }
        }
    }
}

You can change the implementation to get the connection string from a more secure medium (like Azure KeyVault or appsettings.json). But the basic idea here is pretty simple, you just need to execute the command “SELECT NEXT VALUE FOR [Seq-name]” to get the next value.

Once this project is done, you can deploy it to Azure. For this demonstration purpose, I just need to right-click on the project and select Publish. 

First, you need to select the Target, select Azure:

Publish to Azure

Select Azure App Service (Windows) > Create the App Service by giving Name, select the Subscription name, Resource Group + Hosting plan > hit Create button:

Create App Service

Select your App Service Instance and you can click Finish. Your Publish Profile is ready:

Publish Profile

Then you only need to click Publish button and wait for your code to be hosted there. Once the publishing process is done, copy the App Service URL for the next step.

Create Dynamics CRM Plugin

For generating the Autonumber, you need to prepare an Entity with a string attribute. For this demonstration, I’ll just use Contact and put it in the Description attribute.

As usual, you just need to create a plugin project (from Power Platform CLI, you can run the command “pac plugin init” on your folder). Then here is the sample of how to consume and generate the auto number:

using Microsoft.Xrm.Sdk;
using System;
using System.Net.Http;
using System.Threading.Tasks;

namespace DemoPlugin
{
    public class Plugin1 : PluginBase
    {
        private readonly string _unsecureConfiguration;

        public Plugin1(string unsecureConfiguration, string secureConfiguration)
            : base(typeof(Plugin1))
        {
            _unsecureConfiguration = unsecureConfiguration;
        }

        protected override void ExecuteCdsPlugin(ILocalPluginContext localPluginContext)
        {
            if (localPluginContext == null)
            {
                throw new ArgumentNullException("localPluginContext");
            }

            var target = (Entity)localPluginContext.PluginExecutionContext.InputParameters["Target"];
            var sicCode = target.Attributes.ContainsKey("sic") ? target.GetAttributeValue<string>("sic") : "";
            if (string.IsNullOrEmpty(sicCode)) return;

            var client = new HttpClient
            {
                BaseAddress = new Uri("https://sequenceapi20211203131413.azurewebsites.net")
            };
            client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));

            var sequenceName = sicCode.Contains("1") ? "seq1" : "seq2";
            var prefix = sequenceName.ToUpper();

            var httpContent = new StringContent("{\"SequenceName\": \"" + sequenceName + "\", \"Prefix\": \"" + prefix + "-\", \"Suffix\":\"-TEMMY\"}",
                System.Text.Encoding.UTF8, "application/json");
            var response = client.PostAsync("https://sequenceapi20211203131413.azurewebsites.net/Sequence", httpContent).GetAwaiter().GetResult();
            var text = response.Content.ReadAsStringAsync().GetAwaiter().GetResult();

            if (!response.IsSuccessStatusCode) throw new InvalidPluginExecutionException(text);
            target["description"] = text.Replace("\"", "");
        }
    }
}

From the above code, we just need to run an HTTP POST request to our API URL that we created before. 

Register the plugin in the pre-operation of the Contact with message Update (depends on your situation, normally you must implement on PreOperation Create):

The plugin step

When you try for the first time, you most likely will get the below error:

CRM Error

To fix this, copy the IP address (your CRM IP Address). Go to your portal.azure.com > your Azure SQL Server > Firewalls and virtual networks blade. Add your whitelist manually there and don’t forget to click the save button:

Add whitelist

Here is the result of the above implementation:

Demonstration

Happy coding!