Using Dotnet in C/AL to consume JSON web services

RonTaylor
Member Posts: 4
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
:-)
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
:-)
Ron Taylor
ICS Support, Inc.
8541 154th Ave. NE
Redmond, WA. 98052
ICS Support, Inc.
8541 154th Ave. NE
Redmond, WA. 98052
0
Comments
-
Have you succeeded the job?
I'm facing the same challenge.
Tino Ruijs
Microsoft Dynamics NAV specialist0 -
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"}]}
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-8UpdateLearningUser(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!
:-)Ron Taylor
ICS Support, Inc.
8541 154th Ave. NE
Redmond, WA. 980520 -
David Machanick
http://mibuso.com/blogs/davidmachanick/0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions