SQLite Transaction fills a table BEFORE the transaction is commited
- by user1500403
Halo I have a code that creates a datatable (in memory) from a select SQL statement. However I realised that this datatable is filling during the procedure rather as a result of the transaction comit statment, it does the job but its slow. WHat amI doing wrong ?
Inalready.Clear() 'clears a dictionary
Using connection As New SQLite.SQLiteConnection(conectionString)
connection.Open()
Dim sqliteTran As SQLite.SQLiteTransaction = connection.BeginTransaction()
Try
oMainQueryR = "SELECT * FROM detailstable Where name= :name AND Breed= :Breed"
Dim cmdSQLite As SQLite.SQLiteCommand = connection.CreateCommand()
Dim oAdapter As New SQLite.SQLiteDataAdapter(cmdSQLite)
With cmdSQLite
.CommandType = CommandType.Text
.CommandText = oMainQueryR
.Parameters.Add(":name", SqlDbType.VarChar)
.Parameters.Add(":Breed", SqlDbType.VarChar)
End With
Dim c As Long = 0
For Each row As DataRow In list.Rows 'this is the list with 500 names
If Inalready.ContainsKey(row.Item("name")) Then
Else
c = c + 1
Form1.TextBox1.Text = " Fill .... " & c
Application.DoEvents()
Inalready.Add(row.Item("name"), row.Item("Breed"))
cmdSQLite.Parameters(":name").Value = row.Item("name")
cmdSQLite.Parameters(":Breed").Value = row.Item("Breed")
oAdapter.Fill(newdetailstable)
End If
Next
oAdapter.FillSchema(newdetailstable, SchemaType.Source)
Dim z = newdetailstable.Rows.Count
'At this point the newdetailstable is already filled up and I havent even comited the transaction
' sqliteTran.Commit()
Catch ex As Exception
End Try
End Using
