VegasVed VegasVed - 1 month ago 16
C# Question

UWP - Check if table exists

There is a lack of documentation for SQLite in C# on the SQLite website related to the "SQLite for Universal Windows Platform" extension available in VS2015.
Has anyone seen any documentation specific to this extension?

I am trying to see whether a table exists in my DB but cannot find a method to do this.
This is what I am doing and why:

SQLite.Net.SQLiteConnection conn;

string path = path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "entries.sqlite");
if (!System.IO.File.Exists(path))
{
conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
conn.CreateTable<Entry>();
}
else
{
conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
}


I do this because when this is executed:

conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);


The file is created if it does not exist. So first I need to test whether it exists. My assumption is that if the file exists, my table exists, because there is no scenario where the table is not created right after the file is created.
Am I missing some more direct way of testing for the table within the scope of the methods provided?

Thanks!

PS. I have checked whether my question has been answered but have not found anything related directly to this API.

Answer

You can use a query of the system sqlite_master table to see if a table with a given name exists:

var tableQuery = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='Entry';"
bool tableExists = conn.ExecuteScalar<int>( tableQuery ) == 1;

If the table is not present, the query will return 0, if it is present, it will return 1.

However, you don't have to worry about calling conn.CreateTable<Entry>(); even when the table already exists. SQLite.net is smart enough to create the table only when it does not exist yet. If the table is already in the database, this call will be ignored.

Comments