SQLite query to check if table exists using VB.net

Question

I am writing a function to check if a table exists in SQLite database file and return dataout = "Table Not Found in Database" if table does not exist and proceed with checking for other tables and items in them. I have code for checking if database file exists and if an item exists in database table, i need to check if table exists before checking if the item exists in table. Following is my code:

        Dim LocalDirectory As String = "bogus"
        Try
        Dim SQLconnect As New SQLite.SQLiteConnection
        Dim SQLcommand As SQLite.SQLiteCommand
        Dim SQLreader As SQLite.SQLiteDataReader
        Dim SQLTablereader As SQLite.SQLiteDataReader
        Dim Table As String
        Dim Name As String
        Dim NameColumn As String
        Dim ValueColumn As String
        Dim LocalFile As String
        Dim NameIndex, ValueIndex As Integer
        Dim dbfile As String

        Table = CPV.GetTokenValue(TokenString, "Table")
        Name = CPV.GetTokenValue(TokenString, "Name")

        If Table = "" Then
            DataOut = "No Datebase Table specified in Tokenstring"
            OK_to_SelectCase = False
            Return 1
        End If

        If Name = "" Then
            DataOut = "No Datebase Entry Name specified in Tokenstring"
            OK_to_SelectCase = False
            Return 1
        End If

        NameColumn = CPV.GetTokenValue(TokenString, "NameColumn")
        If NameColumn = "" Then NameColumn = "name"

        ValueColumn = CPV.GetTokenValue(TokenString, "ValueColumn")
        If ValueColumn = "" Then ValueColumn = "value"

        dbfile = CPV.GetTokenValue(TokenString, "Source File")

        LocalFile = CPV.GetAppPath & "\TempFiles\" & dbfile
        LocalDirectory = CPV.GetAppPath & "\TempFiles\" & Mid(dbfile, 1, dbfile.LastIndexOf("\"))

        If Not System.IO.File.Exists(LocalFile) Then
            If UCase(CPV.GetTokenValue(TokenString, "ON_ABSENT_FILE")) = "TEST MANUALLY" Then
                DataOut = "<MANUAL>"
            Else
                DataOut = "Datebase File failed to downloaded, verify that ADB interface is enumerated and try again"
            End If
            OK_to_SelectCase = False
            Return 1
        End If

        SQLconnect.ConnectionString = "Data Source=" & LocalFile & ";"
        SQLconnect.Open()

        SQLcommand = SQLconnect.CreateCommand

        If UCase(CPV.GetTokenValue(TokenString, "WALmode")) = "TRUE" Then
            SQLcommand.CommandText = "PRAGMA journal_mode=OFF;"
            SQLcommand.ExecuteNonQuery()
        End If

        <I need the code/query here to check if table exists if not return dataout = "Table Not Found in Database" and continue>

        SQLcommand.CommandText = "SELECT * FROM " & Table
        SQLreader = SQLcommand.ExecuteReader()

        NameIndex = ValueIndex = -1

        NameIndex = SQLreader.GetOrdinal(NameColumn)
        ValueIndex = SQLreader.GetOrdinal(ValueColumn)

        DataOut = "Feature Name Not Found in Database"

        While SQLreader.Read
            If SQLreader.Item(NameIndex) = Name Then
                DataOut = SQLreader.Item(ValueIndex)
                Exit While
            End If
        End While

        SQLcommand.Dispose()
        SQLconnect.Close()

    Catch ex As Exception
        CPV.ErrorHandler(ex, "")
    Finally
        If Directory.Exists(LocalDirectory) Then
            Directory.Delete(LocalDirectory, True)
        End If
    End Try

    If DataOut = "Table Not Found in Database" Then
        OK_to_SelectCase = False
        If UCase(CPV.GetTokenValue(TokenString, "ON_ABSENT_TABLE")) = "TEST MANUALLY" Then
            DataOut = "<MANUAL>"
        End If
    End If

    If DataOut = "Feature Name Not Found in Database" Then
        OK_to_SelectCase = False
        If UCase(CPV.GetTokenValue(TokenString, "ON_ABSENT_ITEM")) = "TEST MANUALLY" Then
            DataOut = "<MANUAL>"
        End If
    End If

    Return 1

End Function

competent_tech competent_tech · Accepted Answer · 2013-01-07T20:54:15

This is the SQL that we use in the iPad to determine if a table exists:

sSQL = string.Format("SELECT 1 FROM sqlite_master WHERE name = '{0}' and type = 'table'", sName);

I suspect the same should work in your implementation.

SQLite query to check if table exists using VB.net

1 Answers