Sample SqlWebHookStore structuring allowing for other DbContext and Entity definitions

DbWebHookStore

// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNet.DataProtection;
using Microsoft.AspNet.WebHooks.Config;
using Microsoft.AspNet.WebHooks.Diagnostics;
using Microsoft.AspNet.WebHooks.Properties;
using Microsoft.AspNet.WebHooks.Storage;
using Newtonsoft.Json;

namespace Microsoft.AspNet.WebHooks
{
    /// <summary>
    /// Provides an abstract implementation of <see cref="IWebHookStore"/> targeting SQL using a parameterized <see cref="DbContext"/>.
    /// The <see cref="DbContext"/> must contain an entity of type <see cref="IRegistration"/> as this is used to access the data
    /// in the DB.
    /// </summary>
    /// <typeparam name="TContext">The type of <see cref="DbContext"/> to be used.</typeparam>
    /// <typeparam name="TRegistration">The type of <see cref="IRegistration"/> to be used.</typeparam>
    [CLSCompliant(false)]
    public abstract class DbWebHookStore<TContext, TRegistration> : WebHookStore
        where TContext : DbContext, new()
        where TRegistration : class, IRegistration, new()
    {
        private readonly JsonSerializerSettings _serializerSettings = new JsonSerializerSettings() { Formatting = Formatting.None };
        private readonly IDataProtector _protector;
        private readonly ILogger _logger;

        /// <summary>
        /// Initializes a new instance of the <see cref="DbWebHookStore{TContext,TRegistration}"/> class with the given <paramref name="settings"/>,
        /// <paramref name="protector"/>, and <paramref name="logger"/>.
        /// </summary>
        protected DbWebHookStore(SettingsDictionary settings, IDataProtector protector, ILogger logger)
        {
            if (settings == null)
            {
                throw new ArgumentNullException("settings");
            }
            if (protector == null)
            {
                throw new ArgumentNullException("protector");
            }
            if (logger == null)
            {
                throw new ArgumentNullException("logger");
            }

            CheckSqlStorageConnectionString(settings);
            _protector = protector;
            _logger = logger;
        }

        /// <inheritdoc />
        public override async Task<ICollection<WebHook>> GetAllWebHooksAsync(string user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            user = NormalizeKey(user);

            try
            {
                using (var context = new TContext())
                {
                    var registrations = await context.Set<TRegistration>().Where(r => r.User == user).ToArrayAsync();
                    ICollection<WebHook> result = registrations.Select(r => ConvertToWebHook(r))
                        .Where(w => w != null)
                        .ToArray();
                    return result;
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Get", ex.Message);
                _logger.Error(msg, ex);
                throw new InvalidOperationException(msg, ex);
            }
        }

        /// <inheritdoc />
        public override async Task<ICollection<WebHook>> QueryWebHooksAsync(string user, IEnumerable<string> actions, Func<WebHook, string, bool> predicate)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            user = NormalizeKey(user);

            predicate = predicate ?? DefaultPredicate;

            try
            {
                using (var context = new TContext())
                {
                    var registrations = await context.Set<TRegistration>().Where(r => r.User == user).ToArrayAsync();
                    ICollection<WebHook> matches = registrations.Select(r => ConvertToWebHook(r))
                        .Where(w => MatchesAnyAction(w, actions) && predicate(w, user))
                        .ToArray();
                    return matches;
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Get", ex.Message);
                _logger.Error(msg, ex);
                throw new InvalidOperationException(msg, ex);
            }
        }

        /// <inheritdoc />
        public override async Task<WebHook> LookupWebHookAsync(string user, string id)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (id == null)
            {
                throw new ArgumentNullException("id");
            }

            user = NormalizeKey(user);
            id = NormalizeKey(id);

            try
            {
                using (var context = new TContext())
                {
                    var registration = await context.Set<TRegistration>().Where(r => r.User == user && r.Id == id).FirstOrDefaultAsync();
                    if (registration != null)
                    {
                        return ConvertToWebHook(registration);
                    }
                    return null;
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Lookup", ex.Message);
                _logger.Error(msg, ex);
                throw new InvalidOperationException(msg, ex);
            }
        }

        /// <inheritdoc />
        public override async Task<StoreResult> InsertWebHookAsync(string user, WebHook webHook)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (webHook == null)
            {
                throw new ArgumentNullException("webHook");
            }

            user = NormalizeKey(user);

            try
            {
                using (var context = new TContext())
                {
                    var registration = ConvertFromWebHook(user, webHook);
                    context.Set<TRegistration>().Attach(registration);
                    context.Entry(registration).State = EntityState.Added;
                    await context.SaveChangesAsync();
                }
            }
            catch (DbUpdateException uex)
            {
                string error = uex.GetBaseException().Message;
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Insert", error);
                _logger.Error(msg, uex);
                return StoreResult.Conflict;
            }
            catch (OptimisticConcurrencyException ocex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_ConcurrencyError, "Insert", ocex.Message);
                _logger.Error(msg, ocex);
                return StoreResult.Conflict;
            }
            catch (SqlException sqlex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Insert", sqlex.Message);
                _logger.Error(msg, sqlex);
                return StoreResult.OperationError;
            }
            catch (DbException dbex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Insert", dbex.Message);
                _logger.Error(msg, dbex);
                return StoreResult.OperationError;
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Insert", ex.Message);
                _logger.Error(msg, ex);
                return StoreResult.InternalError;
            }
            return StoreResult.Success;
        }

