= Custom SQLite Client in C# =
== Setup ==
Install support in C# solution, using one of these methods:
* [[http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki|Download SQLite.dll]] and add reference.
* [[http://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q5|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: [[https://www.connectionstrings.com/sqlite/|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 ====
string sql = "INSERT INTO MyTable (Key,Value) VALUES ('key one','value one')";
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.ExecuteNonQuery();
Console.WriteLine("NewRecID: " + conn.LastInsertRowId);
==== 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 ====
///---------------------------------------------------------------------
///
/// Get table fields.
///
///
/// List of table fields
///---------------------------------------------------------------------
public static List GetDatabaseTables(string dbPath)
{
List result = new List();
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 ====
///---------------------------------------------------------------------
///
/// Generate SQL CREATE TABLE statement.
///
///
///
/// string with schema
///---------------------------------------------------------------------
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;
}
///---------------------------------------------------------------------
///
/// Generate SQL CREATE TABLE statement basing on metadata retrieved with GetSchema method.
/// The generated script will work with all catalog management systems that support
/// ANSI standard. Only column name and type are included in the script.
/// Source: https://www.devart.com/dotconnect/sqlite/docs/MetaData.html
///
///
///
///---------------------------------------------------------------------
public static void GetCreateTable(SQLiteConnection conn, string tableName)
{
// Open the connection
conn.Open();
// Fill DataTable with columns information
DataTable tbl = conn.GetSchema(
"Columns", new string[] { "main", tableName }
);
string sql = $"CREATE TABLE {tableName} (\n";
string fieldLine;
DataRow row;
// For every row in the table
for (int i = 0; i < tbl.Rows.Count; i++)
{
// Get column name and type
row = tbl.Rows[i];
fieldLine = row["Name"] + " " + row["TypeName"];
// Add comma or closing bracket
if (i < tbl.Rows.Count - 1)
{
fieldLine = fieldLine + ",\n";
}
else
{
fieldLine = fieldLine + ")";
}
// Add new column to script
sql += fieldLine;
}
Console.WriteLine(sql);
// Close the connection
conn.Close();
}
==== Get Table Fields ====
///---------------------------------------------------------------------
///
/// Get table fields.
///
///
///
///
/// List of table fields
///---------------------------------------------------------------------
public static List GetTableFields(string dbPath, string tableName, bool getAllSchemaData=false)
{
List result = new List();
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;
}
///---------------------------------------------------------------------
///
/// Get table fields.
///
///
///
///
/// List of table fields
///---------------------------------------------------------------------
public static string GetTableFieldsAsCSV(string dbPath, string tableName, bool getAllSchemaData=false)
{
List lstFields = GetTableFields(dbPath, tableName, getAllSchemaData);
string fields = "";
foreach (string field in lstFields)
{
fields += $"{field},";
}
fields = fields.TrimEnd(',');
return fields;
}
///---------------------------------------------------------------------
///
/// Retrieve columns information from a table and render it to console.
/// Source: https://www.devart.com/dotconnect/sqlite/docs/MetaData.html
///
///
///
/// result with table fields
///---------------------------------------------------------------------
public static List GetTableInfo(SQLiteConnection conn, string tableName)
{
List result = new List();
conn.Open();
DataTable tbl = conn.GetSchema(
collectionName: "Columns",
restrictionValues: new string[] { "main", tableName }
);
for (int i = 0; i < tbl.Columns.Count; i++)
{
Console.Write(tbl.Columns[i].Caption + "\t");
result.Add(tbl.Columns[i].Caption);
}
Console.WriteLine();
foreach (DataRow row in tbl.Rows)
{
if (tableName == (string)row["TABLE_NAME"])
{
foreach (DataColumn col in tbl.Columns)
{
Console.Write(row[col] + "\t");
result.Add($"{row[col]}");
}
Console.WriteLine();
}
}
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();
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 Search(string keyValue)
{
Console.WriteLine("Time in milliseconds to search for item in the 24000 rows:");
var stopwatch = new Stopwatch();
List results = new List();
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
{
///
/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
private string dbFile = @"C:\TEMP\data.db";
private string dbConnectionString = "Version=3;";
///
/// Main routine.
///
public MainWindow()
{
InitializeComponent();
this.dbConnectionString = $"Data Source={dbFile};Version=3;";
}
///
/// Click event handler for btnCreateDB.
///
///
///
private void btnCreateDB_Click(object sender, RoutedEventArgs e)
{
ResetDatabase(this.dbConnectionString);
}
///
/// Click event handler for btnConnect.
///
///
///
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();
}
///
/// Reset database to original state.
///
///
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("&", "&")
// .Replace("<", "<").Replace(">", ">").Replace("\"", """).Replace("'", "'");
//string ProductDataEscaped = ProductData.Replace("&", "&")
// .Replace("\"", """).Replace("'", "'");
string ProductDataEscaped = ProductData.Replace("&", "&");
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
);
}
}
///
/// Update DataGrid to show existing table data.
///
///
///
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);
}
///
/// SelectionChanged event handler for DataGrid.
///
///
///
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);
}
}
}
== References ==
* [[http://www.technical-recipes.com/2016/using-sqlite-in-c-net-environments/|Using SQLite in C# .NET Environments]]
* [[https://www.codeproject.com/Articles/153407/WPF-and-SQLite-Database|WPF and SQLite Database]]
* [[https://www.youtube.com/watch?v=KfL0DgUdv0g|?SQLite Database connection with WPF C# (1/2)]]
* [[http://www.youtube.com/watch?v=0f7Oa2JzRj0|?SQLite Database connection with WPF C# (1/2)]]