swdev:sqlite:custom_client_in_csharp [Audina Documentation]

This is an old revision of the document!

Custom 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();

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
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 dbConnectionString = @"Data Source=C:\TEMP\data.db;Version=3;";
 
        public MainWindow()
        {
            InitializeComponent();
        }
 
        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();
        }
 
        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);
        }
    }
}