        /// <inheritdoc />
        public override async Task<StoreResult> UpdateWebHookAsync(string user, WebHook webHook)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (webHook == null)
            {
                throw new ArgumentNullException("webHook");
            }

            user = NormalizeKey(user);

            try
            {
                using (var context = new TContext())
                {
                    var registration = await context.Set<TRegistration>().Where(r => r.User == user && r.Id == webHook.Id).FirstOrDefaultAsync();
                    if (registration == null)
                    {
                        return StoreResult.NotFound;
                    }
                    UpdateRegistrationFromWebHook(user, webHook, registration);
                    context.Entry(registration).State = EntityState.Modified;
                    await context.SaveChangesAsync();
                }
            }
            catch (OptimisticConcurrencyException ocex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_ConcurrencyError, "Update", ocex.Message);
                _logger.Error(msg, ocex);
                return StoreResult.Conflict;
            }
            catch (SqlException sqlex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Update", sqlex.Message);
                _logger.Error(msg, sqlex);
                return StoreResult.OperationError;
            }
            catch (DbException dbex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Update", dbex.Message);
                _logger.Error(msg, dbex);
                return StoreResult.OperationError;
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Update", ex.Message);
                _logger.Error(msg, ex);
                return StoreResult.InternalError;
            }
            return StoreResult.Success;
        }

        /// <inheritdoc />
        public override async Task<StoreResult> DeleteWebHookAsync(string user, string id)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (id == null)
            {
                throw new ArgumentNullException("id");
            }

            user = NormalizeKey(user);

            try
            {
                using (var context = new TContext())
                {
                    var match = await context.Set<TRegistration>().Where(r => r.User == user && r.Id == id).FirstOrDefaultAsync();
                    if (match == null)
                    {
                        return StoreResult.NotFound;
                    }
                    context.Entry(match).State = EntityState.Deleted;
                    await context.SaveChangesAsync();
                }
            }
            catch (OptimisticConcurrencyException ocex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_ConcurrencyError, "Delete", ocex.Message);
                _logger.Error(msg, ocex);
                return StoreResult.Conflict;
            }
            catch (SqlException sqlex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Delete", sqlex.Message);
                _logger.Error(msg, sqlex);
                return StoreResult.OperationError;
            }
            catch (DbException dbex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_SqlOperationFailed, "Delete", dbex.Message);
                _logger.Error(msg, dbex);
                return StoreResult.OperationError;
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Delete", ex.Message);
                _logger.Error(msg, ex);
                return StoreResult.InternalError;
            }
            return StoreResult.Success;
        }

        /// <inheritdoc />
        public override async Task DeleteAllWebHooksAsync(string user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            user = NormalizeKey(user);

            try
            {
                using (var context = new TContext())
                {
                    var matches = await context.Set<TRegistration>().Where(r => r.User == user).ToArrayAsync();
                    foreach (var m in matches)
                    {
                        context.Entry(m).State = EntityState.Deleted;
                    }
                    await context.SaveChangesAsync();
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "DeleteAll", ex.Message);
                _logger.Error(msg, ex);
                throw new InvalidOperationException(msg, ex);
            }
        }

        /// <inheritdoc />
        public override async Task<ICollection<WebHook>> QueryWebHooksAcrossAllUsersAsync(IEnumerable<string> actions, Func<WebHook, string, bool> predicate)
        {
            if (actions == null)
            {
                throw new ArgumentNullException("actions");
            }

            predicate = predicate ?? DefaultPredicate;

            try
            {
                using (var context = new TContext())
                {
                    var registrations = await context.Set<TRegistration>().ToArrayAsync();
                    var matches = new List<WebHook>();
                    foreach (var registration in registrations)
                    {
                        WebHook webHook = ConvertToWebHook(registration);
                        if (MatchesAnyAction(webHook, actions) && predicate(webHook, registration.User))
                        {
                            matches.Add(webHook);
                        }
                    }
                    return matches;
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_OperationFailed, "Get", ex.Message);
                _logger.Error(msg, ex);
                throw new InvalidOperationException(msg, ex);
            }
        }

        internal static string CheckSqlStorageConnectionString(SettingsDictionary settings)
        {
            if (settings == null)
            {
                throw new ArgumentNullException("settings");
            }

            ConnectionSettings connection;
            if (!settings.Connections.TryGetValue(WebHookStoreContext.ConnectionStringName, out connection) || connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_NoConnectionString, WebHookStoreContext.ConnectionStringName);
                throw new InvalidOperationException(msg);
            }
            return connection.ConnectionString;
        }

        private static bool DefaultPredicate(WebHook webHook, string user)
        {
            return true;
        }

        private WebHook ConvertToWebHook(TRegistration registration)
        {
            if (registration == null)
            {
                return null;
            }

            try
            {
                string content = _protector.Unprotect(registration.ProtectedData);
                WebHook webHook = JsonConvert.DeserializeObject<WebHook>(content, _serializerSettings);
                return webHook;
            }
            catch (Exception ex)
            {
                string msg = string.Format(CultureInfo.CurrentCulture, SqlStorageResources.SqlStore_BadWebHook, typeof(WebHook).Name, ex.Message);
                _logger.Error(msg, ex);
            }
            return null;
        }

        private TRegistration ConvertFromWebHook(string user, WebHook webHook)
        {
            string content = JsonConvert.SerializeObject(webHook, _serializerSettings);
            string protectedData = _protector.Protect(content);
            var registration = new TRegistration
            {
                User = user,
                Id = webHook.Id,
                ProtectedData = protectedData
            };
            return registration;
        }

        private void UpdateRegistrationFromWebHook(string user, WebHook webHook, TRegistration registration)
        {
            registration.User = user;
            registration.Id = webHook.Id;
            string content = JsonConvert.SerializeObject(webHook, _serializerSettings);
            string protectedData = _protector.Protect(content);
            registration.ProtectedData = protectedData;
        }
    }
}

