Using Visual Basic for FTP Access on an AS/400

Introduction

Every morning when I open my eyes, I wonder what the day will bring. Today was such a day where I had the pleasure of encountering different technologies for the first time and this technology is an AS/400. Okay, well not hundred percent the first time—does knowing someone that has worked on these systems count as well? Hopefully not.

Jokes aside. Today, I will show you how to use FTP in VB to gain access to an AS/400's file system, read the data, and store the data into a database.

What's the big fuss?

Sometimes, not everyone shares my enthusiasm. Sometimes, I am making a big deal out of nothing, or, sometimes maybe anything out of the ordinary is something to get excited about…

AS/400

The AS/400 (Application System/400)—now rebranded as IBM iSeries 400—is a mid-range server designed specifically for small businesses and departments in large enterprises.

The AS/400 makes use of the PowerPC microprocessor with its reduced instruction set computer technology. Its operating system is OS/400. For more information regarding AS/400, Wikipedia is always helpful.

File System

The IFS (Integrated File System) was created due to the fact that the AS/400, or rather its Operating System OS/400, has got multiple file systems. The Integrated File System supports stream input/output and storage management in a way similar to personal computer and UNIX operating systems. Similar to a PC file system, it has a root/directory structure and multiple levels of directories.

The file system can include several libraries. A Library is similar to a Windows folder. An important differentiating factor is that the OS/400 system puts all libraries in one library, called QSYS, and there is no hierarchy allowed, meaning all libraries in QSYS are only one level deep. File names can be only 10 characters and file extensions do not have to be specified. QSYS.LIB serves the same purpose as your Program Files folder on your PC.

FTP

FTP (File Transfer Protocol) is the most-used protocol for exchanging files over the Internet. I wrote a few articles about FTP and UTP some time ago; please read through them to get more background information on what we will be doing today.

Our Program

Basically, we will be building an app today that accesses a file that is located on an AS/400 server with the help of FTP. Let's get started

Design

Create a new Visual Basic Windows Forms Project and design your form to resemble Figure 1.

AS400
Figure 1: Our design

Open your App.Config file and edit it to look like the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <startup>
      <supportedRuntime version="v4.0"
         sku=".NETFramework,Version=v4.5" />
   </startup>

   <appSettings>

      <add key="ImportPath" value="C:\Temp\Import"/>

      <add key="ConnectionString" value="Data Source=SERVER;
         Initial Catalog=DBNAME;Persist Security Info=True;
         User ID=DBUSER;
         Password=DBPASSWORD"/>

      <add key="FTPServer" value="IPADDRESS"/>
      <add key="FTPUsername" value="FTPUSERNAME"/>
      <add key="FTPPassword" value="FTPPASSWORD"/>

      <add key="Directory" value="CSPBWUDB"/>
      <add key="File" value="file"/>

      <add key="ClientSettingsProvider.ServiceUri" value=""/>

   </appSettings>
</configuration>

As you can probably tell, a few settings have been added. These settings include the following:

  • ImportPath: This is the path into which the file received from the AS/400 system will be copied
  • ConnectionString: This hosts the connection string to your SQL Database
  • FTPServer: The name of the server which you want to FTP in to
  • FTPUserName: Your user name for FTP
  • FTPPassword: FTP Password
  • Directory: This is the name of the AS/400 library
  • File: The AS/400 file name

Add the following Namespaces:

Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports System.Configuration
Imports System.IO
Imports System.Threading
Imports System.Text.RegularExpressions
Imports System.Globalization
Imports EnterpriseDT.Net.Ftp
Imports System.Data.SqlClient

