Creating an IPersistentDictionary in C# with SQLite Implementation

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 inherits from IDictionary for compatibility and implement it using SQLite. Complex values will be serialized to JSON before being stored in the database.


Step 1: Define the IPersistentDictionary Interface

The IPersistentDictionary interface provides basic CRUD operations for a persistent key-value store and inherits from IDictionary for compatibility with standard dictionary operations.

using System.Collections.Generic;

public interface IPersistentDictionary<TKey, TValue> : IDictionary<TKey, TValue>
{
    // Additional methods specific to persistent dictionaries can be added here
}

This inheritance ensures that the interface is compatible with existing IDictionary operations while allowing extensions for persistence-specific functionality.


Step 2: SQLite Implementation of IPersistentDictionary

We’ll now create an SQLitePersistentDictionary class that implements IPersistentDictionary using SQLite and JSON serialization for complex types.

Dependencies

To work with SQLite and JSON serialization in C#, you need the following NuGet packages:

Install-Package System.Data.SQLite
Install-Package System.Text.Json

Implementation

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text.Json;

public class SQLitePersistentDictionary<TKey, TValue> : IPersistentDictionary<TKey, TValue>, IDisposable
{
    private readonly string _connectionString;
    private SQLiteConnection _connection;

    public SQLitePersistentDictionary(string databasePath)
    {
        _connectionString = $"Data Source={databasePath};Version=3;";
        _connection = new SQLiteConnection(_connectionString);
        _connection.Open();
        InitializeDatabase();
    }

    private void InitializeDatabase()
    {
        using var command = _connection.CreateCommand();
        command.CommandText = @"
            CREATE TABLE IF NOT EXISTS PersistentDictionary (
                Key TEXT PRIMARY KEY,
                Value TEXT NOT NULL
            );";
        command.ExecuteNonQuery();
    }

    public void Add(TKey key, TValue value)
    {
        if (ContainsKey(key))
        {
            throw new ArgumentException($"An element with the same key '{key}' already exists.", nameof(key));
        }

        using var command = _connection.CreateCommand();
        command.CommandText = "INSERT INTO PersistentDictionary (Key, Value) VALUES (@key, @value);";
        command.Parameters.AddWithValue("@key", key.ToString());
        command.Parameters.AddWithValue("@value", SerializeValue(value));
        command.ExecuteNonQuery();
    }

    public bool TryGetValue(TKey key, out TValue value)
    {
        using var command = _connection.CreateCommand();
        command.CommandText = "SELECT Value FROM PersistentDictionary WHERE Key = @key;";
        command.Parameters.AddWithValue("@key", key.ToString());

        using var reader = command.ExecuteReader();
        if (reader.Read())
        {
            value = DeserializeValue(reader.GetString(0));
            return true;
        }

        value = default;
        return false;
    }

    public void Remove(TKey key)
    {
        using var command = _connection.CreateCommand();
        command.CommandText = "DELETE FROM PersistentDictionary WHERE Key = @key;";
        command.Parameters.AddWithValue("@key", key.ToString());
        command.ExecuteNonQuery();
    }

    bool IDictionary<TKey, TValue>.Remove(TKey key)
    {
        if (ContainsKey(key))
        {
            Remove(key);
            return true;
        }

        return false;
    }

    public bool ContainsKey(TKey key)
    {
        using var command = _connection.CreateCommand();
        command.CommandText = "SELECT 1 FROM PersistentDictionary WHERE Key = @key;";
        command.Parameters.AddWithValue("@key", key.ToString());

        using var reader = command.ExecuteReader();
        return reader.Read();
    }

    public void Clear()
    {
        using var command = _connection.CreateCommand();
        command.CommandText = "DELETE FROM PersistentDictionary;";
        command.ExecuteNonQuery();
    }

