swdev:sqlite:custom_client_in_csharp [Audina Documentation]

This is an old revision of the document!

Custom SQLite Client in C#

Setup

Install support in C# solution, using one of these methods:

Download SQLite.dll and add reference.
Use NuGet.

In your code, add reference:

using System.Data.SQLite;

Create connection code:

// Create a database file
SQLiteConnection.CreateFile(@"C:\TEMP\MyDatabase.sqlite");
 
SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=C:\TEMP\MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
 
string sql = "create table highscores (name varchar(20), score int)";
 
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
 
sql = "insert into highscores (name, score) values ('Me', 9001)";
 
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
 
m_dbConnection.Close();

When using transactions:

using (TransactionScope tran = new TransactionScope())
{
     //Insert create script here.
 
     //Indicates that creating the SQLiteDatabase went succesfully, so the database can be committed.
     tran.Complete();
}

Usage

Create Database

Create a database file:

string DatabaseFile = @"C:\Data\MyDatabase.sqlite";  // or: data.db
SQLiteConnection.CreateFile(DatabaseFile);

Connect to Database

string DatabaseSource = @"Data Source=C:\Data\MyDatabase.sqlite;Version=3;UseUTF8Encoding=True;Password=myPassword;";
SQLiteConnection conn = new SQLiteConnection(DatabaseSource)
conn.Open();
//...
conn.Close();

See more: SQLite connection strings

Create Table

string sql = @"CREATE TABLE IF NOT EXISTS [MyTable] (
                        [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                        [Key] NVARCHAR(2048)  NULL,
                        [Value] VARCHAR(2048)  NULL
                        )";
// Method 1
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.ExecuteNonQuery();
 
// Method 2
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = sql;
command.ExecuteNonQuery();

Insert Table Entries

command.CommandText = "INSERT INTO MyTable (Key,Value) VALUES ('key one','value one')";
command.ExecuteNonQuery();

Query a Database

string sql = "SELECT * FROM MyTable";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read()) {
     Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
}

Get Database Tables

///---------------------------------------------------------------------
/// <summary>
/// Get table fields.
/// </summary>
/// <param name="dbPath"></param>
/// <returns>List of table fields</returns>
///---------------------------------------------------------------------
public static List<string> GetDatabaseTables(string dbPath)
{
    List<string> result = new List<string>();
 
    if (System.IO.File.Exists(dbPath))
    {
        string dbConnectionString = $"Data Source={dbPath};Version=3;";
 
        SQLiteConnection conn = new SQLiteConnection(dbConnectionString);
        conn.Open();
 
        DataTable tbl = conn.GetSchema(
            collectionName: "Tables",
            restrictionValues: new string[] {
                "main"       // catalog
                //tableName  // including wildcards % or _
            }
        );
 
        // Print table list
        Console.WriteLine($"GetDatabaseTables(): Available tables:");
        foreach (DataRow row in tbl.Rows)
        {
            Console.WriteLine($"- {row[2]}");  // table name
            result.Add(row[2].ToString());
            //Console.WriteLine($"- {row[6]}");  // table schema
        }
 
        conn.Close();
    }
 
    return result;
}

Get Table Schema

///---------------------------------------------------------------------
/// <summary>
/// Generate SQL CREATE TABLE statement.
/// </summary>
/// <param name="dbPath"></param>
/// <param name="tableName"></param>
/// <returns>string with schema</returns>
///---------------------------------------------------------------------
public static string GetTableSchema(string dbPath, string tableName)
{
    string result = "";
 
    if (System.IO.File.Exists(dbPath))
    {
        string dbConnectionString = $"Data Source={dbPath};Version=3;";
 
        SQLiteConnection conn = new SQLiteConnection(dbConnectionString);
        conn.Open();
 
        DataTable tbl = conn.GetSchema(
            collectionName: "Tables",
            restrictionValues: new string[] {
                "main"       // catalog
                //tableName  // including wildcards % or _
            }
        );
 
        // Print table list
        Console.WriteLine($"GetDatabaseTables(): Available tables:");
        foreach (DataRow row in tbl.Rows)
        {
            if (tableName == $"{row[2]}")       // table name match
            {
                result = row[6].ToString();     // table schema
            }
        }
 
        conn.Close();
    }
 
    return result;
}

Get Table Fields

