Microsoft Dynamics NAV: Download with FTP using dotNET Interop

Having an extensive C# development background, as I had mentioned in a previous post, I was thrilled with the ability to access the .NET framework through the dotNET Interoperability introduced with the Microsoft Dynamics NAV Roletailored client.

When interfacing data with other systems it is often necessary to upload files via FTP. It may also be necessary to download files via FTP.  This can be accomplished directly in Microsoft Dynamics NAV via Interop.

 

The local variables defined:

Name	DataType	Subtype
FtpWebRequest	DotNet	System.Net.FtpWebRequest.'System'
FtpWebResponse	DotNet	System.Net.FtpWebResponse.'System'
NetworkCredential	DotNet	System.Net.NetworkCredential.'System'
Stream	DotNet	System.IO.Stream.'mscorlib'
SIOFile	DotNet	System.IO.File.'mscorlib'
StreamReader	DotNet	System.IO.StreamReader.'mscorlib'
MemoryStream	DotNet	System.IO.MemoryStream.'mscorlib'

The method to download the file from the remote site:

DownloadFile(siteaddress : Text[250];username : Text[80];password : Text[80];filename : Text[250];path : Text[250])

FtpWebRequest := FtpWebRequest.Create(siteaddress + '/' + filename);
FtpWebRequest.Credentials := NetworkCredential.NetworkCredential(username, password);

FtpWebRequest.Method := 'RETR';
FtpWebRequest.KeepAlive := TRUE;
FtpWebRequest.UseBinary := TRUE;
//FtpWebRequest.UsePassive := FALSE;

filename := path + filename;

FtpWebResponse := FtpWebRequest.GetResponse;
Stream := FtpWebResponse.GetResponseStream;

// Text files only
//StreamReader := StreamReader.StreamReader(Stream);
//SIOFile.WriteAllText(path + filename, StreamReader.ReadToEnd);

MemoryStream := MemoryStream.MemoryStream();
Stream.CopyTo(MemoryStream);
SIOFile.WriteAllBytes(filename, MemoryStream.GetBuffer());

Stream.Close;

IF GUIALLOWED THEN BEGIN
  MESSAGE('%1 %2', FtpWebResponse.StatusCode, FtpWebResponse.StatusDescription);
END;

Microsoft Dynamics NAV: FTPWebRequest Methods String Values

I had previously posted on using dotNET Interop to FTP a file to a remote server. In the code the command is specified in the line: FtpWebRequest.Method := ‘STOR’;  The other methods available are:

‘APPE’  AppendFile
‘DELE’  DeleteFile
‘RETR’  DownloadFile
‘MDTM’  GetDateTimestamp
‘SIZE’  GetFileSize
‘NLIST’ ListDirectory
‘LIST’  ListDirectoryDetails
‘MKD’   MakeDirectory
‘PWD’   PrintWorkingDirectory
‘RMD’   RemoveDirectory
‘RENAME’ Rename
‘STOR’  UploadFile
‘STOU’  UploadFileWithUniqueName 

 

Microsoft Dynamics NAV: Upload with FTP using dotNET Interop

One of the biggest additions to the development environment with the advent of the RoleTailored Client Microsoft and Dynamics NAV Server was the availability of the Microsoft .NET Framework assemblies. This allows Microsoft Dynamics NAV objects can interact with .NET Framework objects. You can easily use assemblies from the .NET Framework class library, your own custom assemblies; or third-party assemblies. This allows you to extend your solution and not be limited to only the NAV or COM objects.

One such example of using dotNET Interoperability is the ability to use the dotNET framework to FTP files to a remote server. Without access to the dotNET framework it was necessary to create a third party COM control, call a batch file, or schedule some third party software to transfer a file. Now, you can generate and send files all from within NAV. This can centralize the task of generating and transmitting files to one application.

A function that can be used (and easily extended) to upload a file to a remote FTP server via dotNET Interop in NAV is listed below.

Local Variables

Name			DataType	Subtype
FtpWebRequest		DotNet		System.Net.FtpWebRequest.'System'
FtpWebResponse		DotNet		System.Net.FtpWebResponse.'System'
NetworkCredential	DotNet		System.Net.NetworkCredential.'System'
Stream			DotNet		System.IO.Stream.'mscorlib'
SIOFile			DotNet		System.IO.File.'mscorlib'
UploadFile(siteaddress : Text[250];username : Text[80];password : Text[80];filename : Text[250];path : Text[250])

FtpWebRequest := FtpWebRequest.Create(siteaddress + '/' + filename);

FtpWebRequest.Credentials := NetworkCredential.NetworkCredential(username, password);
FtpWebRequest.Method := 'STOR';
FtpWebRequest.KeepAlive := TRUE;
FtpWebRequest.UseBinary := TRUE;
//FtpWebRequest.UsePassive := FALSE;
filename := path + filename;