    public IEnumerable<TKey> Keys
    {
        get
        {
            using var command = _connection.CreateCommand();
            command.CommandText = "SELECT Key FROM PersistentDictionary;";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                yield return (TKey)Convert.ChangeType(reader.GetString(0), typeof(TKey));
            }
        }
    }

    public IEnumerable<TValue> Values
    {
        get
        {
            using var command = _connection.CreateCommand();
            command.CommandText = "SELECT Value FROM PersistentDictionary;";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                yield return DeserializeValue(reader.GetString(0));
            }
        }
    }

    ICollection<TKey> IDictionary<TKey, TValue>.Keys => this.Keys.ToList();

    ICollection<TValue> IDictionary<TKey, TValue>.Values => this.Values.ToList();

    public IEnumerator<KeyValuePair<TKey, TValue>> GetEnumerator()
    {
        using var command = _connection.CreateCommand();
        command.CommandText = "SELECT Key, Value FROM PersistentDictionary;";

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            var key = (TKey)Convert.ChangeType(reader.GetString(0), typeof(TKey));
            var value = DeserializeValue(reader.GetString(1));
            yield return new KeyValuePair<TKey, TValue>(key, value);
        }
    }

    IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();

    public int Count
    {
        get
        {
            using var command = _connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM PersistentDictionary;";
            return Convert.ToInt32(command.ExecuteScalar());
        }
    }

    public bool IsReadOnly => false;

    public TValue this[TKey key]
    {
        get
        {
            if (TryGetValue(key, out var value))
                return value;

            throw new KeyNotFoundException($"The key '{key}' was not found.");
        }
        set
        {
            if (ContainsKey(key))
            {
                using var command = _connection.CreateCommand();
                command.CommandText = "UPDATE PersistentDictionary SET Value = @value WHERE Key = @key;";
                command.Parameters.AddWithValue("@key", key.ToString());
                command.Parameters.AddWithValue("@value", SerializeValue(value));
                command.ExecuteNonQuery();
            }
            else
            {
                Add(key, value);
            }
        }
    }

    public void Add(KeyValuePair<TKey, TValue> item) => Add(item.Key, item.Value);

    public bool Contains(KeyValuePair<TKey, TValue> item) => ContainsKey(item.Key);

    public void CopyTo(KeyValuePair<TKey, TValue>[] array, int arrayIndex)
    {
        foreach (var item in this)
        {
            array[arrayIndex++] = item;
        }
    }

    public bool Remove(KeyValuePair<TKey, TValue> item)
    {
        if (ContainsKey(item.Key))
        {
            Remove(item.Key);
            return true;
        }

        return false;
    }

    private string SerializeValue(TValue value)
    {
        return JsonSerializer.Serialize(value);
    }

    private TValue DeserializeValue(string serializedValue)
    {
        return JsonSerializer.Deserialize<TValue>(serializedValue);
    }

    public void Dispose()
    {
        _connection?.Dispose();
    }
}

Key Features of the Implementation

  • Fixed the UNIQUE constraint failed issue by adding a check in the Add method to verify if the key already exists.
  • Updated the this[key] indexer to allow updating an existing key’s value.
  • Added the bool IDictionary<TKey, TValue>.Remove(TKey key) implementation for better compliance with IDictionary.
  • Added explicit implementations for ICollection<TKey>.Keys and ICollection<TValue>.Values to return lists.
  • Generic implementation allows flexibility in key and value types.
  • Values are serialized to JSON using System.Text.Json before storage and deserialized upon retrieval.
  • SQLite is used to store key-value pairs in a local database file.
  • Implements IDisposable to ensure the SQLite connection is closed properly.

Step 3: Usage Example

Here’s how to use the generic SQLitePersistentDictionary in your application:

class Program
{
    static void Main()
    {
        string dbPath = "persistentDictionary.db";

        using var dictionary = new SQLitePersistentDictionary<string, object>(dbPath);

        dictionary.Add("Name", "John Doe");
        dictionary["Age"] = 30; // Add or update
        dictionary.Add("Attributes", new { Height = 180, Weight = 75 });

        if (dictionary.TryGetValue("Name", out var name))
        {
            Console.WriteLine($"Name: {name}");
        }

        foreach (var key in dictionary.Keys)
        {
            Console.WriteLine($"Key: {key}");
        }

        dictionary.Remove("Age");
        dictionary.Clear();
    }
}

Optional Step: Browse Data Using DB Browser for SQLite

To inspect the data stored in the SQLite database, you can use DB Browser for SQLite. Here’s how:

  1. Install DB Browser for SQLite:
  2. Open the Database:
    • Launch DB Browser for SQLite.
    • Click on Open Database and navigate to the persistentDictionary.db file created by the program.
  3. Browse Data:
    • Go to the Browse Data tab.
    • Select the PersistentDictionary table from the dropdown menu to view the stored key-value pairs.
  4. Verify or Edit Data:
    • You can manually inspect, edit, or delete records directly from the tool to test or debug your implementation.

This step provides a visual representation of the data stored in your SQLite database and can be a helpful debugging tool.


Conclusion

The generic IPersistentDictionary 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.

Feel free to experiment with the code and adapt it to your specific needs!

Avatar von admin