Return rows from sqlite query function

PatM

Well-known member
Joined
Dec 5, 2012
Messages
52
Programming Experience
10+
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
Example:
    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
 
Solution
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.

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,726
Location
Sydney, Australia
Programming Experience
10+
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.
VB.NET:
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:
VB.NET:
Using reader = GetDataReader("SELECT * FROM Table1")
    While reader.Read()
        '...
    End While
End Using
Alternatively, you can return a DataTable instead:
VB.NET:
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

Well-known member
Joined
Dec 5, 2012
Messages
52
Programming Experience
10+
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,726
Location
Sydney, Australia
Programming Experience
10+
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.
 
Solution
Top Bottom