Data Access Pattern

Overview

This pattern describes the standard data access architecture used across MepDash dashboards. The implementation combines Entity Framework for complex object mapping with Dapper for performance-critical queries, providing both developer productivity and runtime performance.

Core Concepts

Hybrid Data Access: Entity Framework + Dapper combination
DbContext Management: Centralized database context configuration
Repository Pattern: Abstracted data access through service methods
Query Optimization: Performance-tuned data retrieval strategies
Transaction Support: ACID compliance for critical operations
Connection Pooling: Efficient database connection management

Pattern Implementation

DbContext Configuration

All dashboards implement a custom DbContext for SYSPRO database integration:

// Pattern: DbContext Configuration
public class PluginSysproDataContext : DbContext
{
    // Constructor patterns vary by dashboard requirements
    
    // Standard constructor with connection string
    public PluginSysproDataContext() : base("name=SysproEntities")
    {
        // Disable lazy loading for performance
        this.Configuration.LazyLoadingEnabled = false;
        this.Configuration.ProxyCreationEnabled = false;
    }
    
    // Alternative with SYSPRO session
    public PluginSysproDataContext(ISysproSession sysproSession)
        : base(sysproSession?.GetConnectionString() ?? "")
    {
    }
    
    // Entity Sets
    public DbSet<ArCustomer> ArCustomers { get; set; }
    public DbSet<ArInvoice> ArInvoices { get; set; }
    public DbSet<ApSupplier> ApSuppliers { get; set; }
    public DbSet<InvMaster> InvMaster { get; set; }
    
    // Model configuration
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        // Configure entity mappings
        modelBuilder.Entity<ArCustomer>()
            .ToTable("ArCustomer")
            .HasKey(e => e.Customer);
        
        modelBuilder.Entity<ArInvoice>()
            .ToTable("ArInvoice")
            .HasKey(e => new { e.Customer, e.Invoice });
            
        // Configure relationships
        modelBuilder.Entity<ArInvoice>()
            .HasRequired(i => i.CustomerRef)
            .WithMany(c => c.Invoices)
            .HasForeignKey(i => i.Customer);
    }
    
    // Proper disposal
    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            // Clean up resources
            base.Dispose();
        }
    }
}

Entity Models

Standard entity model patterns for SYSPRO tables:

// Pattern: Entity Model
[Table("ArCustomer")]
public class ArCustomer
{
    [Key]
    [StringLength(15)]
    public string Customer { get; set; }
    
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
    
    public string ShortName { get; set; }
    public string Branch { get; set; }
    public string CreditLimit { get; set; }
    public string Currency { get; set; }
    
    // Navigation properties
    public virtual ICollection<ArInvoice> Invoices { get; set; }
    
    // Custom properties for business logic
    [NotMapped]
    public bool HasOutstandingBalance => 
        OutstandingBalance > 0;
}

Repository Pattern Implementation

Data access is abstracted through service methods following repository patterns:

// Pattern: Repository Service
public class DataService : IDataService
{
    private readonly PluginSysproDataContext _context;
    private readonly ILogger<DataService> _logger;
    
    public DataService(
        PluginSysproDataContext context,
        ILogger<DataService> logger)
    {
        _context = context;
        _logger = logger;
    }
    
    // Generic query methods
    public async Task<IEnumerable<T>> GetAllAsync<T>() where T : class
    {
        try
        {
            return await _context.Set<T>().ToListAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to retrieve all entities of type {EntityType}", 
                typeof(T).Name);
            throw;
        }
    }
    
    public async Task<T> GetByIdAsync<T>(params object[] keyValues) where T : class
    {
        try
        {
            return await _context.Set<T>().FindAsync(keyValues);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to retrieve entity by ID {@KeyValues}", 
                keyValues);
            throw;
        }
    }
    
    // CRUD operations
    public async Task<T> CreateAsync<T>(T entity) where T : class
    {
        _context.Set<T>().Add(entity);
        await _context.SaveChangesAsync();
        
        _logger.LogInformation("Created entity {@Entity}", entity);
        return entity;
    }
    
    public async Task<T> UpdateAsync<T>(T entity) where T : class
    {
        _context.Entry(entity).State = EntityState.Modified;
        await _context.SaveChangesAsync();
        
        _logger.LogInformation("Updated entity {@Entity}", entity);
        return entity;
    }
    
    public async Task DeleteAsync<T>(T entity) where T : class
    {
        _context.Set<T>().Remove(entity);
        await _context.SaveChangesAsync();
        
        _logger.LogInformation("Deleted entity {@Entity}", entity);
    }
}

