Question SQLite Select Function

[scarz]

Hi,

I have been trying to create a function for selecting data from a table in SQLite.
You would pass the function the query below and the query would return the results.

sqlQuerySelect("SELECT * FROM tableName")

The function I have created works but not how i would like, first off if i try to return the SQLiteDataReader and continue to read then close after some time it crashes, probably due to too many connections remaining open. The other alternative I have tried is placing the data into an array and then returning but I would need a dynamic 2d array for that which I haven't been successful with.
Any help with this is appreciated.

    Function sqlQuerySelect(ByVal query As String)


        Dim SQLconnect As New SQLiteConnection()
        Dim SQLcommand As SQLiteCommand
        Dim SQLreader As SQLiteDataReader


        SQLconnect.ConnectionString = "Data Source=" & Chr(34) & database & Chr(34) & ";"
        SQLconnect.Open()
        SQLcommand = SQLconnect.CreateCommand


        SQLcommand.CommandText = query


        SQLreader = SQLcommand.ExecuteReader()
        While SQLreader.Read()
            MsgBox(SQLreader(0))
        End While


        SQLcommand.Dispose()
        SQLconnect.Close()


        Return True


    End Function

 

[jmcilhinney]

When you call ExecuteReader, use the overload that lets you specify a CommandBehavior. Specify CloseConnection as the behaviour and then the connection will be closed when you close the reader.

Alternatively, use a DataAdapter to populate a DataTable. The Fill method will build the schema for you.

 

[scarz]

thanks I will look into this right now