Using Sqlite and C# to determine if a specified table exists

I had need of a bit of code in C# to determine whether several tables in a Sqlite database had been created, so …

public static class DbExtensions
{
    /// <summary>
    /// Determines whether the table exists
    /// </summary>
    /// <param name="connection">Existing, opened, database connection</param>
    /// <param name="tableName">The name of the table to test for.</param>
    /// <returns>True if table exists.</returns>
    public static bool TableExists(this IDbConnection connection, string tableName)
    {
        Debug.Assert(connection != null);
        Debug.Assert(!string.IsNullOrWhiteSpace(tableName));

        var cmd = connection.CreateCommand();
        cmd.CommandText = @"SELECT COUNT(*) FROM sqlite_master WHERE name=@TableName";
        var p1 = cmd.CreateParameter();
        p1.DbType = DbType.String;
        p1.ParameterName = "TableName";
        p1.Value = tableName;
        cmd.Parameters.Add(p1);

        var result = cmd.ExecuteScalar();
        return ((long)result) == 1;
    }
}

Nothing too complex … only determining that the list of tables is stored in a system table named sqlite_master. If you want to find all indexes that have dependencies on a specific table, you can instead use the column tbl_name.

If you’re using Dapper and the DapperExtensions, you might find this useful:

_connection = new System.Data.SQLite.SQLiteConnection();
DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.SqliteDialect();
_connection.ConnectionString = new DbConnectionStringBuilder()
{
    {"Data Source", "thumbnailer.db"},
    {"Version", "3"},
    {"FailIfMissing", "False"},
}.ConnectionString;

First, it creates the SQLite connection (nuget) and then configures the DapperExtensions to use the SqliteDialect. What that means is that the extensions will generate SQL that actually works! 🙂 Finally, in my case, I wanted the DB to be created if it didn’t exist, so I set FailIfMissing to False.