Return rows from sqlite query function

[PatM]

I used to be fairly proficient with vb and c# but haven't used either of them in 10 or twenty years. I have a need to use sqlite in vb.net (vs2015) but I simply cannot remember (or find out) how to have a generic function read the database and return a result containing multiple rows. Supplying the query is easy but the datareader is unavailable as soon as I close the connection. I think I need to copy the data into a different object and return that but that is all.

Anyone have an example?

This is what I have so far

    Public Function ReadDatabase(query As String) As SQLiteDataReader


        sqlConn.ConnectionString = dbSource
        sqlConn.Open()

        sqlCmd.Connection = sqlConn
        sqlCmd.CommandText = query
        sqlReader = sqlCmd.ExecuteReader()


        Using sqlReader
            While (sqlReader.Read)
                Debug.Print(sqlReader.GetString(1)) '+ " " + sqlReader.GetString(2))
            End While
        End Using


        sqlReader.Close()
        sqlConn.Close()
    End Function

 

[jmcilhinney]

You have declared a function that returns a data reader but your code doesn't actually return one. One option would be to not close the connection and open your data reader with the appropriate CommandBehavior so that the connection is closed when the reader is closed. You then rely on the consumer to use and close the reader, e.g.

Public Function GetDataReader(query As String) As SqlDataReader
    Dim connection As New SqlConnection("connection string here")
    Dim command As New SqlCommand(query, connection)

    connection.Open()

    Return command.ExecuteReader(CommandBehavior.CloseConnection)
End Function

and then:

Using reader = GetDataReader("SELECT * FROM Table1")
    While reader.Read()
        '...
    End While
End Using

Alternatively, you can return a DataTable instead:

Public Function GetDataTable(query As String) As DataTable
    Using connection As New SqlConnection("connection string here"),
          command As New SqlCommand(query, connection)
        connection.Open()

        Dim table As New DataTable

        Using reader = command.ExecuteReader(CommandBehavior.CloseConnection)
            table.Load(reader)
        End Using

        Return table
    End Using
End Function

 

[PatM]

Thanks very much. This morning I changed my search words and found out about dataadapters. I now use an sqliteDataadapter to fill a dataset then return the dataset (much simpler for the consumer to learn about and use anyway). Now I''m starting to rock!

 

[jmcilhinney]

Don't use a DataSet unless you actually need one. The Fill method of a data adapter populates a DataTable either way. You can either pass it a loose DataTable or pass it a DataSet and it will populate a DataTable within that. There's no point having the wrapping DataSet unless you need one to contain multiple DataTables.

 

[PatM]

I didn't know that, thanks very much!