///---------------------------------------------------------------------
/// <summary>
/// Get table fields.
/// </summary>
/// <param name="dbPath"></param>
/// <param name="tableName"></param>
/// <param name="getAllSchemaData"></param>
/// <returns>List of table fields</returns>
///---------------------------------------------------------------------
public static List<string> GetTableFields(string dbPath, string tableName, bool getAllSchemaData=false)
{
    List<string> result = new List<string>();
 
    if (System.IO.File.Exists(dbPath))
    {
        string dbConnectionString = $"Data Source={dbPath};Version=3;";
 
        SQLiteConnection conn = new SQLiteConnection(dbConnectionString);
        conn.Open();
 
        DataTable tbl = conn.GetSchema(
            collectionName: "Columns",
            restrictionValues: new string[] { "main", tableName }  // main & tableName
        );
 
        if (getAllSchemaData)
        {
            // Print schema column captions:
            //   TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_GUID,COLUMN_PROPID,
            //   ORDINAL_POSITION,COLUMN_HASDEFAULT,COLUMN_DEFAULT,COLUMN_FLAGS,IS_NULLABLE,DATA_TYPE,
            //   TYPE_GUID,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,
            //   NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA,
            //   CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME,DOMAIN_CATALOG,
            //   DOMAIN_NAME,DESCRIPTION,PRIMARY_KEY,EDM_TYPE,AUTOINCREMENT,UNIQUE
            for (int i = 0; i < tbl.Columns.Count; i++)
            {
                //Console.Write($"{tbl.Columns[i].ColumnName} ({tbl.Columns[i].DataType}),");
                Console.Write(tbl.Columns[i].Caption + ",");
            }
            Console.WriteLine();
 
            // Print table schema data. Eg: In table 'entry', field 'order_number':
            // main,sqlite_default_schema,entry,order_number,,,1,True,0,,True,integer,,8,,19,0,,,,,,,BINARY,,,,False,integer,False,False	
            foreach (DataRow row in tbl.Rows)
            {
                // Print only field's column name and type
                if (tableName == (string)row["TABLE_NAME"])
                {
                    // Print all field's metadata
                    string str = "";
                    foreach (DataColumn col in tbl.Columns)
                    {
                        string cellContent = row[col] + ",";
                        Console.Write(cellContent);
                        str += cellContent;
                    }
                    result.Add(str.TrimEnd(','));
                    Console.WriteLine();
                }
            }
        }
        else
        {
            // Print table fields
            foreach (DataRow row in tbl.Rows)
            {
                // Print only field's column name and type
                if (tableName == (string)row["TABLE_NAME"])
                {
                    string fieldLine = $"### {row["COLUMN_NAME"]} ({row["DATA_TYPE"]})";
                    Console.WriteLine(fieldLine);
                    result.Add($"{row["COLUMN_NAME"]}");
                }
            }
        }
 
        conn.Close();
    }
 
    return result;
}

Full Example

using System;
using System.Data.SQLite;
using System.IO;
 
namespace SQLiteDemo
{
   internal class Program
   {
      private const string CreateTableQuery = @"CREATE TABLE IF NOT EXISTS [MyTable] (
                                               [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                               [Key] NVARCHAR(2048)  NULL,
                                               [Value] VARCHAR(2048)  NULL
                                               )";
 
      private const string DatabaseFile = @"C:\TEMP\databaseFile.db";
      private const string DatabaseSource = "data source=" + DatabaseFile;
 
      private static void Main(string[] args)
      {
         // Create the file which will be hosting our database
         if (!File.Exists(DatabaseFile))
         {
            SQLiteConnection.CreateFile(DatabaseFile);
         }
 
         // Connect to the database 
         using(var connection = new SQLiteConnection(DatabaseSource))
         {
            // Create a database command
            using(var command = new SQLiteCommand(connection))
            {
               connection.Open();
 
               // Create the table
               command.CommandText = CreateTableQuery;
               command.ExecuteNonQuery();
 
               // Insert entries in database table
               command.CommandText = "INSERT INTO MyTable (Key,Value) VALUES ('key one','value one')";
               command.ExecuteNonQuery();
               command.CommandText = "INSERT INTO MyTable (Key,Value) VALUES ('key two','value two')";
               command.ExecuteNonQuery();
 
               // Select and display database entries
               command.CommandText = "Select * FROM MyTable"; 
 
               using(var reader = command.ExecuteReader())
               {
                  while (reader.Read())
                  {
                     Console.WriteLine(reader["Key"] + " : " + reader["Value"]); 
                  }
               }
               connection.Close(); // Close the connection to the database
            }
         }
      }
   }
}

Example Testing Load

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Diagnostics;
 
