log errors to the error table

 using Harsco.AFIWeb.API.Helpers;

using Microsoft.AspNetCore.Hosting;

using Microsoft.AspNetCore.Mvc;

using Microsoft.AspNetCore.Mvc.Filters;

using Microsoft.AspNetCore.Mvc.ModelBinding;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Threading.Tasks;

using Microsoft.Extensions.Logging;

using Harsco.EmailService;

using Microsoft.Extensions.Configuration;

namespace Harsco.AFIWeb.API.Filters 

{

    public class ApiExceptionFilter : IExceptionFilter

    {

        private readonly IHostingEnvironment _hostingEnvironment;

        private readonly IModelMetadataProvider _modelMetadataProvider;

        private readonly ILogger<ApiExceptionFilter> _logger;

        private readonly EmailConfiguration _emailConfig;

        private readonly EmailConfigService _emailConfigService;

        private string Harsco_AFIWeb_ConnectionString;

        public ApiExceptionFilter(IHostingEnvironment hostingEnvironment, IModelMetadataProvider modelMetadataProvider, ILogger<ApiExceptionFilter> logger, EmailConfiguration emailConfig, EmailConfigService emailConfigService, IConfiguration configuration)

        {

            _hostingEnvironment = hostingEnvironment;

            _modelMetadataProvider = modelMetadataProvider;

            _logger = logger;

            _emailConfig = emailConfig;

            _emailConfigService = emailConfigService;

            this.Harsco_AFIWeb_ConnectionString = configuration["ConnectionStrings:Harsco.AFIWeb.connectionString"].ToString();

        }

        public void OnException(ExceptionContext context)

        {

            // Log the exception

            _logger.LogError(context.Exception, "Unhandled exception occurred.");

            // Log error into the tbl_Errors table

            try

            {

                // Prepare error details

                var exception = context.Exception;

                int errorNumber = exception.HResult;

                int errorState = 0; // Not available, set to 0 or map as needed

                int errorSeverity = 0; // Not available, set to 0 or map as needed

                int errorLine = 0; // Not available, set to 0 or map as needed

                string errorProcedure = exception.TargetSite?.Name ?? "";

                // Try to get controller name from route data

                string controllerName = "";

                if (context.RouteData.Values.TryGetValue("controller", out var controllerObj) && controllerObj != null)

                {

                    controllerName = controllerObj.ToString();

                }

                // Include controller name in error message if available

                string errorMessage = string.IsNullOrEmpty(controllerName)

                    ? exception.Message

                    : $"Controller: {controllerName} | {exception.Message}";

                DateTime errorDateTime = DateTime.UtcNow;

                // Use ADO.NET to insert into tbl_Errors

                using (var connection = new System.Data.SqlClient.SqlConnection(this.Harsco_AFIWeb_ConnectionString))

                {

                    connection.Open();

                    using (var command = connection.CreateCommand())

                    {

                        command.CommandText = @"INSERT INTO tbl_Errors

                            (ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage, ErrorDateTime)

                            VALUES (@ErrorNumber, @ErrorState, @ErrorSeverity, @ErrorLine, @ErrorProcedure, @ErrorMessage, @ErrorDateTime)";

                        command.Parameters.AddWithValue("@ErrorNumber", errorNumber);

                        command.Parameters.AddWithValue("@ErrorState", errorState);

                        command.Parameters.AddWithValue("@ErrorSeverity", errorSeverity);

                        command.Parameters.AddWithValue("@ErrorLine", errorLine);

                        command.Parameters.AddWithValue("@ErrorProcedure", errorProcedure);

                        command.Parameters.AddWithValue("@ErrorMessage", errorMessage);

                        command.Parameters.AddWithValue("@ErrorDateTime", errorDateTime);

                        command.ExecuteNonQuery();

                    }

                }

            }

            catch (Exception dbEx)

            {

                _logger.LogError(dbEx, "Failed to log error to tbl_Errors.");

            }          

            var errorResponse = new

            {

                Message = "An unexpected error occurred. Please contact support if the problem persists.",

                // Optionally add a trace id or timestamp

                TraceId = context.HttpContext.TraceIdentifier

            };

            context.Result = new ObjectResult(errorResponse)

            {

                StatusCode = 500

            };

            context.ExceptionHandled = true;

        }

    }

}