fedeteka fedeteka - 1 month ago 11
Vb.net Question

Check if a table is empty on SQLite

I'm trying to adapt to VB.NET the code of the most voted answer from this post:

Sqlite Check if Table is Empty

Original code is

SQLiteDatabase db = table.getWritableDatabase();
String count = "SELECT count(*) FROM table";
Cursor mcursor = db.rawQuery(count, null);
mcursor.moveToFirst();
int icount = mcursor.getInt(0);
if(icount>0)
//leave
else
//populate table


My code looks like ('Only to have a message on the screen I will fill the If - Else code later')

Using conn As New SQLiteConnection("Data Source=myDataBase.sqlite;Version=3;foreign keys=true")

Try
conn.Open()
Dim emptyUserTable = "SELECT COUNT(*) FROM usersTable"
Dim cmdIsEmpty As SQLiteCommand = New SQLiteCommand(emptyUserTable, conn)

Try
Dim Answer As Integer
Answer = cmdIsEmpty.ExecuteNonQuery()
MsgBox(Answer)
Catch ex As Exception
MsgBox(ex.ToString())
End Try

End Using


But the "Answer" is allways -1, with empty table or not.

I donĀ“t know how to use getWritableDataBase because I get a
getWritableDatabase is not a member of SQLiteConnection

The same with rawQuery.

How can I check if usersTable is empty or not on VB.NET?

Answer

I've abstracted your code a little so it can be used for any table:

Private Function IsTableEmpty(tblName As String) As Boolean
    Dim sql = String.Format("SELECT COUNT(*) FROM {0}", tblName)

    Using conn As New SQLiteConnection(LiteConnStr)
        Using cmd As New SQLiteCommand(sql, conn)

            conn.Open()

            Dim rows = Convert.ToInt32(cmd.ExecuteScalar())
            Return rows = 0
        End Using
    End Using
End Function

Usage:

If IsTableEmpty("usersTable") Then
    Console.Beep()
End If

Notes

  • The command object should be disposed when you are done with it, so it is used on a Using block.
  • There is not need to copy your connection string everywhere. You can define it once as a form/class level variable and reuse it everywhere
  • ExecuteScalar() gets the count back, then it is tested for 0 rows
Comments