Built a Service but memory keeps climbing

Question

I build a service that looks into a database and if a parameter is missing it adds it. Then it makes sure that while reading other parameters that the folders exists and if not it creates them. Then if a file exists in one folder it moves it to another to be processed.

The problem that I have is that the memory just keeps climbing as it runs over multiple days. It climbs from 35k to 1000s of megs.

Any feedback would be appreciated

Here is the code

Imports log4net
Imports log4net.Config
Imports System.Configuration
Imports System.IO
Imports System.Reflection
Imports System.Security.AccessControl
Imports System.Security.Principal
Imports System.Threading
Imports System.Data.SqlClient
Imports Microsoft


Public Class EDIService
    Implements IDisposable

    Private Shared ReadOnly _log As ILog = LogManager.GetLogger(GetType(EDIService))

    Dim strStorageFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)
    Dim strWorkFolder As String = strStorageFolder & "\EDIService"
    Dim strLogFolder As String = strWorkFolder & "\Log"

    Dim objDSMS As DataSet
    Dim objDTMS As DataTable
    Dim objDSMT As DataSet
    Dim objDTMT As DataTable
    Dim objDSP As DataSet
    Dim objDTP As DataTable

    Private stopping As Boolean
    Private stoppedEvent As ManualResetEvent


    Public Sub New()
        InitializeComponent()
        'Init the log for net settings (must have in 4.0 Framework)
        log4net.Config.XmlConfigurator.Configure()
        Me.stopping = False
        Me.stoppedEvent = New ManualResetEvent(False)

        StartUp()

    End Sub

#Region " On Start "
    ''' <summary>
    ''' The function is executed when a Start command is sent to the service
    ''' by the SCM or when the operating system starts (for a service that 
    ''' starts automatically). It specifies actions to take when the service 
    ''' starts. In this code sample, OnStart logs a service-start message to 
    ''' the Application log, and queues the main service function for 
    ''' execution in a thread pool worker thread.
    ''' </summary>
    ''' <param name="args">Command line arguments</param>
    ''' <remarks>
    ''' A service application is designed to be long running. Therefore, it 
    ''' usually polls or monitors something in the system. The monitoring is 
    ''' set up in the OnStart method. However, OnStart does not actually do 
    ''' the monitoring. The OnStart method must return to the operating 
    ''' system after the service's operation has begun. It must not loop 
    ''' forever or block. To set up a simple monitoring mechanism, one 
    ''' general solution is to create a timer in OnStart. The timer would 
    ''' then raise events in your code periodically, at which time your 
    ''' service could do its monitoring. The other solution is to spawn a 
    ''' new thread to perform the main service functions, which is 
    ''' demonstrated in this code sample.
    ''' </remarks>
    Protected Overrides Sub OnStart(ByVal args() As String)
        ' Log a service start message to the Application log.
        _log.Info("EDI Service in OnStart.")

        ' Queue the main service function for execution in a worker thread.
        ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf ServiceWorkerThread))

    End Sub
#End Region

#Region " Startup "
    Public Sub StartUp()
        Try

            'Dim applicationName As String = Environment.GetCommandLineArgs()(0)
            'Dim exePath As String = System.IO.Path.Combine(Environment.CurrentDirectory, applicationName)
            'Dim cfg As Configuration = ConfigurationManager.OpenExeConfiguration(exePath)
            ''XmlConfigurator.Configure(New System.IO.FileInfo(Application.ExecutablePath + ".config"))
            '_log.Info("Got the config file " & cfg.ToString)

            _log.Info("--------------------------------------------------------------------------------")
            _log.Info("New Startup Date: " & Format(Now(), "yyyy-MM-dd HH:mm:ss"))
            _log.Info("--------------------------------------------------------------------------------")

            'set folder to be accessable from authenticated users
            Dim folderinfolog As DirectoryInfo = New DirectoryInfo(strLogFolder)
            Dim folderinfowork As DirectoryInfo = New DirectoryInfo(strWorkFolder)

            'Check to make sure log folder is there first
            If Not folderinfolog.Exists Then
                Directory.CreateDirectory(strLogFolder)
                _log.Info("Created Folder " & folderinfolog.ToString)
            End If

            If Not folderinfowork.Exists Then
                Directory.CreateDirectory(strWorkFolder)
                _log.Info("Created Folder " & folderinfowork.ToString)
            End If

            _log.Info("Setting folder and files permissions")
            Dim folderacllog As New DirectorySecurity(strLogFolder, AccessControlSections.Access)
            folderacllog.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.BuiltinUsersSid, Nothing), FileSystemRights.FullControl, InheritanceFlags.ContainerInherit Or InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow))
            folderinfolog.SetAccessControl(folderacllog)

            Dim folderaclwork As New DirectorySecurity(strWorkFolder, AccessControlSections.Access)
            folderaclwork.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.BuiltinUsersSid, Nothing), FileSystemRights.FullControl, InheritanceFlags.ContainerInherit Or InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow))
            folderinfowork.SetAccessControl(folderaclwork)


            ' Queue the main service function for execution in a worker thread.
            ' Uncomment to run manually
            'ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf ServiceWorkerThread))

        Catch ex As Exception
            _log.Error(ex.ToString & vbCrLf & ex.StackTrace.ToString)

        End Try
    End Sub
