sql learn

 https://sqlserverguides.com/

based on column get procedures list

 SELECT OBJECT_NAME(OBJECT_ID),

definition

FROM sys.sql_modules

WHERE definition LIKE '%' + 'ManagersManagerName' + '%'

GO

-- Search in Stored Procedure Only

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),

object_definition(OBJECT_ID)

FROM sys.Procedures

WHERE object_definition(OBJECT_ID) LIKE '%' + 'ManagersManagerName' + '%'

GO

sql server error in api

error when there is no network.

transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call

  

 services.AddDbContextPool<DBContext>(options =>

        {
            options.UseMySql(
                mySqlConnectionStr, 
                ServerVersion.AutoDetect(mySqlConnectionStr),
                options => options.EnableRetryOnFailure(
                    maxRetryCount: 5,
                    maxRetryDelay: System.TimeSpan.FromSeconds(30),
                    errorNumbersToAdd: null)
                );
        });

microservice

 Develop Microservices on .NET 8 used ASP.NET Web API, Docker, RabbitMQ, MassTransit, gRPC, Yarp Gateway, Redis,SqlServer

APIexceptionglobal class

 The ApiExceptionFilter class implements IExceptionFilter, which allows it to catch and handle exceptions globally in an ASP.NET Core application. This class is useful for creating a consistent error-handling mechanism for API responses. Here’s a breakdown of how it works:

1. Class Structure and Dependencies

csharp
public class ApiExceptionFilter : IExceptionFilter
{
    private readonly IHostingEnvironment _hostingEnvironment;
    private readonly IModelMetadataProvider _modelMetadataProvider;

    public ApiExceptionFilter(IHostingEnvironment hostingEnvironment, IModelMetadataProvider modelMetadataProvider)
    {
        _hostingEnvironment = hostingEnvironment;
        _modelMetadataProvider = modelMetadataProvider;
    }

• Constructor Dependencies: The class constructor accepts two dependencies:
  • IHostingEnvironment: Provides environment-specific information (e.g., Development, Production).
  • IModelMetadataProvider: Used to handle and interact with model metadata, though it’s not directly used in this filter. It may be intended for future enhancements or customization based on model metadata.

2. Exception Handling Logic

The core functionality is in the OnException method, which processes exceptions thrown during the execution of API requests.

csharp
public void OnException(ExceptionContext context)
{
    if (context.Exception is DuplicateRecordException)
    {
        context.ModelState.AddModelError(string.Empty, context.Exception.Message);
        context.Result = new BadRequestObjectResult(context.ModelState.Errors());
        context.HttpContext.Response.StatusCode = (int)HttpStatusCode.BadRequest;
    }

• DuplicateRecordException: If the exception is of type DuplicateRecordException, it’s assumed to represent a bad request due to a duplicate entry (e.g., duplicate record error in the database). The filter:
  • Adds the error message to ModelState.
  • Sets Result to BadRequestObjectResult, which returns an HTTP 400 (Bad Request) response with the error details.
  • Sets StatusCode to 400.

csharp
    else if (context.Exception is UnAuthorisedAccessException)
    {
        context.ModelState.AddModelError(string.Empty, context.Exception.Message);
        context.Result = new UnauthorizedObjectResult(context.ModelState.Errors());
        context.HttpContext.Response.StatusCode = (int)HttpStatusCode.Unauthorized;
    }

• UnAuthorisedAccessException: If the exception is of type UnAuthorisedAccessException, it’s treated as an unauthorized access error (e.g., insufficient permissions). The filter:
  • Adds the error message to ModelState.
  • Sets Result to UnauthorizedObjectResult, returning an HTTP 401 (Unauthorized) response with the error details.
  • Sets StatusCode to 401.

csharp
    else
    {
        context.ModelState.AddModelError(string.Empty, context.Exception.Message);
        context.Result = new ObjectResult(context.ModelState.Errors());
        context.HttpContext.Response.StatusCode = (int)HttpStatusCode.InternalServerError;
    }
}

• General Exception Handling: For other unhandled exceptions, the filter:
  • Adds the error message to ModelState.
  • Sets Result to ObjectResult, which returns a generic error response.
  • Sets StatusCode to 500 (Internal Server Error), indicating an unexpected issue on the server side.

Summary

The ApiExceptionFilter class provides a consistent way to handle specific exceptions in the application by setting a custom error response and appropriate HTTP status code based on the exception type. This approach allows for clear API error responses, which are particularly helpful for client applications that consume the API, as they can handle these errors appropriately.

entity transcation rollback

