In ASP.NET Core Web API, With Dapper I am consuming this MSSQL Store Procedure TO CHECK FOR DUPLICATE:

ALTER PROCEDURE [dbo].[can_create_new_acct] 
    -- Add the parameters for the stored procedure here
    @psAccountName              varchar(80),
    @pnInitialClassCode         smallint,
    @psSex                      char(1),
    @psAccountHolderType        varchar(20),
    @psAccountType              char(3),
    @psFirstName                varchar(40),
    @psLastName                 varchar(40),
    @pdtDateOfBirth             datetime,
    @rsAccountExist             char(1)     output
AS
BEGIN
    select @rsAccountExist = 'Y'
    select @pdtDateOfBirth = convert(date, @pdtDateOfBirth)

    if not exists (select 1
                    from [AccountOpeningDB].[dbo].[zib_acop_acct_info]
                    where AccountName = @psAccountName
                    and InitialClassCode = @pnInitialClassCode
                    and Sex = @psSex
                    and AccountHolderType = @psAccountHolderType
                    and AccountType = @psAccountType
                    and FirstName = @psFirstName
                    and LastName = @psLastName
                    and convert(date, DateOfBirth) = @pdtDateOfBirth)
    begin
        select @rsAccountExist = 'N'
    end

    return 0
END

I observed that the code allows for duplicate,

Kindly help prevent duplicate

NOTE:

DateOfBirth from the DB is like this:

1978-07-07 00:00:00.0000000

When I checked the Date here, it comes this way:

param.Add(“@pdtDateOfBirth”, account.DateOfBirth, DbType.Date);

07/07/1978 12:00:00

MAIN CODE:

ASP.NET Core Web API:

DbDataAccess:

public class DbDataAccess : IDbDataAccess
{
    private readonly IConfiguration _configuration;
    private readonly IDapperDbAccess _dapperDbAccess;
    private readonly ILogger<DbDataAccess> _logger;
    private readonly ICacheHelper _cacheHelper;
    private readonly IFlexcubeAccountService _flexcubeAccountService;

    public DbDataAccess(
        IFlexcubeAccountService flexcubeAccountService,
        IDapperDbAccess dapperDbAccess, 
        ILogger<DbDataAccess> logger,
        ICacheHelper cacheHelper,
        IConfiguration configuration
        )
    {
        _flexcubeAccountService = flexcubeAccountService ?? throw new ArgumentNullException(nameof(flexcubeAccountService));
        _dapperDbAccess = dapperDbAccess ?? throw new ArgumentNullException(nameof(dapperDbAccess));
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        _cacheHelper = cacheHelper ?? throw new ArgumentNullException(nameof(cacheHelper));
        _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
    }

    public async Task<bool> CanCreateAccountAsync(DuplicateAccountCheck account)
    {
        try
        {
            DynamicParameters param = new DynamicParameters();
            param.Add("@psAccountName", account.AccountName, DbType.String);
            param.Add("@pnInitialClassCode", account.InitialClassCode, DbType.Int16);
            param.Add("@psSex", account.Gender, DbType.String);
            param.Add("@psAccountHolderType", account.AccountHolderType, DbType.String);
            param.Add("@psAccountType", account.AccountType, DbType.String);
            param.Add("@psFirstName", account.FirstName, DbType.String);
            param.Add("@psLastName", account.LastName.ToUpper(), DbType.String);
            param.Add("@pdtDateOfBirth", account.DateOfBirth, DbType.Date);
            param.Add("@rsAccountExist", null, DbType.String, ParameterDirection.Output, 1);

            string query = "can_create_new_acct";

            await _dapperDbAccess.InsertOrUpdateAsync(query, CommandType.StoredProcedure, param);

            string accountExists = param.Get<string>("@rsAccountExist");
            return accountExists == "N";
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"Error checking if account can be created for {account.AccountName}");
            return false;
        }
    }

}

AccountProcessor:

    private async Task<CreateAccountResponseDto> CheckForDuplicateAccountAsync(DuplicateAccountCheck account)
    {
        var response = new CreateAccountResponseDto();
        try
        {
            if (!await CanCreateAccountAsync(account))
            {
                response.SetResponse(ResponseCodes.INSERT_ACCOUNT_FAILED, "Account with this details already exists");
                return response;
            }
            var hasAccountWithMobile = await HasAccountWithMobileAsync(account.PreferredPhoneNumber, account.InitialClassCode);
            var hasAccountWithBvn = !string.IsNullOrWhiteSpace(account.Bvn) && await HasAccountWithBvnAsync(account.Bvn, account.InitialClassCode);
            var hasAccountWithMobileBvnService = await HasAccountWithMobileBvnServiceAsync(account.PreferredPhoneNumber, account.InitialClassCode);

            if (hasAccountWithMobile)
            {
                response.SetResponse(ResponseCodes.INSERT_ACCOUNT_FAILED, "Account in the same class with this mobile number already exists");
                return response;
            }
            if (hasAccountWithMobileBvnService)
            {
                response.SetResponse(ResponseCodes.INSERT_ACCOUNT_FAILED, "Account in the same class with this mobile number (via BVN service) already exists");
                return response;
            }
            if (hasAccountWithBvn)
            {
                response.SetResponse(ResponseCodes.INSERT_ACCOUNT_FAILED, "Account in the same class with this BVN already exists");
                return response;
            }
            //response.SetResponse(ResponseCodes.SUCCESS);
            //return response;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "An error occurred while checking for duplicate accounts for {AccountName}", account.AccountName);
            response.SetResponse(ResponseCodes.INSERT_ACCOUNT_FAILED, "An unexpected error occurred. Please try again later.");
            return response;
        }
        response.SetResponse(ResponseCodes.SUCCESS);
        return response;
    }
    private async Task<bool> CanCreateAccountAsync(DuplicateAccountCheck account)
    {
        try
        {
            return await _dbDataAccess.CanCreateAccountAsync(account);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error checking if account can be created for {AccountName}", account.AccountName);
            return false; // Default to prevent creating accounts in case of errors.
        }
    }