EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Service/MeterValueInsertHandler.cs

using Dapper;
using EVCB_OCPP.Domain;
using EVCB_OCPP.WSServer.Helper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EVCB_OCPP.WSServer.Service;

public interface IHandler<T>
{
    public void Handle(IEnumerable<T> parms);
}

public class MeterValueInsertHandler : IHandler<InsertMeterValueParam>
{
    private static Queue<string> _existTables = new();
    private readonly IDbContextFactory<MeterValueDBContext> meterValueDbContextFactory;
    private readonly ILogger<MeterValueInsertHandler> logger;
    private readonly string meterValueConnectionString;

    public MeterValueInsertHandler(
        IDbContextFactory<MeterValueDBContext> meterValueDbContextFactory,
        ILogger<MeterValueInsertHandler> logger,
        IConfiguration configuration
        )
    {
        this.meterValueDbContextFactory = meterValueDbContextFactory;
        this.logger = logger;
        this.meterValueConnectionString = configuration.GetConnectionString("MeterValueDBContext");
    }

    public void Handle(IEnumerable<InsertMeterValueParam> parms)
    {
        //var watch = Stopwatch.StartNew();
        //long t0, t1, t2, t3;

        var parmsList = parms.ToList();
        //t0 = watch.ElapsedMilliseconds;
        foreach (var param in parms)
        {
            if (!GetTableExist(param.createdOn).Result)
            {
                InsertWithStoredProcedure(param).Wait();
                parmsList.Remove(param);
            }
            //t1 = watch.ElapsedMilliseconds;
            //watch.Stop();
            //if (t1 > 500)
            //{
            //    logger.LogWarning("MeterValue InsertWithStoredProcedure {0}/{1}", t0, t1);
            //}
        }

        //t1 = watch.ElapsedMilliseconds;
        //logger.LogInformation("MeterValue bundle insert cnt {0}", parmsList.Count);
        var gruopParams = parmsList.GroupBy(x => GetTableName(x.createdOn));

        //t2 = watch.ElapsedMilliseconds;
        foreach (var group in gruopParams)
        {
            using SqlConnection sqlConnection = new SqlConnection(meterValueConnectionString);
            sqlConnection.Open();
            using var tans = sqlConnection.BeginTransaction();

            var tableName = group.Key;
            string command = $"""
                INSERT INTO {tableName} (ConnectorId, Value, CreatedOn, ContextId, FormatId, MeasurandId, PhaseId, LocationId, UnitId, ChargeBoxId, TransactionId)
                VALUES (@ConnectorId, @Value, @CreatedOn, @ContextId, @FormatId, @MeasurandId, @PhaseId, @LocationId, @UnitId, @ChargeBoxId, @TransactionId);
                """;
            foreach (var param in group)
            {
                var parameters = new DynamicParameters();
                parameters.Add("ConnectorId", param.connectorId, DbType.Int16);
                parameters.Add("Value", param.value, DbType.Decimal, precision: 18, scale: 8);
                parameters.Add("CreatedOn", param.createdOn, DbType.DateTime);
                parameters.Add("ContextId", param.contextId, DbType.Int32);
                parameters.Add("FormatId", param.formatId, DbType.Int32);
                parameters.Add("MeasurandId", param.measurandId, DbType.Int32);
                parameters.Add("PhaseId", param.phaseId, DbType.Int32);
                parameters.Add("LocationId", param.locationId, DbType.Int32);
                parameters.Add("UnitId", param.unitId, DbType.Int32);
                parameters.Add("ChargeBoxId", param.chargeBoxId, DbType.String, size: 50);
                parameters.Add("TransactionId", param.transactionId, DbType.Int32);
                sqlConnection.Execute(command, parameters, tans);
            }

            tans.Commit();

        }

        //watch.Stop();
        //t3 = watch.ElapsedMilliseconds;
        //if (t3 > 700)
        //{
        //    logger.LogWarning("MeterValue Dapper {0}/{1}/{2}/{3}", t0, t1, t2, t3);
        //}
        return ;
    }

    private async ValueTask<bool> GetTableExist(DateTime tableDateTime)
    {
        var tableName = GetTableName(tableDateTime);
        if (_existTables.Contains(tableName))
        {
            return true;
        }

        FormattableString checkTableSql = $"SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = {tableName}";

        using var db = await meterValueDbContextFactory.CreateDbContextAsync();
        var resultList = db.Database.SqlQuery<int>(checkTableSql)?.ToList();

        if (resultList is not null && resultList.Count > 0 && resultList[0] > 0)
        {
            _existTables.Enqueue(tableName);
            if (_existTables.Count > 30)
            {
                _existTables.TryDequeue(out _);
            }
            return true;
        }

        return false;
    }

    private async Task InsertWithStoredProcedure(InsertMeterValueParam param)
    {
        using var db = await meterValueDbContextFactory.CreateDbContextAsync();

        string sp = "[dbo].[uspInsertMeterValueRecord] @ChargeBoxId," +
"@ConnectorId,@Value,@CreatedOn,@ContextId,@FormatId,@MeasurandId,@PhaseId,@LocationId,@UnitId,@TransactionId";

        List<SqlParameter> parameter = new List<SqlParameter>();
        parameter.AddInsertMeterValueRecordSqlParameters(
            chargeBoxId: param.chargeBoxId
            , connectorId: (byte)param.connectorId
            , value: param.value
            , createdOn: param.createdOn
            , contextId: param.contextId
            , formatId: param.formatId
            , measurandId: param.measurandId
            , phaseId: param.phaseId
            , locationId: param.locationId
            , unitId: param.unitId
            , transactionId: param.transactionId);

        db.Database.ExecuteSqlRaw(sp, parameter.ToArray());
    }

    private static string GetTableName(DateTime dateTime)
        => $"ConnectorMeterValueRecord{dateTime:yyMMdd}";
}