Wrapper around the .net SQLite library to make working with it easier.

SQLiteHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;



namespace Helpers.Storage
{

    using System.Diagnostics;
    using UnityEngine;
    using Mono.Data;
    using Mono.Data.Sqlite;


    using Debug = UnityEngine.Debug;

    public enum SQLiteTypes {
        NULL,
        INTEGER,
        NUMERIC,
        REAL,
        TEXT,
        BLOB
    }

    public class SQLiteHelper {
        private String conn;

        private SqliteConnection dbConn;

        private SqliteDataReader dbReader;

        private SqliteCommand dbCmd;

        private static SQLiteHelper instance;

        private SQLiteHelper(string dbName)
        {
            try {
                conn = string.Format("URI=file:{0}", dbName);
                dbConn = new SqliteConnection(conn);
                dbConn.Open();
            } catch (Exception e) {
                Debug.Log(string.Format("Error trying to open or connect to db {0}. Exception: {1}", dbName, e));
            }
        }

        public static SQLiteHelper init(string dbName) { return instance ?? (instance = new SQLiteHelper(dbName)); }

        public SqliteDataReader rawQuery(string query)
        {
            try {
                dbCmd = dbConn.CreateCommand();
                dbCmd.CommandText = query;

                dbReader = dbCmd.ExecuteReader();
                
                return dbReader;
            } catch (Exception e) {
                Debug.Log(string.Format("Error trying to execute query: '{0}'. Exception: {1}", query, e));
            }
            return null;
        }

        public List<int> testQuery(string query)
        {


            

            var result = dbCmd.ExecuteReader();
            var items = new List<int>();

            while (result.Read()) {
                items.Add((int)result[0]);    
            }
            dbCmd.Dispose();
            return items;
        }

        public object rawQueryScalar(string query)
        {

            try {
                dbConn = new SqliteConnection(conn);
                dbConn.Open();
                dbCmd = dbConn.CreateCommand();
                dbCmd.CommandText = query;
                var result = dbCmd.ExecuteScalar();
                return result;
            } catch (Exception e) {
                Debug.Log(string.Format("Error trying to execute query: '{0}'. Exception: {1}", query, e));
            }
            return null;

        }

        private int execute(string query)
        {
            dbCmd = dbConn.CreateCommand();
            dbCmd.CommandText = query;
            int rowsAffected = dbCmd.ExecuteNonQuery();
            return rowsAffected;
        }

        public int createTable(string tblName, string[] colNames, SQLiteTypes[] colTypes)
        {
            var autoInc = colTypes[0] == SQLiteTypes.INTEGER ? " AUTOINCREMENT" : "";
            var query = string.Format("CREATE TABLE IF NOT EXISTS {0} ({1} {2} PRIMARY KEY{3}", 
                tblName, 
                colNames[0], 
                colTypes[0], 
                autoInc);

            for (var i = 1; i < colNames.Length; ++i) {
                query += string.Format(", {0} {1}", colNames[i], colTypes[i]);
            }
            query += ")";
            Debug.Log(query);
            int rowsAffected = execute(query);
            return rowsAffected;
        }

        public int dropTable(string tblName)
        {
            var query = string.Format("DROP TABLE IF EXISTS {0}", tblName);
            int rowsAffected = execute(query);
            return rowsAffected;
        }

        /// <summary>
        /// Insert a sin
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int insert(string tblName, string colName, string value)
        {
            var query = string.Format("INSERT INTO {0} ({1}) VALUES('{2}')", tblName, colName, value);
            int rowsAffected = execute(query);
            return rowsAffected;
        }

        /// <summary>
        /// Insert into a table with specified columns and specified values
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="colNames"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int insert<T>(string tblName, string[] colNames, IEnumerable<T> values)
        {
            var query = string.Format("INSERT INTO {0} ({1}) VALUES('{2}')", 
                tblName, 
                string.Join(",", colNames),
                string.Join("','", values.ToArray() as string[] ));

            int rowsAffected = execute(query);
            return rowsAffected;
        }

        /// <summary>
        /// Insert into a full row into a table (all columns in that table)
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int insert<T>(string tblName, IEnumerable<T> values)
        {
           // Debug.Log(string.Join(",", values));
            var query = string.Format("INSERT INTO {0} VALUES('{1}')",
                tblName, 
                string.Join("','", values.ToArray() as string[]));
            //Debug.Log(query);
            int rowsAffected = execute(query);
            return rowsAffected;
        }

        /// <summary>
        /// Unlikely needed to be called directly as deconstructor calls this
        /// when object is being cleaned up and freed from memory
        /// </summary>
        public void closeDb()
        {
            dbReader.Close();
            dbReader = null;
            dbCmd.Dispose();
            dbCmd = null;
            dbConn.Close();
            dbConn = null;
        }
        ~SQLiteHelper()
        {
            closeDb();
        }
    }
}