Read rows from a SQLite Database

[cjolly]

I use Sql Server for most of my database needs. In SQL Server, I have a class that sets up commands, connections, SQLadapter, etc and when I need to pull data or to save to a SQL table, I can call the class and do what I need to. I am porting a vb app to an Ipad so I am going to use SQLite for my database and keep it local. I would like to be able to set up a class in SQLite just like my class for SQL Server.
I found this class on the internet and tweaked it for my usage. I have transposed the SQL server class to SQLite and it seems to be working. Below is my SQLite class.

Imports System.Data.SqlClient
Imports System.Data.SQLite

Public Class SQLiteControl
    Public ReadOnly Constring As String = ""
    Public ReadOnly DBCon As New SQLiteConnection
    Public ReadOnly ConnString As String = "Data Source = C:\iSpot\iSpot.db;Version = 3"
    '  Dim reader As SQLiteDataReader = Command.ExecuteReader()
    Public DBCmd As New SQLiteCommand
    Public DBDA As New SQLiteDataAdapter
    Public DBDT As New DataTable

    Public Params As New List(Of SQLiteParameter)

    Public Exception As String
    'Query Stats
    Public RecordCount As Integer

    Public Sub ExecQuery(Query As String)
        RecordCount = 0
        Exception = ""
        Try
            DBCon.Open()
            DBCmd = New SQLiteCommand(Query, DBCon)
            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
            'Clear Parm List
            Params.Clear()
            DBDT = New DataTable
            DBDA = New SQLiteDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)
            DBDA.Fill(DBDT)
        Catch ex As Exception
            Exception = "Execquery Error: " & vbNewLine & ex.Message
        End Try
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

In my form, I add the following line to read the class
[Code]
 ReadOnly Sql As New SQLiteControl

The problem I have is reading the data once I have it saved to table. For example, this is how I read the SQL Server table:

  Private Sub LoadSingleRecord
            Sql.ExecQuery("Select FirstColumn From Table Where Index = 0")
        For Each r As DataRow In Sql.DBDT.Rows
           FirstrColumn = r("FirstColumn")
        Next
   FirstColumnLabel.text = FirstColumn

I have googled how to read data from my table but have failed to find one that works. This is an example of what I have tried:

     Sql.ExecQuery("Select Seq,game From RawSpots order by game desc") 'Limit 1
        SQLiteReader = SQLitecmd.ExecuteReader()
        While SQLiteReader.Read()
            reccnt += 1
            Seq = SQLiteReader.GetInt32(0)
            Game = SQLiteReader.GetString(1)
        End While
        SeqNbr.Text = Seq
        Gamenbr.Text = Game
        RecCount.Text = reccnt
    End Sub

Trying this, I get an error saying System.NullReferenceException: 'Object variable or With block variable not set' So it looks like what I am trying to pass back isn't sending anything back.
I can read my data within the form, but would like to use the class. Can someone show me what I am doing wrong?sd

Thanks

 

[jmcilhinney]

You don't need to do anything different for any other database than you're already doing for SQL Server. All you need to do is change the types from one namespace to the other and then any modifications required to the SQL and connection string. The structure of the code doesn;t need to change at all. However you're reading data from SQL Server is the same way you should read data from SQLite. You are using a DataTable for the former so do the same for the latter. The DataTable class is the same no matter what database the data came from.

The last code snippet you posted seems to be executing the same query twice. It appears that your ExecQuery method executes the query, then ExecuteReader does again. If you're calling ExecQuery in both cases, why should anything after that change?