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

Dynamics NAV 2013 & Expired Cronus License

Dynamics NAV 2013 & Expired Cronus License

We found an interesting problem - we were not able to run the development environment for Dynamics NAV 2013. Whenever we tried to run the development client, we got the following error message: "Your program license has expired" and the development client has closed...

read more
Indirect Dependencies and Access Modifiers

Indirect Dependencies and Access Modifiers

Last week, there was a discussion on Yammer on how to get values from the "Sent Email" record when all fields are marked as Internal. I was surprised that many people do not know what can/can't access modifiers (such as local, protected, or internal) be used for. I...

read more
AL Extensions: Replace Document Attachment

AL Extensions: Replace Document Attachment

I have published a new simple, open-source extension that allows replacing existing document attachments in all master entities as well as in open documents. The source code as well as the app file that can be installed in your environment is available on my GitHub...

read more
Clean up your copied environments

Clean up your copied environments

One of the most important things every developer should handle is to clean up the environment when the environment (or a company) is copied. Especially if the environment is managed directly by a client and they can create new copies anytime. Similarly, for copied...

read more
7 git commands you should know

7 git commands you should know

For many years, developers in C/AL did not need to know anything about Git or other versioning tools. That has changed with Business Central, or more specifically with AL Language. Today, we will look at the most important (and basic) git commands any developer should...

read more
How to define the source for item reservations?

How to define the source for item reservations?

It's not uncommon to have a customer's request to limit from which source items could be reserved. For example, customers may not want to reserve items from return orders. How can we achieve this goal? It's really simple. All the magic is done in the procedure...

read more
NavigationAction for ErrorInfo data type

NavigationAction for ErrorInfo data type

One more article about ErrorInfo data type. Have you already read my previous posts about ErrorInfo and Collectible Errors? ErrorInfo data type & Collectible Errors | MSDyn365 Business Central - Ing. Tomáš Kapitán (kepty.cz) Collectible Errors?! | MSDyn365...

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