Dapper Integration for Performance

Performance-critical queries use Dapper for direct SQL execution:

// Pattern: Dapper Query Service
public class PerformanceDataService
{
    private readonly string _connectionString;
    private readonly ILogger<PerformanceDataService> _logger;
    
    public async Task<IEnumerable<T>> ExecuteQueryAsync<T>(
        string sql, 
        object parameters = null)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            try
            {
                var result = await connection.QueryAsync<T>(sql, parameters);
                
                if (_logger.IsEnabled(LogLevel.Trace))
                {
                    _logger.LogTrace("Query executed: {Sql} with {@Parameters}", 
                        sql, parameters);
                }
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Query failed: {Sql}", sql);
                throw;
            }
        }
    }
    
    // Complex query example
    public async Task<IEnumerable<PaymentSummary>> GetPaymentSummaryAsync(
        DateTime startDate, 
        DateTime endDate)
    {
        const string sql = @"
            SELECT 
                p.PaymentNumber,
                p.Supplier,
                p.PaymentDate,
                SUM(pd.PaymentAmount) as TotalAmount,
                COUNT(pd.Invoice) as InvoiceCount
            FROM ApPayment p
            INNER JOIN ApPaymentDetail pd ON p.PaymentNumber = pd.PaymentNumber
            WHERE p.PaymentDate BETWEEN @StartDate AND @EndDate
            GROUP BY p.PaymentNumber, p.Supplier, p.PaymentDate
            ORDER BY p.PaymentDate DESC";
        
        return await ExecuteQueryAsync<PaymentSummary>(sql, 
            new { StartDate = startDate, EndDate = endDate });
    }
}

Transaction Management

Complex operations use transactions to ensure data consistency:

// Pattern: Transaction Management
public class TransactionalService
{
    private readonly PluginSysproDataContext _context;
    private readonly ILogger<TransactionalService> _logger;
    
    public async Task<OperationResult> ExecuteInTransactionAsync(
        Func<Task> operation)
    {
        using (var transaction = _context.Database.BeginTransaction())
        {
            try
            {
                await operation();
                
                transaction.Commit();
                _logger.LogInformation("Transaction committed successfully");
                
                return OperationResult.Success();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                _logger.LogError(ex, "Transaction rolled back due to error");
                
                return OperationResult.Failure(ex.Message);
            }
        }
    }
    
    // Example usage
    public async Task<OperationResult> ProcessBatchOperationAsync(
        BatchRequest request)
    {
        return await ExecuteInTransactionAsync(async () =>
        {
            // Multiple operations in single transaction
            foreach (var item in request.Items)
            {
                await ProcessItemAsync(item);
            }
            
            // Update audit log
            await LogBatchOperationAsync(request);
            
            // Save all changes
            await _context.SaveChangesAsync();
        });
    }
}

Query Optimization Patterns

Optimized query patterns for performance:

// Pattern: Query Optimization
public class OptimizedQueryService
{
    private readonly PluginSysproDataContext _context;
    
    // Projection to reduce data transfer
    public async Task<IEnumerable<CustomerSummary>> GetCustomerSummariesAsync()
    {
        return await _context.ArCustomers
            .Where(c => c.Active)
            .Select(c => new CustomerSummary
            {
                Customer = c.Customer,
                Name = c.Name,
                Balance = c.OutstandingBalance
            })
            .ToListAsync();
    }
    
    // Eager loading to prevent N+1 queries
    public async Task<Customer> GetCustomerWithInvoicesAsync(string customerId)
    {
        return await _context.ArCustomers
            .Include(c => c.Invoices)
            .Include(c => c.Invoices.Select(i => i.InvoiceLines))
            .FirstOrDefaultAsync(c => c.Customer == customerId);
    }
    
