SQL Server: Convert datetime from UTC to specific time zone

A clean and nice way to convert datetime from UTC to specific time zone using AT TIME ZONE.

SELECT CONVERT(datetime, CONVERT(datetimeoffset, GETDATE()) AT TIME ZONE 'E. Australia Standard Time');

view raw utc-to-local.sql hosted with ❤ by GitHub

This way can take into account DST too.

Set-AzApiManagementApi : Value cannot be null. Parameter name: source

I came across an interesting error today when running the following Azure PowerShell scripts

$ApiMgmtContext = New-AzApiManagementContext -ResourceGroupName $ResourceGroup -ServiceName $ApimName

Set-AzApiManagementApi -Context $ApiMgmtContext -ApiId $ApiId -SubscriptionKeyQueryParamName "auth-key"

The error is as below.

Set-AzApiManagementApi : Value cannot be null.
Parameter name: source
At line:1 char:1
+ Set-AzApiManagementApi -Context $ApiMgmtContext -ApiId $ApiId -Subscr ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (System.Argument...ExecuteCmdlet():ArgumentNullException) [Set-AzApiManagementApi], ArgumentNullException
    + FullyQualifiedErrorId : Value cannot be null.
Parameter name: source,Microsoft.Azure.Commands.ApiManagement.ServiceManagement.Commands.SetAzureApiManagementApi


After a number of trial and error tests, I found that the missing parameter is '-Protocols'.

So the final script that worked was:

$ApiMgmtContext = New-AzApiManagementContext -ResourceGroupName $ResourceGroup -ServiceName $ApimName

Set-AzApiManagementApi -Context $ApiMgmtContext -ApiId $ApiId -Protocols @('https') -SubscriptionKeyQueryParamName "auth-key"

What interesting is that the documentation at https://docs.microsoft.com/en-us/powershell/module/az.apimanagement/set-azapimanagementapi?view=azps-3.7.0 does not mention -Protocol is a required parameter.

Exam AZ-400: Microsoft Azure DevOps Solutions - Passed

Just cleared AZ-400 ... So now I am

The allowed time of the exam is 180 minutes. And it took me 140 minutes to complete.
I would say it was not an easy exam for me who already have pretty much practical experience, and who been working with Azure day in day out in my past and current projects.

Salesforce CLI - How to fix error: 'EACCES': listen EACCES 127.0.0.1:1717

I faced the following error today when logging in Salesforce Dev Hub using the CLI.

'EACCES': listen EACCES 127.0.0.1:1717

And what I did to get over it was to change that default port 1717 to something else.

The steps are as below.

1. Open a Command Prompt window.
2. Go to a folder in your local machine using cd command. For example, in my case: cd Samples\Salesforce\my_sfdx_project
3. Create a Salesforce DX project using sfdx force:project:create command. In my case: sfdx force:project:create -n force-proj01
4. In the newly created folder force-proj01, open sfdx-project.json file and add following line: "oauthLocalPort": "7717",.
   The file will eventually contain:
   

   	{
   	"packageDirectories": [
   	{
   	"path": "force-app",
   	"default": true
   	}
   	],
   	"namespace": "",
   	"sfdcLoginUrl": "https://login.salesforce.com",
   	"oauthLocalPort": "7717",
   	"sourceApiVersion": "45.0"
   	}

   view raw sfdx-project.json hosted with ❤ by GitHub
5. Log in using the command sfdx force:auth:web:login -d -a DevHub. Now the browser window can open up. Fill in your user name and password to log in.
6. The browser now will redirect to such a link as http://localhost:1717/OauthRedirect?code=..., which is not existing.
7. Change the port in the link above from 1717 to 7717 and press Enter.
8. Now I can log in to Dev Hub successfully.

Certified Azure Developer - Passed

I just got certified today by taking the transition exam AZ-202.

It was a quick exam with 18 questions. It took me half an hour to complete, even though the allowed time is 150 minutes.

Dynamics 365 SDK - Real life example of using optimistic concurrency (auto-number)

Dynamics 365 has introduced the support for optimistic concurrency since some time back. And the in documentation there is a generic sample here https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/sample-use-optimistic-concurrency-update-delete-operations.

In this post I will give an example in a real life scenario when this feature will be used. That is the scenario when a field in an entity needs to be numerically auto-generated with a particular pattern.

Auto number in Salesforce vs Dynamics 365

In Salesforce, auto-number is an out of the box feature when defining an object, as in the image below.

Meanwhile in Dynamics 365 this feature is not there out of the box in Version 8 and before. This was only introduced in Version 9, but it is not part of the entity definition user interface. You need to add it programmatically via Web API or using SDK. More details can be found here: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/create-auto-number-attributes.

Another approach, which is the purpose of this post, is to build a custom plugin that will generate the next sequence number for the field in question.

Auto number custom plugin

In this approach we will create an entity, named as autonumber_config, that will centrally manage the sequence number and number format for a list of several fields in one or more entities. That is also the advantage of this approach over the built-in one where the auto number is configured individually in each field/entity.

The autonumber_config entity will have following fields.

• entity_name
• field_name
• field_format
• next_number