Add the following modular variables and the constructor:

   Public Shared strImportPath As String = _
      ConfigurationManager.AppSettings("ImportPath").ToString()
   Public Shared strProcessedPath As String = _
      ConfigurationManager.AppSettings("ProcessedPath").ToString()
   Public Shared strDiscarded As String = _
      ConfigurationManager.AppSettings("Discarded").ToString()

   Public Shared strConn As String

   Private _sync As SynchronizationContext

   Public Sub New()

   InitializeComponent()

   strConn = ConfigurationManager.AppSettings _
      ("ConnectionString").ToString()

   End Sub

The variables make communication with our App.Config file easier. For more information regarding the App.Config file, read through here.

Sync is a SynchronizationContext object. For more information regarding the SynchronizationContext class, have a read through here.

All is well in variable world because all variables have been initialized. Add the Form_Load event:

   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load

   _sync = System.Threading.SynchronizationContext.Current
   worker.RunWorkerAsync()

   End Sub

This starts the Backgroundworker. Add the following:

   Private Sub worker_DoWork(sender As Object, _
      e As DoWorkEventArgs)

      If FTPConnectGet() Then

         Dim intFileCount As Integer = _
            Directory.GetFiles(strImportPath).Count()

         Dim intIndex As Integer = 0

         Try

            Dim strLine As [String]

            Dim strStudentName As String
            Dim strStudentSurname As String
            Dim strCourse As String
            Dim intAge As Integer

            For Each strFile As String In _
               Directory.GetFiles(strImportPath)

               intIndex += 1

               'Update label status.'
               _sync.Send(Function(state)
                  lblStatus.Text = "Processing file " + _
                     intIndex.ToString() + _
                     " of " + intFileCount.ToString()

                  lblFile.Text = "File: " + _
                     Path.GetFileName(strFile)

               End Function, Nothing)


               Dim sr As New StreamReader(strFile)

               While (sr.ReadLine()) IsNot Nothing

                  strStudentName = strLine.Substring(0, 10).Trim()
                  strStudentSurname = strLine.Substring(11, 10).Trim()
                  strCourse = strLine.Substring(21, 30).Trim()
                  intAge = Integer.Parse(strLine.Substring(51, 53).Trim())


                  Try
                     ImportStudents(strStudentName, strStudentSurname, _
                        strCourse, intAge)

                  Catch ex As Exception

                     Dim strFileWithDate As String = " " + _
                        Path.GetFileName(strFile).AppendTimeStampToFileName()

                     File.Copy(strFile, Convert.ToString(strDiscarded + "\") _
                        & strFileWithDate)

                     Using swWriter As New StreamWriter("ErrorLog.txt", True)

                        Dim vdate As DateTime = DateTime.Now

                        swWriter.WriteLine()
                        swWriter.WriteLine(vdate.ToString())
                        swWriter.WriteLine("worker_DoWork() - WHILE - " + _
                           ex.ToString())

                     End Using

                  End Try

               End While

               sr.Close()

               sr = Nothing

               Try

                  Dim strFileWithDate As String = " " + _
                     Path.GetFileName(strFile).AppendTimeStampToFileName()

                  File.Copy(strFile, Convert.ToString(strProcessedPath + "\") _
                     & strFileWithDate)

               Catch ex As Exception

                  Using swWriter As New StreamWriter("ErrorLog.txt", True)

                     Dim dtDate As DateTime = DateTime.Now

                     swWriter.WriteLine()
                     swWriter.WriteLine(dtDate.ToString())
                     swWriter.WriteLine("worker_DoWork() - FOR LOOP - " + _
                        ex.ToString())

                  End Using


               End Try


            Next

         Catch ex As System.Exception

            Console.WriteLine(ex.Message.ToString())

            Using swWriter As New StreamWriter("ErrorLog.txt", True)

               Dim dtDdate As DateTime = DateTime.Now

               swWriter.WriteLine()
               swWriter.WriteLine(dtDdate.ToString())
               swWriter.WriteLine("worker_DoWork() - " + ex.ToString())

            End Using

         End Try

      End If

   End Sub

Okay, have some coffee, smoke a cigarette, drink some juice, have a snack, or just simply take a quick break. You will need it.

…Waiting…

Finished waiting. Let's proceed, shall we? It looks more complicated than what it actually is. The IF clause checks to see if FTPConnectGet was successful or not. FTPConnectGet is a function that will be used to download the specified file from the AS/400 server via FTP. The FTP Library I have made use of can be found here. Let's quickly add this function now, and then afterwards I will explain the rest of the Backgroundworker's DoWork event. Add the FTPConnectGet function:

   Private Function FTPConnectGet() As Boolean

      Dim strServer = ConfigurationManager.AppSettings("FTPServer").ToString()
      Dim strUsername = ConfigurationManager.AppSettings("FTPUsername").ToString()
      Dim strPassword = ConfigurationManager.AppSettings("FTPPassword").ToString()
      Dim strFolder = ConfigurationManager.AppSettings("Directory").ToString()
      Dim strFile = ConfigurationManager.AppSettings("File").ToString()

      Dim btDownloadedFile As Byte()

      Dim fcFTP As New FTPClient()

      Try

         fcFTP.RemoteHost = strServer

         fcFTP.Connect()
         fcFTP.ConnectMode = FTPConnectMode.PASV
         fcFTP.Timeout = 60000

         fcFTP.Login(strUsername, strPassword)


         fcFTP.TransferType = FTPTransferType.ASCII

         fcFTP.Site("SITE NAMEFMT 1")

         fcFTP.ChDir(strFolder)

         btDownloadedFile = fcFTP.[Get](strFile)

         Dim strPath = strImportPath + "\" + strFile.ToString()

         _sync.Send(Function(state)
               lblStatus.Text = "Copying File(s) From FTP Server. _
                  Please be Patient."
               lblFile.Text = "File: " + strFile

            End Function, Nothing)

         Dim fsFile As New FileStream(strPath, FileMode.Create, _
            FileAccess.ReadWrite)

         Dim bwWriter As New BinaryWriter(fsFile)

         bwWriter.Write(btDownloadedFile)
         bwWriter.Close()


         Return True

      Catch ex As Exception

         Using swWriter As New StreamWriter("ErrorLog.txt", True)

            Dim dtDate As DateTime = DateTime.Now

            swWriter.WriteLine()
            swWriter.WriteLine(dtDate.ToString())
            swWriter.WriteLine("ftpConnectGet() - " + ex.ToString())

         End Using

         FTPConnection.LogToConsole = True


         Return False

      Finally

         GC.Collect()

      End Try

   End Function

The first few lines update the associated variables with their values in the App.Config file. btDownloadedFile will be the physical file. fcFTP is an FTPClient object; later, I will explain which FTP library I have made use of here and explain where to find these FTP libraries and how to add them to your solution.

I connect to the remoteHost, which is the AS/400 server and specify the username and password that was stored in the App.Config file. I send an FTP command through with the use of the Site object.

I then changed the directory, or changed to the AS/400 library and started the download process. Because I am downloading, I need to update the user interface to indicate our progress; this is where the SynchronizationContext object comes in handy. I then simply write whatever data is being received into the btDownloadedFile object.

If this function succeeds without error, the rest of the Backgroundworker's DoWork event will fire. Let's continue with this event now.

We determine how many files have been downloaded (in this case, only one). This event is supposed to store the contents of the downloaded file into a database. This where proper communication comes in (communication between all departments). Why? Because you will need to know what information is stored in the file. In this example, case student information gets stored.

The file that was downloaded is not a normal CSV or Tab delimited file. Unfortunately, it is a flat file.

You will notice that inside the While loop for the StreamReader I had to hardcode the locations of where each particular piece of information is stored. Working with flat files in this regard takes a bit of getting used to because it can be quite a pain getting the precise values. The information gets stored inside variables and later on, with the use of the Import sub procedure, gets written to a database. The ImportStudents sub procedure looks like the following:

   Public Sub ImportStudents(StudentName As String, _
      StudentSurname As String, StudentCourse As String, _
      StudentAge As Integer)

      Dim con As New SqlConnection(strConn)
      Dim com As New SqlCommand("Import_Students", con)
      '
      com.CommandType = CommandType.StoredProcedure

      Try

         com.Parameters.Clear()

         com.Parameters.AddWithValue("@StudentName", StudentName)
         com.Parameters.AddWithValue("@StudentSurname", StudentSurname)
         com.Parameters.AddWithValue("@StudentCourse", StudentCourse)
         com.Parameters.AddWithValue("@StudentAge", StudentAge)

         com.CommandTimeout = 6000

         con.ConnectionString = strConn

         con.Open()
         com.ExecuteNonQuery()

        com.Dispose()
        con.Close()
        con.Dispose()

      Catch ex As Exception

         con.Close()

      Finally

         GC.Collect()

      End Try

   End Sub

This sub stores the information received from the flat file into a database table with the help of a stored procedure. For those who don't know anything about stored procedures, I would suggest reading this article: Using SQL Stored Procedures with VB.NET.

The physical stored procedure in the SQL database would look more or less like the following:

CREATE proc [dbo].[Import_Students]

      @StudentName VARCHAR(20),
      @StudentSurname VARCHAR(20),
      @StudentCourse VARCHAR(30),
      @StudentAge INT

as
BEGIN

DECLARE @StudentID INT = 1

MERGE Students WITH (HOLDLOCK) AS t
USING (VALUES (   @StudentID)) AS s (     ID)
   ON s.ID = t.ID
WHEN NOT MATCHED BY TARGET THEN

INSERT (
      [StudentName],
      [StudentSurname],
      [StudentCourse],
      [StudentAge]
)


VALUES (
      @StudentName,
      @StudentSurname,
      @StudentCourse,
      @StudentAge
);

For more information regarding the SQL MERGE statement, have a read through here.

Inside the BackgroundWorker_DoWork procedure, you may noticed that I have made use of another function, named AppendTimeStampToFileName. Add this now inside a Module:

Imports System.IO

Module Module1
   <System.Runtime.CompilerServices.Extension> _
   Public Function AppendTimeStampToFileName _
      (strFileName As String) As String

      Return String.Concat(Path.GetFileNameWithoutExtension(strFileName), _
         " ", DateTime.Now.ToString("yyyy-MM-dd, HH-mm-ss"), _
         Path.GetExtension(strFileName))

   End Function

End Module

This is what is called an Extension method, whereby you can extend certain functionality to suit your needs. In this case, it extended the built-in string methods to be able to add a date and time stamp to a file name.

Here is more information regarding extension methods.

Add the last pieces of code for the backgroundworker:

   Private Sub worker_ProgressChanged(sender As Object, _
      e As ProgressChangedEventArgs)

      _sync.Send(Function(state)
         prgBarStatus.Value = e.ProgressPercentage

            lblProgress.Text = [String].Format("{0}%", _
               e.ProgressPercentage)

      End Function, Nothing)


   End Sub

   Private Sub worker_RunWorkerCompleted(sender As Object, _
      e As RunWorkerCompletedEventArgs)

      Application.[Exit]()

   End Sub

This updates the form's display, notifying the user of the progress.

Conclusion

Not a day should go by that you as a programmer do not strive to learn something strange, wonderful, and exciting such as this. AS/400 servers are still very much in use, and programming them is not so complicated, as you can see.



About the Author

Hannes DuPreez

Hannes du Preez is a Microsoft MVP for Visual Basic for the ninth consecutive year. He loves technology and loves Visual Basic. He loves writing articles and proving that Visual Basic is more powerful than what most believe. His ultimate dream is to write a Visual Basic book, hopefully one day that dream will come true. You are most welcome to reach him at: ojdupreez1978@gmail.com

Related Articles

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date