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
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:
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:
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:
