Using Dotnet in C/AL to consume JSON web services

RonTaylor · 2015-09-01

I am having trouble working with a web service API which is using JSON data. I'm a long time C/AL programmer but I'm new to JSON type data and I'm not a .NET programmer so I'd like to achieve the goals of the project without having to write a new class. I have connected to the web service and can use the GET verb to retrieve data which comes in and apparently is deserialized by NAV since I can work with it immediately. The problem is that I need to use POST to create and update records in the API and i can't seem to get that to work. I'm not certain if I need to serialize the data being posted to the API or not. The API provider has very limited documentation.

Does anyone have an example of using standard .NET libraries to interact with a JSON web service from inside NAV? I learn really well from seeing examples. I have read all the posts on this forum that I could find on this forum and have learned much but i'm still coming up short.

Thanks!
Ron
:-)

tinoruijs · 2015-09-21

Have you succeeded the job?
I'm facing the same challenge.

RonTaylor · 2015-09-23

Tino,
I'm still working on it but I have figured out several things. It turns out that I have not had to do any dotnet serialization work to get it all working to this point. I have used several ideas found in other Mibuso posts to get me started. There are a few dotnet objects needed but not the serialize or deserialize stuff. Although if you figure that part out it might help.
Here are my functions that are used in the code unit to call the webservice with various requests.

This small function is an example of calling the webservice function to get the json data and parse it into local data tables. The data is parsed and written to local data tables in the ParseResponseJson function. That function must be designed with sections for each different dataset you are expecting to receive. There are probably better ways to do this but with my limited knowledge of dotnet and desire to keep as much as possible inside the C/Side code this is what I was able to figure out.

UpdateUsersTable Function:
Local Variables: Response (Text)

LOCAL UpdateUsersTable()
Response := CallWebservice('GET','users','','');
ParseResponseJson(Response);
COMMIT;

The CallWebService function has several dotnet variables:
StreamReader - DotNet - System.IO.StreamReader.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
StreamWriter - DotNet - System.IO.StreamWriter.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
Request - DotNet - System.Net.HttpWebRequest.'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
WebResponse - DotNet - System.Net.HttpWebResponse.'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
Credentials - DotNet - System.Net.NetworkCredential.'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

And several text constants:
WebserviceRoot - https://portal.yourJsonSite.com/api/v1/
UserName - yourAPIusername
Password - yourAPIpassword
Host - yourHostSite.com
ContentType - application/json; charset=utf-8

The parameter RequestData would only be used with PUT or POST methods
The parameters parameter contains extra search parameters, etc.
I use this function primarily with the GET method. I had trouble getting the POST method to work correctly, this is one of the areas i'm still working on. I'm using the WinHTTP automation objects for the POST functions that I needed (see below). I'd like to figure out how to stream the web request using this function also.

LOCAL CallWebservice(Method : Text[10];API : Text[15];RequestData : Text;Parameters : Text) ResponseData : Text

Credentials := Credentials.NetworkCredential(UserName,Password);
Request := Request.HttpWebRequest.Create(WebserviceRoot+API+Parameters);
Request.Method := Method;
Request.ContentType := ContentType;
Request.Host := Host;
Request.Credentials := Credentials;

WebResponse := Request.GetResponse();

HttpStatus := WebResponse.StatusDescription;

StreamReader := StreamReader.StreamReader(WebResponse.GetResponseStream);
ResponseData := StreamReader.ReadToEnd;

The variable ResponseData will have the text response from the web service that needs to be parsed.
This is an example of the response data I'm receiving.

{"user":[{"id":562156,"first_name":"Test","last_name":"Tester","email":"notarealname@gmail.com","created_at":"2015-09-17T15:34:59Z","sign_in_count":0,"last_sign_in_at":null,"account_expires":"2015-12-25T00:00:00Z","number_of_enrollments":0,"number_of_enrollments_accessed":0,"username":"test3","locale":"en","enabled":false,"user_type":"learner"}]}

The code below is an example of my parsing code, all in C/Side. It strips all the unnecessary characters and grabs the object name from the data. It then splits the Json data into element pairs (FieldName,FieldValue) using SELECTSTR. Then saves the data into a local data file. in the code below there are two sections, one for the user object and one for the enrollments object.

Function ParseJsonResponse;
This function does the parsing and writing of the data into local tables. It could also be used to pass the data back to a calling function with the hash tables or a dimensioned variable I think. Note: The element name must match the case of the JSON data element name

Local Variables: x (Integer), l (Integer). p (Integer), ValuePair (Text), CurrentObject (Text), CurrentElement (Text), CurrentValue (Text), ElementName (Text), ElementValue (Text), NewString (Text), RecId (Integer), tmpDate (Date)

Text Constants: FormatChar - {}"'[]

LOCAL ParseResponseJson(Value : Text)
p := 0;
x := 1;

IF STRPOS(Value,'[]')>0 THEN EXIT; // empty response, no need to parse

CurrentObject := COPYSTR(Value,STRPOS(Value,'{')+1,STRPOS(Value,':'));
Value := COPYSTR(Value,STRLEN(CurrentObject)+1);

CurrentObject := DELCHR(CurrentObject,'=',FormatChar);
CurrentObject := DELCHR(CurrentObject,'=',':');
Value := DELCHR(Value,'=','{}');
l := STRLEN(Value);

WHILE p < l DO BEGIN
  ValuePair := SELECTSTR(x,Value);  // get comma separated pairs of values and element names

  p := STRPOS(Value,ValuePair)+STRLEN(ValuePair); // move pointer to the end of the current pair in Value

  ValuePair := DELCHR(ValuePair,'=',FormatChar);

  CurrentElement := COPYSTR(ValuePair,1,STRPOS(ValuePair,':'));
  CurrentElement := DELCHR(CurrentElement,'=',':');
  
  CurrentValue := COPYSTR(ValuePair,STRPOS(ValuePair,':'));
  CurrentValue := DELCHR(CurrentValue,'=',':');

  CASE CurrentObject OF
    'enrollments':
      CASE CurrentElement OF
        'id':
          // must be a new record or lookup the existing record
          BEGIN
            Enrollment.SETFILTER(Enrollment_Id,CurrentValue);
            EVALUATE(RecId,CurrentValue);
            IF NOT Enrollment.FINDFIRST THEN BEGIN
              Enrollment.INIT;
              Enrollment.Enrollment_Id := RecId;
              Enrollment.INSERT;
            END;
          END;
        'course_id':
          EVALUATE(Enrollment.Course_Id,CurrentValue);
        'user_id':
          EVALUATE(Enrollment.User_Id,CurrentValue);
        'username':
          EVALUATE(Enrollment.User_name,CurrentValue);
        'percentage':
          IF CurrentValue <> 'null' THEN
            Enrollment.Percentage := CurrentValue;
        'date_started':
          Enrollment.Date_started := ConvertDateTimeToDate(CurrentValue);
        'date_completed':
          Enrollment.Date_completed := ConvertDateTimeToDate(CurrentValue);
        'date_lastaccessed':
          Enrollment.Date_lastaccessed := ConvertDateTimeToDate(CurrentValue);
        'date_enrolled':
          Enrollment.Date_enrolled := ConvertDateTimeToDate(CurrentValue);
        'last_name':
          EVALUATE(Enrollment.Last_name,CurrentValue);
        'first_name':
          EVALUATE(Enrollment.First_name,CurrentValue);
        'course_name':
          EVALUATE(Enrollment.Course_name,CurrentValue);
        'email':
          EVALUATE(Enrollment.Email,CurrentValue);
        'certificate_name':
          EVALUATE(Enrollment.Certificate_name,CurrentValue);
        'cert_expires_at':
          Enrollment.Cert_expires_at := ConvertDateTimeToDate(CurrentValue);
        'percentage_complete':
          BEGIN
            IF NOT EVALUATE(Enrollment.Percentage_complete,CurrentValue) THEN
              Enrollment.Percentage_complete := 0;
            Enrollment.CertName := COPYSTR(Enrollment.Certificate_name,1,10);
            Enrollment.MODIFY;
          END;
      END;

    'user':
      CASE CurrentElement OF
        'id':
          // must be a new record or lookup the existing record
          BEGIN
            EVALUATE(RecId,CurrentValue);
            LearningUser.SETRANGE(id,RecId);
            IF NOT LearningUser.FINDFIRST THEN BEGIN
              LearningUser.INIT;
              LearningUser.id := RecId;
              LearningUser.INSERT;
            END;
          END;
      'first_name':
        EVALUATE(LearningUser.last_name,CurrentValue);
      'last_name':
        EVALUATE(LearningUser.first_name,CurrentValue);
      'email':
        EVALUATE(LearningUser.email,CurrentValue);
      'username':
        EVALUATE(LearningUser.username,CurrentValue);
      'enabled':
        EVALUATE(LearningUser.enabled,CurrentValue);
      'user_type':
        BEGIN
          EVALUATE(LearningUser.user_type,CurrentValue);
          LearningUser.MODIFY;
        END;   
      END;
  END;
  x := x+1; // next pair
