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
