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>
Public Sub New(_conn As SQLiteConnection)
Me.DbConnection = _conn
Me.RefreshSchema()
End Sub
''' <summary>
''' Clears and refreshes the database schema.
''' </summary>
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>
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>
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>
Public Property DbConnection As SQLiteConnection
''' <summary>
''' The name of all of the tables in the database.
''' </summary>
Public Property Tables As New List(Of String)
''' <summary>
''' The name of all of the views in the database.
''' </summary>
Public Property Views As New List(Of String)
''' <summary>
''' Represents the metadata for a SQLite column.
''' </summary>
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>
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>
Public Sub New()
End Sub
''' <summary>
''' The table that owns this column.
''' </summary>
Public Property Table As String = ""
''' <summary>
''' The data type of the column.
''' </summary>
Public Property Type As String = ""
''' <summary>
''' The column ID which represents the ordinal the column is in order in the table.
''' </summary>
Public Property CId As Integer = 0
''' <summary>
''' The name of the column.
''' </summary>
Public Property Name As String = ""
''' <summary>
''' Whether a column can contain null data or not.
''' </summary>
Public Property NotNull As Boolean = False
''' <summary>
''' Whether the column is a primary key or not.
''' </summary>
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>
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>
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>
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>
public SQLiteConnection DbConnection { get; set; }
/// <summary>
/// The name of all of the tables in the database.
/// </summary>
public List<string> Tables { get; set; }
/// <summary>
/// The name of all of the views in the database.
/// </summary>
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>
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>
public SQLiteColumn()
{
}
/// <summary>
/// The table that owns this column.
/// </summary>
public string Table { get; set; }
/// <summary>
/// The data type of the column.
/// </summary>
public string Type { get; set; }
/// <summary>
/// The column ID which represents the ordinal the column is in order in the table.
/// </summary>
public int CId { get; set; }
/// <summary>
/// The name of the column.
/// </summary>
public string Name { get; set; }
/// <summary>
/// Whether a column can contain null data or not.
/// </summary>
public bool NotNull { get; set; }
/// <summary>
/// Whether the column is a primary key or not.
/// </summary>
public bool PrimaryKey { get; set; }
}
}