END;

I also added a small function to convert the UTC datetime in the data stream to a Date variable to make things a little easier
This does not convert the datetime to local datetime. That is not needed in my implementation.

Local Variables: tmpDatetxt (Text)

LOCAL ConvertDateTimeToDate(InDate : Text) OutDate : Date
// convert text datetime field to Nav Date
// OutDate is returned to the calling function

IF InDate = 'null' THEN EXIT(0D);
tmpDatetxt := COPYSTR(InDate,6,2)+COPYSTR(InDate,9,2)+COPYSTR(InDate,1,4);
EVALUATE(OutDate,tmpDatetxt);

An now for the real challenge, updating data on the Json server from the Employee file in NAV.
I have not been able to get the streaming to work with sending the data to the json server using the PUT verb with dotnet objects, so I have reverted to the automation "WinHTTPRequest" object to get this to work. The first function below will update the user account on the remote server with Employee information.

Local Variables:
WinHttpService - Automation 'Microsoft WinHTTP Services, version 5.1'.WinHttpRequest
RequestVar - Text

Local Text Constants:
UserJson - {'User': {'last_name' : '%1' , 'first_name' : '%2' , 'email' : '%3' , 'language' : '%4' , 'username' : '%5' , 'enabled' : '%6' }}
Parameter - /%1
WebserviceRoot - https://portal.yourJsonSite.com/api/v1/
UserName - yourAPIusername
Password - yourAPIpassword
Host - yourHostSite.com
ContentType - application/json; charset=utf-8

UpdateLearningUser(VAR EmployeeRec : Record Employee) : Boolean
// called from the Employee Table OnModify trigger to update the Learnupon User record

WITH EmployeeRec DO BEGIN
  RequestVar := STRSUBSTNO(UserJson,"Last Name","First Name","E-Mail",'en',"No.","Login Enabled");
END;

IF ISCLEAR(WinHttpService) THEN
  CREATE(WinHttpService,FALSE,TRUE);

WinHttpService.Open('PUT',WebserviceRoot+'users'+STRSUBSTNO(Parameter,EmployeeRec."LearnUpon Id"));
WinHttpService.SetRequestHeader('Host',Host);
WinHttpService.SetRequestHeader('Accept-Encoding','gzip,deflate');
WinHttpService.SetRequestHeader('Content-Type',ContentType);
WinHttpService.SetCredentials(UserName,Password,0);

WinHttpService.Send(RequestVar);

IF WinHttpService.Status <> 200 THEN BEGIN
  IF WinHttpService.Status <> 400 THEN
    MESSAGE('Status %1 %2',WinHttpService.Status,WinHttpService.StatusText);
  EXIT(FALSE);
END;

I'm not satisfied with this but it seems to work so far. If you come up with better solutions let me know!
:-)

davmac1 · 2015-09-24

Have you looked at Gunnar's blog?
http://www.dynamics.is/?p=2303
JSON meets NAV

Howdy, Stranger!

Quick Links

Categories

Using Dotnet in C/AL to consume JSON web services

Comments