Part 3 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Consume or Read file details or data from the blob folder using X++)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about Consuming or Reading file details or data from the blob folder using X++ in Dynamics D365 Finance and Operations.

Perquisite:

1. Storage account
2. Blob container
3. Create folder in the blob container
4. Add a file to the blob container for testing (Using the Upload option in the Azure portal)

Sample text file

Create a new class in the Dynamics 365 finance and operations and add following methods in it.

• connectToAzureBlob
• GetFileNameList
• readFileValueFromMemoryStream

And we will be using following .NET libraries.

• using Microsoft.WindowsAzure.Storage;
• using Micorosft.WindowsAzure.Storage.Blob;
• using System.IO;

Sample code for the classes:

Output :  

Part 2 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Establish connection between Azure BLOB and D365 F&O)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about establishing the connection between azure blob and D365 F&O.

Perquisite:

1. Storage account
2. Blob container

1- Storage account

The application will require to access the connection string at runtime in order to authorize requests made to Azure Storage. We will use the connection string in Dynamics 365 Finance and Operations environment variable. (That variable can be part of parameters on the form)

The format of the connection string is :

DefaultEndpointsProtocol = [http|https];AccountName=myAccountName;AccountKey=myAccountKey

• HTTP or HTTPS (Whether you want to connect to either of one protocol)
• myAccountName : Replace it with your storage account
• myAccountKey : Replace with your account access key

You can find out the connection key value on this path in the Azure portal.

- Go to Storage account - Access keys 

2- Blob Account

- Go to Storage account > Storage Explorer (preview) > BLOB CONTAINER




3- Store the BLOB file path in the Dynamics 365 Finance and Operations

Blob container has a folder type structure and files will be accessed from certain path.
We will store the file path and store it in the Dynamics 365 F&O variable to access the file at the particular location. With the help of file path we can iterate the blob container to particular folder and access the files.

Steps to create folder in blob container.

• Go to Storage account > Storage Explorer (preview) > BLOB CONTAINER
• Click New Folder and enter the name
• Click OK

4- Create a new class and a code in it.(It will establish connection between Azure blob and D365 F&O)

We will use two .NET libraries to use Azure blob.

• using Microsoft.WindowsAzure.Storage;
• using Micorosft.WindowsAzure.Storage.Blob;

Output:

Part 1 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Create Storage account and container in it)

Today, I will share the list of blogs which give the understanding of how to connect azure blob storage with Dynamics 365 for finance and operations and how to perform different operations on it.

Consider one of the common scenario where to need to exchange files between different applications such file can be created by D365 for finance and operations and third party app. As we all know that D365 does not provide accessibility to local path for Dynamics Production URL so we need to rely on another source.

The best and convenient way will be to handle situation using Microsoft Azure BLOB storage as central repository to exchange files between external system.

Part 1 - Create Azure BLOB and establish connection with Dynamics 365 for finance and operations

Perquisite:

• Need Azure subscription to access Azure storage and can be used by creating a free account here .

• Create a free account on Azure portal.
• Sign into the Azure portal.

1) Go to Storage account by click home icon and click on it to open storage accounts page.

2) Create Create button.

3) Enter the information for storage account and click Review+Create button.

4) Click Create (once validation is passed) as it will deploy the storage account.

5) Go back to the storage accounts menu and check the newly created storage accounts. 

6) Click the Storage account and go the access keys. Click the show keys to view the details of keys.

Note : Key 1 and Key 2 are basically the connections strings which will be used to access this storage account.

7) Go to the container and click the Container icon to create new one.

8) Enter the name and click Create.

 

Output : Newly created container.

View the Extensible Data Security Policy by using SQL query

 Today, I will share out the query which can used to view the Extensible data security policy in order to get insight what is happening on the backend  whether policy is created correctly or not.

This query will list down the details of all policies available in the system.

Note : We will use AxDB for it in the SQL

SELECT PrimaryTableAotName, QueryObjectAOTName,

ConstrainedTable, ModeledQueryDebugInfo, ContextType,ContextString,IsModeled

FROM [ModelSecPolRuntimeEx]

Output:

SQL Query to get security details (such as Security roles, duties and privileges)

 Today, I will sharing out the list of queries which can used to get the security details.

It will cover following combinations of data.

• Security roles
• Security roles to duties
• Security roles with privileges
• Security role and duty combination along with privileges

Following list of tables are used in it.

• SECURITYROLES
• SECURITYOBJECTCHILDREREFERENCES
• SECURITYDUTY
• SECURITYPRIVILEGE

-- Get the list  of all security roles 

Select Name as SecurityRoleName FROM SecurityRole;

-- Get the list of all security roles to duties

SELECT T2.Name as SecurityRole, T3.NAME as Duty 

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 ;

-- Get the ;ist of all security roles with privileges 

SELECT T2.Name as SecurityRole, T3.NAME as Privileges

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYPRIVILEGE T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 2 ;

-- Get the list of all role-duty combination with privilege 

SELECT T2.Name as SecurityRole, T2.AOTNAME as RoleSystemName,  T3.NAME AS Duty, T3.IDENTIFIER as DutySystemName, T5.NAME as Privilege, T5.IDENTIFIER as PrivilegeSystemName

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

JOIN SECURITYOBJECTCHILDREREFERENCES T4 on T4.IDENTIFIER = T3.IDENTIFIER

JOIN SECURITYPRIVILEGE T5 on T4.CHILDIDENTIFIER = T5.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 

AND T4.OBJECTTYPE = 1 AND T4.CHILDOBJECTTYPE = 2;

Read JSON file data in AX 2009 using batch processing

Today, I will share the code snippet which can be used to read a json
file in AX 2009.

