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.
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"]); }
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("&", "&") // .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 ); } } /// <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); } } }