EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Helper/AddPortalDbContext.cs

using EVCB_OCPP.Domain;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using MongoDB.Driver.Core.Configuration;
using NLog.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity.Infrastructure;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EVCB_OCPP.WSServer.Helper;

public static class AddPortalDbContext
{
    public const string CommandTimeoutKey = "CommandTimeout";
    public static IServiceCollection AddMainDbContext(this IServiceCollection services, IConfiguration configuration)
    { 
        const string DbUserIdKey = "MainDbUserIdKey";
        const string DbPassKey = "MainDbPass";
        const string DbConnectionStringKey = "MainDBContext";

        var conneciotnString = GetConnectionString(configuration, DbUserIdKey, DbPassKey, DbConnectionStringKey);

        services.AddSingleton(
            (serviceProvider) =>
            new SqlConnectionFactory<MainDBContext>(serviceProvider.GetRequiredService<ILogger<SqlConnectionFactory>>())
            {
                ConnectionString = conneciotnString
            });
        AddPortalDbContextInternal<MainDBContext>(services, configuration, conneciotnString, logToConsole: false);
        return services;
    }

    public static IServiceCollection AddMeterValueDbContext(this IServiceCollection services, IConfiguration configuration)
    {
        const string DbUserIdKey = "MeterValueDbUserId";
        const string DbPassKey = "MeterValueDbPass";
        const string DbConnectionStringKey = "MeterValueDBContext";

        var conneciotnString = GetConnectionString(configuration, DbUserIdKey, DbPassKey, DbConnectionStringKey);
        services.AddSingleton(
            (serviceProvider) =>
            new SqlConnectionFactory<MeterValueDBContext>(serviceProvider.GetRequiredService<ILogger<SqlConnectionFactory>>())
            {
                ConnectionString = conneciotnString
            });
        AddPortalDbContextInternal<MeterValueDBContext>(services, configuration, conneciotnString, logToConsole: false);
        return services;
    }

    public static IServiceCollection AddConnectionLogDbContext(this IServiceCollection services, IConfiguration configuration)
    {
        const string DbUserIdKey = "ConnectionLogDbUserId";
        const string DbPassKey = "ConnectionLogDbPass";
        const string DbConnectionStringKey = "ConnectionLogDBContext";

        var conneciotnString = GetConnectionString(configuration, DbUserIdKey, DbPassKey, DbConnectionStringKey);
        services.AddSingleton(
            (serviceProvider) =>
            new SqlConnectionFactory<ConnectionLogDBContext>(serviceProvider.GetRequiredService<ILogger<SqlConnectionFactory>>())
            {
                ConnectionString = conneciotnString
            });
        AddPortalDbContextInternal<ConnectionLogDBContext>(services, configuration, conneciotnString);
        return services;
    }

    public static IServiceCollection AddWebDBConetext(this IServiceCollection services, IConfiguration configuration)
    {
        const string DbUserIdKey = "WebDbUserId";
        const string DbPassKey = "WebDbPass";
        const string DbConnectionStringKey = "WebDBContext";

        var conneciotnString = GetConnectionString(configuration, DbUserIdKey, DbPassKey, DbConnectionStringKey);
        services.AddSingleton(
            (serviceProvider) =>
            new SqlConnectionFactory<WebDBConetext>(serviceProvider.GetRequiredService<ILogger<SqlConnectionFactory>>())
            {
                ConnectionString = conneciotnString
            });
        return services;
    }

    public static IServiceCollection AddOnlineLogDBContext(this IServiceCollection services, IConfiguration configuration)
    {
        const string DbUserIdKey = "OnlineLogDbUserId";
        const string DbPassKey = "OnlineLogDbPass";
        const string DbConnectionStringKey = "OnlineLogDBContext";

        var conneciotnString = GetConnectionString(configuration, DbUserIdKey, DbPassKey, DbConnectionStringKey);
        services.AddSingleton(
            (serviceProvider) =>
            new SqlConnectionFactory<OnlineLogDBContext>(serviceProvider.GetRequiredService<ILogger<SqlConnectionFactory>>())
            {
                ConnectionString = conneciotnString
            });
        return services;
    }

    private static void AddPortalDbContextInternal<T>(
        IServiceCollection services, IConfiguration configuration,
        string connectionString,bool logToConsole = false) where T : DbContext
    {

        var commandTimeout = int.TryParse(configuration[CommandTimeoutKey], out var temp) ? temp : 180;
        
        services.AddPooledDbContextFactory<T>((serviceProvider, options) => {
            options.UseSqlServer(connectionString, dbOptions =>
            {
                dbOptions.CommandTimeout(commandTimeout);
            });
            options.UseLoggerFactory(serviceProvider.GetRequiredService<ILoggerFactory>());
        });
    }

    private static string GetConnectionString(IConfiguration configuration, string UserIdKey, string DbPassKey, string ConnectionStringKey ) 
    {
        string mainDbUserId = string.IsNullOrEmpty(configuration[UserIdKey]) ? string.Empty : $"user id={configuration[UserIdKey]};";
        string mainDbUserPass = string.IsNullOrEmpty(configuration[DbPassKey]) ? string.Empty : $"password={configuration[DbPassKey]};";
        return $"{configuration.GetConnectionString(ConnectionStringKey)}{mainDbUserId}{mainDbUserPass}";
    }
}

public class SqlConnectionFactory<T> where T: DbContext
{
    private readonly ILogger<SqlConnectionFactory> logger;

    public string ConnectionString { get; init; }
    public SqlConnectionFactory(ILogger<SqlConnectionFactory> logger)
    {
        this.logger = logger;
    }

    public SqlConnection Create()
    {
        var sqlConnection = new SqlConnection(ConnectionString);
        sqlConnection.Open();
        return sqlConnection;
    }

    public async Task<SqlConnection> CreateAsync()
    {
        var timer = Stopwatch.StartNew();
        long t0, t1;

        var sqlConnection = new SqlConnection(ConnectionString);
        t0 = timer.ElapsedMilliseconds;

        await sqlConnection.OpenAsync();
        t1 = timer.ElapsedMilliseconds;
        timer.Stop();

        if (t1 > 500)
        {
            logger.LogWarning($"{typeof(T)} SqlConnection Open slow {t0}/{t1}");
        }

        return sqlConnection;
    }
}

/// <summary>
/// Dummy
/// </summary>
public class WebDBConetext : DbContext {}

/// <summary>
/// Dummy
/// </summary>
public class OnlineLogDBContext : DbContext { }