CODEWITHSUNDEEP
c#sqlite
matthewrdev
matthewrdev

Reputation: 12170

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

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?

Upvotes: 1

Views: 5586

Answers (1)

matthewrdev
matthewrdev

Reputation: 12170

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;
        }
    }
}

Upvotes: 4

Related Questions