namespace SQLiteSearch
{
    public class Data
    {
        public Data(string key, string value)
        {
            Key = key;
            Value = value;
        }
 
        public string Key { get; set; }
        public string Value { get; set; }
    }
 
    internal class Program
    {
        private const string CreateTableQuery = @"CREATE TABLE IF NOT EXISTS [MyTable] (                                              
                                               [Key] NVARCHAR(2048)  NULL,
                                               [Value] VARCHAR(2048)  NULL
                                               )";
 
        private const string DatabaseFile = @"C:\TEMP\databaseFile.db";
        private const string DatabaseSource = "data source=" + DatabaseFile;
 
        private static void Initialize()
        {
            // Recreate database if already exists
            if (File.Exists(DatabaseFile))
            {
                File.Delete(DatabaseFile);
                SQLiteConnection.CreateFile(DatabaseFile);
            }
 
            using (var connection = new SQLiteConnection(DatabaseSource))
            {
                connection.Open();
 
                using (var command = new SQLiteCommand(connection))
                {                    
                    command.CommandText = CreateTableQuery;
                    command.ExecuteNonQuery();
 
                    int count = 0;
 
                    using (var transaction = connection.BeginTransaction())
                    {
                        while (count++ < 24000)
                        {
                            var key = "key " + count.ToString();
                            var value = "value " + count.ToString();
 
                            command.CommandText = "INSERT INTO MyTable (Key,Value) VALUES ('" + key + "','" + value + "')";
                            command.ExecuteNonQuery();
                        }
 
                        transaction.Commit();
                    }                   
                }
 
                connection.Close();
            }
        }
 
        private static void Load()
        {
            Console.WriteLine("Time in milliseconds to insert 24000 rows:");
            var stopwatch = new Stopwatch();
            var items = new List<Data>();
 
            using (var connection = new SQLiteConnection(DatabaseSource))
            {                
                using (var command = new SQLiteCommand(connection))
                {
                    connection.Open();                    
                    command.CommandText = "Select * FROM MyTable";
 
                    stopwatch.Start();
 
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var data = new Data(reader["Key"].ToString(), 
                                reader["Value"].ToString());
                            items.Add(data);
                        }
                    }
 
                    stopwatch.Stop();
                    Console.WriteLine("Time elapsed: {0} ms", stopwatch.ElapsedMilliseconds);
                    connection.Close();
                }
            }
        }
 
        private static List<string> Search(string keyValue)
        {
            Console.WriteLine("Time in milliseconds to search for item in the 24000 rows:");
            var stopwatch = new Stopwatch();
            List<string> results = new List<string>();
 
            using (var connection = new SQLiteConnection(DatabaseSource))
            {
                connection.Open();
 
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "Select * FROM MyTable WHERE Key='" + keyValue + "';";
                    stopwatch.Start();
 
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            results.Add(Convert.ToString(reader["Value"]));
                        }
 
                        stopwatch.Stop();
                        Console.WriteLine("Time elapsed: {0} ms", stopwatch.ElapsedMilliseconds);
                    }
                }
 
                connection.Close();
            }
 
            return results;
        }
 
        private static void Main(string[] args)
        {
            Initialize();
            Load();
 
            var results = Search("key 14400");
        }
    }   
}

Example using DataGrid (WPF)

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
 
