SQLite Schema Class for VB.Net and C#

Posted: 12/13/2013

This simple class pulls in some basic information about a SQLite database. It uses the official ADO.NET System.Data.SQLite library provided by the SQLite project. This requires that an initialized and open connection is passed into the class (the class will not close or Dispose of the connection).

When the class is initialized the names of all of the tables and views will be loaded into those respective properties. Information about columns of those tables can be obtained via the “Columns” function or you can use the AllDatabaseColumns method and return all columns in the database. The column entries have the table they belong to as a property. I did not list indexes though I may in the future if the need arises.

VB.NET

    Imports System.Data
    Imports System.Data.SQLite

    ''' <summary>
    ''' Loads the schema for a SQLite database.  This requires an open connection to the SQLite database.  This class will not close
    ''' or Dispose of that connection when done, it is strickly referencing an open one that is managed outside of this class.
    ''' </summary>
    ''' <remarks>
    ''' This is dependent on the official ADO.NET System.Data.SQLite libraries provided by the SQLite project.
    ''' </remarks>
    Public Class SQLiteSchema
        '*********************************************************************************************************************
        '
        '             Class:  SQLiteSchema
        '      Organization:  http://www.blakepell.com     
        '      Initial Date:  12/14/2013
        '      Last Updated:  12/14/2013
        '     Programmer(s):  Blake Pell, blakepell@hotmail.com
        '
        '*********************************************************************************************************************

        ''' <summary>
        ''' Constructor
        ''' </summary>
        ''' <param name="_conn"></param>
        ''' <remarks></remarks>
        Public Sub New(_conn As SQLiteConnection)
            Me.DbConnection = _conn
            Me.RefreshSchema()
        End Sub

        ''' <summary>
        ''' Clears and refreshes the database schema.
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub RefreshSchema()
            Me.Tables.Clear()
            Me.Views.Clear()
            Using cmd As SQLiteCommand = Me.DbConnection.CreateCommand
                cmd.CommandText = "select name from sqlite_master where type='table' order by name"
                Dim dr As SQLiteDataReader = cmd.ExecuteReader
                While dr.Read
                    Me.Tables.Add(dr("name").ToString)
                End While
                dr.Close()
                cmd.CommandText = "select name from sqlite_master where type='view' order by name"
                dr = cmd.ExecuteReader
                While dr.Read
                    Me.Views.Add(dr("name").ToString)
                End While
                dr.Close()
            End Using
        End Sub

        ''' <summary>
        ''' A list of columns and their metadata for a specified table.
        ''' </summary>
        ''' <param name="tableName"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function Columns(tableName As String) As List(Of SQLiteColumn)
            Dim lst As New List(Of SQLiteColumn)
            Using cmd As SQLiteCommand = Me.DbConnection.CreateCommand
                cmd.CommandText = String.Format("pragma table_info({0})", tableName)
                Dim dr As SQLiteDataReader = cmd.ExecuteReader
                While dr.Read
                    lst.Add(New SQLiteColumn(tableName, dr("type").ToString, CInt(dr("cid").ToString), dr("name").ToString, CBool(dr("notnull").ToString), CBool(dr("pk").ToString)))
                End While
                dr.Close()
            End Using
            Return lst
        End Function

        ''' <summary>
        ''' Returns all SQLiteColumn records in the database.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function AllDatabaseColumns() As List(Of SQLiteColumn)
            Dim lst As New List(Of SQLiteColumn)
            Using cmd As SQLiteCommand = Me.DbConnection.CreateCommand
                For Each tableName In Me.Tables
                    cmd.CommandText = String.Format("pragma table_info({0})", tableName)
                    Dim dr As SQLiteDataReader = cmd.ExecuteReader
                    While dr.Read
                        lst.Add(New SQLiteColumn(tableName, dr("type").ToString, CInt(dr("cid").ToString), dr("name").ToString, CBool(dr("notnull").ToString), CBool(dr("pk").ToString)))
                    End While
                    dr.Close()
                Next
            End Using
            Return lst
        End Function

        ''' <summary>
        ''' A connection to the SQLite database that has already been opened.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property DbConnection As SQLiteConnection

        ''' <summary>
        ''' The name of all of the tables in the database.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property Tables As New List(Of String)

        ''' <summary>
        ''' The name of all of the views in the database.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property Views As New List(Of String)

        ''' <summary>
        ''' Represents the metadata for a SQLite column.
        ''' </summary>
        ''' <remarks></remarks>
        Public Class SQLiteColumn

            ''' <summary>
            ''' Constructor
            ''' </summary>
            ''' <param name="table"></param>
            ''' <param name="type"></param>
            ''' <param name="cId"></param>
            ''' <param name="name"></param>
            ''' <param name="notNull"></param>
            ''' <param name="primaryKey"></param>
            ''' <remarks></remarks>
            Public Sub New(table As String, type As String, cId As Integer, name As String, notNull As Boolean, primaryKey As Boolean)
                Me.Table = table
                Me.Type = type
                Me.CId = cId
                Me.Name = name
                Me.NotNull = notNull
                Me.PrimaryKey = primaryKey
            End Sub

            ''' <summary>
            ''' Constructor
            ''' </summary>
            ''' <remarks></remarks>
            Public Sub New()
            End Sub

            ''' <summary>
            ''' The table that owns this column.
            ''' </summary>
            ''' <value></value>
            ''' <returns></returns>
            ''' <remarks></remarks>
            Public Property Table As String = ""

            ''' <summary>
            ''' The data type of the column.
            ''' </summary>
            ''' <value></value>
            ''' <returns></returns>
            ''' <remarks></remarks>
            Public Property Type As String = ""

            ''' <summary>
            ''' The column ID which represents the ordinal the column is in order in the table.
            ''' </summary>
            ''' <value></value>
            ''' <returns></returns>
            ''' <remarks></remarks>
            Public Property CId As Integer = 0

            ''' <summary>
            ''' The name of the column.
            ''' </summary>
            ''' <value></value>
            ''' <returns></returns>
            ''' <remarks></remarks>
            Public Property Name As String = ""

            ''' <summary>
            ''' Whether a column can contain null data or not.
            ''' </summary>
            ''' <value></value>
            ''' <returns></returns>
            ''' <remarks></remarks>
            Public Property NotNull As Boolean = False

            ''' <summary>
            ''' Whether the column is a primary key or not.
            ''' </summary>
            ''' <value></value>
            ''' <returns></returns>
            ''' <remarks></remarks>
            Public Property PrimaryKey As Boolean = False

        End Class

    End Class

