Data Services

Overview

The data services layer in the EFT Remittance Dashboard provides a robust abstraction over database operations, combining Entity Framework for ORM capabilities with Dapper for performance-critical queries. This hybrid approach ensures both developer productivity and runtime performance while maintaining data integrity and consistency.

Key Concepts

• Hybrid Data Access: Entity Framework + Dapper combination
• DbContext Management: PluginSysproDataContext for SYSPRO integration
• Connection Pooling: Efficient database connection management
• Query Optimization: Performance-tuned data retrieval
• Transaction Support: ACID compliance for critical operations

Implementation Details

DbContext Configuration

The foundation of data access is the PluginSysproDataContext:

// MepApps.Dash.Ap.Rpt.EftRemittance/Db/PluginSysproDataContext.cs
public class PluginSysproDataContext : SysproSessionEmptyDataContext
{
    public PluginSysproDataContext(ISysproSession sysproSession)
        : base(sysproSession?.GetConnectionString() ?? "")
    {
    }

    public virtual DbSet<ApRemit> ApRemits { get; set; }
    public virtual DbSet<MepAppsApEftRemittanceAudit> MepAppsApEftRemittanceAudit { get; set; }

    protected override void OnModelCreating(DbModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<ApRemit>()
            .ToTable("ApRemit")
            .HasKey(e => new { e.Supplier, e.Invoice })
            .HasIndex(e => new { e.Supplier, e.Invoice });
        
        builder.Entity<MepAppsApEftRemittanceAudit>()
            .ToTable("MepAppsApEftRemittanceAudit")
            .HasKey(e => e.ID)
            .HasIndex(e => e.ID);
    }
}

Entity Models

Custom entity models represent business data:

// MepApps.Dash.Ap.Rpt.EftRemittance/Db/CustomDbModels/ApRemit.cs
[Table("ApRemit")]
public partial class ApRemit
{
    [Key]
    [Column(Order = 0)]
    [StringLength(15)]
    public string PaymentNumber { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(15)]
    public string Bank { get; set; }

    public DateTime? PaymentDate { get; set; }

    [Key]
    [Column(Order = 2)]
    [StringLength(20)]
    public string Invoice { get; set; }

    [Key]
    [Column(Order = 3)]
    public decimal GrossPayValue { get; set; }

    [StringLength(255)]
    public string RemitEmail { get; set; }

    [Key]
    [Column(Order = 29)]
    [StringLength(1)]
    public string PaymentType { get; set; }
}

Examples

Example 1: Dapper Query Implementation

// MepApps.Dash.Ap.Rpt.EftRemittance/Services/EftRemittanceService.cs
public async Task<IEnumerable<PaymentDetail>> QueryPaymentNumber(
    string paymentNumber, 
    string checkFilter)
{
    try
    {
        string sql = @"
            SELECT 
                PaymentNumber,
                Supplier,
                SupplierName,
                Cheque,
                RemitEmail,
                Invoice,
                GrossPayValue,
                DiscPayValue,
                NetPayValue,
                PaymentDate
            FROM ApRemit 
            WHERE PaymentNumber = @PaymentNumber";

        if (!string.IsNullOrWhiteSpace(checkFilter))
        {
            sql += " AND Cheque LIKE @CheckFilter";
        }

        sql += " ORDER BY Supplier, Invoice";

        using (var connection = _context.Database.Connection)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@PaymentNumber", paymentNumber);
            
            if (!string.IsNullOrWhiteSpace(checkFilter))
            {
                parameters.Add("@CheckFilter", $"%{checkFilter}%");
            }

            var results = await connection.QueryAsync<PaymentDetail>(
                sql, 
                parameters,
                commandTimeout: 30);

            _logger.LogDebug("Retrieved {Count} payment details for {PaymentNumber}. {@QueryContext}", 
                results.Count(), 
                paymentNumber,
                new { paymentNumber, checkFilter });

            return results;
        }
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Failed to query payment details. {@QueryContext}", 
            new { paymentNumber, checkFilter });
        throw;
    }
}

Example 2: Entity Framework CRUD Operations

public async Task UpdateSupplierRemitEmail(string supplier, string remitEmail)
{
    try
    {
        // Validate email format
        if (!IsValidEmail(remitEmail))
        {
            throw new ValidationException($"Invalid email format: {remitEmail}");
        }

        // Update using Entity Framework
        var remittances = await _context.ApRemits
            .Where(r => r.Supplier == supplier)
            .ToListAsync();

        if (!remittances.Any())
        {
            _logger.LogWarning("No remittances found for supplier {Supplier}", supplier);
            return;
        }

        foreach (var remittance in remittances)
        {
            remittance.RemitEmail = remitEmail;
        }

        await _context.SaveChangesAsync();

        _logger.LogInformation("Updated {Count} remittance emails for supplier {Supplier}. {@UpdateContext}", 
            remittances.Count, 
            supplier,
            new { supplier, remitEmail, count = remittances.Count });
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Failed to update supplier email. {@UpdateContext}", 
            new { supplier, remitEmail });
        throw;
    }
}

Example 3: Audit Trail Implementation