namespace SqliteClient
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private string dbFile = @"C:\TEMP\data.db";
        private string dbConnectionString = "Version=3;";
 
        /// <summary>
        /// Main routine.
        /// </summary>
        public MainWindow()
        {
            InitializeComponent();
 
            this.dbConnectionString = $"Data Source={dbFile};Version=3;";
        }
 
        /// <summary>
        /// Click event handler for btnCreateDB.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCreateDB_Click(object sender, RoutedEventArgs e)
        {
            ResetDatabase(this.dbConnectionString);
        }
 
        /// <summary>
        /// Click event handler for btnConnect.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnConnect_Click(object sender, RoutedEventArgs e)
        {
            SQLiteConnection conn = new SQLiteConnection(this.dbConnectionString);
            conn.Open();
 
            string sql = "SELECT * FROM entry";
            UpdateDataGrid(conn, sql);
 
            conn.Close();
        }
 
        /// <summary>
        /// Reset database to original state.
        /// </summary>
        /// <param name="connectionString"></param>
        private void ResetDatabase(string connectionString)
        {
            // Create database
            try
            {
                SQLiteConnection.CreateFile(this.dbFile);
 
                SQLiteConnection conn = new SQLiteConnection(connectionString);
                conn.Open();
 
                // Create table 'entry'
                string sql = @"CREATE TABLE IF NOT EXISTS `entry` (
	                `id`	            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                `order_number`	    INTEGER DEFAULT 0,
	                `serial_number`	    TEXT DEFAULT 0,
	                `brand`	            TEXT DEFAULT 'Audina',
	                `product_code`	    TEXT,
	                `product_data_hash` TEXT,
	                `product_data`	    TEXT,
	                `workstation`	    TEXT,
	                `program_version`   TEXT,
	                `created_at`	    TEXT,
	                `updated_at`	    TEXT
                );";
 
                SQLiteCommand command = new SQLiteCommand(sql, conn);
                command.ExecuteNonQuery();
 
                // Insert sample table entries
                //
                // Required fields:
                //   "brand"
                //   "order_number"
                //   "serial_number"
                //   "product_code"
                //   "product_data"
                //   "workstation"
                //   "program_version"
 
                string guid = Guid.NewGuid().ToString();
                //string ProductDataEscaped = System.Security.SecurityElement.Escape(ProductData);
                //string ProductDataEscaped = ProductData.Replace("&", "&amp;")
                //    .Replace("<", "&lt;").Replace(">", "&gt;").Replace("\"", "&quot;").Replace("'", "&apos;");
                //string ProductDataEscaped = ProductData.Replace("&", "&amp;")
                //    .Replace("\"", "&quot;").Replace("'", "&apos;");
                string ProductDataEscaped = ProductData.Replace("&", "&amp;");
 
                string fields = "id, guid, order_number, serial_number, brand, product_code, 
                    product_data_hash, product_data, workstation, program_version, 
                    created_at, updated_at, is_submitted, is_deleted";
                //string values = $"1, '2a202e53-fabf-43aa-a93c-bbfbcc65c572', 11111, '18060466', 'Acme', 
                //    'prod_Vehicle', '3a4969c65f6dec10d56a9d6b63affe54', '{ProductDataEscaped}', 'QCPC2', 
                //    'MyApp 4.2.6670.18099', '2018-06-22 10:30:17', ''";
                string values = $"null, '{guid}', null, '{SerialNumber}', '{Brand}', 
                    '{ProductCode}', null, '{ProductDataEscaped}', '{Workstation}', 
                    'MyApp {TAppVersionBuild.VERSIONPUBLIC}.{TAppVersionBuild.VERSIONBUILD}', 
                    datetime('now', 'localtime'), '', 0, 0";
                string sql    = $"INSERT INTO 'entry' ({fields}) VALUES ({values}) ";
                SQLiteCommand command = new SQLiteCommand(sql, conn);
                command.ExecuteNonQuery();
 
                //sql = $"UPDATE 'entry' SET product_data = '{ProductDataEscaped}' WHERE guid = '{guid}'";
                //command.CommandText = sql;
                //command.ExecuteNonQuery();
 
                conn.Close();
 
                MessageBox.Show($"Created database {dbFile}.", 
                    $"Database {dbFile}", 
                    MessageBoxButton.OK, MessageBoxImage.Information
                );
            }
            catch (Exception exc)
            {
                MessageBox.Show($"Attempting to create a database {dbFile}.\r\n\r\nException: {exc.Message}", 
                    "Exception", MessageBoxButton.OK, MessageBoxImage.Error
                );
            }
        }
 
        /// <summary>
        /// Update DataGrid to show existing table data.
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        private void UpdateDataGrid(SQLiteConnection conn, string sql)
        {
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sql, conn);
 
            // Method 1
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            gridData.ItemsSource = dataSet.Tables[0].DefaultView;
 
            // Method 2
            DataTable tbl = new DataTable("entry");
            dataAdapter.Fill(tbl);
            gridData.ItemsSource = tbl.DefaultView;
 
 
            dataAdapter.Update(tbl);
        }
 
        /// <summary>
        /// SelectionChanged event handler for DataGrid.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void gridData_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            // Get selected Row
            DataRowView row = (DataRowView)gridData.SelectedItem;
 
            // Get selected Row Cell base on which the datagrid will be changed
            string entry_id      = row.Row["id"].ToString();
            string order_number  = row.Row["order_number"].ToString();
            string serial_number = row.Row["serial_number"].ToString();
            string brand         = row.Row["brand"].ToString();
 
            MessageBox.Show($"entry_id={entry_id}\r\norder_number={order_number}\r\nserial_number={serial_number}\r\nbrand={brand}\r\n", "Record Details", MessageBoxButton.OK, MessageBoxImage.Information);
        }
    }
}