Implements a generic table gateway for SQLite that supports Create, Read, Update and Delete operations.

SQLiteCrudTableGateway.cs

namespace DapperTest
{
    using Dapper;
    using System.Data;
    using static Dapper.SqlMapper;

    namespace DapperTest
    {

        interface IDbConnectionFactory
        {
            IDbConnection Create();
        }


        /// <summary>
        /// Table gateway that only implements Create, Read, Update and Delete methods for a single database table.
        /// <see href="https://www.martinfowler.com/eaaCatalog/tableDataGateway.html">See Patterns of Enterprise Application Architecture</see>
        /// </summary>
        /// <typeparam name="TKey">A subset of the properties <typeparamref name="TEntity"/> which form the Key of the table.</typeparam>
        /// <typeparam name="TEntity">A Data Transfer Object which represents a row of data in the table.</typeparam>
        class SQLiteCrudTableGateway<TKey, TEntity>
        {
            private readonly IDbConnectionFactory dbConnectionFactory;
            private readonly string tableName;
            private readonly List<string> keyColumns;
            private readonly List<string> entityColumns;

            public SQLiteCrudTableGateway(IDbConnectionFactory dbConnectionFactory, string tableName)
            {
                this.dbConnectionFactory = dbConnectionFactory;
                this.tableName = tableName;
                this.keyColumns = GetClassPropertyNames<TKey>().ToList();
                this.entityColumns = GetClassPropertyNames<TEntity>().ToList();
            }

            public TKey Create(TEntity entity)
            {
                var cols = string.Join(", ", entityColumns);
                var vals = string.Join(", ", entityColumns.Select(n => "@" + n));
                var keyCols = string.Join(", ", keyColumns);
                var query = $"INSERT INTO {this.tableName} ({cols}) VALUES ({vals}) RETURNING {keyCols}";

                using (var conn = this.dbConnectionFactory.Create())
                {
                    return conn.QuerySingle<TKey>(query, entity);
                }
            }

            public TEntity Read(TKey key)
            {
                var cols = string.Join(", ", entityColumns);
                var predicate = GetPredicate(key);
                var parameters = GetPredicateQueryParameters(key);
                var query = $"SELECT {cols} FROM {this.tableName} WHERE {predicate}";

                using (var conn = this.dbConnectionFactory.Create())
                {
                    return conn.QuerySingle<TEntity>(query, parameters);
                }
            }

            public void Update(TKey key, TEntity entity)
            {

                var keyProperties = new Dictionary<string, object>();
                foreach (var prop in key.GetType().GetProperties())
                {
                    keyProperties.Add(prop.Name, prop.GetValue(key));
                }

                var entityProperties = new Dictionary<string, object>();
                foreach (var prop in entity.GetType().GetProperties())
                {
                    if (!keyProperties.Keys.Contains(prop.Name))    // Don't update the key
                    {
                        entityProperties.Add(prop.Name, prop.GetValue(entity));
                    }
                }

                var queryParameters = new DynamicParameters();
                foreach (var (k, v) in keyProperties)
                {
                    queryParameters.Add($"@KEY_{k}", v);
                }
                foreach (var (k, v) in entityProperties)
                {
                    queryParameters.Add($"@ENTITY_{k}", v);
                }

                var updates = string.Join(", ", entityProperties.Keys.Select(n => $"{n} = @ENTITY_{n}"));
                var predicate = GetPredicate(key);

                var query = $"UPDATE {this.tableName} SET {updates} WHERE {predicate}";

                using (var conn = this.dbConnectionFactory.Create())
                {
                    conn.Execute(query, queryParameters);
                }
            }

            public void Delete(TKey key)
            {
                var predicate = GetPredicate(key);
                var parameters = GetPredicateQueryParameters(key);
                var query = $"DELETE FROM {this.tableName} WHERE {predicate}";
                using (var conn = this.dbConnectionFactory.Create())
                {
                    conn.Execute(query, parameters);
                }
            }

            private static IEnumerable<string> GetClassPropertyNames<T>()
            {
                return typeof(T).GetProperties().Select(propInfo => propInfo.Name);
            }

            private string GetPredicate(TKey key)
            {
                var conditions = typeof(TKey)
                                    .GetProperties()
                                    .Select(prop => prop.GetValue(key) is null ? $"{prop.Name} IS NULL" : $"{prop.Name} = @KEY_{prop.Name}");
                return string.Join(" AND ", conditions);
            }

            private DynamicParameters GetPredicateQueryParameters(TKey key)
            {
                var parameters = new DynamicParameters();
                foreach (var prop in key.GetType().GetProperties())
                {
                    var name = $"@KEY_{prop.Name}";
                    parameters.Add(name, prop.GetValue(key));
                }
                return parameters;
            }

        }
    }
}