    // Pagination for large datasets
    public async Task<PagedResult<T>> GetPagedAsync<T>(
        IQueryable<T> query,
        int pageNumber,
        int pageSize) where T : class
    {
        var totalCount = await query.CountAsync();
        
        var items = await query
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();
        
        return new PagedResult<T>
        {
            Items = items,
            TotalCount = totalCount,
            PageNumber = pageNumber,
            PageSize = pageSize
        };
    }
}

Caching Strategy

Implementing caching for frequently accessed data:

// Pattern: Data Caching
public class CachedDataService
{
    private readonly IMemoryCache _cache;
    private readonly IDataService _dataService;
    private readonly ILogger<CachedDataService> _logger;
    
    public async Task<T> GetCachedAsync<T>(
        string cacheKey,
        Func<Task<T>> factory,
        TimeSpan? expiration = null) where T : class
    {
        if (_cache.TryGetValue(cacheKey, out T cached))
        {
            _logger.LogDebug("Cache hit for key: {CacheKey}", cacheKey);
            return cached;
        }
        
        _logger.LogDebug("Cache miss for key: {CacheKey}", cacheKey);
        
        var data = await factory();
        
        if (data != null)
        {
            var cacheOptions = new MemoryCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = expiration ?? TimeSpan.FromMinutes(5)
            };
            
            _cache.Set(cacheKey, data, cacheOptions);
        }
        
        return data;
    }
    
    // Example usage
    public async Task<IEnumerable<Customer>> GetActiveCustomersAsync()
    {
        return await GetCachedAsync(
            "active-customers",
            async () => await _dataService.GetAllAsync<Customer>()
                .Where(c => c.Active),
            TimeSpan.FromMinutes(10)
        );
    }
}

Dashboard-Specific Implementations

AR Payment Reversal

Uses Entity Framework with Dapper for performance queries
Implements complex queue management queries
Transaction support for payment reversal operations
See: AR Data Services

Inventory Mini MRP

Dual ORM strategy for flexibility
Complex MRP calculation queries
Batch processing with transactions
See: Inventory Data Services

AP EFT Remittance

Hybrid data access approach
SSRS integration for reporting
Audit trail implementation
See: AP Data Services

Best Practices

Always dispose DbContext properly using using statements or dependency injection
Use async methods for all database operations
Implement retry logic for transient failures
Use parameterized queries to prevent SQL injection
Optimize queries with proper indexing and projections
Use transactions for multi-step operations
Cache frequently accessed reference data
Log all data operations with structured logging

Common Pitfalls

N+1 Query Problem

// BAD: Causes N+1 queries
foreach (var customer in customers)
{
    Console.WriteLine(customer.Invoices.Count); // Lazy loads each time
}

// GOOD: Eager load related data
var customers = await _context.Customers
    .Include(c => c.Invoices)
    .ToListAsync();

Missing Disposal

// BAD: Context not disposed
var context = new PluginSysproDataContext();
var data = context.Customers.ToList();

// GOOD: Proper disposal
using (var context = new PluginSysproDataContext())
{
    var data = context.Customers.ToList();
}

SQL Injection

// BAD: String concatenation
var sql = $"SELECT * FROM Customer WHERE Name = '{userInput}'";

// GOOD: Parameterized query
var sql = "SELECT * FROM Customer WHERE Name = @Name";
var result = await connection.QueryAsync(sql, new { Name = userInput });

Overview​

Core Concepts​

Pattern Implementation​

DbContext Configuration​

Entity Models​

Repository Pattern Implementation​

Dapper Integration for Performance​

Transaction Management​

Query Optimization Patterns​

Caching Strategy​

Dashboard-Specific Implementations​

AR Payment Reversal​

Inventory Mini MRP​

AP EFT Remittance​

Best Practices​

Common Pitfalls​

N+1 Query Problem​

Missing Disposal​

SQL Injection​

Related Patterns​

Examples​