Service to import files

The task is to import every file that is dropped into a specific folder on my local drive into NAV.  The solution is a windows service programmed in Visual Studio 2008 VB.NET.

The first step is to create a web service in Dynamics NAV that accepts a text line and a file name.  Another function to remove the file if the import fails and the third to process the file after it has been imported.

The vb.net code from Visual Studio

[code lang=”vb”]Imports System
Imports System.Timers
Imports System.Net
Imports System.IO

Public Class FileImportService
Dim Salvor1 As Salvor.SalvorWebService
Dim Timer1 As System.Timers.Timer
Dim User As New System.Net.NetworkCredential

Protected Overrides Sub OnStart(ByVal args() As String)
‘ Add code here to start your service. This method should set things
‘ in motion so your service can do its work.
Salvor1 = New Salvor.SalvorWebService
User.Domain = "<Domain>"
User.UserName = "<User>"
User.Password = "<Password>"
Salvor1.Credentials = User

Timer1 = New System.Timers.Timer(30000)
AddHandler Timer1.Elapsed, AddressOf OnTimedEvent

Timer1.Interval = 30000
Timer1.Enabled = True
Timer1.Start()
WriteDebug("Timer 1 Started")

‘ If the timer is declared in a long-running method, use
‘ KeepAlive to prevent garbage collection from occurring
‘ before the method ends.
GC.KeepAlive(Timer1)

End Sub

Protected Overrides Sub OnStop()
‘ Add code here to perform any tear-down necessary to stop your service.
End Sub

Protected Sub OnTimedEvent(ByVal source As Object, ByVal e As ElapsedEventArgs)
Timer1.Enabled = False
Timer1.Stop()
WriteDebug("File Event Started")
Try
If Salvor1.AboutCompany = "SAM" Then
ReadFolder()
End If
Catch ex As Exception
WriteToEventLog("Web Service unavailable:" & ex.ToString, EventLogEntryType.Error)
End Try
Timer1.Enabled = True
Timer1.Start()
End Sub

Protected Sub ReadFolder()
Dim dirInfo As New DirectoryInfo(My.Settings.ImportFolder)
Dim FileArray As FileInfo() = dirInfo.GetFiles()

For Each TextFile In FileArray
If ReadFile(TextFile) Then
WriteDebug("Check File: " & TextFile.Name)
If Salvor1.ProcessFile(TextFile.Name) Then
DeleteFile(TextFile)
Else
WriteDebug("Rollback File: " & TextFile.Name)
Salvor1.RemoveFile(TextFile.Name)
End If

Else
Salvor1.RemoveFile(TextFile.Name)
End If
Next

End Sub

Protected Function ReadFile(ByVal TextFile As FileInfo) As Boolean
Dim Success As Boolean
Try
If File.Exists(TextFile.FullName) Then
Dim ioFile As New StreamReader(TextFile.FullName)
Dim ioLine As String
Success = True

While Not ioFile.EndOfStream
ioLine = ioFile.ReadLine
Success = Success And Salvor1.InsertLine(TextFile.Name, ioLine)
End While
ioFile.Close()
End If
Catch ex As Exception
Success = False
WriteToEventLog("Import of file " & TextFile.FullName & " failed:" & ex.ToString, EventLogEntryType.Error)
End Try
Return Success
End Function

Protected Sub DeleteFile(ByVal TextFile As FileInfo)
Try
WriteDebug("Delete File: " & TextFile.Name)
TextFile.Delete()
Catch ex As Exception
WriteToEventLog("Failed to delete file " & TextFile.FullName & ":" & ex.ToString, EventLogEntryType.Error)
End Try
End Sub

Protected Sub WriteToEventLog(ByVal Message As String, ByVal EntryType As EventLogEntryType)
Dim MyLog As New EventLog()
‘ Check if the the Event Log Exists
If Not Diagnostics.EventLog.SourceExists(Me.ServiceName) Then
Diagnostics.EventLog.CreateEventSource(Me.ServiceName, Me.ServiceName & " Log")
‘ Create Log
End If
MyLog.Source = Me.ServiceName
‘ Write to the Log
Diagnostics.EventLog.WriteEntry(MyLog.Source, Message, EntryType)
End Sub

Protected Sub WriteDebug(ByVal Message As String)
If My.Settings.Debug Then
WriteToEventLog(Message, EventLogEntryType.Information)
End If
End Sub
End Class[/code]

Using Common Dialog Cancel button

In the default Codeunit 412 is a function that opens a open or a save dialog.  The problem with this function is that we do not know if the open, save or cancel button was pressed.  I used the code
[code htmlscript=”false”]FileName := CommonDialogMgt.OpenFile(”,FileName,1,CommonDialogMgt.GetFilterString(1),1);[/code]
where CommondialogMgt is Codeunit 412 to ask the user where to save the file.  If I passed a default file name to this function and the user simply approved the suggestion I would get the same result as if the user pressed cancel.  I wanted to change this and added a few lines to Codeunit 412.

