POP3-Client mit Relationaler Datenbank

pop3download.exe /server:pop.mailserver.de /user:USERNAME /password:PASSWORD /dbpath:"C:\Pfad\database.mdb" /fileroot:"C:\Pfad\Attachments"

Imports System.Text
Imports System.Text.RegularExpressions
Imports Limilabs.Client.POP3
Imports Limilabs.Mail
Imports Limilabs.Mail.MIME
Imports Limilabs.Mail.Headers
Imports System.Data

Module Pop3ToDatabase
    Private _server As String
    Private _user As String
    Private _password As String
    Private _attachmentRootPath As String
    Private _dbPath As String
    Private _dbConn As OleDb.OleDbConnection

    Sub Main()
        Dim cmdArgs() As String = System.Environment.GetCommandLineArgs()
        If cmdArgs.Length = 6 Then
            Dim line As String = [String].Join("|", cmdArgs, 1, 5)  
            _server = New Regex("/server:([^\|]*)", RegexOptions.IgnoreCase).Match(line).Groups(1).Value  
            _user = New Regex("/user:([^\|]*)", RegexOptions.IgnoreCase).Match(line).Groups(1).Value  
            _password = New Regex("/password:([^\|]*)", RegexOptions.IgnoreCase).Match(line).Groups(1).Value  
            _attachmentRootPath = New Regex("/fileroot:([^\|]*)", RegexOptions.IgnoreCase).Match(line).Groups(1).Value  
            _dbPath = New Regex("/dbpath:([^\|]*)", RegexOptions.IgnoreCase).Match(line).Groups(1).Value  
        Else
            Console.WriteLine("Falsche Anzahl an Parametern!")  
            System.Environment.Exit(1)
        End If

        Using pop3 As New Pop3
            Try
                pop3.Connect(_server)                           ' Use overloads or ConnectSSL if you need to specify different port or SSL.  
                pop3.UseBestLogin(_user, _password)
            Catch ex As Exception
                Console.WriteLine("Error connecting to POP3-Server." & vbCrLf & ex.Message)  
                System.Environment.Exit(2)
            End Try

            Dim uids As List(Of String) = pop3.GetAll()             ' Get unique-ids of all messages.  

            If uids.Count > 0 Then
                If ConnectDB() Then
                    For Each uid As String In uids
                        Dim email As IMail = New MailBuilder().CreateFromEml(pop3.GetMessageByUID(uid))      ' Download and parse each message  
                        WriteMessageToDatabase(email)
                        pop3.DeleteMessageByUID(uid)
                    Next
                Else
                    pop3.Close()
                    System.Environment.Exit(3)
                End If
                CloseDB()
                pop3.Close()
            End If
            Console.WriteLine(vbCrLf & "Finished.")  
            System.Environment.Exit(0)
        End Using

    End Sub

    Function ConnectDB() As Boolean
        Try
            Console.WriteLine("Connecting to DB...")  
            _dbConn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _dbPath)  
            _dbConn.Open()
        Catch ex As Exception
            Console.WriteLine("Could not connect for some reason.... is the file on the right location? --> check connectionstring ### " & vbCrLf & ex.Message)  
            Return False
        End Try
        If _dbConn.State = ConnectionState.Open Then
            Return True
        Else
            Return False
        End If
    End Function

    Sub CloseDB()
        _dbConn.Close()
    End Sub

    Sub WriteMessageToDatabase(ByVal email As IMail)

        Try

            Console.WriteLine("Saving Mail to Database: " + email.MessageID)  
            Dim uniqueID As String = tobase36(DateTime.Now.Ticks)
            Dim attSavePath As String = _attachmentRootPath + "\" + uniqueID + "_" + email.MessageID  

            Dim SQL As New OleDb.OleDbCommand("Select * FROM MAILS", _dbConn)  
            Dim da As New OleDb.OleDbDataAdapter(SQL)
            Dim builder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
            Dim dataSet As DataSet = New DataSet
            da.Fill(dataSet, "MAILS")  
            da.InsertCommand = builder.GetInsertCommand()

            Dim SQL2 As New OleDb.OleDbCommand("Select * FROM ATTACHMENTS", _dbConn)  
            Dim da2 As New OleDb.OleDbDataAdapter(SQL2)
            Dim builder2 As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da2)
            Dim dataSet2 As DataSet = New DataSet
            da2.Fill(dataSet2, "ATTACHMENTS")  
            da2.InsertCommand = builder2.GetInsertCommand()

            'Add email-content to database  
            Dim new_row As DataRow = dataSet.Tables("MAILS").NewRow()  
            new_row("mailID") = uniqueID & "_" & email.MessageID  
            new_row("mailDate") = email.Date  
            new_row("mailSubject") = email.Subject  
            new_row("mailText") = email.Text  
            new_row("mailHTML") = email.Html  
            new_row("mailFrom") = JoinMailboxes(email.From)  
            new_row("mailTo") = JoinAddresses(email.To)  
            new_row("mailCc") = JoinAddresses(email.Cc)  
            new_row("mailBcc") = JoinAddresses(email.Bcc)  
            dataSet.Tables("MAILS").Rows.Add(new_row)  
            da.Update(dataSet, "MAILS")  

            'Process attachments  
            If email.Attachments.Count > 0 Then
                'Process normal attachments  
                For Each attachment As MimeData In email.NonVisuals
                    Dim new_row_att As DataRow = dataSet2.Tables("ATTACHMENTS").NewRow()  
                    new_row_att("mailID") = uniqueID & "_" & email.MessageID  
                    new_row_att("attPath") = attSavePath  
                    new_row_att("attFilename") = attachment.SafeFileName  
                    new_row_att("attExtension") = Mid(attachment.SafeFileName, InStr(attachment.SafeFileName, ".") + 1)  
                    new_row_att("attFileSize") = attachment.Data.Length  
                    new_row_att("attType") = "normal"  
                    dataSet2.Tables("ATTACHMENTS").Rows.Add(new_row_att)  
                    If Not My.Computer.FileSystem.DirectoryExists(attSavePath) Then
                        My.Computer.FileSystem.CreateDirectory(attSavePath)
                    End If
                    attachment.Save(attSavePath + "\" + attachment.SafeFileName)  
                Next
                'Process inline attachments (in HTML body)  
                For Each attachment As MimeData In email.Visuals
                    Dim new_row_att As DataRow = dataSet2.Tables("ATTACHMENTS").NewRow()  
                    new_row_att("mailID") = uniqueID & "_" & email.MessageID  
                    new_row_att("attPath") = attSavePath  
                    new_row_att("attFilename") = attachment.SafeFileName  
                    new_row_att("attExtension") = Mid(attachment.SafeFileName, InStr(attachment.SafeFileName, ".") + 1)  
                    new_row_att("attFileSize") = attachment.Data.Length  
                    new_row_att("attType") = "inline"  
                    dataSet2.Tables("ATTACHMENTS").Rows.Add(new_row_att)  
                    If Not My.Computer.FileSystem.DirectoryExists(attSavePath) Then
                        My.Computer.FileSystem.CreateDirectory(attSavePath)
                    End If
                    attachment.Save(attSavePath + "\" + attachment.SafeFileName)  
                Next

                da2.Update(dataSet2, "ATTACHMENTS")  
            End If

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub


    Private Function JoinMailboxes(ByVal mailboxes As IList(Of MailBox)) As String
        Return String.Join(",", New List(Of MailBox)(mailboxes).ConvertAll(Function(x As MailBox) String.Format("{0} <{1}>", x.Name, x.Address)).ToArray())  
    End Function

    Private Function JoinAddresses(ByVal addresses As IList(Of MailAddress)) As String
        Dim builder As New StringBuilder

        For Each address As MailAddress In addresses
            If (TypeOf address Is MailGroup) Then
                Dim group As MailGroup = CType(address, MailGroup)
                builder.AppendFormat("{0}: {1};, ", group.Name, JoinAddresses(group.Addresses))  
            End If
            If (TypeOf address Is MailBox) Then
                Dim mailbox As MailBox = CType(address, MailBox)
                builder.AppendFormat("{0} <{1}>, ", mailbox.Name, mailbox.Address)  
            End If
        Next
        Return builder.ToString()
    End Function


    Private Function tobase36(ByVal number As Long) As String
        Dim cList() As Char = ("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ").ToCharArray  
        Dim finalString As String = ""  

        While Not number = 0
            finalString += cList(CInt(number Mod 36))
            number /= 36
        End While
        Return finalString
    End Function
End Module

pop3todatabase.exe /server:pop.mailserver.de /user:USERNAME /password:PASSWORD /dbpath:"C:\Pfad\database.mdb" /fileroot:"C:\Pfad\Attachments"