The search for a near perfect FSW.

[bachphi]

FileSystemWatcher (FSW) is pretty useful to monitor a certain folder for new file creation, a text file that usually contains some result data. The file is processed by first parsing its content, next send its parsed data on to a SQL DB and finally move the file to an archived folder.

The key here is to know when process of file creation is complete. Most solutions are either relying on regular interval timer check or use several of FSW notify filters ( CreationTime,  FileName,  LastWrite,  Size )  to trigger event and then trying to open the file to check for its completion status. This approach is fine for low rate of file creation, but if there's a lot of files created in a short period of time, buffer overflow and missing file watching can occur.

FSW use a limited & non-paged memory and therefore long file name, deep level of sub directories, filter can all affect its performance. Increase the InternalBufferSize can help but MS only recommend its maximum size of 64KB.

Code: [Select]

Private Sub FileWatcher()
        Dim fsw As New FileSystemWatcher With {
            .Path = sourceDir,                               
            .InternalBufferSize = 65536,
            .IncludeSubdirectories = True,
            .Filter = "*.DAT",
            .NotifyFilter = (NotifyFilters.LastWrite)
            }
        AddHandler fsw.Changed, New FileSystemEventHandler(AddressOf OnChanged)
        fsw.EnableRaisingEvents = True
 End Sub

Perhaps, the most logical choice is the LastWrite notify filter. Oddly enough, LastWrite will actually trigger events twice, it is the second trigger mark the end of file writing and so it's good time to open the file to parse its content.

Code: [Select]

Private _fileCreated As Boolean = False
Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
        If _fileCreated Then                     'catching the second trigger event
             ParseFileContent(e.FullPath)
       'End If
        _fileCreated = Not _fileCreated
End Sub

The next logical one is the Size NotifyFilter, it trigger only once and appear to be at the end of file writing. I added the while loop for self assurance, may not needed:

Code: [Select]

Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
        Dim fileSize As Long = 0
        Dim currentFile As New FileInfo(e.FullPath)
        While Not (FileIsReady(e.FullPath))
            fileSize = currentFile.Length
            Thread.Sleep(25)
            currentFile.Refresh()
        End While
        ParseFileContent(e.FullPath)
End Sub
Private Function FileIsReady(ByVal path As String) As Boolean
        Try
            Using file = System.IO.File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.None)
                Return True
            End Using
        Catch __unusedIOException1__ As IOException
            Return False
        End Try
End Function


[bachphi]

FSW can not watch for files that are already exist in the folder prior to the application running, therefore we need to check for these existing files and process them as well.

You may create a separate thread at the application startup and check for these existing files

Code: [Select]

Dim thr As New Thread(AddressOf CheckFilesPreExist)
        thr.Start()
and the sub

Code: [Select]

Sub CheckFilesPreExist()
        Dim files = Directory.EnumerateFiles(sourceDir, "*.DAT", SearchOption.AllDirectories)
        If (files.Count > 0) Then
            For Each Fil As String In files
                Dim fileName = Path.GetFileName(Fil)
                ParseFileContent(Fil)
            Next
        End If
End Sub

ParseFileContent  parse the result data text file. If the file type is comma delimited type,  one can use TextFieldParser to parse its content. The goal here is to put everything in one string to send to SQL DB.

Code: [Select]

Private Sub ParseFileContent(ByVal filepath As String)

        Dim currentRow As String()
        Dim intCount As Int16 = 0

        txtFile = New FileIO.TextFieldParser(filepath)
        txtFile.TextFieldType = FileIO.FieldType.Delimited
        txtFile.SetDelimiters(";")
        txtFile.CommentTokens = {"Parameter", "Leakage"}
        'Skip header row
        txtFile.ReadFields()
        Try
            While Not txtFile.EndOfData
                currentRow = txtFile.ReadFields()               'Read all field in a row into a string array
                If currentRow.Length = 8 Then
                    str = currentRow
                    str = (String.Join(";", str) & ";").Split(";")      'Add new col8 . Length = 9
                    ' ...
                End If
                If currentRow.Length = 6 Then               'selecting parameters data to record
                    Select Case currentRow(0)
                        ' ...

                    End Select
                End If

            End While
        Catch ex As Exception
            Debug.Print(ex.Message)
        End Try
        txtFile.Close()         'close the open file
        If str IsNot Nothing Then
            Invoke(New MethodInvoker(Sub() Me.dgvCSV.Rows.Add(str)))  ' display data on a DataGridView
        End If

       For i = 0 To 5
                SQL.AddParam("@col" & i, str(i))
       Next
       SQL.ExecQuery("INSERT INTO TB_Data (SerialNo, ResDate, ResTime, NioBits, Th, Pn) " &
                 "VALUES (@col0,@col1,@col2,@col3,@col4,@col5);", True)
       If SQL.HasException(True) Then Exit Sub
           

End Sub


In the above example, I use SQL from a class, courtesy from VB Toolbox user@YouTube which made the challenging of dealing with SQL DB so much easier and more flexible. For example, in my execQuery statement, with the 'True' option being on, I can now check for the last row insert with

Code: [Select]

If SQL.DBDT.Rows.Count > 0 Then
            Dim r As DataRow = SQL.DBDT.Rows(0)
            MsgBox(r("LastID").ToString)
        End If


[bachphi]

well that's sounds wonderful, are you planning to publish a book or something?

[Godra]

That would probably be "The Book of Nonsense".