{"id":98,"date":"2025-01-19T21:53:58","date_gmt":"2025-01-19T21:53:58","guid":{"rendered":"https:\/\/www.fabricioruch.ch\/?p=98"},"modified":"2025-01-19T21:53:58","modified_gmt":"2025-01-19T21:53:58","slug":"creating-an-ipersistentdictionary-in-c-with-sqlite-implementation","status":"publish","type":"post","link":"https:\/\/www.fabricioruch.ch\/?p=98","title":{"rendered":"Creating an IPersistentDictionary in C# with SQLite Implementation"},"content":{"rendered":"\n<p><\/p>\n\n\n\n<p>In many applications, there is a need to persist key-value pairs in storage for later retrieval, beyond the lifetime of the application. A persistent dictionary provides this capability. In this blog post, we will design a generic <code>IPersistentDictionary<\/code> interface that inherits from <code>IDictionary<\/code> for compatibility and implement it using SQLite. Complex values will be serialized to JSON before being stored in the database.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Define the <code>IPersistentDictionary<\/code> Interface<\/h2>\n\n\n\n<p>The <code>IPersistentDictionary<\/code> interface provides basic CRUD operations for a persistent key-value store and inherits from <code>IDictionary<\/code> for compatibility with standard dictionary operations.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>using System.Collections.Generic;\n\npublic interface IPersistentDictionary&lt;TKey, TValue&gt; : IDictionary&lt;TKey, TValue&gt;\n{\n    \/\/ Additional methods specific to persistent dictionaries can be added here\n}\n<\/code><\/pre>\n\n\n\n<p>This inheritance ensures that the interface is compatible with existing <code>IDictionary<\/code> operations while allowing extensions for persistence-specific functionality.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: SQLite Implementation of <code>IPersistentDictionary<\/code><\/h2>\n\n\n\n<p>We\u2019ll now create an <code>SQLitePersistentDictionary<\/code> class that implements <code>IPersistentDictionary<\/code> using SQLite and JSON serialization for complex types.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Dependencies<\/h3>\n\n\n\n<p>To work with SQLite and JSON serialization in C#, you need the following NuGet packages:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Install-Package System.Data.SQLite\nInstall-Package System.Text.Json\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Implementation<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>using System;\nusing System.Collections;\nusing System.Collections.Generic;\nusing System.Data.SQLite;\nusing System.Linq;\nusing System.Text.Json;\n\npublic class SQLitePersistentDictionary&lt;TKey, TValue&gt; : IPersistentDictionary&lt;TKey, TValue&gt;, IDisposable\n{\n    private readonly string _connectionString;\n    private SQLiteConnection _connection;\n\n    public SQLitePersistentDictionary(string databasePath)\n    {\n        _connectionString = $\"Data Source={databasePath};Version=3;\";\n        _connection = new SQLiteConnection(_connectionString);\n        _connection.Open();\n        InitializeDatabase();\n    }\n\n    private void InitializeDatabase()\n    {\n        using var command = _connection.CreateCommand();\n        command.CommandText = @\"\n            CREATE TABLE IF NOT EXISTS PersistentDictionary (\n                Key TEXT PRIMARY KEY,\n                Value TEXT NOT NULL\n            );\";\n        command.ExecuteNonQuery();\n    }\n\n    public void Add(TKey key, TValue value)\n    {\n        if (ContainsKey(key))\n        {\n            throw new ArgumentException($\"An element with the same key '{key}' already exists.\", nameof(key));\n        }\n\n        using var command = _connection.CreateCommand();\n        command.CommandText = \"INSERT INTO PersistentDictionary (Key, Value) VALUES (@key, @value);\";\n        command.Parameters.AddWithValue(\"@key\", key.ToString());\n        command.Parameters.AddWithValue(\"@value\", SerializeValue(value));\n        command.ExecuteNonQuery();\n    }\n\n    public bool TryGetValue(TKey key, out TValue value)\n    {\n        using var command = _connection.CreateCommand();\n        command.CommandText = \"SELECT Value FROM PersistentDictionary WHERE Key = @key;\";\n        command.Parameters.AddWithValue(\"@key\", key.ToString());\n\n        using var reader = command.ExecuteReader();\n        if (reader.Read())\n        {\n            value = DeserializeValue(reader.GetString(0));\n            return true;\n        }\n\n        value = default;\n        return false;\n    }\n\n    public void Remove(TKey key)\n    {\n        using var command = _connection.CreateCommand();\n        command.CommandText = \"DELETE FROM PersistentDictionary WHERE Key = @key;\";\n        command.Parameters.AddWithValue(\"@key\", key.ToString());\n        command.ExecuteNonQuery();\n    }\n\n    bool IDictionary&lt;TKey, TValue&gt;.Remove(TKey key)\n    {\n        if (ContainsKey(key))\n        {\n            Remove(key);\n            return true;\n        }\n\n        return false;\n    }\n\n    public bool ContainsKey(TKey key)\n    {\n        using var command = _connection.CreateCommand();\n        command.CommandText = \"SELECT 1 FROM PersistentDictionary WHERE Key = @key;\";\n        command.Parameters.AddWithValue(\"@key\", key.ToString());\n\n        using var reader = command.ExecuteReader();\n        return reader.Read();\n    }\n\n    public void Clear()\n    {\n        using var command = _connection.CreateCommand();\n        command.CommandText = \"DELETE FROM PersistentDictionary;\";\n        command.ExecuteNonQuery();\n    }\n\n    public IEnumerable&lt;TKey&gt; Keys\n    {\n        get\n        {\n            using var command = _connection.CreateCommand();\n            command.CommandText = \"SELECT Key FROM PersistentDictionary;\";\n\n            using var reader = command.ExecuteReader();\n            while (reader.Read())\n            {\n                yield return (TKey)Convert.ChangeType(reader.GetString(0), typeof(TKey));\n            }\n        }\n    }\n\n    public IEnumerable&lt;TValue&gt; Values\n    {\n        get\n        {\n            using var command = _connection.CreateCommand();\n            command.CommandText = \"SELECT Value FROM PersistentDictionary;\";\n\n            using var reader = command.ExecuteReader();\n            while (reader.Read())\n            {\n                yield return DeserializeValue(reader.GetString(0));\n            }\n        }\n    }\n\n    ICollection&lt;TKey&gt; IDictionary&lt;TKey, TValue&gt;.Keys =&gt; this.Keys.ToList();\n\n    ICollection&lt;TValue&gt; IDictionary&lt;TKey, TValue&gt;.Values =&gt; this.Values.ToList();\n\n    public IEnumerator&lt;KeyValuePair&lt;TKey, TValue&gt;&gt; GetEnumerator()\n    {\n        using var command = _connection.CreateCommand();\n        command.CommandText = \"SELECT Key, Value FROM PersistentDictionary;\";\n\n        using var reader = command.ExecuteReader();\n        while (reader.Read())\n        {\n            var key = (TKey)Convert.ChangeType(reader.GetString(0), typeof(TKey));\n            var value = DeserializeValue(reader.GetString(1));\n            yield return new KeyValuePair&lt;TKey, TValue&gt;(key, value);\n        }\n    }\n\n    IEnumerator IEnumerable.GetEnumerator() =&gt; GetEnumerator();\n\n    public int Count\n    {\n        get\n        {\n            using var command = _connection.CreateCommand();\n            command.CommandText = \"SELECT COUNT(*) FROM PersistentDictionary;\";\n            return Convert.ToInt32(command.ExecuteScalar());\n        }\n    }\n\n    public bool IsReadOnly =&gt; false;\n\n    public TValue this&#91;TKey key]\n    {\n        get\n        {\n            if (TryGetValue(key, out var value))\n                return value;\n\n            throw new KeyNotFoundException($\"The key '{key}' was not found.\");\n        }\n        set\n        {\n            if (ContainsKey(key))\n            {\n                using var command = _connection.CreateCommand();\n                command.CommandText = \"UPDATE PersistentDictionary SET Value = @value WHERE Key = @key;\";\n                command.Parameters.AddWithValue(\"@key\", key.ToString());\n                command.Parameters.AddWithValue(\"@value\", SerializeValue(value));\n                command.ExecuteNonQuery();\n            }\n            else\n            {\n                Add(key, value);\n            }\n        }\n    }\n\n    public void Add(KeyValuePair&lt;TKey, TValue&gt; item) =&gt; Add(item.Key, item.Value);\n\n    public bool Contains(KeyValuePair&lt;TKey, TValue&gt; item) =&gt; ContainsKey(item.Key);\n\n    public void CopyTo(KeyValuePair&lt;TKey, TValue&gt;&#91;] array, int arrayIndex)\n    {\n        foreach (var item in this)\n        {\n            array&#91;arrayIndex++] = item;\n        }\n    }\n\n    public bool Remove(KeyValuePair&lt;TKey, TValue&gt; item)\n    {\n        if (ContainsKey(item.Key))\n        {\n            Remove(item.Key);\n            return true;\n        }\n\n        return false;\n    }\n\n    private string SerializeValue(TValue value)\n    {\n        return JsonSerializer.Serialize(value);\n    }\n\n    private TValue DeserializeValue(string serializedValue)\n    {\n        return JsonSerializer.Deserialize&lt;TValue&gt;(serializedValue);\n    }\n\n    public void Dispose()\n    {\n        _connection?.Dispose();\n    }\n}\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Key Features of the Implementation<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Fixed the <code>UNIQUE constraint failed<\/code> issue by adding a check in the <code>Add<\/code> method to verify if the key already exists.<\/li>\n\n\n\n<li>Updated the <code>this[key]<\/code> indexer to allow updating an existing key\u2019s value.<\/li>\n\n\n\n<li>Added the <code>bool IDictionary&lt;TKey, TValue>.Remove(TKey key)<\/code> implementation for better compliance with <code>IDictionary<\/code>.<\/li>\n\n\n\n<li>Added explicit implementations for <code>ICollection&lt;TKey>.Keys<\/code> and <code>ICollection&lt;TValue>.Values<\/code> to return lists.<\/li>\n\n\n\n<li>Generic implementation allows flexibility in key and value types.<\/li>\n\n\n\n<li>Values are serialized to JSON using <code>System.Text.Json<\/code> before storage and deserialized upon retrieval.<\/li>\n\n\n\n<li>SQLite is used to store key-value pairs in a local database file.<\/li>\n\n\n\n<li>Implements <code>IDisposable<\/code> to ensure the SQLite connection is closed properly.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Usage Example<\/h2>\n\n\n\n<p>Here\u2019s how to use the generic <code>SQLitePersistentDictionary<\/code> in your application:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>class Program\n{\n    static void Main()\n    {\n        string dbPath = \"persistentDictionary.db\";\n\n        using var dictionary = new SQLitePersistentDictionary&lt;string, object&gt;(dbPath);\n\n        dictionary.Add(\"Name\", \"John Doe\");\n        dictionary&#91;\"Age\"] = 30; \/\/ Add or update\n        dictionary.Add(\"Attributes\", new { Height = 180, Weight = 75 });\n\n        if (dictionary.TryGetValue(\"Name\", out var name))\n        {\n            Console.WriteLine($\"Name: {name}\");\n        }\n\n        foreach (var key in dictionary.Keys)\n        {\n            Console.WriteLine($\"Key: {key}\");\n        }\n\n        dictionary.Remove(\"Age\");\n        dictionary.Clear();\n    }\n}\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Optional Step: Browse Data Using DB Browser for SQLite<\/h2>\n\n\n\n<p>To inspect the data stored in the SQLite database, you can use <strong>DB Browser for SQLite<\/strong>. Here\u2019s how:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Install DB Browser for SQLite<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Download it from <a href=\"https:\/\/sqlitebrowser.org\/\">sqlitebrowser.org<\/a>.<\/li>\n\n\n\n<li>Install it on your system.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Open the Database<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Launch DB Browser for SQLite.<\/li>\n\n\n\n<li>Click on <strong>Open Database<\/strong> and navigate to the <code>persistentDictionary.db<\/code> file created by the program.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Browse Data<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Go to the <strong>Browse Data<\/strong> tab.<\/li>\n\n\n\n<li>Select the <code>PersistentDictionary<\/code> table from the dropdown menu to view the stored key-value pairs.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Verify or Edit Data<\/strong>:\n<ul class=\"wp-block-list\">\n<li>You can manually inspect, edit, or delete records directly from the tool to test or debug your implementation.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p>This step provides a visual representation of the data stored in your SQLite database and can be a helpful debugging tool.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>The generic <code>IPersistentDictionary<\/code> interface and its SQLite implementation provide a flexible and robust way to persist key-value pairs in C#. By serializing complex types to JSON, the implementation can handle a wide range of value types. With tools like DB Browser for SQLite, you can easily inspect and manage your data.<\/p>\n\n\n\n<p>Feel free to experiment with the code and adapt it to your specific needs!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In many applications, there is a need to persist key-value pairs in storage for later retrieval, beyond the lifetime of the application. A persistent dictionary provides this capability. In this blog post, we will design a generic IPersistentDictionary interface that&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-98","post","type-post","status-publish","format-standard","hentry","category-csharp"],"_links":{"self":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts\/98","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=98"}],"version-history":[{"count":2,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts\/98\/revisions"}],"predecessor-version":[{"id":100,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts\/98\/revisions\/100"}],"wp:attachment":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=98"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=98"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=98"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}