The logic for this plugin is that, whenever a record is created in the target entity that the plugin is registered to, the plugin will query the respective record in autonumber_config entity to obtain the current value of next_number field, then increase it by 1 and update back to the autonumber_config entity. The value of next_number field, together with field_format field, will be used to generate the actual auto-number for the target field in target entity.

Now comes the issue. When multi users try to create a new record of the same entity at the same time, there will be chance that the same next_number value will be obtained for multiple records.

That is when optimistic concurrency comes into play. The idea is that, after the plugin has obtained a next_number value, when updating the new value back to the record it will use the UpdateRequest with the ConcurrencyBehavior set to ConcurrencyBehavior.IfRowVersionMatches. If request is successful, the next_number value can be used as a valid number.

If a FaultException exception thrown back from service with ConcurrencyVersionMismatch (code=-2147088254) then it means that the record has been updated by another user. In that case the plugin will try to increase next_number value by 1 and update again. This can be repeated until a new next_number value can be updated.

Enough talk, time for code. Below is the main part in the Execute method of the plugin.

	Entity targetEntity = (Entity)context.InputParameters[Parameters.ContextParameter.Target];
	// this is an extension method of IOrganizationService
	EntityCollection autoNumberConfigs = organizationService.GetAutoNumberConfigs(targetEntity);
	foreach (Entity configEntity in autoNumberConfigs.Entities)
	{
	AutoNumberConfig autoNumberConfigRecord = configEntity.ToEntity<AutoNumberConfig>();
	string autoNumberFieldName = autoNumberConfigRecord.FieldName;
	if (!targetEntity.Contains(autoNumberFieldName))
	{
	int nextNumber = 1;
	string autoNumberFormat;
	bool success = false;
	while (!success)
	{
	nextNumber = autoNumberConfigRecord.NextNumber;
	autoNumberFormat = autoNumberConfigRecord.FieldFormat;
	// Create an in-memory auto number object from the retrieved auto number.
	Entity updatedAutonumber = new Entity()
	{
	LogicalName = autoNumberConfigRecord.LogicalName,
	Id = autoNumberConfigRecord.Id,
	RowVersion = autoNumberConfigRecord.RowVersion
	};
	updatedAutonumber["next_number"] = nextNumber + 1;
	UpdateRequest updateRequest = new UpdateRequest
	{
	Target = updatedAutonumber,
	ConcurrencyBehavior = ConcurrencyBehavior.IfRowVersionMatches
	};
	try
	{
	organizationService.Execute(updateRequest);
	success = true;
	}
	catch (FaultException<OrganizationServiceFault> e)
	{
	if (e.Detail.ErrorCode == ErrorCodes.ConcurrencyVersionMismatch)
	{
	success = false;
	// this is an extension method of IOrganizationService
	autoNumberConfigRecord = organizationService.GetAutoNumberConfigRecord(configEntity);
	}
	else
	{
	throw;
	}
	}
	}
	targetEntity[autoNumberFieldName] = FormatAutoNumber(autoNumberFormat, nextNumber);
	}
	}

view raw dynamics-365-sdk-optimistic-concurrency.cs hosted with ❤ by GitHub