IRegistration.cs

// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information

namespace Microsoft.AspNet.WebHooks.Storage
{
    /// <summary>
    /// Defines a the WebHook registration data model for rows stored in a SQL DB.
    /// </summary>
    public interface IRegistration
    {
        /// <summary>
        /// Gets or sets the user ID for this WebHook registration.
        /// </summary>
        string User { get; set; }

        /// <summary>
        /// Gets or sets the ID of this WebHook registration.
        /// </summary>
        string Id { get; set; }

        /// <summary>
        /// Gets or sets the data included in this WebHook registration. Note that this is encrypted 
        /// as it contains sensitive information.
        /// </summary>
        string ProtectedData { get; set; }
    }
}

Registration.cs

// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics.CodeAnalysis;

namespace Microsoft.AspNet.WebHooks.Storage
{
    /// <summary>
    /// Defines the WebHook registration data model for rows stored in Microsoft SQL.
    /// </summary>
    [Table("WebHooks")]
    public class Registration : IRegistration
    {
        /// <inheritdoc />
        [Key]
        [StringLength(256)]
        [Column(Order = 0)]
        public string User { get; set; }

        /// <inheritdoc />
        [Key]
        [StringLength(64)]
        [Column(Order = 1)]
        public string Id { get; set; }

        /// <inheritdoc />
        [Required]
        public string ProtectedData { get; set; }

        /// <summary>
        /// Gets or sets a unique row version.
        /// </summary>
        [SuppressMessage("Microsoft.Performance", "CA1819:PropertiesShouldNotReturnArrays", Justification = "This is the pattern for row version.")]
        [Timestamp]
        public byte[] RowVer { get; set; }
    }
}

SqlWebHookStore

// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information

using System;
using Microsoft.AspNet.DataProtection;
using Microsoft.AspNet.WebHooks.Config;
using Microsoft.AspNet.WebHooks.Diagnostics;
using Microsoft.AspNet.WebHooks.Services;
using Microsoft.AspNet.WebHooks.Storage;

namespace Microsoft.AspNet.WebHooks
{
    /// <summary>
    /// Provides an implementation of <see cref="IWebHookStore"/> storing registered WebHooks in Microsoft SQL Server.
    /// </summary>
    [CLSCompliant(false)]
    public class SqlWebHookStore : DbWebHookStore<WebHookStoreContext, Registration>
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="SqlWebHookStore"/> class with the given <paramref name="settings"/>,
        /// <paramref name="protector"/>, and <paramref name="logger"/>.
        /// </summary>
        public SqlWebHookStore(SettingsDictionary settings, IDataProtector protector, ILogger logger)
            : base(settings, protector, logger)
        {
        }

        /// <summary>
        /// Provides a static method for creating a standalone <see cref="SqlWebHookStore"/> instance.
        /// </summary>
        /// <param name="logger">The <see cref="ILogger"/> instance to use.</param>
        /// <returns>An initialized <see cref="SqlWebHookStore"/> instance.</returns>
        public static IWebHookStore CreateStore(ILogger logger)
        {
            SettingsDictionary settings = CommonServices.GetSettings();
            IDataProtector protector = DataSecurity.GetDataProtector();
            IWebHookStore store = new SqlWebHookStore(settings, protector, logger);
            return store;
        }
    }
}