#End Region

#Region "Loop Message Systems"
    Public Sub LoopMessageSystems()
        Try

            Dim builder As New SqlConnectionStringBuilder()
            builder.DataSource = My.Settings.MsSQLHostName
            _log.Info("Set the MsSQL Hostname to " & My.Settings.MsSQLHostName)
            builder.InitialCatalog = My.Settings.MsSQLDataBaseName
            _log.Info("Set the MsSQL Database Name to " & My.Settings.MsSQLDataBaseName)
            builder.IntegratedSecurity = True
            builder.MultipleActiveResultSets = True

            Using comm As New SqlConnection(builder.ConnectionString)
                Try
                    comm.Open()
                    If comm.State Then
                        _log.Info("Connected to SQL Database...")
                    Else
                        _log.Error("Not connected to SQL Database...")
                        Return
                    End If

                    'Clean up logs files
                    Dim logFiles As String() = Directory.GetFiles(strLogFolder)
                    For Each logFile As String In logFiles
                        Dim fileInfo As New FileInfo(logFile)

                        'set file to be accessable from authenticated users
                        'Dim fileacl As New FileSecurity(fileInfo.FullName, AccessControlSections.Access)
                        'fileacl.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.AuthenticatedUserSid, Nothing), FileSystemRights.FullControl, AccessControlType.Allow))
                        'fileInfo.SetAccessControl(fileacl)

                        If fileInfo.CreationTime < DateTime.Now.AddMonths("-" & My.Settings.PurgeLogFilesMonth) Then
                            _log.Info("Found Log file(s) that are more then " & My.Settings.PurgeLogFilesMonth & " month old to delete " & fileInfo.ToString)

                            _log.Info("Verifying that the old file ends with .txt")
                            If fileInfo.Name.Contains(".txt") Then
                                fileInfo.Delete()
                                _log.Info("Deleted the file " & fileInfo.Name.ToString)
                            Else
                                _log.Info("Did not delete the file " & fileInfo.Name.ToString)
                            End If
                        Else
                            _log.Info("This file" & fileInfo.Name.ToString & " is not older then " & My.Settings.PurgeLogFilesMonth & " month")
                        End If

                    Next

                    'Clean up work files
                    Dim strFiles As String() = Directory.GetFiles(strWorkFolder)
                    For Each strFile As String In strFiles
                        Dim fileInfo As New FileInfo(strFile)

                        'set file to be accessable from authenticated users
                        'Dim fileacl As New FileSecurity(fileInfo.FullName, AccessControlSections.Access)
                        'fileacl.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.AuthenticatedUserSid, Nothing), FileSystemRights.FullControl, AccessControlType.Allow))
                        'fileInfo.SetAccessControl(fileacl)

                        If fileInfo.CreationTime < DateTime.Now.AddMonths("-" & My.Settings.PurgeLogFilesMonth) Then
                            _log.Info("Found file(s) that are more then " & My.Settings.PurgeLogFilesMonth & " month old to delete " & fileInfo.ToString)
                            fileInfo.Delete()
                            _log.Info("Deleted the file " & fileInfo.Name.ToString)
                        End If
                    Next

                    _log.Info("Building a list of active message systems")
                    Dim command As SqlCommand = Nothing
                    Dim sqlda As SqlDataAdapter = Nothing

                    Dim strSQL = "select * from mscmessagesystem where adapter='Folder' and status =0"
                    '_log.Info(strSQL)

                    command = New SqlCommand(strSQL, comm)
                    command.CommandType = CommandType.Text
                    command.CommandTimeout = 0
                    sqlda = New SqlDataAdapter
                    sqlda.SelectCommand = command
                    objDSMS = New DataSet
                    sqlda.Fill(objDSMS)

                    objDTMS = objDSMS.Tables(0)
                    objDTP = New DataTable
                    objDTP.Columns.Add("SectionID", Type.GetType("System.Int32"))
                    objDTP.Columns.Add("Name", Type.GetType("System.String"))
                    objDTP.Columns.Add("Value", Type.GetType("System.String"))

                    Dim pr As DataRow = objDTP.NewRow

                    Dim intInputFileCount As Int32 = 0
                    Dim strInputFolder As String = ""
                    Dim strEDIServiceFolder As String

                    For Each msr As DataRow In objDTMS.Rows

                        _log.Info("Looking at message system parameter called " & msr.Item("Name"))

                        strSQL = "select sectionID,[Name],Value from infParameter where sectionID = " & msr.Item("sectionID").ToString & " and ([name] like 'Input Folder Name%' or [name] like 'EDIService Folder%') order by ID asc"
                        '_log.Info(strSQL)

                        command = New SqlCommand(strSQL, comm)
                        command.CommandType = CommandType.Text
                        command.CommandTimeout = 0
                        sqlda = New SqlDataAdapter
                        sqlda.SelectCommand = command
                        objDSP = New DataSet
                        sqlda.Fill(objDSP)
                        objDTP = objDSP.Tables(0)

                        For Each r As DataRow In objDTP.Rows

                            If objDTP.Rows.Count < 2 Then
                                _log.Info("Looks like we do not have a EDIService Folder setup")
                                strSQL = "INSERT INTO [dbo].[infParameter]
                                       ([mainttime]
                                       ,[userID]
                                       ,[name]
                                       ,[scope]
                                       ,[value]
                                       ,[comments]
                                       ,[sectionID]
                                       ,[helpcontextid]
                                       ,[isaudited]
                                       ,[datatype]
                                       ,[allowpersonalvalues]
                                       ,[allowgroupvalues]
                                       ,[allowplantvalues]
                                       ,[parametertype]
                                       ,[obsolete]
                                       ,[enumeration]
                                       ,[businessClassID]
                                       ,[configurationchangereference]
                                       ,[configurationnotes])
                                 VALUES
                                       (getdate(),system_user,'EDIService Folder',0,'',null," & r.Item("SectionID") & ",null,-1,0,null,null,null,0,null,null,null,null,null)"
                                command = New SqlCommand(strSQL, comm)
                                command.CommandType = CommandType.Text
                                command.CommandTimeout = 0
                                command.ExecuteNonQuery()
                                _log.Info("Added the EDIService folder to the section ID " & r.Item("SectionID"))
                                Exit For
                            End If

                            _log.Info("Looking at infparameter row ID " & String.Join(", ", r.ItemArray))


                            If r.Item("Name") = "Input Folder Name" Then
                                If IsDBNull(r.Item("value")) Then
                                    _log.Info("The input folder is null for message system named " & msr.Item("Name") & ", you must have it set moving on to the next one")
                                    Exit For
                                End If
                                If r.Item("Name") = "Input Folder Name" AndAlso r.Item("Value") = Nothing Then
                                    _log.Info("The input folder does not have a value for message system named " & msr.Item("Name") & ", you must have it set")
                                    Exit For
                                Else
                                    _log.Info("The input folder does have a value set as " & r.Item("Value") & " continue to verify the Input Folder")
                                    strInputFolder = r.Item("value")
                                    If Not strInputFolder = Nothing Then
                                        If Directory.Exists(strInputFolder) = False Then
                                            _log.Info("Creating directory " & strInputFolder)
                                            Directory.CreateDirectory(strInputFolder)
                                            _log.Info("Created directory " & strInputFolder)
                                        Else
                                            _log.Info("Folder already exists " & strInputFolder)
                                        End If
                                    Else
                                        _log.Info("Folder value is blank, quiting and moving on to the next folder")
                                        Exit For
                                    End If

                                    'look if file exist to be processed
                                    _log.Info("Looking in folder: " & strInputFolder)

                                    _log.Info("Sorting the files in write time ascending order")
                                    Dim files = From file In New DirectoryInfo(strInputFolder).GetFileSystemInfos Where file.Name Like "*.*"
                                                Order By file.LastWriteTime Ascending Select file
                                    _log.Info("Done sorting the files in write time ascending order")

                                    If files.Count > 0 Then
                                        intInputFileCount = files.Count
                                        _log.Info("Found " & files.Count.ToString & " files..., so we are exiting for this message system until files are processed by KMC")
                                    Else
                                        _log.Info("Did not find any files..., lets go look to see if there are any files waiting to be moved")
                                    End If
                                End If
                            End If


                            If r.Item("Name") = "EDIService Folder" AndAlso intInputFileCount = 0 Then
                                If IsDBNull(r.Item("value")) Then
                                    _log.Info("The EDIService folder is null for message system named " & msr.Item("Name") & ", you must have it set moving on to the next one")
                                    Exit For
                                End If
                                If r.Item("Name") = "EDIService Folder" AndAlso r.Item("Value") = Nothing Then
                                    _log.Info("The EDIService folder does not have a value for message system named " & msr.Item("Name") & ", you must have it set")
                                    Exit For
                                Else
                                    _log.Info("The EDIService folder does have a value set as " & r.Item("Value") & " continue to verify the ESIService Folder")
                                    strEDIServiceFolder = r.Item("value")
                                    If Not strEDIServiceFolder = Nothing Then
                                        If Directory.Exists(strEDIServiceFolder) = False Then
                                            _log.Info("Creating directory " & strEDIServiceFolder)
                                            Directory.CreateDirectory(strEDIServiceFolder)
                                            _log.Info("Created directory " & strEDIServiceFolder)
                                        Else
                                            _log.Info("Folder already exists " & strEDIServiceFolder)
                                        End If
                                    Else
                                        _log.Info("Folder value is blank, quiting and moving on to the next folder")
                                        Exit For
                                    End If

                                    'look if file exist to be processed
                                    _log.Info("Looking in folder: " & strEDIServiceFolder)

                                    Dim files = From file In New DirectoryInfo(strEDIServiceFolder).GetFileSystemInfos Where file.Name Like "*.*"
                                                Order By file.LastWriteTime Ascending Select file

                                    If files.Count > 0 Then
                                        _log.Info("Found " & files.Count.ToString & " files... going to move on file ")
                                        File.Copy(strEDIServiceFolder & "\" & files(0).Name, strInputFolder & "\" & files(0).Name)
                                        _log.Info("Copied file " & strEDIServiceFolder & "\" & files(0).Name & " to " & strInputFolder & "\" & files(0).Name)
                                        File.Delete(strEDIServiceFolder & "\" & files(0).Name)
                                        _log.Info("Deleted file " & strEDIServiceFolder & "\" & files(0).Name)
                                    Else
                                        _log.Info("Did not find any files to process...")
                                    End If
                                End If
                            Else
                                intInputFileCount = 0
                                _log.Info("Resetting the file count in that folder of " & intInputFileCount & " to 0")
                            End If

                            'Dim _Dir As New DirectoryInfo(r.Item("value"))

                            'If Directory.Exists(r.Item("value")) Then
                            '    _log.Info("Looking in folder: " & _Dir.ToString)
                            '    Dim files = From file In _Dir.GetFileSystemInfos Where file.Name Like "*.*"
                            '                Order By file.CreationTime Ascending Select file

                            '    _log.Info("Found " & files.Count.ToString & " files...")


                            'End If

                        Next
                        objDSP.Clear()
                        objDTP.Clear()

                    Next

                Catch ex As Exception
                    _log.Error(ex.ToString & vbCrLf & ex.StackTrace.ToString)
                Finally
                    comm.Close()
                    _log.Info("Closed the connection")
                End Try
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End Using

        Catch ex As Exception
            _log.Error(ex.ToString & vbCrLf & ex.StackTrace.ToString)
        Finally

        End Try

    End Sub
#End Region

#Region " Service Work Thread "
    ''' <summary>
    ''' The method performs the main function of the service. It runs on a 
    ''' thread pool worker thread.
    ''' </summary>
    ''' <param name="state"></param>
    Private Sub ServiceWorkerThread(ByVal state As Object)
        ' Periodically check if the service is stopping.
        Do While Not Me.stopping

            _log.Info("We are starting to loop through the message systems")
            LoopMessageSystems()
            _log.Info("We are finished looping through the message systems")
            _log.Info("Sent to garbage collector")

            Dispose(True)
            ' Perform main service function here...
            _log.Info("Sleeping for " & My.Settings.TimerMilliseconds / 1000 & " seconds")
            Thread.Sleep(My.Settings.TimerMilliseconds)  ' Simulate some lengthy operations.
        Loop

        ' Signal the stopped event.
        Me.stoppedEvent.Set()

    End Sub
#End Region

#Region " On Stop "
    ''' <summary>
    ''' The function is executed when a Stop command is sent to the service 
    ''' by SCM. It specifies actions to take when a service stops running. In 
    ''' this code sample, OnStop logs a service-stop message to the 
    ''' Application log, and waits for the finish of the main service 
    ''' function.
    ''' </summary>
    Protected Overrides Sub OnStop()
        ' Log a service stop message to the Application log.
        _log.Info("EDIService in OnStop.")

        ' Indicate that the service is stopping and wait for the finish of 
        ' the main service function (ServiceWorkerThread).
        Me.stopping = True
        Me.stoppedEvent.WaitOne()
    End Sub
#End Region

End Class

Answer 1

The following shows how to create a Windows Service in VB.NET that uses BackgroundWorker. The database table definitions weren't provided in the OP, so the code will need to be modified to work with your database.

VS 2022:

• Open Visual Studio 2022
• Click
• Click File
• Select New
• Select Project
• For filter, select:
• 
• Click Next
• Enter desired project name (ex: WindowsServiceTestRH)
• Select desired .NET Framework (ex: .NET Framework 4.8)
• Click Create

Open Toolbox:

• In VS menu, click View
• Select Toolbox

Open Solution Explorer:

• In VS menu, click View
• Select Solution Explorer

Open Properties Window

• In VS menu, click View
• Select Properties Window

Add project reference:

• In VS menu, click Project
• Select Add Reference...
• Click Assemblies
• Check System.Configuration

Download/install NuGet package:

• In Solution Explorer, right-click <project name> (ex: WindowsServiceTestRH)
• Select Manage NuGet Packages...
• Click Browse tab
• In the search box type: log4net
• Select log4net
• Select desired version (ex: 2.0.15)
• Click Install
• If a MessageBox appears, click OK

Add a class: (name: CurrentState.vb)

• In VS menu, click Project
• Select Add New Item...
• Select Class (name: CurrentState.vb)
• Click Add

CurrentState.vb:

Public Class CurrentState
    'ToDo: if desired, add additional properties
    Public Property Status As String
    Public Property PercentDone As Integer
End Class

Add a class: (name: Helper.vb)

Note: It's not necessary to create a separate class for the code that's run by the BackgoundWorker. See here for how to do it without creating a separate class.

• In VS menu, click Project
• Select Add New Item...
• Select Class (name: Helper.vb)
• Click Add

Helper.vb:

'Add reference
'Project => Add reference... => Assemblies => System.Configuration


Imports log4net
Imports System.Configuration
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Public Class Helper

    Private _cleanLogFiles As Boolean = False
    Private _connectionStr As String = String.Empty
    Private _log As ILog = Nothing
    Private _logFolder As String = String.Empty
    Private _purgeLogFilesMonth As Integer = 0
    Private _workFolder As String = String.Empty

    Public Property CleanLogFiles As Boolean
        Get
            Return _cleanLogFiles
        End Get
        Set(value As Boolean)
            'set value
            _cleanLogFiles = value
        End Set
    End Property

    Public Property Log As ILog
        Get
            Return _log
        End Get
        Set(value As ILog)
            _log = value
        End Set
    End Property

    Public Property LogFolder As String
        Get
            Return _logFolder
        End Get
        Set(value As String)
            'set value
            _logFolder = value
        End Set
    End Property

    Public Property PurgeLogFilesMonth As Integer
        Get
            Return _purgeLogFilesMonth
        End Get
        Set(value As Integer)
            'set value
            _purgeLogFilesMonth = value
        End Set
    End Property

    Public Property WorkFolder As String
        Get
            Return _workFolder
        End Get
        Set(value As String)
            'set value
            _workFolder = value
        End Set
    End Property

    Public Sub New()
        'Windows authentication
        '_connectionStr = ConfigurationManager.ConnectionStrings("TestRH").ConnectionString
        _connectionStr = ConfigurationManager.ConnectionStrings("TestRHSqlServerAuthentication").ConnectionString
    End Sub

    Private Sub CleanUpLogs(worker As System.ComponentModel.BackgroundWorker, e As System.ComponentModel.DoWorkEventArgs)
        'Dim state As CurrentState = New CurrentState()

        If worker.CancellationPending Then
            'set value
            e.Cancel = True
            Return
        End If

        Try
            'Clean up logs files
            Dim logFiles As String() = Directory.GetFiles(_logFolder)

            For Each logFile As String In logFiles

                If worker.CancellationPending Then
                    'set value
                    e.Cancel = True
                    Return
                End If

                'state.Status = $"Processing file {logFile}..."

                'report progress (raise event)
                'worker.ReportProgress(0, state)

                Dim fileInfo As New FileInfo(logFile)

                'set file to be accessable from authenticated users
                'Dim fileacl As New FileSecurity(fileInfo.FullName, AccessControlSections.Access)
                'fileacl.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.AuthenticatedUserSid, Nothing), FileSystemRights.FullControl, AccessControlType.Allow))
                'fileInfo.SetAccessControl(fileacl)

                If fileInfo.CreationTime < DateTime.Now.AddMonths(_purgeLogFilesMonth * -1) Then
                    _log.Info("Found Log file(s) that are more then " & _purgeLogFilesMonth.ToString() & " month old to delete " & fileInfo.ToString)

                    _log.Info("Verifying that the old file ends with .txt")

                    If fileInfo.Name.Contains(".txt") Then
                        fileInfo.Delete()
                        _log.Info("Deleted the file " & fileInfo.Name.ToString)
                    Else
                        _log.Info("Did not delete the file " & fileInfo.Name.ToString)
                    End If
                Else
                    _log.Info("This file" & fileInfo.Name.ToString & " is not older then " & _purgeLogFilesMonth.ToString() & " month")
                End If
            Next
        Catch ex As Exception
            _log.Error("CleanUpLogs - {ex.Message}")
            Throw
        End Try
    End Sub

    Private Sub CleanUpWorkFiles(worker As System.ComponentModel.BackgroundWorker, e As System.ComponentModel.DoWorkEventArgs)
        'Dim state As CurrentState = New CurrentState()

        If worker.CancellationPending Then
            'set value
            e.Cancel = True
            Return
        End If

        Try
            'Clean up work files
            Dim strFiles As String() = Directory.GetFiles(_workFolder)
            For Each strFile In strFiles
                If worker.CancellationPending Then
                    'set value
                    e.Cancel = True
                    Return
                End If

                'state.Status = $"Processing file {strFile}..."

                'report progress (raise event)
                'worker.ReportProgress(0, state)

                Dim fileInfo As New FileInfo(strFile)

                'set file to be accessable from authenticated users
                'Dim fileacl As New FileSecurity(fileInfo.FullName, AccessControlSections.Access)
                'fileacl.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.AuthenticatedUserSid, Nothing), FileSystemRights.FullControl, AccessControlType.Allow))
                'fileInfo.SetAccessControl(fileacl)

                If fileInfo.CreationTime < DateTime.Now.AddMonths(_purgeLogFilesMonth * -1) Then
                    _log.Info("Found file(s) that are more then " & _purgeLogFilesMonth.ToString() & " month old to delete " & fileInfo.ToString)
                    fileInfo.Delete()
                    _log.Info("Deleted the file " & fileInfo.Name.ToString)
                End If
            Next
        Catch ex As Exception
            _log.Error("CleanUpWorkFiles - {ex.Message}")
            Throw
        End Try
    End Sub

    Public Sub Execute(worker As System.ComponentModel.BackgroundWorker, e As System.ComponentModel.DoWorkEventArgs)
        'ToDo: add desired code

        If worker.CancellationPending Then
            'set value
            e.Cancel = True
            Return
        End If

        _log.Info("In Sub Execute.")

        If _cleanLogFiles Then
            CleanUpLogs(worker, e)
            CleanUpWorkFiles(worker, e)
        End If

        'update database
        UpdateDatabase(worker, e)
    End Sub

    Private Sub UpdateDatabase(worker As System.ComponentModel.BackgroundWorker, e As System.ComponentModel.DoWorkEventArgs)
        'ToDo: re-write this method
        'ensure that tables have been properly designed (search "database normalization")
        'and perform a JOIN

        Dim strSql As String = String.Empty
        Dim dtMscMessageSystem As DataTable = New DataTable()
        Dim dtInfParameter As DataTable = New DataTable()

        _log.Info("In Sub UpdateDatabase.")

        If worker.CancellationPending Then
            'set value
            e.Cancel = True
            Return
        End If

        Try
            Using con As SqlConnection = New SqlConnection(_connectionStr)
                'open
                con.Open()

                Using cmd As SqlCommand = New SqlCommand("select * from mscmessagesystem where adapter = @adapter and status = @status", con)
                    With cmd
                        .Parameters.Add("@adapter", SqlDbType.VarChar).Value = "Folder"
                        .Parameters.Add("@status", SqlDbType.Int).Value = 0
                    End With

                    'set value
                    strSql = cmd.CommandText

                    Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                        'get data from database
                        da.Fill(dtMscMessageSystem)
                    End Using
                End Using

                If dtMscMessageSystem.Rows.Count = 1 Then
                    _log.Info($"Retrieved {dtMscMessageSystem.Rows.Count} row from table 'mscmessagesystem' (SQL: '{strSql}')")
                Else
                    _log.Info($"Retrieved {dtMscMessageSystem.Rows.Count} rows from table 'mscmessagesystem' (SQL: '{strSql}')")
                End If

                For Each msr As DataRow In dtMscMessageSystem.Rows
                    Using cmd As SqlCommand = New SqlCommand("SELECT ID, sectionID, name, value from infParameter WHERE sectionID = @sectionID AND ([name] like @name1 OR [name] like @name2) order by ID asc", con)
                        With cmd
                            .Parameters.Add("@sectionID", SqlDbType.VarChar).Value = 1
                            .Parameters.Add("@name1", SqlDbType.VarChar).Value = $"Input Folder Name%"
                            .Parameters.Add("@name2", SqlDbType.VarChar).Value = $"EDIService Folder%"
                        End With

                        'set value
                        strSql = cmd.CommandText

                        'create new instance
                        'ToDo: consider renaming this
                        Dim dtP As DataTable = New DataTable()

                        Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                            'get data from database
                            da.Fill(dtP)
                        End Using

                        If dtP.Rows.Count = 1 Then
                            _log.Info($"Retrieved {dtP.Rows.Count} row from table 'infParameter' (SQL: '{strSql}')")
                        Else
                            _log.Info($"Retrieved {dtP.Rows.Count} rows from table 'infParameter' (SQL: '{strSql}')")
                        End If

                        For Each row As DataRow In dtP.Rows
                            'ToDo: add desired code
                            _log.Info($"Processing SectionID: '{row("sectionID")?.ToString()}' Name: '{row("name")}'")
                        Next
                    End Using

                    'ToDo: add desired code

                Next
            End Using
        Catch ex As SqlException
            _log.Error($"UpdateDatabase - {ex.Message} (SQL: '{strSql}')")
            Throw ex
        Catch ex As Exception
            _log.Error("UpdateDatabase - {ex.Message}")
            Throw ex
        End Try

    End Sub
End Class

Note: In the code above, method "UpdateDatabase" needs to be modified to your desired code. While I didn't utilize "ReportsProgress", I added the code (and commented it out) in case you wish to use it. See the documentation for BackgroundWorker.ReportsProgress and BackgroundWorker.ProgressChanged Event for more information. The database connection string is retrieved from "App.config" (see below) and will need to be modified.

Rename Service1.vb:

• In Solution Explorer, right-click "Service1.vb"

• Select Rename

• Enter desired name (ex: EDIService.vb)

• When the following MessageBox appears:

  

  Click Yes

Add EventLog:

• In Solution Explorer, right-click EDIService.vb
• Select View Designer
• Open Toolbox, and select EventLog
• Drag EventLog to the design (gray) area

Modify code (EDIService.vb):

• In Solution Explorer, right-click EDIService.vb
• Select View Code

EDIService.vb:

Imports log4net
Imports System.Configuration
Imports System.IO
Imports System.Reflection
Imports System.Security.AccessControl
Imports System.Security.Principal
Imports System.Timers
Imports System.ComponentModel

Public Class EDIService

    Private _backgroundWorker1 As BackgroundWorker = Nothing
    Private _cleanLogFileIntervalMinutes As Integer
    Private _helper As Helper = Nothing
    Private _log As ILog = Nothing
    Private _logFilesLastCleaned As DateTime = DateTime.MinValue
    Private _purgeLogFilesMonth As Integer = 0
    Private _strStorageFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) 'C:\ProgramData
    Private _strWorkFolder As String = Path.Combine(_strStorageFolder, "EDIService")
    Private _strLogFolder As String = Path.Combine(_strWorkFolder, "Logs")
    Private _timer1 As System.Timers.Timer = Nothing

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

        'Init the log for net settings (must have in 4.0 Framework)
        log4net.Config.XmlConfigurator.Configure()

        _log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType)

        'set properties
        EventLog1.Log = "Application"
        EventLog1.Source = "EDI"

        If Not EventLog.SourceExists("EDI") Then
            EventLog.CreateEventSource("EDI", "EDI Log")
        End If
    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As DoWorkEventArgs)
        Dim worker As BackgroundWorker = DirectCast(sender, BackgroundWorker)
        Dim helper1 As Helper = DirectCast(e.Argument, Helper)

        'execute (call sub)
        helper1.Execute(worker, e)
    End Sub

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs)
        Dim state As CurrentState = DirectCast(e.UserState, CurrentState)

    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs)
        'ToDo: add any code here that needs to execute after the BackgroundWorker finishes

        If e.Error IsNot Nothing Then
            'handle errors here. such as logging them
            'replace newline in message with space
            _log.Error($"{e.Error.Message.Replace("\r\n", "\n").Replace("\r", "\n").Replace("\n", " ")} InnerException: {e.Error.InnerException} StackTrace: {e.Error.StackTrace}")
        ElseIf e.Cancelled Then
            'user cancelled the operation

            'ToDo: add desired code
        Else
            'completed successfully 

            'ToDo: add desired code
        End If
    End Sub

    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.

        'initialize
        StartUp()

        ' Log a service start message to the Application log.
        _log.Info("EDI Service in OnStart.")

        'EventLog1.WriteEntry($"EDI service started. Log location: {_strLogFolder}")
    End Sub

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

        'stop timer
        _timer1.Stop()

        If _backgroundWorker1.IsBusy Then
            'cancel BackgroundWorker
            _backgroundWorker1.CancelAsync()
        End If

        Do
            System.Threading.Thread.Sleep(300)
        Loop While _backgroundWorker1.IsBusy()

        'unsubscribe from events
        RemoveHandler _timer1.Elapsed, AddressOf Timer1_Elapsed

        'unsubscribe to events (add event handlers)
        RemoveHandler _backgroundWorker1.DoWork, AddressOf BackgroundWorker1_DoWork
        RemoveHandler _backgroundWorker1.ProgressChanged, AddressOf BackgroundWorker1_ProgressChanged
        RemoveHandler _backgroundWorker1.RunWorkerCompleted, AddressOf BackgroundWorker1_RunWorkerCompleted

        _timer1 = Nothing
        _backgroundWorker1 = Nothing
        _helper = Nothing

        'EventLog1.WriteEntry($"EDI service stopped. Log location: {_strLogFolder}")
    End Sub

    Private Sub Timer1_Elapsed(sender As Object, e As ElapsedEventArgs)
        'ToDo: add desired code

        _log.Info("Timer1_Elapsed event.")

        Dim elapsed As TimeSpan = DateTime.Now.Subtract(_logFilesLastCleaned)

        'clean log files every 6 hours
        If elapsed.Minutes >= _cleanLogFileIntervalMinutes Then
            'set value
            _logFilesLastCleaned = DateTime.Now
            RunTasks(True)
        Else
            RunTasks(False)
        End If
    End Sub

    Public Sub StartUp()
        Try
            '_log = LogManager.GetLogger(GetType(EDIService))
            '_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType)

            _log.Info("EDI service starting...")
            _log.Info("Reading app.config 'CleanLogFileIntervalMinutes'...")

            Dim cleanLogFileIntervalMinutesStr As String = ConfigurationManager.AppSettings("CleanLogFileIntervalMinutes")
            Dim purgeLogFilesMonthStr As String = ConfigurationManager.AppSettings("PurgeLogFilesMonth")

            If Not String.IsNullOrEmpty(cleanLogFileIntervalMinutesStr) Then
                If Not Integer.TryParse(cleanLogFileIntervalMinutesStr, _cleanLogFileIntervalMinutes) Then
                    'log error
                    _log.Error("Couldn't parse CleanLogFileIntervalMinutesStr.")

                End If
            End If

            If Not String.IsNullOrEmpty(purgeLogFilesMonthStr) Then
                If Not Integer.TryParse(purgeLogFilesMonthStr, _purgeLogFilesMonth) Then
                    'log error
                    _log.Error("Couldn't parse PurgeLogFilesMonthStr.")

                End If
            End If

            _log.Info("Initializing backgroundworker...")

            'create new instance
            _backgroundWorker1 = New BackgroundWorker()

            'set properties
            _backgroundWorker1.WorkerReportsProgress = True
            _backgroundWorker1.WorkerSupportsCancellation = True

            'subscribe to events (add event handlers)
            AddHandler _backgroundWorker1.DoWork, AddressOf BackgroundWorker1_DoWork
            AddHandler _backgroundWorker1.ProgressChanged, AddressOf BackgroundWorker1_ProgressChanged
            AddHandler _backgroundWorker1.RunWorkerCompleted, AddressOf BackgroundWorker1_RunWorkerCompleted

            _log.Info("Initializing timer...")

            'timer
            _timer1 = New System.Timers.Timer()

            'set properties
            _timer1.Interval = 60000 'ms 

            'subscribe to events
            AddHandler _timer1.Elapsed, AddressOf Timer1_Elapsed

            'Dim applicationName As String = Environment.GetCommandLineArgs()(0)
            'Dim exePath As String = System.IO.Path.Combine(Environment.CurrentDirectory, applicationName)
            'Dim cfg As Configuration = ConfigurationManager.OpenExeConfiguration(exePath)
            ''XmlConfigurator.Configure(New System.IO.FileInfo(Application.ExecutablePath + ".config"))
            '_log.Info("Got the config file " & cfg.ToString)

            _log.Info("--------------------------------------------------------------------------------")
            _log.Info("New Startup Date: " & Format(Now(), "yyyy-MM-dd HH:mm:ss"))
            _log.Info("--------------------------------------------------------------------------------")

            'set folder to be accessable from authenticated users
            Dim folderinfolog As DirectoryInfo = New DirectoryInfo(_strLogFolder)
            Dim folderinfowork As DirectoryInfo = New DirectoryInfo(_strWorkFolder)

            'Check to make sure log folder is there first
            If Not folderinfolog.Exists Then
                Directory.CreateDirectory(_strLogFolder)
                _log.Info("Created Folder " & folderinfolog.ToString)
            End If

            If Not folderinfowork.Exists Then
                Directory.CreateDirectory(_strWorkFolder)
                _log.Info("Created Folder " & folderinfowork.ToString)
            End If

            _log.Info("Setting folder and files permissions")

            Dim folderacllog As New DirectorySecurity(_strLogFolder, AccessControlSections.Access)
            folderacllog.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.BuiltinUsersSid, Nothing), FileSystemRights.FullControl, InheritanceFlags.ContainerInherit Or InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow))
            folderinfolog.SetAccessControl(folderacllog)

            Dim folderaclwork As New DirectorySecurity(_strWorkFolder, AccessControlSections.Access)
            folderaclwork.AddAccessRule(New FileSystemAccessRule(New SecurityIdentifier(WellKnownSidType.BuiltinUsersSid, Nothing), FileSystemRights.FullControl, InheritanceFlags.ContainerInherit Or InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow))
            folderinfowork.SetAccessControl(folderaclwork)

            _log.Info("Starting timer...")

            'start
            _timer1.Start()

        Catch ex As Exception
            _log.Error($"Error: {ex.Message} InnerException: {ex.InnerException} StackTrace: {ex.StackTrace}")
        End Try
    End Sub


    Private Sub RunTasks(cleanLogFiles As Boolean)
        If _helper Is Nothing Then
            'create new instance and set properties
            _helper = New Helper() With {.Log = _log, .LogFolder = _strLogFolder, .PurgeLogFilesMonth = _purgeLogFilesMonth, .WorkFolder = _strWorkFolder}
        End If

        'set value
        _helper.CleanLogFiles = cleanLogFiles

        If Not _backgroundWorker1.IsBusy Then
            _backgroundWorker1.RunWorkerAsync(_helper)
        End If
    End Sub
End Class

Add installer:

• In Solution Explorer, right-click EDIService.vb

• Select View Designer

  

• Right-click in gray area, and select Add installer

  

Set installer properties

• In Solution Explorer, click ProjectInstaller.vb
• Click ServiceInstaller1 to select it
• In Properties Window, set desired property (ex: Description, DisplayName, ServiceName) value(s)

Modify App.config:

Note: The connection strings will need to be modified to work with your database.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <!-- configSections MUST be the first element -->
    <configSections>
        <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
    </configSections>
    <appSettings>
        <add key="CleanLogFileIntervalMinutes" value="360"/>
        <add key="PurgeLogFilesMonth" value="1"/>
    </appSettings>
    <connectionStrings>
        <add name="TestRH" connectionString="Server='.\SQLExpress'; Database='TestRH'; Trusted_Connection=True" providerName="System.Data.SqlClient" />
        <add name="TestRHSqlServerAuthentication" connectionString="Server=.\SQLExpress; Database=TestRH; User Id=EDIUser; Password=*mysecretpassword*;" providerName="System.Data.SqlClient" />
    </connectionStrings>
    <log4net>
        <appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender">
            <param name="File" value="C:\ProgramData\EDIService\Logs\log.txt"/>
            <lockingModel type="log4net.Appender.FileAppender.MinimalLock" />
            <appendToFile value="true" />
            <rollingStyle value="Size" />
            <maxSizeRollBackups value="2" />
            <maximumFileSize value="1MB" />
            <staticLogFileName value="true" />
            <layout type="log4net.Layout.PatternLayout">
                <param name="ConversionPattern" value="%d [%t] %-5p %c %m%n"/>
            </layout>
        </appender>

        <root>
            <level value="ALL" />
            <appender-ref ref="LogFileAppender" />
        </root>
    </log4net>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
</configuration>

Compile as x64

Copy files from "bin" folder to desired location

To Install service:

• %windir%\Microsoft.NET\Framework64\<version>\installutil.exe <fully-qualified-path to WindowsServiceTestRH.exe>

To uninstall service:

• %windir%\Microsoft.NET\Framework64\<version>\installutil.exe /u <fully-qualified-path to WindowsServiceTestRH.exe>

Note: Other installation options may be available if you search.

Resources:

• Tutorial: Create a Windows service app
• Async/await vs BackgroundWorker
• unable to get log4net working with .net windows service