I added a global function SetProperties and GetFileName and a local function OpenFileWithError.
[code htmlscript=”false”]SetProperties(WindowTitle : Text[50];DefaultFileName : Text[1024];DefaultFileType : ‘ ,Text,Excel,Word,Custom,Xml,Htm,Xsd,Xslt’;FilterS
GlobalWindowTitle := WindowTitle;
GlobalDefaultFileName := DefaultFileName;
GlobalDefaultFileType := DefaultFileType;
GlobalFilterString := FilterString;
GlobalAction := Action;

GetFileName() : Text[1024]
EXIT(GlobalFileName);

OpenFileWithError(WindowTitle : Text[50];DefaultFileName : Text[1024];DefaultFileType : ‘ ,Text,Excel,Word,Custom,Xml,Htm,Xsd,Xslt’;Fil
IF DefaultFileType = DefaultFileType::Custom THEN BEGIN
GetDefaultFileType(DefaultFileName,DefaultFileType);
Filter := FilterString;
END ELSE
Filter := GetFilterString(DefaultFileType);

CommonDialogControl.MaxFileSize := 2048;
CommonDialogControl.FileName := DefaultFileName;
CommonDialogControl.DialogTitle := WindowTitle;
CommonDialogControl.Filter := Filter;
CommonDialogControl.InitDir := DefaultFileName;
CommonDialogControl.CancelError := TRUE;

IF Action = Action::Open THEN
CommonDialogControl.ShowOpen
ELSE
CommonDialogControl.ShowSave;

EXIT(CommonDialogControl.FileName);[/code]
I added a few global variables and a code to the onRun trigger
[code htmlscript=”false”]GlobalFileName :=
OpenFileWithError(GlobalWindowTitle,GlobalDefaultFileName,GlobalDefaultFileType,GlobalFilterString,GlobalAction);[/code]This means that I have to change the code
[code htmlscript=”false”]FileName := CommonDialogMgt.OpenFile(”,FileName,1,CommonDialogMgt.GetFilterString(1),1);[/code]to
[code htmlscript=”false”]CommonDialogMgt.SetProperties(”,FileName,1,CommonDialogMgt.GetFilterString(1),1);
IF CommonDialogMgt.RUN THEN
FileName := CommonDialogMgt.GetFileName
ELSE
FileName := ”;[/code]
and I will get an empty file name if cancel is pressed.  Attached is the NAVW16.00.01 Codeunit for NAV 2009 R2.

Codeunit 412

Client Temporary Path

In one of my solutions I create a lot of Excel and PDF documents.  All these documents are stored in BLOB fields and then downloaded to the client computer temporary folder and opened for the user.

Every time I use the ClientTempFileName function in Codeunit 419 a file is being created in the client computer temporary folder and that file is not deleted until the Role Tailored Client is closed.

Since the user is creating temporary files his whole workday I decided that a single instance codeunit would be a better way to store information about the client and server temporary file paths.  I created codeunit 50060 and two functions; GetClientTempPath and GetServerTempPath.
[code htmlscript=”false”]OBJECT Codeunit 50060 Application Temp Path Mgt.
{
OBJECT-PROPERTIES
{
Date=27.03.12;
Time=16:11:00;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
END;

}
CODE
{
VAR
ThreeTierMgt@1200050000 : Codeunit 419;
ClientTempPath@1200050001 : Text[1024];
ServerTempPath@1200050002 : Text[1024];

PROCEDURE GetClientTempPath@1200050000() : Text[1024];
BEGIN
IF ClientTempPath = ” THEN
ClientTempPath := ThreeTierMgt.Path(ThreeTierMgt.ClientTempFileName(”,”));
EXIT(ClientTempPath);
END;

PROCEDURE GetServerTempPath@1200050001() : Text[1024];
BEGIN
IF ServerTempPath = ” THEN
ServerTempPath := ThreeTierMgt.Path(ThreeTierMgt.ServerTempFileName(”,”));
EXIT(ServerTempPath);
END;

BEGIN
END.
}
}[/code]
The Source is here, Application Temporary Path

 

Reading a text file

To follow up the post about writing a text file with Automation I would also like to post about reading a text file with that same Automation.  Now we add the automation automation ‘Windows Script Host Object Model’.File.  First you need to create a file system object ‘Windows Script Host Object Model’.FileSystemObject. Then open the file in the File automation to get the size of the file. Finally open the textstream and start reading. This will give you correct progress dialog and the correct character set when reading.
[code htmlscript=”false”]IF FileName = ” THEN
ERROR(Text001);

CLEAR(SystemFilesSystem);
CREATE(SystemFilesSystem,TRUE,TRUE);

IF NOT SystemFilesSystem.FileExists(FileName) THEN
ERROR(Text002);

SystemFile := SystemFilesSystem.GetFile(FileName);
SystemTextStream := SystemFile.OpenAsTextStream;
FileSize := SystemFile.Size;
FilePos := 0;

Window.OPEN(Text003 + ‘\\@1@@@@@@@@@@@@@@@@@@@@’);
WindowLastUpdated := CURRENTDATETIME;
WHILE NOT SystemTextStream.AtEndOfStream DO BEGIN
Line := SystemTextStream.ReadLine;
FilePos := FilePos + STRLEN(Line) + 2;

‘Handle Line

IF (CURRENTDATETIME – WindowLastUpdated) > 100 THEN BEGIN
Window.UPDATE(1,ROUND(FilePos / FileSize * 10000,1));
WindowLastUpdated := CURRENTDATETIME;
END;
END;
Window.Close;
SystemTextStream.Close;
CLEAR(SystemFilesSystem);[/code]
 

File download with RTC

My earlier post on File Download used the responsestream property of WinHTTP. On my latest project I needed to use Windows Authentication on my website and found out that I needed to create the WinHTTP automation on client level to login with the current user. This also means that I cannot use built in streaming functions to download the file. Instead I used ADOStream function to download the file and in the example that follows I am saving the file to the temporary directory for the current user.
[code htmlscript=”false”]DownloadFile(URL : Text[1024]) FileName : Text[1024]
IF ISCLEAR(WinHTTP) THEN
CREATE(WinHTTP,TRUE,TRUE);

WinHTTP.open(‘GET’,URL,FALSE);
WinHTTP.send(”);

IF WinHTTP.status <> 200 THEN
ERROR(Text023,WinHTTP.status,WinHTTP.statusText);

FileName := WinHTTP.getResponseHeader(‘Content-Disposition’);
IF STRPOS(FileName,’filename=’) = 0 THEN
FileName := ”
ELSE BEGIN
FileName := COPYSTR(FileName,STRPOS(FileName,’filename=’) + 10);
IF ISCLEAR(ADOStream) THEN
CREATE(ADOStream,TRUE,TRUE);

IF ADOStream.State = 1 THEN
ADOStream.Close;

ADOStream.Type := 1; // adVarBinary
ADOStream.Open;
ADOStream.Write(WinHTTP.responseBody);

IF ISCLEAR(FileSystem) THEN
CREATE(FileSystem,TRUE,TRUE);

FileFolder := FileSystem.GetSpecialFolder(2);
FilePath := FileFolder.Path;
ADOStream.SaveToFile(FilePath + ‘\’ + FileName,2);
FileName := FilePath + ‘\’ + FileName;

ADOStream.Close;
END;

CLEAR(WinHTTP);[/code]
Where my variables are

FileSystem – Automation – ‘Windows Script Host Object Model’.FileSystemObject
FileFolder – Automation – ‘Windows Script Host Object Model’.Folder
WinHTTP – Automation – ‘Microsoft XML, v6.0’.XMLHTTP
ADOStream – Automation – ‘Microsoft ActiveX Data Objects 2.8 Library’.Stream
FilePath – Text[1024]

Unzip Files

By using the Automation “‘Microsoft Shell Controls And Automation’.Shell” you can unzip a file within Dynamics NAV.

Create a Global

Name DataType Subtype
SystemShellControl Automation ‘Microsoft Shell Controls And Automation’.Shell
SystemShellItem Automation ‘Microsoft Shell Controls And Automation’.FolderItem
SystemShellItems Automation ‘Microsoft Shell Controls And Automation’.FolderItems
FileName Text
ZipFileName Text
DestFolderName Text
Index Integer
Pos Integer

And then simply
[code]ZipFileName := ‘C:\TEMP\ZipFile.Zip’;
DestFolderName := ‘C:\TEMP\’;
SystemShellItems := SystemShellControl.NameSpace(ZipFileName).Items;
SystemShellControl.NameSpace(DestFolderName).CopyHere(SystemShellItems);
FOR Index := 1 TO SystemShellItems.Count DO BEGIN
SystemShellItem := SystemShellItems.Item(Index – 1);
IF ISSERVICETIER THEN
FileName := SystemShellItem.Path
ELSE
FOR Pos := 1 TO STRLEN(SystemShellItem.Path) DO
IF COPYSTR(SystemShellItem.Path,Pos,1) = ‘\’ THEN
FileName := COPYSTR(SystemShellItem.Path,Pos + 1);
// Do what ever you whant to DestFolderName + FileName
END;[/code]

Download a File

In Dynamic NAV it is possible to use the Automation “‘Microsoft XML, v6.0’.XMLHTTP” to download files.  The code would be
[code]IF ISCLEAR(WinHTTP) THEN
CREATE(WinHTTP,TRUE,FALSE);

WinHTTP.open(‘GET’,URL,FALSE);
WinHTTP.send(”);

IF WinHTTP.status <> 200 THEN
ERROR(Text003,WinHTTP.status,WinHTTP.statusText);

TempFile.CREATE(TempFileName);
TempFile.CREATEOUTSTREAM(OutStr);
InStr := WinHTTP.responseStream;
COPYSTREAM(OutStr,InStr);
TempFile.CLOSE;[/code]
Where Text003 is “Status error %1 %2”

The URL will be downloaded to the filename “TempFileName”