Encrypt/Decrypt strings in C/AL

Following can be used, if you need a encryption/decryption function in NAV. In this quite simple sample there is shown the usage of the TripleDES encryption algorithm. The base are the cryptograhic function in namespace System.Security.Cryptography of the .net framework.

// local variables
TripleDESEnc	DotNet	System.Security.Cryptography.TripleDES.'mscorlib, Version=4.0.0.0, culture=neutral, PublicKeyToken=b77a5c561934e089'	
cStream	DotNet	System.Security.Cryptography.CryptoStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
cMode	DotNet	System.Security.Cryptography.CryptoStreamMode.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
cEnc	DotNet	System.Security.Cryptography.ICryptoTransform.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
TripleDESDec	DotNet	System.Security.Cryptography.TripleDES.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
cDec	DotNet	System.Security.Cryptography.ICryptoTransform.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
cKey	DotNet	System.Byte.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
initVector	DotNet	System.Byte.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
fStream	DotNet	System.IO.FileStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
fMode	DotNet	System.IO.FileMode.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
sWriter	DotNet	System.IO.StreamWriter.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
sReader	DotNet	System.IO.StreamReader.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
sData	Text	 1024
FileName	Text	 1024
file	File	

// the code
sData := 'Here is some data to encrypt.';
FileName := 'c:\temp\CText.txt';

// encrypt part
fMode := fMode.OpenOrCreate;
fStream := fStream.FileStream(FileName,fMode);

TripleDESEnc := TripleDESEnc.Create('TripleDES');
// create encryptor and internally a random key and a random IV
cEnc := TripleDESEnc.CreateEncryptor;

cMode := cMode.Write;
cStream := cStream.CryptoStream(fStream,cEnc,cMode);
sWriter := sWriter.StreamWriter(cStream);
sWriter.WriteLine(sData);

sWriter.Close;
cStream.Close;
fStream.Close;

file.OPEN(FileName);
file.READ(sData);
file.CLOSE;

// display encrypted string
MESSAGE('Encrypted: ' + sData);

// decrypt
fMode := fMode.OpenOrCreate;
fStream := fStream.FileStream(FileName, fMode);

TripleDESDec := TripleDESDec.Create;
// reuse the key/iv-pair, created above
cDec := TripleDESDec.CreateDecryptor(TripleDESEnc.Key,TripleDESEnc.IV);

cMode := cMode.Read;
cStream := cStream.CryptoStream(fStream,cDec,cMode);
sReader := sReader.StreamReader(cStream);
sData := sReader.ReadLine;

sReader.Close;
cStream.Close;
fStream.Close;

MESSAGE('Decrypted: ' + sData);

In the sample the same Key/IV pair was used for encryption and decryption, automatically created by method CreateEncryptor. To use your own Key/IV pair use method CreateEncryptor(byte[] key, byte[] iv) or set the Key and IV property of the encryptor/decryptor.

...
CreateByteArray(cKey, 'abcdefghijklmnoqprstuvwx'); // length: 24
CreateByteArray(initVector, 'abcdefgh'); // length: 8
TripleDESEnc.Key(cKey);
TripleDESEnc.IV(initVector);
cEnc := TripleDESEnc.CreateEncryptor;
...

CreateByteArray(VAR ByteArray : DotNet "System.Array";InputString : Text)
// local variables
// idx	Integer		
// ArrayType	DotNet	System.Type.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
// ByteValue	Byte		
// StrLength	Integer		
StrLength := strlen(InputString);
ArrayType := ArrayType.GetType('System.Byte',FALSE);
ByteArray := ByteArray.CreateInstance(ArrayType,StrLength);
FOR idx := 1 TO StrLength DO BEGIN
  ByteValue := InputString[idx];
  ByteArray.SetValue(ByteValue,idx - 1);
END;

Links:
http://msdn.microsoft.com/de-de/library/z565ef9x(v=vs.110).aspx

Advertisements

Sql Statements in C/AL – Part 2 : Create View

The following code can be used to create a sql view in a given, external database.

// local variables
ADOConnection	  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADOCommand	  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command
ADORecSet	  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
connString	  Text 1024
activeConnection  Variant
viewName	  Text 100

// the code
// replace the server ip and the ext. databasename with your values
connString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';
CREATE(ADOConnection);
ADOConnection.ConnectionString(connString);
ADOConnection.Open;
// convert ADO Connection to Variant
activeConnection := ADOConnection;

