System.Data.SQLite

 Here it is:

Public Shared Sub SQLite_ExecuteNonQuerys(ByVal table As DataTable)
        If SQLite_Connect() = False Then
            Return
        End If

        Dim adapter As New SQLiteDataAdapter()
        adapter.SelectCommand = New SQLiteCommand(String.Format("SELECT * FROM {0}", table.TableName), SQLite_Connection)

        Dim update As String = String.Format("UPDATE {0} SET ", table.TableName)
        Dim insert_columns As String = ""
        Dim insert_values As String = ""
        Dim paramaters As New List(Of SQLiteParameter)
        Dim key_index As Integer = -1
        For i As Integer = 0 To (table.Columns.Count - 1) Step 1
            If i <> (table.Columns.Count - 1) Then
                update = update & String.Format("{0} = {1},", table.Columns(i).ColumnName, String.Format("@{0}", table.Columns(i).ColumnName))
                insert_columns = insert_columns & String.Format("{0},", table.Columns(i).ColumnName)
                insert_values = insert_values & String.Format("{0},", String.Format("@{0}", table.Columns(i).ColumnName))
            Else
                update = update & String.Format("{0} = {1}", table.Columns(i).ColumnName, String.Format("@{0}", table.Columns(i).ColumnName))
                insert_columns = insert_columns & String.Format("{0}", table.Columns(i).ColumnName)
                insert_values = insert_values & String.Format("{0}", String.Format("@{0}", table.Columns(i).ColumnName))
            End If
            paramaters.Add(New SQLiteParameter(String.Format("@{0}", table.Columns(i).ColumnName), table.Columns(i).ColumnName))

            If table.Columns(i).Unique Then
                key_index = i
            End If
        Next

        Dim insert As String = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", table.TableName, insert_columns, insert_values)
        Dim delete As String = String.Format("DELETE FROM {0} ", table.TableName)
        If key_index <> -1 Then
            update = update & String.Format("WHERE {0} = {1}", table.Columns(key_index).ColumnName, String.Format("@{0}", table.Columns(key_index).ColumnName))
            delete = delete & String.Format("WHERE {0} = {1}", table.Columns(key_index).ColumnName, String.Format("@{0}", table.Columns(key_index).ColumnName))
        End If

        adapter.UpdateCommand = New SQLiteCommand(update, SQLite_Connection)
        adapter.InsertCommand = New SQLiteCommand(insert, SQLite_Connection)
        adapter.DeleteCommand = New SQLiteCommand(delete, SQLite_Connection)
        For i As Integer = 0 To (paramaters.Count - 1) Step 1
            adapter.UpdateCommand.Parameters.Add(paramaters(i))
            adapter.InsertCommand.Parameters.Add(paramaters(i))
            adapter.DeleteCommand.Parameters.Add(paramaters(i))
        Next
        adapter.Update(table)

        SQLite_Close()
    End Sub

Problem is this line:

 adapter.Update(table)

Takes close to an hour to execute with only 50,000 records to insert/update/delete.

I was wondering what is the easiest way to increase the speed of this methods execution? I was using DbTransaction but i could not figure out how to get it working with nothing but a DataTable as an argument, however DbTransaction could do the same thing in about 0.3 seconds.

So anyone have an answer to this?

Thanks a heap for any help.

Use transactions.

Instead of updating all in one go, call your ExecuteNonQuerys subroutine for for example 1000 records
and start a transaction before adapter.Update and commit the transaction after the adapter.update

 Can i get an example of how i would use a DbTransaction in this method? and adapter.Update() is executing all the queries i built, which depend on the DataTable i've retrieved from the database and modified. Is there some one to use a DbTranaction and DataAdapter together in this method?

If you are having 50000 record, processing all records in one go would probabely be to much to handle in one transaction.

So lets say you have originalTable  as the table containing the 50000 records.
You can then copy the rows per 1000 to a new table and process that table in 1 transaction

Something like this :

tempTable =originalTable.Clone()
For i As Integer = 0 To (originalTable.Rows.Count - 1) Step 1
     if i mod 1000 = 0 andalso i <> 0 then
        SQLite_ExecuteNonQuerys(tempTable)
        tempTable = new DataTable
        tempTable =originalTable.Clone()
     end if
     tempTable.ImportRow(originalTable.Row(i)
Next
If tempTable.Rows.Count > 0 Then
        SQLite_ExecuteNonQuerys(tempTable)
end if


Within SQLite_ExecuteNonQuerys you have to start a transaction.
My prefered way is to use TransActionScope  (Need to reference System.Transactions)

Something like this

Public Shared Sub SQLite_ExecuteNonQuerys(ByVal table As DataTable)
          Using scope As New TransactionScope()    
               ......  
          End Using
End Sub

I more thing : because SQLite is using non-managed code inside, you should always dispose the SQLite objects you use like SQLiteConnection, SQLiteCommand etc to avoid memory leaks and having the database locked until you end your program.

Hope this helps

You have to first close the connection and then dispose the Connection object.
Disposing the connection object should also close th connection, but to be sure close it yourself