Month: November 2011

Posted on

My asp.net website using NAV Web Service

I am building an asp.net website that communicates with NAV via Web Services.  One of the issues I had to solve was the authentication between the web and the NAV Web Services.

You can ether use NTLM authentication or the current user.  If you will be using NTLM you will need code similar to this in you website.
[code htmlscript=”false” land=”vb”]Dim NAVPunch1 As New WebService.NAVPunch
Dim User As New System.Net.NetworkCredential
User.Domain = "Dynamics.is"
User.UserName = "Gunnar"
User.Password = "<password>"
NAVPunch1.Credentials = User
NAVPunch1.Url = "http://Dynamics.is:7047/DynamicsNAV/WS/" & _
"Dynamics/Codeunit/NAVPunch"[/code]
This also means that you have to store the username, domain and password in you web site code. You will also need to enable NTLM authentication in your CustomSettings.xml
[code htmlscript=”false” lang=”vb”]<add key="WebServicesUseNTLMAuthentication" value="true"></add>[/code]
The other way is to use the credentials of the user running the web. In that case the code would be similar to this:
[code htmlscript=”false” lang=”vb”]NAVPunch1.UseDefaultCredentials = True
NAVPunch1.Url = "http://Dynamics.is:7047/DynamicsNAV/WS/" & _
"Dynamics/Codeunit/NAVPunch"[/code]
And no changes to CustomSettings.xml are required.  The authentication will be handled with IIS.  You will need to go into Internet Information Services (IIS) Manager.  Go into Application Pools and add a new application pool

Select a name that fits you web site and then go to Advanced Settings…

and update the Idendity.

Then go and select this Application Pool for the web site.

The final step is to make sure that the user you select in your code or in the application pool has access to NAV Web Services.  That is done with the standard authentication methods in Dynamics NAV.

Posted on

WSDL Code Generator

On several occasions I have needed to create a code to communicate with Soap Web Services. The manual labor in creating the functions and XML Ports is something that I would like to be rid of. On that node I created a Batch that does most of the job for me.

Execute this and you will be asked to save the “Web Service Objects.txt” file to your computer and from there you will be able to import the file and continue the work.

You will get XMP Ports for every method, both the request and response.  You will also get a Codeunit with a function for every method and the necessary functions to handle the web service communication.

What is left is for you to connect your data to the functions and the XML Ports.

This is not fully tested, so any updates would be appreciated.

Import WSDL (Updated 2012-12-08)

Posted on

Kill Idle Connections

To save concurrent uses in NAV it might be a good idea to kill idle connections automatically.

This can be done by looping through the Session Virtual Table and check the Idle Time field. I like to add the field “Maximum Idle Time” to the User Setup table and compare that field to the Idle Time in the Session table before deciding to kill the session.

The use running this batch must have permission to kill sessions on the SQL Server.
[code htmlscript=”false”]IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
‘Driver={SQL Server Native Client 10.0};’ +
‘Server=<ServerName>;’ +
‘Database=<DatabaseName>;’ +
‘Trusted_Connection=yes;’);
ADOConnection.CommandTimeout(0);

Session – OnAfterGetRecord()
IF "Application Name" <>
‘Microsoft Dynamics NAV Classic client’
THEN
CurrReport.SKIP;
BackSlashPos := STRPOS("User ID",’\’);
IF NOT
UserSetup.GET(
UPPERCASE(COPYSTR("User ID",BackSlashPos + 1)))
THEN
CurrReport.SKIP;
IF FORMAT(UserSetup."Maximum Idle Time") = ” THEN
CurrReport.SKIP;
Idle := "Idle Time";
MaxIdle := (UserSetup."Maximum Idle Time" – 000000T);
IF Idle < MaxIdle THEN
CurrReport.SKIP;

ADOConnection.Execute(STRSUBSTNO(‘KILL %1’,"Connection ID"));

Session – OnPostDataItem()
ADOConnection.Close;
CLEAR(ADOConnection);[/code]

Name DataType Subtype Length
UserSetup Record User Setup
ADOConnection Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Connection
MaxIdle Decimal
Idle Decimal
BackSlashPos Integer

This can be added as a Job Queue Batch.

See here to check for required permission to use the kill statement.

Posted on

Import a text to RTC Note in the Record Link Table

In the process of importing data from MSSQL into NAV I needed to import a comment text. I wanted to import the text into the Note BLOB field in the Record Link table. It was not obvious how to do this but after some work I successfully imported all my comments.

