wpfvb.netsqliteasynchronousgetfiles

Async GetFiles and Write into Sqlite Database


I have a software where I'd like to get all of the files in a list then write them into an sqlite database. But when I am calling the method it is blocking the main UI, even if I tried to do some await tasks. What am I doing wrong? Here are my codes:

This is when window loaded I call the method to get the filelist and put it into the sqlite database which I am creating.

Private Async Function MainWindow_Loaded() As Task
        Try
            InitializeComponent()

                Await CreateFileList()          

        Catch ex As Exception
            WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
        End Try

    End Function



Create the filelist and put it into sqlite database function. It should be async but something wrong with it.

Private Async Function CreateFileList() As Task
        Try

            lblState1.Content = "Create File List"

            If Directory.Exists(System.AppDomain.CurrentDomain.BaseDirectory & "filelist") Then
                Directory.Delete(System.AppDomain.CurrentDomain.BaseDirectory & "filelist", True)
            End If

            Dim tasks As List(Of Task) = New List(Of Task)()

            Dim t As Task = Task.Run(Sub()

                                         For Each path In Directory.GetFiles(System.AppDomain.CurrentDomain.BaseDirectory, "*.*", SearchOption.AllDirectories)
                                             FilesList.Add(path)
                                         Next
                                     End Sub)
            tasks.Add(t)
            Task.WaitAll(tasks.ToArray)

            Database.DbCreator.createDbFile("filelist", "filelist.db3")

            Database.DbCreator.createDbConnection("filelist\filelist.db3")

            If Database.DbCreator.checkIfTableExist("filelist") = False Then

                Dim create_table As String = String.Empty


                create_table &= "CREATE TABLE IF NOT EXISTS filelist ("
                create_table &= "id INTEGER PRIMARY KEY NOT NULL,"
                create_table &= "filepath TEXT NOT NULL,"
                create_table &= "filesize TEXT NOT NULL DEFAULT 0,"
                create_table &= "needstobedone INTEGER NOT NULL DEFAULT 0,"
                create_table &= "todownload INTEGER NOT NULL DEFAULT 0)"

                Await Database.DbCreator.SqlNewQuery(create_table)

            End If


            Dim i As Integer = 1
            For Each file As String In FilesList
                If Not file.Contains("/Updates/") Or Not file.Contains("/filelist/") Or Not file.Contains("/lic/") Then
                    varPercent = Math.Round(i / FilesList.Count * 100)
                    Dim filesize As Long = New FileInfo(file).Length

                    Dim InsertQuery As String = ""
                    InsertQuery &= "INSERT INTO filelist (filepath, filesize) VALUES "
                    InsertQuery &= String.Format("('{0}','{1}')", file.Replace(System.AppDomain.CurrentDomain.BaseDirectory, ""), filesize)

                    Await Database.DbCreator.SqlNewQuery(InsertQuery)

                    i = i + 1
                End If
            Next

            Database.DbCreator.closeDbConnection("filelist\filelist.db3")

        Catch ex As Exception
            WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
        End Try
    End Function

My Database Creators functions, it should be async as well. Any suggestion on this way?

Namespace Database
    Public Class DbCreator
        Public Shared dbConnection As SQLiteConnection
        Public Shared command As New SQLiteCommand
        Public Shared sqlCommand As String
        Public Shared dbPath As String = System.AppDomain.CurrentDomain.BaseDirectory
        Public Shared dbFilePath As String

        Public Shared Sub createDbFile(dir As String, name As String)
            Try
                If Not String.IsNullOrEmpty(name) AndAlso Not Directory.Exists(dbPath & dir) Then Directory.CreateDirectory(dbPath & dir)
                dbFilePath = dbPath & "\" & dir & "\" & name

                If Not File.Exists(dbFilePath) Then
                    SQLiteConnection.CreateFile(dbFilePath)
                End If
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
            End Try
        End Sub

        Public Shared Function createDbConnection(name) As String
            Dim strCon As String = String.Format("Data Source={0};", System.AppDomain.CurrentDomain.BaseDirectory & name)
            Try
                dbConnection = New SQLiteConnection(strCon)
                dbConnection.Open()
                command = dbConnection.CreateCommand()
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
            End Try
            Return strCon
        End Function

        Public Shared Function DbConnect(name) As SQLiteConnection
            Dim strCon As String = String.Format("Data Source={0};", System.AppDomain.CurrentDomain.BaseDirectory & name)
            Try
                dbConnection = New SQLiteConnection(strCon)
                dbConnection.Open()
                command = dbConnection.CreateCommand()
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
            End Try
            Return dbConnection
        End Function

        Public Shared Function closeDbConnection(name) As String
            Dim strCon As String = String.Format("Data Source={0};", System.AppDomain.CurrentDomain.BaseDirectory & name)
            Try
                dbConnection = New SQLiteConnection(strCon)
                dbConnection.Close()
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Hiba: " & ex.ToString)
            End Try
            Return strCon
        End Function

        Public Shared Async Function SqlNewQuery(command As String) As Task
            Try
                sqlCommand = command
                Await executeQuery(sqlCommand)
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
            End Try
        End Function

        Public Shared Function checkIfTableExist(ByVal tableName As String) As Boolean
            Dim result As Object = Nothing
            command.CommandText = ""
            Try
                command.CommandText = "SELECT name FROM sqlite_master WHERE name='" & tableName & "'"
                result = command.ExecuteScalar()
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Hiba: " & ex.ToString)
            End Try
            Return If(result IsNot Nothing AndAlso result.ToString() = tableName, True, False)
        End Function

        Public Shared Async Function executeQuery(ByVal sqlCommand As String) As Task
            Try
                Dim triggerCommand As SQLiteCommand = dbConnection.CreateCommand()
                triggerCommand.CommandText = sqlCommand
                Await triggerCommand.ExecuteNonQueryAsync()
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Error: " & ex.ToString)
            End Try
        End Function

        Public Shared Function checkIfTableContainsData(ByVal tableName As String) As Boolean
            Dim result As Object = Nothing
            Try
                command.CommandText = "SELECT count(*) FROM " & tableName
                result = command.ExecuteScalar()
            Catch ex As Exception
                WriteLog(DateTime.Now.ToString & " Hiba: " & ex.ToString)
            End Try
            Return If(Convert.ToInt32(result) > 0, True, False)
        End Function

    End Class
End Namespace

Solution

  • Task.WaitAll(tasks.ToArray)
    

    Will block your UI thread, you should instread await for the result of an async method. There's no Directory.GetFilesAsync method so eventually you should white your own code.

    Dim filesList As List(Of String) = Await Task(Of List(Of String)).Run(
    Function()
        Dim files As List(Of String) = New List(Of String)()
        For Each path In Directory.GetFiles(System.AppDomain.CurrentDomain.BaseDirectory, "*.*", SearchOption.AllDirectories)
            files.Add(path)
        Next
        Return files
    End Function)