JSON meets NAV

I have been using SOAP services over the last years.  Only recently the RESTful web services have become more and more popular in my integration work.  Wikipedia says:

In computing, Representational State Transfer (REST) is a software architecture style for building scalable web services. REST gives a coordinated set of constraints to the design of components in a distributed hypermedia system that can lead to a higher performing and more maintainable architecture.

RESTful systems typically, but not always, communicate over the Hypertext Transfer Protocol with the same HTTP verbs (GET, POST, PUT, DELETE, etc.) which web browsers use to retrieve web pages and to send data to remote servers. REST interfaces usually involve collections of resources with identifiers, for example /people/paul, which can be operated upon using standard verbs, such as DELETE /people/paul.

As we are used to XML as the body for our SOAP messages we can also use XML as the body for a RESTful web service.  I just finished writing a code to communicate with Azure from NAV.  This communication was using RESTful web services and XML.

So, what is JSON?  Wikipedia says:

JSON, (canonically pronounced /ˈdʒeɪsən/ JAY-sən; sometimes JavaScript Object Notation), is an open standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is the primary data format used for asynchronous browser/server communication (AJAJ), largely replacing XML (used by AJAX).

Although originally derived from the JavaScript scripting language, JSON is a language-independent data format. Code for parsing and generating JSON data is readily available in many programming languages.

The JSON format was originally specified by Douglas Crockford. It is currently described by two competing standards, RFC 7159 and ECMA-404. The ECMA standard is minimal, describing only the allowed grammar syntax, whereas the RFC also provides some semantic and security considerations. The official Internet media type for JSON is application/json. The JSON filename extension is .json.

With JSON it is possible to deliver similar data structure as with XML.  JSON on the other hand requires a much less metadata.  Here is an example JSON from Wikipedia:

[code lang=”javascript”]{
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021-3100"
},
"phoneNumbers": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "office",
"number": "646 555-4567"
}
],
"children": [],
"spouse": null
}[/code]

There is not a good support for JSON in native .NET from Microsoft.  However, with Visual Studio, Microsoft installs an external DLL in to the folder “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies”

Newtonsoft

With this Json.NET in Dynamics NAV Add-ins folder we now have some way to handle JSON files.  Using this Add-in I created a NAV Codeunit to manage JSON text.

This Codeunit contains functions to build a JSON document, like

[code lang=”csharp”]
StartJSon;
AddToJSon(‘newssn’,CompanyInformation."Registration No.");
AddToJSon(‘billtossn’,BillToCustNo);
AddToJSon(‘newcompanyname’,CompanyInformation.Name);
AddToJSon(‘newemail’,CompanyInformation."E-Mail");
AddToJSon(‘register_einvoice’,EInvoiceEnabled);
AddToJSon(‘register_supdoc’,SupDocEnabled);
AddToJSon(‘register_natreg’,NRLookupEnabled);
EndJSon;
Json := Json.Copy(GetJSon);[/code]

A function to import values from a JSON document to a temporary table, like

[code lang=”csharp”]
ReadJSon(String,TempPostingExchField);

WITH TempPostingExchField DO BEGIN
SETCURRENTKEY("Line No.","Column No.");
IF FIND(‘-‘) THEN REPEAT
SETRANGE("Column No.","Column No.");
InsertFileDetails(TempPostingExchField,WebServiceURL);
FINDLAST;
SETRANGE("Column No.");
UNTIL NEXT = 0;
END;[/code]

Or just a simple way to return a single value from a simple JSON string, like

[code lang=”csharp”]FileName := GetValueFromJsonString(String,’filename’);[/code]

With these functions NAV should be able to handle JSON files without any problems.

Now you can add JSON handling to your arsenal.

Json Codeunit and required add-ins