I first start to create the record link with the following code
[code htmlscript=”false”]IF Note <> ” THEN BEGIN
LinkID := DestTbl.ADDLINK(”,DestTbl.TABLECAPTION);
RecordLink.GET(LinkID);
RecordLink.Type := RecordLink.Type::Note;
RecordLink.Note.CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Text2NoteText(Note));
RecordLink.MODIFY;
END;[/code]
After my first round I found out that I needed to include the length of the Note as a Char in the beginning of the note. Then I noticed that my Icelandic characters where not correctly imported and after some study found out that the content of the BLOB needs to be UFT-8 encoded. This led me to create the Text2NoteText function.
[code htmlscript=”false”]Text2NoteText(NoteToImport : Text[1024]) NAVNode : Text[1024]
NAVNode := UTF8Encode(NoteToImport);
NoteLength := STRLEN(NAVNode);
IF NoteLength <= 255 THEN BEGIN
Char1 := NoteLength;
NAVNode := FORMAT(Char1) + NAVNode;
Char2 := 1;
END ELSE BEGIN
Char1 := 128 + (NoteLength – 256) MOD 128;
Char2 := 2 + (NoteLength – 256) DIV 128;
NAVNode := FORMAT(Char1) + FORMAT(Char2) + NAVNode;
END;[/code]
And the UFT8Encode function
[code htmlscript=”false”]UTF8Encode(String2Encode : Text[1024]) EncodedString : Text[1024]
MakeVars;
String2Encode := CONVERTSTR(String2Encode,NavStr,AsciiStr);
FOR Index := 1 TO STRLEN(String2Encode) DO BEGIN
Char2 := String2Encode[Index];
IF Char2 <= 127 THEN
EncodedString := EncodedString + FORMAT(Char2)
ELSE IF Char2 <= 192 THEN BEGIN
Char1 := 194;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END ELSE BEGIN
Char1 := 195;
Char2 := Char2 – 64;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END;
END;[/code]
The MakeVars function creates the NavStr and AsciiStr that I need for the conversion. This is similar to function InitCharTables in Codeunit 424 except I am not using Excel to convert the strings.

Attached is a codeunit with these functions.

RTCNoteTool

 

Posted on

Importing data from MS SQL via CSV File

I have a database with a number of tables that I need to import into NAV in order to create a NAV solution to replace the old outdated solution.

The first step was to build identical tables in NAV.  In my case this was done manually, but there should not be difficult to convert a “CREATE TABLE” command into a NAV object text file.

I then got the name of all the tables in the database with

select name from sys.tables;

and copied the result to Excel. In Excel I first created the select statement in column C

="SELECT * FROM ["&A2&"];"

and then created the sqlcmd function in column G

="sqlcmd -S ServerName -E -d DatabaseName -Q " &
  CHAR(34)&C2&CHAR(34)&" -o "&A2&".csv -s ;"

Then I fill down column C and column G and have all the lines for my command file ready. Note that I use “;” as a decimal separator.

I create a folder for the data and within the folder I create a file called ExtractData.cmd and copy all the lines from column G to that file. Then this command file is executed and that folder fills up with CSV files for each table.

The final step is to import the data with the attached NAV Report.

MSSQL-CSV Import

 

 

Posted on

Web Service that deliveres BLOB data

I wanted to be able to use the Record Link table in NAV to link to my attachments. Since that part of NAV is not customizable every attachment that I link to NAV needs to be accessible via URL.

So, when I import or scan a file into NAV it can be stored in a BLOB or I can store it in a customized external database. After storing the file I create a URL link to that file and insert that as a new link to any record in NAV. To complete this task I created a NAV Web Service that delivers the file as a base64 string. Here is the part of the code that encodes the BLOB stream to a BigText variable.
[code htmlscript=”false”]TempFile.CREATETEMPFILE;
TempFileName := TempFile.NAME;
TempFile.CLOSE;
TempFile.TEXTMODE(FALSE);
TempFile.CREATE(TempFileName);
TempFile.CREATEOUTSTREAM(OutStr);
DocumentStore.Blob.CREATEINSTREAM(InStr);
COPYSTREAM(OutStr,InStr);
TempFile.CLOSE;

CREATE(XMLDoc);
CREATE(ADOStream);
XMLNode := XMLDoc.createNode(‘element’, ‘ImageFile’, ‘SKYRR Signing’);
XMLNode.dataType := ‘bin.base64’;

ADOStream.Type := 1;
ADOStream.Open();
ADOStream.LoadFromFile(TempFileName);
XMLNode.nodeTypedValue := ADOStream.Read();
ADOStream.Close();

Document.ADDTEXT(XMLNode.text);

CLEAR(ADOStream);
CLEAR(XMLNode);
CLEAR(XMLDoc);
ERASE(TempFileName);[/code]
Then I created a aps.net website that can get the document both from this NAV Web Service and also from the customized database all based on the parameters passed with the URL.

Attached is a part of the NAV code and the website required to deliver the attachment.

WebSite

Attachment NAV Source Code