Exploring CosmosDB with Business Central, part 2

by Nov 29, 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

Data types under the microscope: List

Data types under the microscope: List

The List data type in AL language represents an ordered collection of objects that can be accessed by their index. Unlike an Array data type, a List does not have a fixed size and does not need to have its dimension specified when it is declared. The List data type...

read more
Connect to Azure Function in BC 2022 wave 2 (v21)

Connect to Azure Function in BC 2022 wave 2 (v21)

The new version of the Microsoft Dynamics 365 Business Central brought a new system module "Azure Functions" that makes integration with Azure Functions much easier and straightforward. The typical scenario, why to use Azure Functions together with Business Central,...

read more
Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use? This is the second part of my new article series about Collectible Errors. Let's check out the first part here: Collectible Errors?! | Microsoft Dynamics 365 - Ing. Tomáš Kapitán (kepty.cz) or you might be interested in my...

read more
Collectible Errors?!

Collectible Errors?!

Collectible Errors?! It has been already almost a year since ErrorInfo datatype & CollectibleErrors were introduced (I already have an article about basic structure: ErrorInfo data type & Collectible Errors). This article was released for the first time in...

read more
Substituting standard reports

Substituting standard reports

Report objects cannot be extended in versions previous to the Business Central 2021 release wave 1 version when ReportExtensions object was introduced. Since then, many of changes we need to do in reports can be done without creating a new copy of object. On the other...

read more
Isolated events

Isolated events

With Business Central 2022 wave 1, a new setting for event publishers was introduced. Until this version, any error in any event subscriber caused interruption to the current running process and stopped the whole activity. In some cases (such as log-in), this is...

read more

Sign Up for News

Certifications

Highest certification
Microsoft Data Management and
also in D365 Business Central

Microsoft Certified: Dynamics 365 Business Central Functional Consultant Associate

See other certifications here