viewName := 'CRONUS$Item View';
CREATE(ADOCommand);
ADOCommand.ActiveConnection := activeConnection;
ADOCommand.CommandType := 1;
ADOCommand.CommandText :=
  'SELECT * FROM INFORMATION_SCHEMA.TABLES where ' +
  '(TABLE_TYPE = ''VIEW'') and (TABLE_NAME =''' + viewName + ''')';
ADORecSet := ADOCommand.Execute;
IF ADORecSet.EOF = TRUE THEN BEGIN
  ADOCommand.CommandText := 'CREATE VIEW [' + viewName + ']' +
    'AS SELECT No_, Description FROM dbo.[CRONUS$Item]' +
    'WHERE No_ like ''100%''';
  ADOCommand.Execute;
  message('view created');
END ELSE
  MESSAGE('view already exists');

ADORecSet.Close;
ADOConnection.Close;
CLEARALL;

follow also posting “Sql Statements in C/AL – Part 1 : Select”.

cheers

Error, when creating an instance of a .net class

Following error can occur, when creating an instance of a .net class in C/AL:

nav_error_1

Reasons:

  • If the assembly is used on client side (property RunOnClient=True), then the assembly was not copied to the local Add-ins Folder of the RTC Client. To copy the assembly into this folder you need in most cases Admin rights. So, check that first.
  • If the assembly is used on server side (property RunOnClient=False), then there might be a version conflict between the assembly file located in the Add-ins Folder of the Nav Service and the used version in the C/AL Code. That can easy occur after installing Updates. So update the code and restart the nav service.
  • Missing System permission: That means, that the assembly was copied from the web or from a computer outside the local network to a target computer/server with windows server OS. Then, the assembly is automatically blocked by the system.

Executing external programs

Sometimes it’s needed to add functionality not delivered by NAV. One way is to use .Net assemblies or automations. Another scenario can be to run an external program in C/AL. One way is to use the quite old SHELL Command or class WshShell of the “Windows Scripting Host” (automation). In both cases, when running an external program, a security warning is displayed … not so good for automatic execution (batch).

Class System.Diagnostics.Process from .Net assembly System.dll
To avoid that and to have also a much prettier solution, use the class System.Diagnostics.Process from the .Net Framework (search for assembly System.dll). For usage in RTC Client set the property RunOnClient to true, for usage on the server only like job queues set the value to false.

Sample: creating a file using the dir command in a dos shell

// variable process | dotnet | System.Diagnostics.Process (assembly System.dll)
process := process.Process;
process.StartInfo.UseShellExecute := FALSE;
process.StartInfo.FileName := ‘cmd.exe’;
process.StartInfo.Arguments := ‘/c dir > c:\temp\dir.txt’;
process.StartInfo.CreateNoWindow := TRUE;
process.Start();
CLEAR(process);

sample: extract a rar file

process := process.Process;
process.StartInfo.UseShellExecute := FALSE;
process.StartInfo.FileName := ‘”C:\Program Files (x86)\WinRAR\unrar.exe”‘;
process.StartInfo.Arguments := ‘e -y -o+ c:\temp\archive.rar c:\temp’;
process.StartInfo.CreateNoWindow := TRUE;
process.Start();
CLEAR(process);

Stop pending processes:
Use methods WaitForExit or Kill to stop pending processes.

Links:
http://msdn.microsoft.com/en-us/library/system.diagnostics.process%28v=vs.110%29.aspx
http://msdn.microsoft.com/en-us/library/dd355282.aspx
http://msdn.microsoft.com/en-us/library/system.diagnostics.process.waitforexit(v=vs.110).aspx

cheers

Convert date string containing a month text value to a date value

Assuming you want to enter date values in the RTC client like 15-MAR-14. that won’t work. date values are only accepted with syntax like 15.03, 15.03.14, etc. But it can be that users prefer the above syntax. so then you can use a text field in your page and convert internally the value into a date value. Following function converts that kind of date string to a date value.

// local variables
// dateString | Text
// dateValue  | Date
dateString := '15-MAR-14';
dateValue := ConvertDateString(dateString);
MESSAGE(FORMAT(dateValue,0,'<day,2>.<month,2>.<year4>'));