FtpWebRequest.ContentLength := SIOFile.ReadAllBytes(filename).Length;
Stream := FtpWebRequest.GetRequestStream;
Stream.Write(SIOFile.ReadAllBytes(filename), 0, FtpWebRequest.ContentLength);
Stream.Close;

FtpWebResponse := FtpWebRequest.GetResponse;

IF GUIALLOWED THEN BEGIN
    MESSAGE('%1 %2', FtpWebResponse.StatusCode, FtpWebResponse.StatusDescription);
END;

Microsoft Dynamics NAV: Copy Table Data from one Company to another Company

In Microsoft Dynamics NAV you can setup separate distinct companies. Unless a table is setup to share data across all companies, by setting the DataPerCompany property to No, each company’s data is separate and distinct from the other. In many cases it may be desirable to have tables that contain the same values across companies. Due to validation and performance considerations the setting of the DataPerCompany property may not be an option.

Fortunately in C/SIDE we can use the CHANGECOMPANY function to specify the company that a record variable references. Using this method we can easily copy data from one company to the other. It is simple enough to explicitly define two variables for a particular record and write a few lines of code to copy all of the data from one company to the other. I am all about reuse and prefer not modifying code for each table or scenario. Fortunately in C/SIDE we can make use of the RecordRef variable type. This allows us to dynamically specify the tables to reference and create a neat little function:

CopyTableFromTo(FromCompanyName : Text[30];ToCompanyName : Text[30];TableNo : Integer)

IF GUIALLOWED THEN BEGIN
  Window.OPEN('#1###############\' +
              '@2@@@@@@@@@@@@@@@');
END;

// Open source table
FromTableRef.OPEN(TableNo, FALSE, FromCompanyName);
IF GUIALLOWED THEN BEGIN
  Window.UPDATE(1, FromTableRef.NAME);
END;
// Open destination table
ToTableRef.OPEN(TableNo, FALSE, ToCompanyName);
RecCount := FromTableRef.COUNT;

// Loop through the fields
IF FromTableRef.FINDFIRST THEN REPEAT
  RecNo += 1;
  IF GUIALLOWED THEN BEGIN
        Window.UPDATE(2,ROUND(RecNo/RecCount * 10000, 1));
  END;
  ToTableRef.INIT;
  FOR i := 1 TO FromTableRef.FIELDCOUNT DO BEGIN
    FromFieldRef := FromTableRef.FIELDINDEX(i);
    ToFieldRef := ToTableRef.FIELDINDEX(i);
    //TODO: Check field types
    ToFieldRef.VALUE := FromFieldRef.VALUE;
  END;
  IF NOT ToTableRef.INSERT THEN ToTableRef.MODIFY;
UNTIL FromTableRef.NEXT = 0;

ToTableRef.CLOSE;
FromTableRef.CLOSE;

IF GUIALLOWED THEN BEGIN
  Window.CLOSE;
END;

With the variables:

 

This function can be use in ways similar to:

//This is intended to be called in a function:
//CopyFromSource(CompanyName : Text[30];TableNo : Integer)

// Verify company is valid
Company.GET('CRONUS USA, Inc.');
// Set the table number; in this case Country/Region
TableNo := 9;

Company.SETFILTER(Name, '<>%1', 'CRONUS USA, Inc.');
IF Company.FINDSET(FALSE, FALSE) THEN REPEAT
  CopyTableFromTo(CompanyName, Company.Name, TableNo);
UNTIL Company.NEXT = 0;

Note: This function does not trigger and code (or data) validation. When using the CHANGECOMPANY function if you run validation code on a variable for Company B in Company A, it will modify the record in Company B, but it will run the trigger in Company A.

Microsoft Dynamics NAV: Set the Color Style of a List Page Row

In Microsoft Dynamics NAV a List page displays content from a table in a list format. It is often desired to differentiate the data in the list based upon a record value. Fortunately this can be accomplished by setting the Style Property of a Field type on the page. The text in a field will be displayed according to the Style Property value selected. The Style can be set to one of the following values and be formatted as:

StandardStandard
StandardAccentBlue
Strong  – Bold
Strong AccentBlue + Bold
AttentionRed + Italic
AttentionAccentBlue + Italic
FavorableBold + Green
UnfavorableBold + Italic + Red
AmbiguousYellow
SubordinateGrey

Setting the Style property fixes the style of the field contents to the option selected. What if you want to set the style dynamically? That can be done with the StyleExpr property. The StyleExpr property sets whether the format that is specified in the Style Property is applied to text in a field. You can set the StyleExpr value to the name of a variable or a Style Property text (in apostrophes) ‘Strong’.

 

Setting the StyleExpr Property to a variable allows it to be set dynamically by code. For example, create a function SetStyle that returns a Text value according to the desired logic:

 

Then set the variable after you retrieve the record, the formatting will be applied to each record.