Exploring CosmosDB with Business Central, part 2

by Nov 22, 2021AL Language

Home 9 Development 9 AL Language 9 Exploring CosmosDB with Business Central, part 2

This part explains how to connect to Azure Cosmos DB from your Business Central extension using AL Language and how to get some values from the database.

——————————————————————————————————————

This is the second part of series of three articles about integration between Microsoft Azure Cosmos DB and Microsoft Dynamics 365 Business Central. This part explains how to connect to Azure Cosmos DB from your Business Central extension using AL Language and how to get some values from the database.

The first part expains how to create Azure Cosmos DB environment (Database, Container), how to add new values to your newly created database and how to obtain your API keys that will be used later for API authorization.

The third and last part describes how to send your own SQL queries using POST request from AL Language to get filtered results based on variable requests made in AL Language.

——————————————————————————————————————

How to connect to Azure Cosmos DB

SAS Token

First of all, we have to create secured connection from AL project to Azure Cosmos DB. The authorization is based on SAS token (A shared access signature provides secure delegated access to resources in your storage account. With a SAS, you have granular control over how a client can access your data, see Microsoft Docs).

The SAS token is created based on three parts.

SAS  :=  'type=master&ver=1.0&sig=SECURITYHMACTOKEN';

First two parts are contants, the “type=master” and “ver=1.0″.

The most interesting part is “sig” part. Let’s see how to create it.

Sig part (HMAC)

The SECURITYHMACTOKEN from the previous part is a string created using a cryptographic technique that combines public keys, private keys, and a hash (more details about HMAC could be found here). As said, we have to define three things: string to encode, private kay and hash.

Luckily we do not have to care about creating HMAC itself as it is already prepared by Microsoft directly in the Business Central. The whole magic about this cryptographic technique is wrapped into Codeunit 1266 “Cryptography Management”, procedure GenerateBase64KeyedHashAsBase64String().

This procedure accepts three parameters: Input String, Sign Key and Hash Algorithm. See the picture below.

As you can see there is a warning for the third parameter (“An implicit conversion is being performed from a value of type ‘Enum “Hash Algorithm”‘ to a value of type ‘Option’. This conversion can lead to unexpected runtime issues. This warning will become an error in a future release.”). Fortunately, we can solve it simply by adding .AsInteger() to the third parameter (at least until Microsoft upgrade Cryptography Management codeunit to use the corresponding Enum instead of Option data type…).

Ok, but what are the Input and SignKey parameters?

The SignKey is easy to get – it is the Read-only or Read-Write key you can get from your Azure Cosmos DB environment (see the previous part where I described how to obtain this key).

The Input is a bit more complicated. The input is a string that contains all information about the entity we want to access. 

1) Method
The method is GET or POST. In this article, we will start with the GET method, and the POST method will be covered in the next part of this series. It’s important to remember that method must be defined in lower-case!

2) ResourceType
The standard Resource Types are dbs, colls, docs. The resource type specifies which entity you want to get from Cosmos DB. By using dbs you specify a database, colls means collections and docs are documents. 

In other words, with dbs you can get a list of/specific database(s), with colls you can get a list of/specific collection(s) (= containers) and with docs you can get a list of/specific document(s) (= record(s) from the specific container).

3) ResourceId
Using Resource Id you specify which database (and container) should be used for obtaining data. The format of this parameter depends on specified ResourceType but is usually like “dbs/{0}/colls/{1}/docs/{2}” where {0} is database name, {1} is container name and {2} is document name. 

The rule is: Use only the part of this string that ends with the thing you want to get. For example, to get a list of all databases use “dbs” only, to get a list of all collections use “dbs/{0}/colls” and to get specific collection use “dbs/{0}/colls/{1}”.

4) Current Time
Time is used for security reasons to set the time validity of your requests. The value must be specified in RFC1123 (“D, d M Y H:i:s O”, for example “Mon, 01 Jan 2021 14:00:00 GMT”). Fortunately, again, the Type Helper codeunit has the procedure we need! GetCurrUTCDateTimeAsText() is what we need!

 

See the image below with a full example of how to get SAS Token for your GET request to get all documents from KeptyCZTestContainer container in KeptyCZTestDB database.

Get data from Azure Cosmos DB

Now we know everything necessary to be able to get values from Azure Cosmos DB. See the image below to see how to do it.

A few notes:

1) URL
URL is constant and contains your environment name and the resource you want to get. In this case, the environment name is “bcmarket”, you can just replace it with your own. The resource always ends with the resource type (even if it is not part of the SAS token!). As you can see in the picture, I want to list all documents (so the SAS token is built with Resource Id ending with Container name, but the URL ends with docs).

2) Headers
All headers that are in the picture are mandatory. 

3) Time
Time defined in SAS Token must correspond to the time defined in the HTTP header. The validity of x-ms-date header is 15 minutes.

And that is all! You can compile your app and try it with your environment and your own data. In the next (and last) part of this series, we will look at how to create queries and POST requests. It will allow you to filter the results at the CosmosDB before downloading them to the Business Central.

Recent Articles from the category

DynamicsCon 2021 session recording available!

DynamicsCon 2021 session recording available!

Finally, recordings from DynamicsCon 2021 Autumn edition are available! Check the official youtube channel. It was a great conference with over 250 people during my live session! I definitely hope I will be able to attend the next in-person DynamicsCon as a speaker...

read more
Directions EMEA day 0 ;)

Directions EMEA day 0 😉

Directions4Partners EMEA 2021 is still in progress here in Milan. I want to share all details about each day, but unfortunately, there are so many amazing things to see, watch and try I have almost no time. So today just a few words about the day before the...

read more
ErrorInfo data type & Collectible Errors

ErrorInfo data type & Collectible Errors

A major change to AL Language development is here! Collectible errors can hugely improve any validation procedures. And what's new? Everything is about a new data type ErrorInfo. ErrorInfo data type ErrorInfo is a new data type introduced in Microsoft Dynamics 365...

read more
Working with Translation Files (XLIFF)

Working with Translation Files (XLIFF)

How does the translation file work? In Microsoft Dynamic 365 Business Central, displaying different languages is done using the XLIFF files extension (.xlf). The generated XLIFF files have strings that are specified in properties like Captions, Tooltips......

read more
Custom Filter Tokens

Custom Filter Tokens

As a user of the Business Central you have some constants you can use to filter or insert values. These constants contain useful values for data manipulation such as t / today for date field - return current dateq / quarter for date field filters - return range of the...

read more
Field Level Permissions (Restricted Fields)

Field Level Permissions (Restricted Fields)

I just add a new project to my GitHub repositories. It is a new extension for Business Central that extends security functionalities with field-level permission. Using this extension, users are able to define who can or can not change specific fields in any table in...

read more
How to let users choose field(s) properly

How to let users choose field(s) properly

When some complex functionality is developed, it is sometimes necessary to let users choose a specific field. This can be required for field permissions, mapping imported values or any similar process. Earlier, the usual way was to create a link on the Field table...

read more