vendredi 24 juillet 2015

Optimizing this SQLite Query

The code below is my successful attempt at getting all tables(while not knowing their name) and their rows. It works and does what I want, but I know it's not efficient. Can someone help me rewrite this properly? Thanks!

Public Shared Sub LoadTriviaCats()
    If System.IO.File.Exists("trivia.db") Then

        Dim connection As String = "Data Source=trivia.db;Version=3"
        Dim SQLConn As New SQLiteConnection
        SQLConn.ConnectionString = connection
        SQLConn.Open()
        Dim dt As DataTable = SQLConn.GetSchema("Tables")
        For Each i In dt.Rows
            Dim cat As New Trivia
            cat.catName = i(2)
            TriviaGUI.TriviaCats.Add(i(2), cat)
            GetTriviaCatQuestions(i(2))
        Next
        SQLConn.Close()
    End If
End Sub
Public Shared Sub GetTriviaCatQuestions(cat As String)
    Dim SQLstr As String = "Select * FROM " & cat
    Dim connection As String = "Data Source=trivia.db;Version=3"
    Dim SQLConn As New SQLiteConnection
    Dim SQLcmd As New SQLiteCommand
    Dim SQLdr As SQLiteDataReader
    SQLConn.ConnectionString = connection
    SQLConn.Open()
    SQLcmd.Connection = SQLConn
    SQLcmd.CommandText = SQLstr
    SQLdr = SQLcmd.ExecuteReader()
    While SQLdr.Read()
        TriviaGUI.TriviaCats.Item(cat).questions.Add(SQLdr(0), SQLdr(1))
    End While
    SQLdr.Close()
    SQLConn.Close()

End Sub

Aucun commentaire:

Enregistrer un commentaire