public async Task UpdateAuditTable(
    string paymentNumber, 
    string supplier, 
    string cheque, 
    int? mailItemId, 
    string emailAddress, 
    string fileAttachmentPath)
{
    try
    {
        var auditEntry = new MepAppsApEftRemittanceAudit
        {
            TrnTime = DateTime.Now,
            SysproOperator = _sharedShellInterface.CurrentSession.SysproOperator,
            MailItemId = mailItemId?.ToString(),
            PaymentNumber = paymentNumber,
            Supplier = supplier,
            Check = cheque,
            Email = emailAddress,
            Attachment = fileAttachmentPath
        };

        _context.MepAppsApEftRemittanceAudit.Add(auditEntry);
        await _context.SaveChangesAsync();

        _logger.LogInformation("Audit entry created. {@AuditContext}", 
            new { 
                paymentNumber, 
                supplier, 
                mailItemId, 
                sysproOperator = auditEntry.SysproOperator 
            });
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Failed to create audit entry. {@AuditContext}", 
            new { paymentNumber, supplier, cheque, mailItemId });
        // Don't throw - audit failures shouldn't stop processing
    }
}

Transaction Handling

Distributed Transaction Pattern

public async Task<bool> ProcessPaymentBatch(IEnumerable<PaymentDetail> payments)
{
    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        try
        {
            foreach (var payment in payments)
            {
                // Update payment status
                await UpdatePaymentStatus(payment.PaymentNumber, "Processing");
                
                // Process remittance
                await ProcessRemittance(payment);
                
                // Update audit trail
                await UpdateAuditTable(
                    payment.PaymentNumber,
                    payment.Supplier,
                    payment.Cheque,
                    null,
                    payment.RemitEmail,
                    null);
            }

            scope.Complete();
            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Batch processing failed, transaction rolled back");
            return false;
        }
    }
}

Query Optimization Strategies

Index Usage

-- Ensure proper indexes exist for common queries
CREATE NONCLUSTERED INDEX IX_ApRemit_PaymentNumber 
ON ApRemit(PaymentNumber) 
INCLUDE (Supplier, SupplierName, RemitEmail);

CREATE NONCLUSTERED INDEX IX_ApRemit_PaymentDate 
ON ApRemit(PaymentDate DESC, PaymentNumber DESC)
WHERE PaymentType = 'E';

Query Performance Monitoring

public async Task<IEnumerable<SelectionItem>> QueryPaymentsWithMetrics(bool eftPaymentsOnly)
{
    var stopwatch = Stopwatch.StartNew();
    
    try
    {
        var results = await QueryPayments(eftPaymentsOnly);
        
        stopwatch.Stop();
        
        if (stopwatch.ElapsedMilliseconds > 1000)
        {
            _logger.LogWarning("Slow query detected: {ElapsedMs}ms for {RecordCount} records", 
                stopwatch.ElapsedMilliseconds, 
                results.Count());
        }
        
        return results;
    }
    finally
    {
        _logger.LogDebug("Query execution time: {ElapsedMs}ms", 
            stopwatch.ElapsedMilliseconds);
    }
}

Data Caching

public class CachedDataService
{
    private readonly MemoryCache _cache = new MemoryCache(new MemoryCacheOptions
    {
        SizeLimit = 100
    });
    
    public async Task<IEnumerable<SelectionItem>> GetPaymentsAsync(bool eftOnly)
    {
        var cacheKey = $"payments_{eftOnly}";
        
        if (_cache.TryGetValue(cacheKey, out IEnumerable<SelectionItem> cached))
        {
            _logger.LogDebug("Cache hit for key: {CacheKey}", cacheKey);
            return cached;
        }
        
        var data = await _dataService.QueryPayments(eftOnly);
        
        _cache.Set(cacheKey, data, new MemoryCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5),
            Size = 1
        });
        
        _logger.LogDebug("Cache miss for key: {CacheKey}, cached {Count} items", 
            cacheKey, data.Count());
        
        return data;
    }
}

Data Validation

public class DataValidationService
{
    public ValidationResult ValidatePaymentData(PaymentDetail payment)
    {
        var errors = new List<string>();
        
        // Required field validation
        if (string.IsNullOrWhiteSpace(payment.PaymentNumber))
            errors.Add("Payment number is required");
        
        if (string.IsNullOrWhiteSpace(payment.Supplier))
            errors.Add("Supplier is required");
        
        // Email validation
        if (!string.IsNullOrWhiteSpace(payment.RemitEmail) && 
            !IsValidEmail(payment.RemitEmail))
            errors.Add($"Invalid email format: {payment.RemitEmail}");
        
        // Amount validation
        if (payment.NetPayValue <= 0)
            errors.Add("Net payment value must be positive");
        
        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }
}

Best Practices

1. Use parameterized queries to prevent SQL injection
2. Implement connection pooling for performance
3. Add appropriate indexes for frequently queried columns
4. Use async methods for all database operations
5. Implement retry logic for transient failures
6. Log query execution times for performance monitoring
7. Validate data before persistence
8. Use transactions for multi-step operations

Common Pitfalls

• Not disposing database connections properly
• N+1 query problems with Entity Framework
• Missing indexes on frequently queried columns
• Not handling database timeout exceptions
• Inappropriate use of lazy loading

Related Documentation

• Service Architecture
• Business Logic Services
• SYSPRO Data Models
• Data Context Patterns

Summary

The data services layer provides a flexible, performant foundation for database operations in the EFT Remittance Dashboard. By combining Entity Framework's productivity features with Dapper's performance capabilities, the architecture delivers both developer efficiency and runtime performance. Proper transaction handling, query optimization, and caching strategies ensure the system can handle production workloads while maintaining data integrity and consistency.