 try

{

    using (var transaction = db.Database.BeginTransaction())

    {

        var appraisalDB = db.Appraisals.Where(p => p.AppraisalId == appraisalsVM.AppraisalId).SingleOrDefault();

        appraisalsVM.BindModelTo(appraisalDB);

        appraisalDB.ManagersManagerName = appraisalsVM.LoggedInUser.EmployeeName;

        appraisalDB.ManagersManagerSignDate = DateTime.Now;

        appraisalDB.ModifiedOn = DateTime.Now;

        db.SaveChanges();

        // Call archival procedure

        var connectionString = db.Database.GetDbConnection().ConnectionString;

        DataSet ds = new DataSet();

        using (SqlConnection conn = new SqlConnection(connectionString))

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand("[dbo].[proc_Appraisal_Archive]", conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@AppraisalID", appraisalsVM.AppraisalId);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            adapter.Fill(ds);

            conn.Close();

        }

        // Commit transaction if all actions are successful

        transaction.Commit();

        return Ok(new { status = "Success" });

    }

}

catch (Exception ex)

{

    // Rollback transaction and log error

    if (transaction != null)

    {

        transaction.Rollback();

    }

    var data = new TblErrors

    {

        ErrorMessage = ex.Message,

        ErrorNumber = ex.HResult,

        ErrorProcedure = "proc_Appraisal_Archive",

        ErrorLine = 525

    };

    this.SaveErrors(data);

    return Ok(new { status = "Failed" });

}

------------------above code modified in case in below code any error in below procedure falied then need to roolback first transaction also.
below is the old code

public IActionResult SignManagersManagerAppraisal(vmAppraisals appraisalsVM)

{

using (Performance_ManagmentContextDbo db = new Performance_ManagmentContextDbo())

{

try

{

            var appraisalDB = db.Appraisals.Where(p => p.AppraisalId == appraisalsVM.AppraisalId).SingleOrDefault();

            appraisalsVM.BindModelTo(appraisalDB);

            appraisalDB.ManagersManagerName = appraisalsVM.LoggedInUser.EmployeeName;

            appraisalDB.ManagersManagerSignDate = DateTime.Now;

            appraisalDB.ModifiedOn = DateTime.Now;

            db.SaveChanges();

            //call archival procedure

            var connectionString = db.Database.GetDbConnection().ConnectionString;

            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(connectionString))

            {

                conn.Open();

                SqlCommand cmd = new SqlCommand("[dbo].[proc_Appraisal_Archive]", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@AppraisalID", appraisalsVM.AppraisalId);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.Fill(ds);

                conn.Close();

            }

            return Ok(new { status = "Success" });

        }

catch(Exception ex)

{

            var data = new TblErrors();

            data.ErrorMessage = ex.Message;

data.ErrorNumber = 0;

data.ErrorProcedure = "proc_Appraisal_Archive";

            data.ErrorLine = 525;

            this.SaveErrors(data);

            return Ok(new { status = "Failed" });

        }

with cte table logic

 CREATE Procedure [dbo].[USP_Reports_EOYearStatus]  

@AppraisalTypeID INT  

AS   

BEGIN  

 SET NOCOUNT ON  

 DECLARE @Year INT  

   

 SET @Year = (  

  SELECT Top 1 CAST (SUBSTRING(Title, 1, 4) AS INT)   

  FROM AppraisalTypes  

  WHERE AppraisalTypeID = @AppraisalTypeID  

 )  

  

 DECLARE @midYearStart DATE = CAST(CONCAT(@Year, '-05-01') AS DATE);  

 DECLARE @midYearEnd DATE = CAST(CONCAT(@Year, '-09-30') AS DATE);  

 DECLARE @endYearStart DATE = DATEADD(DAY, 1, @midYearEnd);  

 DECLARE @endYearEnd DATE = DATEADD(YEAR, 1, @midYearStart);  

  

 SELECT    

  Distinct P.ProfileID, P.AppraisalID, P.EmployeeNumber, P.EmployeeName,   

  P.DivisionID, D.DisplayName AS Division, P.CountryID, C.CountryName, P.[Location],  

  P.DateHired,A.EmployeeSignDate, M.EmployeeName AS Manager, M.EmployeeNumber AS MgrNumber, A.ManagerSignDate as ManagerSignDate,  

  MM.EmployeeName AS MgrsMgrName, MM.EmployeeNumber AS MgrsMgrNumber, A.ManagersManagerSignDate as ManagersManagerSignDate,  

  A.AppraisalID, AA.AppraisalTypeID, AA.Title,  

  CASE   

   WHEN ISNULL(A.SelfAssessmentComplete, '') = '' OR A.SelfAssessmentComplete = 0 THEN 'Employee Pending'  

   WHEN A.SelfAssessmentComplete = 1 AND (A.ManagerStepComplete = 0 OR ISNULL(A.ManagerStepComplete, '') = '') THEN 'Manager Pending'  

   WHEN A.ManagerStepComplete = 1 AND (A.EmployeeName IS NULL OR A.EmployeeSignDate IS NULL) THEN 'Employee Sign-off Pending'  

   WHEN A.ManagerStepComplete = 1 AND A.EmployeeName IS NOT NULL AND A.EmployeeSignDate IS NOT NULL  

   AND (A.ManagerName IS NULL OR A.ManagerSignDate IS NULL) THEN 'Manager Sign-off Pending'  

   WHEN A.ManagerStepComplete = 1 AND A.EmployeeName IS NOT NULL AND A.EmployeeSignDate IS NOT NULL  

   AND A.ManagerName IS NOT NULL AND A.ManagerSignDate IS NOT NULL AND (A.ManagersManagerName IS NULL OR A.ManagersManagerSignDate IS NULL) THEN 'Manager''s manager Sign-off Pending'  

  END AS EOYearStatus,  

  J.[Description] AS JobFamily, P.Email, H.ASSIGNMENT_STATUS, H.TERMINATIONDATE, H.DIRECT_INDIRECT, AR.[Description] AS Rating,  

  @Year AS AppraisalYear,  

   NULL AS ModifiedOn  

    

 FROM   

  Profiles P  

  JOIN tbl_HRITMasterData H ON H.EMPLOYEE_NUMBER = P.EmployeeNumber  

  JOIN JobFamily J ON J.Id = P.JobFamilyID  

  JOIN Appraisals A ON A.AppraisalID = P.AppraisalID  

  JOIN AppraisalRatings AR ON AR.Id = A.OverallRating  

  JOIN AppraisalTypes AA ON A.AppraisalTypeID = AA.AppraisalTypeID  

  JOIN [dbo].[vw_BusinessUnits] D ON D.BusinessUnitID = P.DivisionID  

  JOIN [dbo].[vw_Countries] C ON C.CountryID = P.CountryID  

  JOIN Profiles M ON P.ManagerID = M.ProfileID  

  JOIN Profiles MM ON M.ManagerID = MM.ProfileID  

 WHERE A.AppraisalTypeID = @AppraisalTypeID  

 --AND A.AppraisalID = 8444  

 AND P.TerminationDate IS NULL  

 AND H.DIRECT_INDIRECT = 'indirect'  

 AND P.Networkid NOT IN ('removed','Duplicate','pdecker','deveritt')  

 AND P.Networkid NOT Like '%Term_Duplicate%'  

 AND H.EMPLOYEE_NUMBER NOT IN ('103098', '78568', '34358')  

  

   

 UNION   

  

 SELECT    

  Distinct P.ProfileID, P.AppraisalID, P.EmployeeNumber, P.EmployeeName,   

  P.DivisionID, D.DisplayName AS Division, P.CountryID, C.CountryName, P.[Location],  

  P.DateHired,A.EmployeeSignDate, M.EmployeeName AS Manager, M.EmployeeNumber AS MgrNumber,A.ManagerSignDate as ManagerSignDate,   

  MM.EmployeeName AS MgrsMgrName, MM.EmployeeNumber AS MgrsMgrNumber,A.ManagersManagerSignDate as ManagersManagerSignDate,    

  A.OriginalAppraisalID AS AppraisalID, AA.AppraisalTypeID, AA.Title,  

  --'Completed' AS EOYearStatus  

  CASE   

   WHEN A.EmployeeName = 'HR Close' OR A.ManagerName = 'HR Close' OR A.ManagersManagerName = 'HR Close' THEN 'HR Close'  

  ELSE 'Completed'  

  END AS EOYearStatus,  

  J.[Description] AS JobFamily, P.Email, H.ASSIGNMENT_STATUS, H.TERMINATIONDATE, H.DIRECT_INDIRECT, AR.[Description] AS Rating,  

  @Year AS AppraisalYear,     CASE       WHEN A.EmployeeName = 'HR Close' OR A.ManagerName = 'HR Close' OR A.ManagersManagerName = 'HR Close'  THEN A.ModifiedOn  ELSE NULL    END AS ModifiedOn  

 FROM   

  Profiles P   

  JOIN tbl_HRITMasterData H ON H.EMPLOYEE_NUMBER = P.EmployeeNumber  

  JOIN JobFamily J ON J.Id = P.JobFamilyID  

  JOIN Archive.Appraisals A ON A.OriginalAppraisalID = P.AppraisalID  

  JOIN AppraisalRatings AR ON AR.Id = A.OverallRating  

  JOIN AppraisalTypes AA ON A.AppraisalType = AA.Title  

  JOIN [dbo].[vw_BusinessUnits] D ON D.BusinessUnitID = P.DivisionID  

  JOIN [dbo].[vw_Countries] C ON C.CountryID = P.CountryID  

  JOIN Profiles M ON P.ManagerID = M.ProfileID  

  JOIN Profiles MM ON M.ManagerID = MM.ProfileID  

 WHERE AA.AppraisalTypeID = @AppraisalTypeID  

 AND H.DIRECT_INDIRECT = 'indirect'  

 AND P.Networkid NOT IN ('removed','Duplicate','pdecker','deveritt')  

 AND P.Networkid NOT Like '%Term_Duplicate%'  

 AND H.EMPLOYEE_NUMBER NOT IN ('103098', '78568', '34358')  

   

 UNION   

  

 SELECT   

  Distinct P.ProfileID, P.AppraisalID, P.EmployeeNumber, P.EmployeeName,   

  P.DivisionID, D.DisplayName AS Division, P.CountryID, C.CountryName, P.[Location],  

  P.DateHired,NULL as EmployeeSignDate, M.EmployeeName AS Manager, M.EmployeeNumber AS MgrNumber,NULL  as ManagerSignDate,  

  MM.EmployeeName AS MgrsMgrName, MM.EmployeeNumber AS MgrsMgrNumber,NULL  as ManagersManagerSignDate,   

  NULL AppraisalID, NULL AppraisalTypeID, NULL Title,  

  'Ineligible' AS EOYearStatus,  

  J.[Description] AS JobFamily, P.Email, H.ASSIGNMENT_STATUS, H.TERMINATIONDATE, H.DIRECT_INDIRECT, NULL AS Rating,  

  @Year AS AppraisalYear,NULL AS ModifiedOn   

 FROM   

  Profiles P  

  JOIN tbl_HRITMasterData H ON H.EMPLOYEE_NUMBER = P.EmployeeNumber  

  JOIN JobFamily J ON J.Id = P.JobFamilyID  

  JOIN [dbo].[vw_BusinessUnits] D ON D.BusinessUnitID = P.DivisionID  

  JOIN [dbo].[vw_Countries] C ON C.CountryID = P.CountryID  

  JOIN Profiles M ON P.ManagerID = M.ProfileID  

  JOIN Profiles MM ON M.ManagerID = MM.ProfileID  

 WHERE P.DateHired BETWEEN @endYearStart AND @endYearEnd  

 AND H.DIRECT_INDIRECT = 'indirect'  

 AND P.Networkid NOT IN ('removed','Duplicate','pdecker','deveritt')  

 AND P.Networkid NOT Like '%Term_Duplicate%'  

 AND H.EMPLOYEE_NUMBER NOT IN ('103098', '78568', '34358')  

 Order BY P.ProfileID  

END