Dynamics 365 - How to execute batch operations using the Web API (C# code)

The documentation of Dynamics 365 for Customer Engagement apps version 9.x has a section that specifies the details about how to execute batch operations using the Web API at https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/webapi/execute-batch-operations-using-web-api.

Unfortunately it does not provide a sample code (in C#) for how to compose the batch request and handle respective response.

So here is the one that I have built for my project:

	public async Task<List<HttpResponseMessage>> SendBatchRequestAsync(List<HttpMessageContent> httpContents)
	{
	if (httpContents == null)
	{
	throw new ArgumentNullException(nameof(httpContents));
	}
	string batchName = $"batch_{Guid.NewGuid()}";
	MultipartContent batchContent = new MultipartContent("mixed", batchName);
	string changesetName = $"changeset_{Guid.NewGuid()}";
	MultipartContent changesetContent = new MultipartContent("mixed", changesetName);
	httpContents.ForEach((c) => changesetContent.Add(c));
	batchContent.Add(changesetContent);
	return await SendBatchRequestAsync(batchContent);
	}
	public async Task<List<HttpResponseMessage>> SendBatchRequestAsync(MultipartContent batchContent)
	{
	HttpRequestMessage batchRequest = new HttpRequestMessage
	{
	Method = HttpMethod.Post,
	RequestUri = new Uri(_httpClient.BaseAddress.AbsoluteUri.Trim() + "$batch")
	};
	batchRequest.Content = batchContent;
	batchRequest.Headers.Add("Prefer", Settings.ClientOptions.DefaultRequestHeaders["Prefer"]);
	batchRequest.Headers.Add("OData-MaxVersion", "4.0");
	batchRequest.Headers.Add("OData-Version", "4.0");
	batchRequest.Headers.Add("Accept", "application/json");
	HttpResponseMessage response = await _httpClient.SendAsync(batchRequest);
	MultipartMemoryStreamProvider body = await response.Content.ReadAsMultipartAsync();
	List<HttpResponseMessage> contents = await ReadHttpContents(body);
	return contents;
	}
	private async Task<List<HttpResponseMessage>> ReadHttpContents(MultipartMemoryStreamProvider body)
	{
	List<HttpResponseMessage> results = new List<HttpResponseMessage>();
	if (body?.Contents != null)
	{
	foreach (HttpContent c in body.Contents)
	{
	if (c.IsMimeMultipartContent())
	{
	results.AddRange(await ReadHttpContents((await c.ReadAsMultipartAsync())));
	}
	else if (c.IsHttpResponseMessageContent())
	{
	HttpResponseMessage responseMessage = await c.ReadAsHttpResponseMessageAsync();
	if (responseMessage != null)
	{
	results.Add(responseMessage);
	}
	}
	else
	{
	HttpResponseMessage responseMessage = DeserializeToResponse(await c.ReadAsStreamAsync());
	if (responseMessage != null)
	{
	results.Add(responseMessage);
	}
	}
	}
	}
	return results;
	}
	private HttpResponseMessage DeserializeToResponse(Stream stream)
	{
	HttpResponseMessage response = new HttpResponseMessage();
	MemoryStream memoryStream = new MemoryStream();
	stream.CopyTo(memoryStream);
	response.Content = new ByteArrayContent(memoryStream.ToArray());
	response.Content.Headers.Add("Content-Type", "application/http;msgtype=response");
	return response.Content.ReadAsHttpResponseMessageAsync().Result;
	}

view raw dynamics-365-batch-request.cs hosted with ❤ by GitHub

The SendBatchRequestAsync method has two overloads which can be used for two different scenarios:

• Execute a batch request that is composed of a list of individual requests, each of them can be in turn a change set, or
• Execute a list of requests that are grouped into a single change set.

Example 1:

	string batchName = $"batch_{Guid.NewGuid()}";
	MultipartContent batchContent = new MultipartContent("mixed", batchName);
	string changesetName = $"changeset_{Guid.NewGuid()}";
	MultipartContent changesetContent = new MultipartContent("mixed", changesetName);
	string taskJson1 = "{\"subject\":\"Task 1 in batch\",\"regardingobjectid_account_task@odata.bind\":\"[Organization URI]/api/data/v9.0/accounts(00000000-0000-0000-000000000001)\"}";
	changesetContent.Add(CreateHttpMessageContent(HttpMethod.Post, "tasks", 1, taskJson1));
	string taskJson2 = "{\"subject\":\"Task 2 in batch\",\"regardingobjectid_account_task@odata.bind\":\"[Organization URI]/api/data/v9.0/accounts(00000000-0000-0000-000000000001)\"}";
	changesetContent.Add(CreateHttpMessageContent(HttpMethod.Post, "tasks", 2, taskJson2));
	batchContent.Add(changesetContent);
	batchContent.Add(CreateHttpMessageContent(HttpMethod.Get, "Account_Tasks?$select=subject"));
	List<HttpResponseMessage> responses = await SendBatchRequestAsync(batchContent);

view raw dynamics-365-batch-request-example1.cs hosted with ❤ by GitHub

Example 2:

	List<HttpMessageContent> httpContents = new List<HttpMessageContent>();
	for (int i = 1; i <= 5; i++)
	{
	string taskJson = "{\"subject\":\"Task " + i.ToString() + " in batch\",\"regardingobjectid_account_task@odata.bind\":\"[Organization URI]/api/data/v9.0/accounts(00000000-0000-0000-000000000001)\"}";
	httpContents.Add(CreateHttpMessageContent(HttpMethod.Post, "tasks", i, taskJson));
	}
	List<HttpResponseMessage> responses = await SendBatchRequestAsync(httpContents);

view raw dynamics-365-batch-request-example2.cs hosted with ❤ by GitHub

Utility method to create HttpMessageContent:

	public HttpMessageContent CreateHttpMessageContent(HttpMethod httpMethod, string requestUri, int contentId = 0, string content = null)
	{
	string baseUrl = _httpClient.BaseAddress.AbsoluteUri.Trim();
	if (!requestUri.StartsWith(baseUrl))
	{
	requestUri = baseUrl + requestUri;
	}
	HttpRequestMessage requestMessage = new HttpRequestMessage(httpMethod, requestUri);
	HttpMessageContent messageContent = new HttpMessageContent(requestMessage);
	messageContent.Headers.Remove("Content-Type");
	messageContent.Headers.Add("Content-Type", "application/http");
	messageContent.Headers.Add("Content-Transfer-Encoding", "binary");
	// only GET request requires Accept header
	if (httpMethod == HttpMethod.Get)
	{
	requestMessage.Headers.Add("Accept", "application/json");
	}
	else
	{
	// request other than GET may have content, which is normally JSON
	if (!string.IsNullOrEmpty(content))
	{
	StringContent stringContent = new StringContent(content);
	stringContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json;type=entry");
	requestMessage.Content = stringContent;
	}
	messageContent.Headers.Add("Content-ID", contentId.ToString());
	}
	return messageContent;
	}

view raw CreateHttpMessageContent.cs hosted with ❤ by GitHub