C# (Provided via Telerik code converter, not tested)

    using Microsoft.VisualBasic;
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics;
    using System.Data.SQLite;

    /// <summary>
    /// Loads the schema for a SQLite database.  This requires an open connection to the SQLite database.  This class will not close
    /// or Dispose of that connection when done, it is strickly referencing an open one that is managed outside of this class.
    /// </summary>
    /// <remarks>
    /// This is dependent on the official ADO.NET System.Data.SQLite libraries provided by the SQLite project.
    /// </remarks>
    public class SQLiteSchema
    {
        //*********************************************************************************************************************
        //
        //             Class:  SQLiteSchema
        //      Organization:  http://www.blakepell.com     
        //      Initial Date:  12/14/2013
        //      Last Updated:  12/14/2013
        //     Programmer(s):  Blake Pell, blakepell@hotmail.com
        //
        //*********************************************************************************************************************
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="_conn"></param>
        /// <remarks></remarks>
        public SQLiteSchema(SQLiteConnection _conn)
        {
            this.DbConnection = _conn;
            this.RefreshSchema();
        }

        /// <summary>
        /// Clears and refreshes the database schema.
        /// </summary>
        /// <remarks></remarks>
        public void RefreshSchema()
        {
            this.Tables.Clear();
            this.Views.Clear();
            using (SQLiteCommand cmd = this.DbConnection.CreateCommand) {
                cmd.CommandText = "select name from sqlite_master where type='table' order by name";
                SQLiteDataReader dr = cmd.ExecuteReader;
                while (dr.Read) {
                    this.Tables.Add(dr("name").ToString);
                }
                dr.Close();
                cmd.CommandText = "select name from sqlite_master where type='view' order by name";
                dr = cmd.ExecuteReader;
                while (dr.Read) {
                    this.Views.Add(dr("name").ToString);
                }
                dr.Close();
            }
        }

        /// <summary>
        /// A list of columns and their metadata for a specified table.
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<SQLiteColumn> Columns(string tableName)
        {
            List<SQLiteColumn> lst = new List<SQLiteColumn>();
            using (SQLiteCommand cmd = this.DbConnection.CreateCommand) {
                cmd.CommandText = string.Format("pragma table_info({0})", tableName);
                SQLiteDataReader dr = cmd.ExecuteReader;
                while (dr.Read) {
                    lst.Add(new SQLiteColumn(tableName, dr("type").ToString, Convert.ToInt32(dr("cid").ToString), dr("name").ToString, Convert.ToBoolean(dr("notnull").ToString), Convert.ToBoolean(dr("pk").ToString)));
                }
                dr.Close();
            }
            return lst;
        }

        /// <summary>
        /// Returns all SQLiteColumn records in the database.
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<SQLiteColumn> AllDatabaseColumns()
        {
            List<SQLiteColumn> lst = new List<SQLiteColumn>();
            using (SQLiteCommand cmd = this.DbConnection.CreateCommand) {
                foreach (void tableName_loopVariable in this.Tables) {
                    tableName = tableName_loopVariable;
                    cmd.CommandText = string.Format("pragma table_info({0})", tableName);
                    SQLiteDataReader dr = cmd.ExecuteReader;
                    while (dr.Read) {
                        lst.Add(new SQLiteColumn(tableName, dr("type").ToString, Convert.ToInt32(dr("cid").ToString), dr("name").ToString, Convert.ToBoolean(dr("notnull").ToString), Convert.ToBoolean(dr("pk").ToString)));
                    }
                    dr.Close();
                }
            }
            return lst;
        }

        /// <summary>
        /// A connection to the SQLite database that has already been opened.
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public SQLiteConnection DbConnection { get; set; }

        /// <summary>
        /// The name of all of the tables in the database.
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<string> Tables { get; set; }

        /// <summary>
        /// The name of all of the views in the database.
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<string> Views { get; set; }

        /// <summary>
        /// Represents the metadata for a SQLite column.
        /// </summary>
        /// <remarks></remarks>
        public class SQLiteColumn
        {
            /// <summary>
            /// Constructor
            /// </summary>
            /// <param name="table"></param>
            /// <param name="type"></param>
            /// <param name="cId"></param>
            /// <param name="name"></param>
            /// <param name="notNull"></param>
            /// <param name="primaryKey"></param>
            /// <remarks></remarks>
            public SQLiteColumn(string table, string type, int cId, string name, bool notNull, bool primaryKey)
            {
                this.Table = table;
                this.Type = type;
                this.CId = cId;
                this.Name = name;
                this.NotNull = notNull;
                this.PrimaryKey = primaryKey;
            }

            /// <summary>
            /// Constructor
            /// </summary>
            /// <remarks></remarks>
            public SQLiteColumn()
            {
            }

            /// <summary>
            /// The table that owns this column.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public string Table { get; set; }

            /// <summary>
            /// The data type of the column.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public string Type { get; set; }

            /// <summary>
            /// The column ID which represents the ordinal the column is in order in the table.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public int CId { get; set; }

            /// <summary>
            /// The name of the column.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public string Name { get; set; }

            /// <summary>
            /// Whether a column can contain null data or not.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool NotNull { get; set; }

            /// <summary>
            /// Whether the column is a primary key or not.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool PrimaryKey { get; set; }
        }
    }