We will be using .NET Newtonsoft.Json.dll file to proceed with this work.

Download the Newtonsoft.Json.dll  and place it on this path : D:\Program Files (x86)\Microsoft Dynamics AX\50\Client\Bin.

We will create a new class with name as 'JSONReaderWrapper' and add following methods in it.

class JSONReaderWrapper

{

   Newtonsoft.Json.Linq.JObject    jObject;

}

//Add these methods

public int getIntNode(str path)

{

    return this.traversePath(path);

}

public real getRealNode(str path)

{

    return this.traversePath(path);

}

public str getStringNode(str path)

{

    return System.Convert::ToString(this.traversePath(path));

}

public boolean isFound(str _path)

{

    return this.traversePath(_path) != null;

}

public void loadJson(str _json)

{

    ;

    jObject = Newtonsoft.Json.Linq.JObject::Parse(_json);

}

public static JSONReaderWrapper parseJson(str _json)

{

    JSONReaderWrapper reader = new JsonReader();

    ;

    reader.loadJson(_json);

    return reader;

}

private anytype traversePath(str                               path,

                             Newtonsoft.Json.Linq.JContainer   obj = jObject)

{

    List                            pathElements;

    ListEnumerator                  le;

    Newtonsoft.Json.Linq.JValue     value;

    Newtonsoft.Json.Linq.JToken     token;

    Newtonsoft.Json.Linq.JTokenType thisType,

                                    nestedType;

    Newtonsoft.Json.Linq.JObject    newObject;

    Newtonsoft.Json.Linq.JArray     newArray;

    str                             current,

                                    thisTypeString,

                                    nestedTypeString;

    #define.JObject("Newtonsoft.Json.Linq.JObject")

    #define.JArray ("Newtonsoft.Json.Linq.JArray")

    ;

    pathElements = strSplit(path, @".\/");

    le = pathElements.getEnumerator();

    if (le.moveNext())

    {

        current = le.current();

        thisType = obj.GetType();

        thisTypeString = thisType.ToString();

        switch (thisTypeString)

        {

            case #JObject:

                token = obj.get_Item(current);

                break;

            case #JArray:

                token = obj.get_Item(str2int(current) - 1);

                break;

            default:

                return null;

        }

        if (token)

        {

            nestedType = token.GetType();

            nestedTypeString = nestedType.ToString();

            if (nestedTypeString != #JObject && nestedTypeString != #JArray)

            {

                switch (thisTypeString)

                {

                    case #JArray:

                        return obj.get_Item(str2int(current) - 1);

                    case #JObject:

                        return obj.get_Item(current);

                    default:

                        return null;

                }

            }

            switch (nestedTypeString)

            {

                case #JObject:

                    newObject = Newtonsoft.Json.Linq.JObject::FromObject(token);

                    return this.traversePath(strDel(path, 1, strLen(current) + 1), newObject);

                case #JArray:

                    newArray = Newtonsoft.Json.Linq.JArray::FromObject(token);

                    return this.traversePath(strDel(path, 1, strLen(current) + 1), newArray);

                default:

                    return null;

            }

        }

        else

        {

            return null;

        }

    }

    else

    {

        return null;

    }

}

//Create another class which extends RunBaseBatch and add a method in it.

Json input string  = "{\"ItemDef\":[{\"ItemId\":\"Test1\",\"Price\":1},{\"ItemId\":\"Test2\",\"Price\":2},{\"ItemId\":\"Test3\",\"Price\":3}]}";

client server static  void readJsonFileData(str _jsonInput)

{

    str              jsonResult;

    System.Exception ex;

    str              itemId;

    str              quantity;

    str              conditionCode;

    real             intResult;

    container        jsonConResult;

    int              i;

    JSONReaderWrapper   reader;

;

    try

    {

        jsonResult = _jsonInput;

        new InteropPermission(InteropKind::ClrInterop).assert();

        reader = JSONReaderWrapper::parseJson(jsonResult);

        for (i = 1; reader.isFound(strfmt("ItemDef.%1.ItemId", i)); i++)

        {

            itemId = reader.getStringNode(strfmt("ItemDef.%1.ItemId", i));

             price = reader.getIntNode(strfmt("ItemDef.%1.Price", i));

             info(strfmt("%1 = %2", itemId, price));

        }

     }

     catch(Exception::CLRError)

     {

        ex = CLRInterop::getLastException().GetBaseException();

        error(ex.get_Message());

     }

}

Read files from folder in AX 2009 with batch processing

 Today, I will be discuss out how to read files from folder in AX 2009.

Below is the code snippet which can be used to read files from folder in ax 2009 using batch job.

public void readFilesFromFolder()

{

    #Evat_NL

    #File

    Filename                        baseFolder;

    Filename                        filename;

    Filename                        foundBaseFileNameVal;

    int                             itemCounter;

    System.IO.DirectoryInfo         directoryFolder;

    System.IO.FileInfo[]            files;

    System.IO.FileInfo              filelist;

    InteropPermission               permission;

    counter                         filesCount;

    counter                         i;

   ;

     filePath      = "D:\\MJ\\Test";

  

    permission  = new InteropPermission(InteropKind::ClrInterop);

    permission.assert();

    baseFolder = filePath;

    directoryFolder  = new System.IO.DirectoryInfo(baseFolder);

    files      = directoryFolder.GetFiles("*.json");  //Depending upon file type

    filesCount = files.get_Length();

    for (i = 0; i < filesCount; i++)

    {

        filelist               = files.GetValue(i);

        fileName        = filelist.get_FullName();

        foundBaseFileNameVal  = filelist.get_Name();

       if (System.IO.File::Exists(FileName))

       {

            //Read txt from file

       }

    }

       CodeAccessPermission::revertAssert();

}