PostgresDataProtectionKeyRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using Microsoft.AspNetCore.DataProtection.Repositories;
using Npgsql;

namespace IdentityService.Infrastructure.DataProtection
{
    public class PostgresDataProtectionKeyRepository : IXmlRepository
    {
        private readonly string _connectionString;
        private const string SchemaName = "identity";
        private const string TableName = "data_protection_keys";

        private class DataProtectionKey
        {
            public Guid Id { get; set; }
            public string Xml { get; set; }
        }

        public PostgresDataProtectionKeyRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public static void EnsureTableExistence(string connectionString)
        {
            using (var dbConnection = new NpgsqlConnection(connectionString))
            {
                dbConnection.Open();

                using (var cmd = dbConnection.CreateCommand())
                {
                    cmd.CommandText =
                        $@"CREATE TABLE IF NOT EXISTS {SchemaName}.{TableName} (id uuid NOT NULL, xml varchar NOT NULL, CONSTRAINT pk_{TableName} PRIMARY KEY (id));";
                    cmd.ExecuteNonQuery();
                }
            }
        }


        public IReadOnlyCollection<XElement> GetAllElements()
        {
            List<DataProtectionKey> keys = new List<DataProtectionKey>();
            using (var dbConnection = new NpgsqlConnection(_connectionString))
            {
                dbConnection.Open();
                using (var cmd = dbConnection.CreateCommand())
                {
                    cmd.CommandText = $"SELECT id, xml FROM {SchemaName}.{TableName}";
                    var reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            keys.Add(new DataProtectionKey {Id = reader.GetGuid(0), Xml = reader.GetString(1)});
                        }
                    }
                }
            }

            return keys.Select(dpk => XElement.Parse(dpk.Xml)).ToList();
        }

        public void StoreElement(XElement element, string friendlyName)
        {
            var dataProtectionKey = new DataProtectionKey
            {
                Id = Guid.NewGuid(),
                Xml = element.ToString(SaveOptions.DisableFormatting)
            };

            using (var dbConnection = new NpgsqlConnection(_connectionString))
            {
                dbConnection.Open();
                using (var cmd = dbConnection.CreateCommand())
                {
                    cmd.CommandText = $"INSERT INTO {SchemaName}.{TableName} (id, xml) values (:id, :xml)";
                    cmd.Parameters.Add(new NpgsqlParameter(":id", dataProtectionKey.Id));
                    cmd.Parameters.Add(new NpgsqlParameter(":xml", dataProtectionKey.Xml));
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}