115 Replies to “JSON meets NAV”

  1. Gunnar,
    Thanks for the very useful blog.
    Since JSON download has support for .net back to 2.0, does this mean it is supported in Visual Studio 2008 and NAV2009SP1/R2?
    Also, the login to a JSON site puzzles me. Can we use this for the initial login as well?

  2. I am not following you Dave. JSON is just a string so if you are not able to use the Newtonsoft.Json.dll you could manually handle the string in C/AL.
    I have treated login with http headers and also with basic authentication. In Azure I even had to create an authorization string. I don’t think that a JSON site has any predefined authorization that must be used. What is your example ?

  3. API requests are JSON. They are sent to the API using a HTTP connection over SSL using all four verbs (GET, PUT, POST and DELETE).
    Authentication

    Calls to the API are managed using HTTP authentication (Only “Basic” is currently supported). Every request must include the Authorisation HTTP header containing username and password. The username is your Partner ID and your password is your signature hash.
    Example:
    curl -H ‘Accept: application/json’ -H ‘Content-Type: application/json’
    -u 123:XXXXXXXXXXXXXXXXXXXXXX
    -d ‘…JSON request object goes here…’ https://gapi.checkmend.com/{method}
    ——
    The curl example does not help me – maybe if I was starting from Unix.
    It looks like it should be easy if I knew how to translate it
    I also have to generate a signature hash.
    It looks like they also expect recommend using a curl client library – which appears to me to be more than JSON.

    Do you think from the above that this is way beyond JSON or am I just getting confused with the terminology?

    1. curl is just a http client, similar to wget in windows. You can add the headers to the httpwebrequest object by using HttpWebRequest.Headers.Add(Key,Value); You should find what you need to build the authorization header in dotnet library System.Security.Cryptography and System.Convert in mscorlib.

  4. Hi Gunnar,

    Thanks for this blog!
    I failed to register the .dll’s. I used “Run as administrator” for cmd-box.
    I’ve tried it in System32 and SysWOW64, but both give the same error:

    [Window Title]
    RegSvr32

    [Content]
    The module “NAVWebRequest.dll” was loaded but the entry-point DllRegisterServer was not found.

    Make sure that “NAVWebRequest.dll” is a valid DLL or OCX file and then try again.

    [OK]

    Do you know what I’m doing wrong?

    Thanks in advance.

    With kind regards,

    Tino Ruijs

  5. Hi Gunnar,

    Thanks for the reply.
    I’ve put the files in a folder called Json in directories “C:Program FilesMicrosoft Dynamics NAV71ServiceAdd-ins” and “C:Program FilesMicrosoft Dynamics NAV71ServiceAdd-ins”.

    Now when I open the Development Enviroment and try to add a variable of DataType DotNet in a codeunit I get an error

    “—————————
    Microsoft Dynamics NAV Development Environment
    —————————
    Could not load type ”.
    —————————
    OK
    —————————

    ” when selecting the NAVWebRequest from the ” Dynamics NAV” in the “Assembly List”.

    I don’t know what I’m doing wrong…

    1. It seems to be a server-problem because I don’t succeed selecting other dll’s.
      I’ll look into it.

      1. It probably has something to do with Security on the copied dll-file. I need to unblock the file.
        I found out some windows-versions/setup mark the downloaded zip as not secure.
        So I had to go to the properties of the zip-file and click “Unblock” and then ok. Then extracted the zip-file.
        Now it works fine! 🙂

  6. Hello,
    Do I need to add .dll into addin folder . Because I have 20 LS POS with separate database. So each and every pos terminal I need to add the .DLL Is there any way ?

    1. You need the .dll in the addin folder of every server that is reading a json file. There is a way to automatically download and install the .dll. I have a blog entry about that.

  7. Hi Gunnar,
    thank you for this very helpful Blogpost.

    Im struggling with basic authentication.
    I created a Demo Scenario where I try to send a Post Request to a Regeust Bin.

    Here is my Demo Function:

    JSONManagement.StartJSon;
    JSONManagement.AddToJSon(‘transition’,41);
    JSONManagement.EndJSon;
    JSONManagement.UploadJSon(‘http://requestb.in/XXX’,’UserName’,’Password’,Json);

    The Post itself works and the Repsonse looks also nice but im missing the Authentification String in the request Header.
    How can i manage Basic Authentification?

    Thanks for your Feedback,

    Alex

    1. Hi Alex

      Basic authentication you do on the HttpRequest object.

      AddCredentials(UserName : Text;Password : Text)
      Credential := Credential.NetworkCredential;
      Credential.UserName := UserName;
      Credential.Password := Password;
      HttpWebRequest.Credentials := Credential;

      1. Hi Gunnar,

        I just read this blog, it’s a great help. I try to implement this, but when DoWebRequest() is call and NAVWebRequest.dll is used, my rtc service got stooped and i got the error for this. Please explain?

      2. Hey Gunnar,

        thank you for the Feedback.
        Unfortunately your answer didnt helped me.

        my understanding is that the Code you wrote is part the same as the CreateCredentials function from your Sample.

        If i call the UploadJson Function with UserName and Password:

        JSONManagement.UploadJSon(‘http://requestb.in/XXX’,’UserName’,’Password’,Json);

        it will call the CreateCredentials Function. with UserName and Passwort.

        Somehow i still miss the Authentification part in the header.

        Im not so super used to .net Variables in NAV so i think that im missing something here?

        1. Hi Alex
          If you are using my UploadJSon function, then that function does not insert any authentication header. Authentication is usually either in the header or as basic authentication (could say as a part of the url). Each service may have different ways of authentication. I am for example talking to Azure API and that has all the authentication in the http request header.

  8. Hi Gunnar.
    SUPER post!!
    I have a problem with compiling your exampel CU 🙁
    I have copyied the two DLL’s to th Add-in folder, both in service directory and in the client directory, but still i get the following error:

    Could not load type ‘Newtonsoft.Json.JsonTextWriter.’Newtonsoft.Json, Version=4.5.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed”.

    There is not installed Visual Studio on the machine, so Newtonsoft.Json.dll is only present in the add-in folders.
    I have tryied to register the DLL, but that is not possible.
    Do you have any suggestions?

    /Michael

      1. Hello Michael,
        I received the same error. I think I downloaded the latest .dll. Could you detail what you did after download? It looks like the Assembly list is still looking at the first .dll
        Thank you

        1. There were a number of Newtonsoft.Json.dll filles. I copied the one from the Net45 folder and pasted it to: C:Program FilesMicrosoft Dynamics NAV90ServiceAdd-ins

      2. Hi Michael, I’m facing the same challenge with registering the .dll file, I have downloaded the latest dll, put it in the ad ins folder. but I am still getting the error you mentioned, how exactly did you manage to resolve it. thank you

  9. Hello Gunnar,
    How can I pass values between Json type and C/AL using this codeunit?
    I have external assembley with Json types and need to set|get values between them.
    BridgePosAPI.put() class need to get amount, cust_name….fields value from NAV
    BridgePosAPI.send() class need to returns into NAV
    How can I use this Json type in NAV?

    Regards,
    Undy

  10. How to write text type into JSon string?
    I dont understand above example because I dont have any experience with it.

    What is type Json below part of code?
    Json := Json.Copy(GetJSon)

  11. Hi Gunnar.
    Is it possible for you to demonstrate how to make the “phoneNumbers” part of the below Wikipedia example in NAV code?
    That would be much appreciated 🙂
    /Michael

    {
    “firstName”: “John”,
    “lastName”: “Smith”,
    “isAlive”: true,
    “age”: 25,
    “address”: {
    “streetAddress”: “21 2nd Street”,
    “city”: “New York”,
    “state”: “NY”,
    “postalCode”: “10021-3100”
    },
    “phoneNumbers”: [
    {
    “type”: “home”,
    “number”: “212 555-1234”
    },
    {
    “type”: “office”,
    “number”: “646 555-4567”
    }
    ],
    “children”: [],
    “spouse”: null
    }

        1. WITH JSonMgt DO BEGIN
          StartJSon;
          AddToJSon(‘firstName’,’John’);
          AddToJSon(‘lastName’,’Smith’);
          AddToJSon(‘isAlive’,TRUE);
          AddToJSon(‘age’,25);
          AddJSonBranch(‘Address’);
          AddToJSon(‘streetAddress’,’21 2nd Street’);
          AddToJSon(‘city’,’New York’);
          AddToJSon(‘state’,’NY’);
          AddToJSon(‘postalCode’,’10021-3100′);
          EndJSonBranch;
          StartJSonArray(‘phoneNumbers’);
          StartJSon;
          AddToJSon(‘type’,’home’);
          AddToJSon(‘number’,’212 555-1234′);
          EndJSon;
          StartJSon;
          AddToJSon(‘type’,’office’);
          AddToJSon(‘number’,’646 555-4567′);
          EndJSon;
          EndJSonArray;
          StartJSonArray(‘children’);
          EndJSonArray;
          AddToJSon(‘spouse’,’null’);
          EndJSon;
          JSon := GetJSon;
          END;

          1. It doesn’t make sense to me because StartJSonArray doesn’t require any parameter…:

            StartJSonArray()
            IF ISNULL(StringBuilder) THEN
            Initialize;
            JsonTextWriter.WriteStartArray;

            In this case it looks like I’m missing something? but I have figured out it (a little bit around…) by myself to make it working.

            But still thank you for your response.

          2. Hi,
            As Patryk says:
            “It doesn’t make sense to me because StartJSonArray doesn’t require any parameter…:
            StartJSonArray()
            IF ISNULL(StringBuilder) THEN
            Initialize;
            JsonTextWriter.WriteStartArray;”

            I’m currently stuck on this. Please enlighten me what i’m doing wrong?

          3. Thank you for your reply!
            I allready came to that solution by myself, i felt pretty stupid 🙂
            I also had to create a “StartJsonObject” and “EndJsonObject” function
            (JSonTextWriter.WriteStartObject; and JSonTextWriter.WriteEndObject;)

          4. Thanks Gunnar, very helpful post and with above code change can export sales header and lines in JSON format

          5. Hi Gunnar, great article, may you kindly assist on how to read Json Values from Nested Json strings. for example getting a value that is in a branch.

            how would I get the json values for the address in the data supplied by Michael Hartmann

  12. There were a number of Newtonsoft.Json.dll filles. I copied the one from the Net45 folder and pasted it to: C:Program FilesMicrosoft Dynamics NAV90ServiceAdd-ins

  13. Hello Gunnar,

    I am trying to pass a Json request to an external API. When I try to pass the request using UploadJson(), it gives the following error;

    System.Net.WebException: The remote server returned an error: (415) Unsupported Media Type.
    at System.Net.HttpWebRequest.GetResponse()
    at NAVWebRequest.NAVWebRequest.doRequest(HttpWebRequest& WebRequest, WebException& WebException, WebRespons

    I add the following piece of code to CreateWebRequest funtion but it didn’t work;
    HttpWebRequest.MediaType := ‘application/json’;

    What could be the reason?

  14. Hi Gunnar,
    It is really useful your post and make life a bit easier for handling JSON. However I come up to a JSON which I get as a response of a web service that I was not able to handle.

    [{
    “Customer” : {
    “Name” : “FirstName”,
    “Company” : “company”,
    “Email” : “FirstName.LastName@company.net”,
    “CountryId” : 16
    },
    “Credentials” : {
    “Username” : “username”,
    “Password” : “password”,
    “EPLI” : “cb66-ddce-42e9-8fb2-b3532ae6bd14”,
    “LicenseKey” : “A4N9-5R2J”,
    “PublicLicenseKey” : “3K9”,
    “CommonTag” : “3d4a2-4900-bca9-143c7bcc34c0”
    },
    “LicenseInfo” : {
    “Product” : “111”,
    “Quantity” : 10,
    “PurchaseType” : “New”,
    “PurchaseTypeId” : 1,
    “ExpirationDate” : “10/10/2017”,
    “State” : “Active”,
    “PublicResellerId” : “”,
    “PartnerId” : 99,
    “Note” : “Test”,
    “ChannelId” : 14,
    “BatchBased” : false,
    “UpdateTypeId” : 1,
    “DealCode” : “”,
    “ActivationToken” : null,
    “SuppressExpirationMessage” : null,
    “ActivationDate” : null
    },
    “Price” : {
    “DistributorPrice” : “240.00”,
    “SystemPrice” : “364.50”,
    “ResellerPrice” : “0”,
    “Currency” : “EUR”,
    “DiscountCode” : “0”
    },
    “BundleProduct” : {
    “Product” : “1500”,
    “Quantity” : 10
    },
    “ParentLicense” : null,
    “ProductInfo” : null,
    “SystemInfo” : {
    “CreatedDate” : “10/13/2016 08:27:15.421”,
    “CreatedUserId” : 217,
    “CanceledDate” : null,
    “CanceledUserId” : null,
    “ModifiedDate” : “10/13/2016 08:27:15.421”,
    “ModifiedUserId” : 217
    },
    “AuxilliaryParams” : [],
    “LicenseFiles” : [{
    “Name” : “ERt.lic”,
    “Code” : “Item32”,
    “FileContent” : “ACfSraPrREAn1TGdPq7bzwWFtXIrhrPnZDKw9yi”
    }
    ],
    “LicenseAdministrator” : {
    “Password” : “ccXTX87uTp”,
    “Email” : “FirstName.LastName@company.net”
    },
    “ProductActivation” : null,
    “Invoicing” : null
    }, {
    “Customer” : {
    “Name” : “FirstName”,
    “Company” : “company”,
    “Email” : “FirstName.LastName@company.net”,
    “CountryId” : 16
    },
    “Credentials” : {
    “Username” : “92202”,
    “Password” : “u9vhm”,
    “EPLI” : “e9fec6e3b833”,
    “LicenseKey” : “DJKF-5R2J”,
    “PublicLicenseKey” : “333-365-XK9”,
    “CommonTag” : “3d4a2e48-dba7”
    },
    “LicenseInfo” : {
    “Product” : “810”,
    “Quantity” : 1,
    “PurchaseType” : “New”,
    “PurchaseTypeId” : 1,
    “ExpirationDate” : “10/10/2017”,
    “State” : “Active”,
    “PublicResellerId” : “”,
    “PartnerId” : 22,
    “Note” : “Test”,
    “ChannelId” : 14,
    “BatchBased” : false,
    “UpdateTypeId” : 1,
    “DealCode” : “”,
    “ActivationToken” : null,
    “SuppressExpirationMessage” : null,
    “ActivationDate” : null
    },
    “Price” : {
    “DistributorPrice” : “80.00”,
    “SystemPrice” : “121.50”,
    “ResellerPrice” : “0”,
    “Currency” : “EUR”,
    “DiscountCode” : “0”
    },
    “BundleProduct” : {
    “Product” : “15000”,
    “Quantity” : 10
    },
    “ParentLicense” : null,
    “ProductInfo” : null,
    “SystemInfo” : {
    “CreatedDate” : “10/13/2016 08:27:15.421”,
    “CreatedUserId” : 217,
    “CanceledDate” : null,
    “CanceledUserId” : null,
    “ModifiedDate” : “10/13/2016 08:27:15.421”,
    “ModifiedUserId” : 217
    },
    “AuxilliaryParams” : [],
    “LicenseFiles” : [{
    “Name” : “FileSecurity.lic”,
    “Code” : “Item32”,
    “FileContent” : “gNUUklORyIgVkFMVUU9IkVylD8/uw==”
    }
    ],
    “LicenseAdministrator” : {
    “Password” : “c87uTp”,
    “Email” : “FirstName.LastName@company.net”
    },
    “ProductActivation” : null,
    “Invoicing” : null
    }, {
    “Customer” : {
    “Name” : “FirstName”,
    “Company” : “company”,
    “Email” : “FirstName.LastName@company.net”,
    “CountryId” : 16
    },
    “Credentials” : {
    “Username” : “411”,
    “Password” : “emj”,
    “EPLI” : “9bf2-10329f369fff”,
    “LicenseKey” : “DJKF-5R2J”,
    “PublicLicenseKey” : “333-365-XK9”,
    “CommonTag” : “3d4a2e48-dba7-4900”
    },
    “LicenseInfo” : {
    “Product” : “710”,
    “Quantity” : 5,
    “PurchaseType” : “New”,
    “PurchaseTypeId” : 1,
    “ExpirationDate” : “10/10/2017”,
    “State” : “Active”,
    “PublicResellerId” : “”,
    “PartnerId” : 22,
    “Note” : “Test”,
    “ChannelId” : 14,
    “BatchBased” : false,
    “UpdateTypeId” : 1,
    “DealCode” : “”,
    “ActivationToken” : null,
    “SuppressExpirationMessage” : null,
    “ActivationDate” : null
    },
    “Price” : {
    “DistributorPrice” : “80.00”,
    “SystemPrice” : “121.50”,
    “ResellerPrice” : “0”,
    “Currency” : “EUR”,
    “DiscountCode” : “0”
    },
    “BundleProduct” : {
    “Product” : “1500”,
    “Quantity” : 10
    },
    “ParentLicense” : null,
    “ProductInfo” : null,
    “SystemInfo” : {
    “CreatedDate” : “10/13/2016 08:27:15.421”,
    “CreatedUserId” : 217,
    “CanceledDate” : null,
    “CanceledUserId” : null,
    “ModifiedDate” : “10/13/2016 08:27:15.421”,
    “ModifiedUserId” : 217
    },
    “AuxilliaryParams” : [],
    “LicenseFiles” : [{
    “Name” : “urity.lic”,
    “Code” : “NOD32”,
    “FileContent” : “CeOLfgEzZ3tXOp0okpTVZCB85NBq4=”
    }
    ],
    “LicenseAdministrator” : {
    “Password” : “c87uTp”,
    “Email” : “FirstName.LastName@company.net”
    },
    “ProductActivation” : null,
    “Invoicing” : null
    }
    ]

    /Sorry for the long text 🙂 /

    The ReadJSon function failed with the following message “The Data Exch. Field already exists. Identification fields and values: Data Exch. No.=’4′,Line No.=’1′,Column No.=’0′,Node ID=’.LicenseFiles.ModifiedUserId.Name'”. I think because there is nested second array after LicenseFile which has objects inside and the ColumnNo is not updated correctly after it.

    I made a small adjustments on ReadJSon function which worked for me.

    FirstChange>>

    ……
    ……
    JsonTextReader.TokenType.CompareTo(JsonToken.StartArray) = 0 :
    BEGIN
    InArray[JsonTextReader.Depth + 1] := TRUE;
    //ColumnNo := 0; //ntopalov disable
    ArrayDepth += 1; //ntopalov count the ArrayDepth
    END;
    ……
    ……

    JsonTextReader.TokenType.CompareTo(JsonToken.EndArray) = 0 :
    BEGIN
    InArray[JsonTextReader.Depth + 1] := FALSE;
    ArrayDepth -= 1; //ntopalov
    END;
    JsonTextReader.TokenType.CompareTo(JsonToken.EndObject) = 0 :
    IF JsonTextReader.Depth > 0 THEN
    IF InArray[JsonTextReader.Depth] THEN //ColumnNo += 1;
    //ntopalov begin
    IF ArrayDepth = 1 THEN
    ColumnNo += 1;
    //ntopalov end
    …….
    …….

    Maybe you can come up with more smartest solution.

    Appreciate your reply!

    Kind Regards,
    Nikolay

    1. Gunnar,
      thanks for your great post )

      Nikolay,
      tested your code – variable ArrayDepth need to be strictly defined for each json file.
      The problem lays in multiple objects, having similar indentation, and each of them contains arrays.
      Possible universal solution would be to check existing records in temp table for duplicates before insert:

      TempPostingExchField.RESET;
      TempPostingExchField.SETRANGE(“Posting Exch. No.”,PostExchNo);
      TempPostingExchField.SETRANGE(“Column No.”,ArrayNo);
      TempPostingExchField.SETRANGE(“Node ID”,PropertyName);
      IF TempPostingExchField.FINDLAST THEN
      LineNo := TempPostingExchFieldR.”Line No.” + 1;

      and use LineNo as unique object identifier in further parsing. To optimize this check, we could add this code not for all values, but on object start.

      Another problem I attended is about array values.
      A temporary solution is to add them directly in one field with comma:
      IF NOT INSERT THEN BEGIN
      IF GET(“Posting Exch. No.”,”Line No.”,”Column No.”,”Node ID”) THEN
      IF (Value ValueV) THEN
      IF NOT (STRLEN(Value) + STRLEN(ValueV) + 1 > MAXSTRLEN(Value)) THEN BEGIN
      Value += ‘,’ + ValueV; // add comma separated value array
      MODIFY;
      END;
      END;

      Thanks for attention.

  15. Great Post Gunnar,

    Need one help:

    how to add a formatkey before start of the body like

    format=json&data=

    {
    “Entry No”: “345667”,
    “Entry_id”: 29
    }

    Please suggest the code in NAV to handle this

  16. Hi there,

    many thanks for your blogpost. It saved me a lot of time.
    In my scenario, I was confronted with an Endpoint that accepts TLS1.1 and TLS1.2 only (it’s a payment service).
    The HttpWebRequest-Objects normally tries with SSL2 first and selects the next “higher” protocol if SSL2 is not accepted (SSL2 -> SSL3 -> TLS1.0 -> TLS1.1 -> TLS1.2). But, my Endpoint refused immediately the SSL2-attempt and closed the whole connection.

    To solve this problem I had to tell the HttpWebRequest-object which protocol to use.
    Two new DotNet-variables are necessary for this:
    ServicePointManager [System.Net.ServicePointManager.’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′]
    SecurityProtocol [System.Net.SecurityProtocolType.’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′]

    And added the following code to the function “DoWebRequest”:
    ServicePointManager.SecurityProtocol(SecurityProtocol.Tls12); // this is the new line
    NAVWebRequest := NAVWebRequest.NAVWebRequest;
    IF NOT NAVWebRequest.doRequest(HttpWebRequest,HttpWebException,HttpWebResponse) THEN

    Works fine 🙂

    Cheers,
    Hannes

    PS: and add the line “HttpWebRequest.ContentType(‘application/json’);” to the function CreateWebRequest

    1. Hi Hannes,

      I am running into same issue, but i was not able create ServicePointManager variable. Do i need install a some dll. I cant find System.Net.ServicePointManage in .net list

      1. Try to put directly “‘System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.ServicePointManager”;
        in the dotnet subtype definition

        1. Thank you so much! it worked and ServicePointManager.SecurityProtocol(SecurityProtocol.Tls12) code did the magic.

  17. Hi can you explain me like to read a string JSON for using with XMLport, becouse I have not found the function :InsertFileDetails(TempPostingExchFiel.
    thanks in advance

  18. Hi Gunnar,
    I’m new to JSON and NAV and very happy to find your blog. Thanks a lot.
    I Have a problem with function getJSon.
    This call
    JSon := StringBuilder.ToString;
    cuts the string, maximum length I get is 250. I tried with Text[1024] and also I built a GetJson( var String ->dotnet.string) function, always the same.
    Any Idea?
    Thanks in appreciation for your answer.
    Kind regards
    Andreas

  19. Hi Gunnar,

    Thanks for the post. it worked for me. But now i have to connect rest api with oauth 1.0(one leg) authentication. how to achieve that please make a blog on that it would be very helfpful for me.

    Thanks and Regards
    Mahesh U

  20. Hi i am facing a challenge with the dll file, Error: Could not load type ‘Newtonsof.Json.JsonTextWriter.’Newtonsoft.Json,Version=4.5.0.0….
    I have downloaded the latest details as well as registered them, but I still get this error and I cant combile my codeunit on Nav 2016

    please help

    1. I suggest that you go into your Client Folder (C:Program Files (x86)Microsoft Dynamics NAV100RoleTailored Client), locate Newtonsoft.Json.dll and copy it into the Add-Ins folder. Use the version that is shipped with your NAV.

      1. Thank you very much! I made the CU compilable in NAV2016 after I moved the dll from RoleTailored client to Add-Ins. But it compiled only after I re-entered the DotNet types. The NewtonSoft.Json assembly is on the Server tab, and the version is 6.0.0.0 instead of 4.5.0.0.

        Hope this helps somebody else.

  21. Thanks’ a lot for this Gunnar! It helps a lot. I’ve just started my JSon journey, but this gave me a jump start 🙂

    Many of you have problems with the Newtonsoft.Json.dll. That dll is already installed with NAV. What you need to do is to change the json variables in Gunnars codeunit to point to the Newtonsoft.Json.dll already in your NAV installation. Make sure you choose the NAV tab when you lookup the .Net assembly. You’ll find Newtonsoft.Json.dll at the bottom of the list. The NAVWebRequest.dll needs to be in the Add-in folder though.

    When you have changed the reference to the existing Newtonsoft.Json.dll you need to change this line in the Initialize function:

    JsonTextWriter.Formatting := Formatting.Indented;

    to this:

    JsonTextWriter.Formatting := 1

    1. After replacing the code in the CreateCredentials function with adding an authorization header and ContentType to the request header I was able to create a ticket in Zendesk 🙂

  22. Thanks for this blog, it has been helpful so far.

    I am however having a weird problem with the original code I can not explain. The JSON is at the bottom of this post as an example!

    What is happening is that the field “Node ID” is getting a weird value if there is an array IN the data.

    It takes the last property used before the array was opened.

    So in the Json below I would expect to get a “Node ID” of things like line_items.price

    but I instead get the following fields:

    .line_items.salutation.item_price
    line_items.salutation.price
    .line_items.salutation.price
    .line_items.salutation.quantity
    .line_items.salutation.sku

    and so on. The problem is ONLY with “line items”. Every other field “Node ID” is perfect without exception.

    [
    {
    “order_number”:”XWVWVK74″,
    “created_at”:”2017-08-07T12:34:37.000+02:00″,
    “status”:”PROCESSING”,
    “currency”:”EUR”,
    “total_line_items_price”:”35.90″,
    “total_price”:”40.89″,
    “total_shipping”:”4.99″,
    “total_tax”:”0.00″,
    “vat_rate”:”19.0″,
    “updated_at”:”2017-08-07T12:34:37.000+02:00″,
    “customer”:{
    “email”:”m-3yc9an2ui3onujqc@checkout-stg.idealo.de”,
    “phone”:”+49 30 1234 5678″
    },
    “shipping_address”:{
    “address1″:”Straße 81”,
    “address2”:null,
    “city”:”Ort”,
    “country”:”DE”,
    “given_name”:”Bob”,
    “family_name”:”Janke”,
    “zip”:”65034″,
    “salutation”:”MRS”
    },
    “billing_address”:{
    “address1″:”Straße 81”,
    “address2”:null,
    “city”:”Ort”,
    “country”:”DE”,
    “given_name”:”Bob”,
    “family_name”:”Janke”,
    “zip”:”65034″,
    “salutation”:”MRS”
    },
    “line_items”:[
    {
    “price”:”35.90″,
    “quantity”:1,
    “sku”:”CM000358-001″,
    “title”:”bq Filaflex Filament F000095 transparent”,
    “delivery_time”:null,
    “item_price”:”35.90″
    }
    ],
    “fulfillment”:{
    “type”:”FORWARDING”,
    “carrier”:null,
    “transaction_code”:null,
    “fulfillment_options”:[

    ]
    },
    “payment”:{
    “payment_method”:”SOFORT”,
    “transaction_id”:”snakeoil-e377dcb”
    }
    }
    ]

    1. Actually I think Ijust fixed it myself but not 100% sure.

      First I implemented the “Array Depth” solution another comment here implemented above.

      Then having done that change I changed the lines in JsonTextReader.TokenType.CompareTo(JsonToken.PropertyName) = 0 :

      from:

      JsonTextReader.Depth – 1

      to:

      JsonTextReader.Depth – ArrayDepth

      in two places. This SEEMS to have worked so far.

    2. If you have challenges with the json data you receive it’s very easy to convert it to an XML document if you find that easier to work with.

      ConvertJsonToXml(JSon, XMLDoc);

  23. Can you please share some file & code to read that JSON file in navision because I have not any idea about JSON & XML.

    Thanks in advance

  24. Gunnar, This is an excellent blog post that has been extremely beneficial. Working off of other’s code suggestions I was able to parse nested arrays within the Json object. I am now stumped how to parse a Json file if there are multiple objects (say, multiple people who each have their own contact information)
    Do you have any suggestions, I have run out of ideas.
    Thanks!

    1. Hello Tonya,

      MS 1221 table primary keys don’t allow to store multiple same Node id without array informations like Gunnar provide in his code unit
      Simply change the line 79 in Gunnar code unit by this :

      TempPostingExchField.”Node ID” := JsonTextReader.Path;

      You would get something like [X].contact[Y] style Node ID where X is people counter and Y contact counter.

      BTW thanks you Gunnar for this helpful post 🙂

      Have a nice day

    2. You can’t handle json array with the Gunnar cu provided, the primary table 1221 key will be same for each array element.

      Change the table Node ID affectation on line 79 of Gunnar’s CU from “PropertyName” to “JsonTextReader.Path”.
      And voila you will be able to handle array and have something like “[X].contact[Y]” in NodeID, where X is people number and Y is contact number.

      Regards

  25. Hi Tonya,

    MS 1221 table primary keys don’t allow to store multiple same Node id, without array informations like Gunnar provide in his code unit.
    Simply change the line 79 in Gunnar code unit by this :

    TempPostingExchField.”Node ID” := JsonTextReader.Path;

    You would get something like [x].contact[y] style in node ID field where X is people number and Y contact number.

    BTW thanks you Gunnar for this helpful post 🙂

    have a nice day

  26. Hi Gunnar, i have a small issue while using AddToJSon func. When i remove the FORMAT func. in JsonTextWriter.WriteValue code line and send Integer values from NAV i get the “System.Int32 cannot be converted to type ‘System.Decimal’” error. The api only accepts Integer values for the quantity field, so i cannot send Decimal. Any suggestion would be appreciated, thanks.

    1. Hi Mert

      I had Codeunit 1234 added to Business Central.
      There you can se

      [External] WriteNumberProperty(VariableName : Text;Variable : Variant)
      CASE TRUE OF
      Variable.ISINTEGER,Variable.ISDECIMAL:
      Decimal := Variable;
      ELSE
      EVALUATE(Decimal,Variable);
      END;
      JsonTextWriter.WritePropertyName(VariableName);
      JsonTextWriter.WriteValue(Decimal);

  27. Hello Gunnar,

    Thanks for the post. Very useful.

    I am having a problem with the GetValueFromJsonString function.
    When it goes to read the data it gives the error on function ReadJson()
    – StringReader := StringReader.StringReader(String);
    Cannot create an instant of the following .Net Framework object: assembley mscorlib, type system.io.StringReader.

    Please can you help in fixing this issue.

    regards,

    Ashish Kapoor

  28. Hi Gunnar,

    Provided Dll Version : Newtonsoft.Json.dll is 7.0.1.18622 But Version Used in Code is 4.5.0.0, Can you please update the DLL.

    Regards
    Shubin

  29. Hello Everyone,

    Who ever is facing Version Related Problem Use below Updated Object for this Version = 7.0.0.0

    OBJECT Codeunit 50000 Dynamics.is JSon Mgt.
    {
    OBJECT-PROPERTIES
    {
    Date=07-09-15;
    Time=[ 1:41:59 PM];
    Modified=Yes;
    Version List=Dynamics.is;
    }
    PROPERTIES
    {
    OnRun=BEGIN
    END;

    }
    CODE
    {
    VAR
    StringBuilder@1002 : DotNet “‘mscorlib’.System.Text.StringBuilder”;
    StringWriter@1004 : DotNet “‘mscorlib’.System.IO.StringWriter”;
    StringReader@1003 : DotNet “‘mscorlib’.System.IO.StringReader”;
    JsonTextWriter@1006 : DotNet “‘Newtonsoft.Json, Version=7.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’.Newtonsoft.Json.JsonTextWriter”;
    JsonTextReader@1001 : DotNet “‘Newtonsoft.Json, Version=7.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’.Newtonsoft.Json.JsonTextReader”;

    LOCAL PROCEDURE Initialize@15();
    VAR
    Formatting@1000 : DotNet “‘Newtonsoft.Json, Version=7.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’.Newtonsoft.Json.Formatting”;
    BEGIN
    StringBuilder := StringBuilder.StringBuilder;
    StringWriter := StringWriter.StringWriter(StringBuilder);
    JsonTextWriter := JsonTextWriter.JsonTextWriter(StringWriter);
    JsonTextWriter.Formatting := Formatting.Indented;
    END;

    PROCEDURE StartJSon@1();
    BEGIN
    IF ISNULL(StringBuilder) THEN
    Initialize;
    JsonTextWriter.WriteStartObject;
    END;

    PROCEDURE StartJSonArray@14();
    BEGIN
    IF ISNULL(StringBuilder) THEN
    Initialize;
    JsonTextWriter.WriteStartArray;
    END;

    PROCEDURE AddJSonBranch@6(BranchName@1001 : Text);
    BEGIN
    JsonTextWriter.WritePropertyName(BranchName);
    JsonTextWriter.WriteStartObject;
    END;

    PROCEDURE AddToJSon@4(VariableName@1001 : Text;Variable@1002 : Variant);
    BEGIN
    JsonTextWriter.WritePropertyName(VariableName);
    JsonTextWriter.WriteValue(FORMAT(Variable,0,9));
    END;

    PROCEDURE EndJSonBranch@7();
    BEGIN
    JsonTextWriter.WriteEndObject;
    END;

    PROCEDURE EndJSonArray@19();
    BEGIN
    JsonTextWriter.WriteEndArray;
    END;

    PROCEDURE EndJSon@3();
    BEGIN
    JsonTextWriter.WriteEndObject;
    END;

    PROCEDURE GetJSon@20() JSon : Text;
    BEGIN
    JSon := StringBuilder.ToString;
    Initialize;
    END;

    PROCEDURE ReadJSon@5(VAR String@1000 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”;VAR TempPostingExchField@1002 : TEMPORARY Record 1221);
    VAR
    JsonToken@1001 : DotNet “‘Newtonsoft.Json, Version=7.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’.Newtonsoft.Json.JsonToken”;
    PrefixArray@1006 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Array”;
    PrefixString@1005 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”;
    PropertyName@1008 : Text;
    ColumnNo@1003 : Integer;
    InArray@1007 : ARRAY [250] OF Boolean;
    BEGIN
    PrefixArray := PrefixArray.CreateInstance(GETDOTNETTYPE(String),250);
    StringReader := StringReader.StringReader(String);
    JsonTextReader := JsonTextReader.JsonTextReader(StringReader);
    WHILE JsonTextReader.Read DO
    CASE TRUE OF
    JsonTextReader.TokenType.CompareTo(JsonToken.StartObject) = 0 :
    ;
    JsonTextReader.TokenType.CompareTo(JsonToken.StartArray) = 0 :
    BEGIN
    InArray[JsonTextReader.Depth + 1] := TRUE;
    ColumnNo := 0;
    END;
    JsonTextReader.TokenType.CompareTo(JsonToken.StartConstructor) = 0 :
    ;
    JsonTextReader.TokenType.CompareTo(JsonToken.PropertyName) = 0 :
    BEGIN
    PrefixArray.SetValue(JsonTextReader.Value,JsonTextReader.Depth – 1);
    IF JsonTextReader.Depth > 1 THEN BEGIN
    PrefixString := PrefixString.Join(‘.’,PrefixArray,0,JsonTextReader.Depth – 1);
    IF PrefixString.Length > 0 THEN
    PropertyName := PrefixString.ToString + ‘.’ + FORMAT(JsonTextReader.Value,0,9)
    ELSE
    PropertyName := FORMAT(JsonTextReader.Value,0,9);
    END ELSE
    PropertyName := FORMAT(JsonTextReader.Value,0,9);
    END;
    JsonTextReader.TokenType.CompareTo(JsonToken.String) = 0 ,
    JsonTextReader.TokenType.CompareTo(JsonToken.Integer) = 0 ,
    JsonTextReader.TokenType.CompareTo(JsonToken.Float) = 0 ,
    JsonTextReader.TokenType.CompareTo(JsonToken.Boolean) = 0 ,
    JsonTextReader.TokenType.CompareTo(JsonToken.Date) = 0 ,
    JsonTextReader.TokenType.CompareTo(JsonToken.Bytes) = 0 :
    BEGIN
    TempPostingExchField.”Data Exch. No.” := JsonTextReader.Depth;
    TempPostingExchField.”Line No.” := JsonTextReader.LineNumber;
    TempPostingExchField.”Column No.” := ColumnNo;
    TempPostingExchField.”Node ID” := PropertyName;
    TempPostingExchField.Value := FORMAT(JsonTextReader.Value,0,9);
    TempPostingExchField.”Data Exch. Line Def Code” := JsonTextReader.TokenType.ToString;
    TempPostingExchField.INSERT;
    END;
    JsonTextReader.TokenType.CompareTo(JsonToken.EndConstructor) = 0 :
    ;
    JsonTextReader.TokenType.CompareTo(JsonToken.EndArray) = 0 :
    InArray[JsonTextReader.Depth + 1] := FALSE;
    JsonTextReader.TokenType.CompareTo(JsonToken.EndObject) = 0 :
    IF JsonTextReader.Depth > 0 THEN
    IF InArray[JsonTextReader.Depth] THEN ColumnNo += 1;
    END;
    END;

    PROCEDURE ReadFirstJSonValue@10000200(VAR String@10000200 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”;ParameterName@10000201 : Text) ParameterValue : Text;
    VAR
    JsonToken@10000203 : DotNet “‘Newtonsoft.Json, Version=7.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’.Newtonsoft.Json.JsonToken”;
    PropertyName@10000202 : Text;
    BEGIN
    StringReader := StringReader.StringReader(String);
    JsonTextReader := JsonTextReader.JsonTextReader(StringReader);
    WHILE JsonTextReader.Read DO
    CASE TRUE OF
    JsonTextReader.TokenType.CompareTo(JsonToken.PropertyName) = 0 :
    PropertyName := FORMAT(JsonTextReader.Value,0,9);
    (PropertyName = ParameterName) AND NOT ISNULL(JsonTextReader.Value) :
    BEGIN
    ParameterValue := FORMAT(JsonTextReader.Value,0,9);
    EXIT;
    END;
    END;
    END;

    PROCEDURE UploadJSon@8(WebServiceURL@1002 : Text;UserName@1001 : Text;Password@1009 : Text;VAR String@1013 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”);
    VAR
    HttpWebRequest@1007 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.HttpWebRequest”;
    HttpWebResponse@1006 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebResponse”;
    BEGIN
    CreateWebRequest(HttpWebRequest,WebServiceURL,’POST’);
    CreateCredentials(HttpWebRequest,UserName,Password);
    SetRequestStream(HttpWebRequest,String);
    DoWebRequest(HttpWebRequest,HttpWebResponse,”);
    GetResponseStream(HttpWebResponse,String);
    END;

    PROCEDURE DownloadString@10(Url@1001 : Text;UserName@1003 : Text;Password@1004 : Text) : Text;
    VAR
    WebClient@1000 : DotNet “‘System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.WebClient”;
    Credential@1002 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.NetworkCredential”;
    BEGIN
    Credential := Credential.NetworkCredential;
    Credential.UserName := UserName;
    Credential.Password := Password;

    WebClient := WebClient.WebClient;
    WebClient.Credentials := Credential;
    EXIT(WebClient.DownloadString(Url));
    END;

    PROCEDURE CreateWebRequest@13(VAR HttpWebRequest@1000 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.HttpWebRequest”;WebServiceURL@1002 : Text;Method@1001 : Text);
    BEGIN
    HttpWebRequest := HttpWebRequest.Create(WebServiceURL);
    HttpWebRequest.Timeout := 30000;
    HttpWebRequest.Method := Method;
    HttpWebRequest.Accept := ‘application/json’;
    END;

    PROCEDURE CreateCredentials@16(VAR HttpWebRequest@1001 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.HttpWebRequest”;UserName@1002 : Text;Password@1003 : Text);
    VAR
    Credential@1000 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.NetworkCredential”;
    BEGIN
    Credential := Credential.NetworkCredential;
    Credential.UserName := UserName;
    Credential.Password := Password;
    HttpWebRequest.Credentials := Credential;
    END;

    PROCEDURE SetRequestStream@18(VAR HttpWebRequest@1000 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.HttpWebRequest”;VAR String@1013 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”);
    VAR
    StreamWriter@1003 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.IO.StreamWriter”;
    Encoding@1004 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Text.Encoding”;
    BEGIN
    StreamWriter := StreamWriter.StreamWriter(HttpWebRequest.GetRequestStream,Encoding.GetEncoding(‘iso8859-1’));
    StreamWriter.Write(String);
    StreamWriter.Close;
    END;

    PROCEDURE DoWebRequest@21(VAR HttpWebRequest@1000 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.HttpWebRequest”;VAR HttpWebResponse@1001 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.WebResponse”;IgnoreCode@1003 : Code[10]);
    VAR
    NAVWebRequest@1008 : DotNet “‘NAVWebRequest, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f53f0925d26e1382’.NAVWebRequest.NAVWebRequest”;
    HttpWebException@1005 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.WebException”;
    HttpWebRequestError@1002 : TextConst ‘ENU=Error: %1\%2;ISL=St”Ðuvilla: %1\%2’;
    BEGIN
    NAVWebRequest := NAVWebRequest.NAVWebRequest;
    IF NOT NAVWebRequest.doRequest(HttpWebRequest,HttpWebException,HttpWebResponse) THEN
    IF (IgnoreCode = ”) OR (STRPOS(HttpWebException.Message,IgnoreCode) = 0) THEN
    ERROR(HttpWebRequestError,HttpWebException.Status.ToString,HttpWebException.Message);
    END;

    PROCEDURE GetResponseStream@23(VAR HttpWebResponse@1000 : DotNet “‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Net.WebResponse”;VAR String@1013 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”);
    VAR
    StreamReader@1011 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.IO.StreamReader”;
    MemoryStream@1001 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.IO.MemoryStream”;
    BEGIN
    StreamReader := StreamReader.StreamReader(HttpWebResponse.GetResponseStream);
    String := StreamReader.ReadToEnd;
    END;

    PROCEDURE GetValueFromJsonString@9(VAR String@1001 : DotNet “‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.String”;ParameterName@1002 : Text) : Text;
    VAR
    TempPostingExchField@1000 : TEMPORARY Record 1221;
    BEGIN
    ReadJSon(String,TempPostingExchField);
    EXIT(GetJsonValue(TempPostingExchField,ParameterName));
    END;

    PROCEDURE GetJsonValue@12(VAR TempPostingExchField@1001 : TEMPORARY Record 1221;ParameterName@1000 : Text) : Text;
    BEGIN
    WITH TempPostingExchField DO BEGIN
    SETRANGE(“Node ID”,ParameterName);
    IF FINDFIRST THEN EXIT(Value);
    END;
    END;

    BEGIN
    END.
    }
    }

  30. Hi,

    And For Registering Newtonsoft.Json.dll Use below Command By Running VS Command Prompt as a Administrator, (In my case Dll is Placed in D Drive )

    gacutil -i D:/Newtonsoft.Json.dll

  31. I have an api integration project that’s going to require a JSON file and would love to explore and play with your codeunit, but when I try to load the object I get an error message of:

    “The backup cannot be used with this version of the program.”

    This is for a NAV 2009 R2 database. Is the object incompatiable with that version of NAV?

  32. Hi Gunnar,

    Thank you for the great blog, I followed all the instructions, but run in one thing that could figure out. I have json file that use the following layout, how can i put { in the sub string.

    Thank you in Advance

    {
    “clearTextCardData”: {
    “cardNumber”: “4111111111111111”,
    “cvv”: “123”,
    “expirationMonth”: 02,
    “expirationYear”: 2022,
    “billingInformation”: {
    “nameOnAccount”: “”,
    “businessName”: “”,
    “firstName”: “”,
    “lastName”: “”,
    “middleName”: “string”,
    “address1”: “string”,
    “address2”: “string”,
    “address3”: “string”,
    “city”: “”,
    “state”: “”,
    “zip”: “”,
    “country”: “”,
    “phone”: “”,
    “phoneCountryCode”: 001,
    “email”: “string”
    }
    },
    “allowPartialAuthorization”: true,
    “cardEntryContext”: 9,
    “subtotalAmount”: 900,
    “taxAmount”: ,
    “totalAmount”: ,
    “customerID”: “string”,
    “requestID”: “string”,
    “shippingInformation”: {
    “businessName”: “”,
    “firstName”: “”,
    “lastName”: “”,
    “middleName”: “string”,
    “address1”: “string”,
    “address2”: “string”,
    “address3”: “string”,
    “city”: “”,
    “state”: “”,
    “zip”: “”,
    “country”: “”,
    “phone”: “”,
    “phoneCountryCode”: 001,
    “email”: “string”
    },
    “returnToken”: true
    }

  33. Hi Gunnar,

    Thank you for great blog post. It really helped but I am running into one that thing that was not able to figure out, I have the following file layout but was not sure how to add the second { in sub-string.

    Thank you so much

    {
    “clearTextCardData”: {
    “cardNumber”: “4111111111111111”,
    “cvv”: “123”,
    “expirationMonth”: 02,
    “expirationYear”: 2022,
    “billingInformation”: {
    “nameOnAccount”: “”,
    “businessName”: “”,
    “firstName”: “”,
    “lastName”: “”,
    “middleName”: “string”,
    “address1”: “string”,
    “address2”: “string”,
    “address3”: “string”,
    “city”: “”,
    “state”: “”,
    “zip”: “”,
    “country”: “”,
    “phone”: “”,
    “phoneCountryCode”: 001,
    “email”: “string”
    }
    },
    “allowPartialAuthorization”: true,
    “cardEntryContext”: 9,
    “subtotalAmount”: 900,
    “taxAmount”: ,
    “totalAmount”: ,
    “customerID”: “string”,
    “requestID”: “string”,
    “shippingInformation”: {
    “businessName”: “”,
    “firstName”: “”,
    “lastName”: “”,
    “middleName”: “string”,
    “address1”: “string”,
    “address2”: “string”,
    “address3”: “string”,
    “city”: “”,
    “state”: “”,
    “zip”: “”,
    “country”: “”,
    “phone”: “”,
    “phoneCountryCode”: 001,
    “email”: “string”
    },
    “returnToken”: true
    }

  34. Hello, I am new to Dynamics and I am learning to develop in Business Central, I have a specific need, I need to read a json file in my machine and insert the records in a table, but I do not know very well the syntax of AL, they would help me with a example please

  35. Hi Gunnar, Thank You for this great Article. I am just working with Dynamics NAV recently and I was given a task to GET data from a webservice and store it in a table. Please what should I take after I have downloaded your CU an import it ?

  36. Hi Gunnar,
    Thanks for this post. It has really helped me making great integrations.
    I want to ask you for the solution of the making of an string[]

    eksample of result:

    ‘address_lines’: [
    “address 1”,
    “address 2″,
    ” a post code and city”
    [

    so no PropertyName only the value.

      1. If i only fill in values i get this error.
        A call to Newtonsoft.Json.JsonTextWriter.WriteValue failed with this message:
        Token String in state ObjectStart would result in an invalid JSON object. Path ‘address_lines[0]’.

        I use
        StartJSonArray(‘address_lines’)
        StartJson;
        AdValueToJson(Adressline1);
        AdValueToJson(Adressline2);
        AdValueToJson(postCode + city);
        endJson;
        EndJsonArray;

        AdValueToJson is a function with one parameter (Variable)
        JsonTextWriter.WriteValue(FORMAT(Variable,0,9));)

        1. You can actually achieve this, by adding one extra function to the JSON Mgt. Codeunit:

          It is the same as AddToJSon, but simply without the property.

          AddToJSonArray(Variable : Variant)
          JsonTextWriter.WriteValue(FORMAT(Variable,0,9));

          Seems to be working for my case, where I needed exactly that – an array of values, without a property name for each of these

  37. Hi Gunnar, first thanks

    I have a problem, I try write this jSon:

    StartJSon;
    AddJSonBranch(‘Data’);
    AddToJSon(‘User’, SignSetup.User);
    AddToJSon(‘Password’, SignSetup.Password);
    AddJSonBranch(‘Document’);
    AddToJSon(‘GuestName’, SignSetup.User);
    AddToJSon(‘GuestAddressLine1’, ”);
    AddToJSon(‘GuestAddressLine2’, ‘MADRID 28008’);
    AddToJSon(‘GuestAddressLine3’, ‘ESPAÑA’);
    AddToJSon(‘Room’, ‘411’);
    AddToJSon(‘Receipt’, ‘AR5000’);
    AddToJSon(‘Concept’, ‘Contenido que tiene que firmar el usuarioTras salto de línea.FIN’);
    EndJSonBranch();
    EndJSonBranch();
    EndJSon;

    And cut in R of ROOM

    Any idea???

      1. This is

        ‘{“data”:{“User”: “User”,”Password”: “Password”,”Document”:{“GuestName”: “NACHO”,”GuestAddressLine1″ :””,”GuestAddressLine2″:”MADRID 28031″,’ +
        ‘”GuestAddressLine3″:”ESPAÑA”,”Room”:”1102″,”Receipt”:”S00001″,”Content”: “Contenido que tiene que firmar el usuarioTras salto de línea.FIN”}}}’;

        But in your codeunit only

        ‘{“data”:{“User”: “User”,”Password”: “Password”,”Document”:{“GuestName”: “NACHO”,”GuestAddressLine1″ :””,”GuestAddressLine2″:”MADRID 28031″,’ +
        ‘”GuestAddressLine3″:”ESPAÑA”,”Ro

        I’ve checked and I don’t see any variables that could cut

        Thanks

  38. I want to use it to have our old NAV 2009 R2 RTC integrated with Shopify. I downloaded the Json.zip file and saved the NewtonSoft.json.dll in the relevant folders on the NAV Server, the Classic Client and the RTC clients Add-ins folders. I can’t get i to work.

    When i try to compile the CU i imported as txt, it says:
    You have specified an unknown variable.
    JsonTextWriter
    Define the variable under ‘Global C/AL symbols’.

    It is defined, and when i press F6 in the subtype i get:

    .NET interop: exception occured, ‘Could not load file or assembly ‘Newtonsoft.Json, Version=4.5.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’ or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.’.

    When i try to declare a new dotnet variable from scratch, i get this, when i press F6 in the Assembly selector:

    .NET interop: exception occured, ‘Could not load file or assembly ‘C:\Program Files (x86)\Microsoft Dynamics NAV\60\Classic\Add-ins\Newtonsoft.Json.dll’ or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.’.

    the .NET 4.8 is installed on my PC.

    I tried .dll’s for different .NET versions and get the same errors.

    For testing i use a standard NAV 2009 R2 database on SQL 2016.

    My user, the Users group and Everyone have Full Control of the files as well as the Add-ins folders..

    my licens includes the Application Builder and the Solution Developer granule.

    I’v tried to unblock the zip file before exporting, and all sorts of advices found by googling.

    now I’m stuck.

    Advice is highly appreciated.

    all the best

    Lars

    1. Hi Gunnar, for json 50000 codeunit is working fine. But how i send form-data in body. Form-data fields are id=1,month=1,Year,2000.

      Please help.

  39. Hi Gunnar, this is great blog.
    For json data 50000 code unit is working fine using dll.
    But how i send formdata in body. My formdata fields are id=1,month=2,year=2000.
    Please help.

      1. The JsonBuffer table has a function, ReadFromText

        Which takes a Json String paramater and populates the Json Buffer table.
        This is potentially a really useful feature however the function uses many dotnet variables.
        In particular a JsonTextReader. I wonder i there is an equivalent built in object or if there is an alternative standard way to populatre the Json Buffer?

        1. You don’t need to bother with the base application using dotnet. You can use both json buffer table and codeunit and also the json read write codeunit. Only the json to xml function in the json management codeunit

  40. Hi.
    Probably a wild shot. But I have to try as I’m stuck 🙂 After runtime upgrading from BC14-CU01 to BC14-CU45 (Latest) this command fails:
    JObject := JConverter.DeserializeObject(ServerRequest(‘GET’, ”, Data,Type::URL),JObject.GetType, JSettings);
    with following error
    Microsoft Dynamics 365 Business Central
    —————————

    Et kald til Newtonsoft.Json.JsonConvert.DeserializeObject mislykkedes med følgende meddelelse: Deserialized JSON type ‘Newtonsoft.Json.Linq.JArray’ is not compatible with expected type ‘Newtonsoft.Json.Linq.JObject’. Path ”, line 1, position 794.
    —————————
    OK
    —————————
    Anyone who have experienced same issue ?
    Thank you.
    René

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.