Using SQLite in C# / .NET Environments

A quick how-to guide on setting up and using SQLite in C# / .NET environments.

Nothing complicated just instructions on installing SQLite and using its APIs for database creation and querying.

Much credit must go to this splendid link on helping me understand SQLite’s usage:

http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/

Step 1. Create a Visual Studio console application

sqlite1

Step 2: Download the SQLite code

For programs running in .NET in Visual Studio install as a NuGet package:

Select Tools > NuGet Package Manager > Package Manager Console:

Enter install-package System.Data.SQLite at the PM prompt:

sqlite2

Otherwise all the other SQLite downloads (Windows, Linux etc) are available here:
https://www.sqlite.org/download.html

Step 3: Implement the SQL APIs for database creation, querying etc

Some common SQLite C# commands

Create a database file:

SQLiteConnection.CreateFile(DatabaseFile)

Connect to a database

SQLiteConnection connection = new SQLiteConnection(DatabaseSource)
connection.Open();

Create a table:

command.CommandText = @"CREATE TABLE IF NOT EXISTS [MyTable] (
                        [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                        [Key] NVARCHAR(2048)  NULL,
                        [Value] VARCHAR(2048)  NULL
                        )";
command.ExecuteNonQuery();

Insert table entries:

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

Query a database

command.CommandText = "Select * FROM MyTable";

Example Usage: full code listing

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 = "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
            }
         }
      }
   }
}

Giving the following output:

sqlite3

Further examples: testing how well SQLite works on large insertions and searches

Some further tests but this time creating a database with 24000 entries, and doing a search on these.

Full code listing:

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 = "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");
        }
    }   
}

Giving the following times in milliseconds to insert 24000 items and do a search on these:

sqlite2

Leave a Reply