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.
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.