Check if a table is empty on SQLite

Question

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

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?

@CL. ups! Do I need to fix the code with ExecuteReader instead? I don't understand why getWritable and rawQuery didn't work as the samples I read — fedeteka, Oct 05 '16 at 13:57
`getWritableDatabase` and `rawQuery` are functions in the Android framework. Are you using VB.NET for Android? — CL., Oct 05 '16 at 14:05
@CL Thanks again for the info. I'm using just standard VB.NET on Visual Studio, my trouble is I can´t find a sample for VB.NET, all the code around is for Java, C or something — fedeteka, Oct 05 '16 at 15:03

score 1 · Accepted Answer · answered Oct 05 '16 at 14:22

1

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

answered Oct 05 '16 at 14:22

Ňɏssa Pøngjǣrdenlarp

38,411
12
59
178

Worked without any change! Wow, a lot of code needed for a simple request. The {0} works as a parameter for the tblName and String.Format is for glue both parts. Your recommendations was noted as well Thanks! – fedeteka Oct 05 '16 at 17:50
Why I can´t use the @ before your name? – fedeteka Oct 05 '16 at 17:51
I guess is also possible to use something like `cmd.CommandText = "SELECT last_insert_rowid()" lastID = cmd.ExecuteScalar()` – fedeteka Oct 05 '16 at 17:54
1

a) `String.Format` is a cleaner way to glue strings together; if the data was in a DataTable, you could just use `Rows.Count` b) no need to ping the owner of the post c) Dont use `last_insert_rowid()`: it *returns the ROWID of the last row insert from the database connection* It works to get the id of a row just added. On a connection you just opened, it will always be zed – Ňɏssa Pøngjǣrdenlarp Oct 05 '16 at 18:30
1

Also note that if/when you want the Id of the last inserted record, you do not have to run a query: `myConnection.LastInsertRowId` should have the value (just dont close the connection before trying to read it) – Ňɏssa Pøngjǣrdenlarp Oct 05 '16 at 18:47
Great answer as allways – fedeteka Oct 05 '16 at 23:58

Check if a table is empty on SQLite

1 Answers1