ConvertDateString(dateString : Text[9]) : Date
// local variables
// dateString | Text
// dayValue   | Integer
// months     | Text
// monthValue | Integer
// yearValue  | Integer
// dateValue  | Date
// mPos       | Integer
// monthText  | Text

if strpos(dateString,'-') > 0 then begin
  dateString := CONVERTSTR(dateString,'-',',');
  EVALUATE(dayValue,SELECTSTR(1,dateString));
  months := 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec';
  monthText := lowercase(SELECTSTR(2,dateString));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(months,monthText);
  if mPos >0 then begin
    monthValue := (mPos + 3) / 4;
    EVALUATE(yearValue,SELECTSTR(3,dateString));
    IF yearValue > 14 THEN
      yearValue := yearValue + 1900
    ELSE
      yearValue := yearValue + 2000;
    dateValue := DMY2DATE(dayValue,monthValue,yearValue);
    exit(dateValue);
  end else
    error('No valid month given: ' + monthText);
end;
exit(0D);

to use the standard functionality of date validation and conversion it’s needed to change function MakeDateText in codeunit 1:

MakeDateText(VAR DateText : Text[250]) : Integer
// additional local variables
// mPos | Integer
// monthText | Text | 10
// monthValue | Integer
// Text Constant: Text022 | jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec

Position := 1;
Length := STRLEN(DateText);
ReadCharacter(' ',DateText,Position,Length);
 
// begin changes
IF STRPOS(DateText,'-') > 0 THEN BEGIN
  DateText := CONVERTSTR(DateText,'-',',');
  monthText := lowercase(SELECTSTR(2,DateText));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(Text022,monthText);
  IF (mPos > 0) THEN BEGIN
    monthValue := (mPos + 3) / 4;
    // optional: add a leading 0, if needed; simple use format(monthValue) should also work
    monthText := PADSTR('',2 - STRLEN(FORMAT(monthValue)),'0') + FORMAT(monthValue);
    DateText := SELECTSTR(1,DateText) + '-' + monthText + '-' + SELECTSTR(3,DateText);
  END ELSE
    error('No valid month given: ' + monthText);
END;
// end changes
 
IF NOT FindText(PartOfText,DateText,Position,Length) THEN
...

cheers

Process decimal values with different regional settings

When importing/processing decimal values, the decimal separator is . or , according to the regional setting. Assuming you want to import decimal values with US Standard regional setting, then the separator is a point (.).

To process such decimal values, you can work with the NAV Command GLOBALLANGUAGE. That is the in NAV at runtime used language.

impValue := 0.25;
impValStr := format(impValue);

// check for GER and FR language
if (GLOBALLANGUAGE = 1031) OR (GLOBALLANGUAGE = 1036) then
  impValStr := Convertstr(impValStr,'.'','); // replaces the point by a comma

Additional you can use the Command WINDOWSLANGUAGE. This command returns the regional system setting (the language of the OS). In common with that command you can handle every case.

for language codes follow
http://technet.microsoft.com/en-us/library/dd346950.aspx

links:
http://msdn.microsoft.com/en-us/library/dd338772.aspx
http://msdn.microsoft.com/en-us/library/dd301095.aspx

nav 2013 web client limitations & possibilities

the nav web client is the second client to connect to dynamics nav. it was introduced with nav 2013. the main purpose is for outdoor activities. but there are some limitations.

  • the menu structure is reduced to the minimum.
  • there are no possibilities to customize that client.
  • not all pages can be reached by default.

but there are possibilities to pimp the web client.

  • to view any page in the webclient you only need the number of the page,
    e.g. http://localhost:8080/List.aspx?page=16
  • the search window:
    the search window works like the search box in the windows client. every page is so reachable.
    to enable the search window, goto folder C:\Program Files\Microsoft Dynamics NAV\70\Web Client, there open the file web.config in admin mode. search for “<add key=”ShowPageSearch” value=”false”/>”. change the value to true.
    restart the nav webclient in the iis admin.
  • addins for usage with the webclient, goto:

    http://msdn.microsoft.com/en-us/library/jj672863(v=nav.71).aspx

    http://msdn.microsoft.com/en-us/library/dn182584(v=nav.71).aspx
  • for special customization (only for web developer pros):
    add/change the settings in section <httpHandlers> in the web.config
and what else (like george clooney says)?
develope your own client app using nav webservices.

what else?