How can I get a list of table names from an existing sqlite database using SQLite.NET?

Question

Using the sqlite.net nuget package, how I can I get a list of tables from the database using an instance of SQLiteConnection? I need this functionality so I can detect when my database schema has changed and the database requires a rebuild.

For example, I've defined the entities:

public class Body
{
    [PrimaryKey]
    public int PrimaryKey { get; set; }
}

public class Foot
{
    [PrimaryKey]
    public int PrimaryKey { get; set; }
}

public class Leg
{
    [PrimaryKey]
    public int PrimaryKey { get; set; }

}

I need to retrieve the tables in a list of strings that would contain: Body, Leg, Foot.

The SQLiteConnection class has the TableMappings property that can perform this behaviour. It can only be used after calling SQLiteConnection.CreateTable; this is incorrect as calling CreateTable generates the table binding for an object AND executes the create table if not exists command, thus changing the schema.

The query "SELECT NAME from sqlite_master" can do this (I've tested it in a database browser) but I can't execute it using Execute, ExecuteScalar or Query. How can I retrieve a list of the tables in a database using this command?

small correction - the TableMappings property is populated when any Type is passed to the connection. So calling connection.Get<T> will add an entry for T in the TableMappings. It's just a cache - sort of misleading — rikkit
– rikkit, Commented Oct 21, 2016 at 6:03

matthewrdev · Accepted Answer · 2015-09-28 23:30:22Z

The following extension method provides the ability to query the tables inside an existing database without using the ORM layer:

using System;
using System.Collections.Generic;
using SQLite;

namespace MyApplication
{
    public static class SqliteExtensions
    {
        public static List<string> Tables (this SQLiteConnection connection)
        {
            const string GET_TABLES_QUERY = "SELECT NAME from sqlite_master";

            List<string> tables = new List<string> ();

            var statement = SQLite3.Prepare2 (connection.Handle, GET_TABLES_QUERY);

            try {
                bool done = false;
                while (!done) {
                    SQLite3.Result result = SQLite3.Step (statement);

                    if (result == SQLite3.Result.Row) {

                        var tableName = SQLite3.ColumnString (statement, 0);

                        tables.Add(tableName);
                    } else if (result == SQLite3.Result.Done) {
                        done = true;
                    } else {
                        throw SQLiteException.New (result, SQLite3.GetErrmsg (connection.Handle));
                    }
                }
            }
            finally {   
                SQLite3.Finalize (statement);
            }

            return tables;
        }
    }
}

Collectives™ on Stack Overflow

How can I get a list of table names from an existing sqlite database using SQLite.NET?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related