Data Services

Overview

The data services layer in MepApps.Dash.Inv.Batch.MiniMrpOrderCreation provides a comprehensive data access infrastructure that interfaces with SYSPRO databases, manages complex queries, handles transactions, and implements caching strategies. The layer uses Entity Framework alongside Dapper for optimal performance and flexibility.

Key Concepts

• Dual ORM Strategy: Entity Framework for complex operations, Dapper for performance-critical queries
• Repository Pattern: Abstracted data access through service methods
• Query Optimization: LINQ and SQL optimization for large datasets
• Transaction Management: Ensuring data consistency across operations

Implementation Details

DbContext Configuration

The core data context is configured in PluginSysproDataContext:

// Db/PluginSysproDataContext.cs
public class PluginSysproDataContext : DbContext
{
    // SYSPRO Tables
    public DbSet<ApSupplier> ApSupplier { get; set; }
    public DbSet<ApSupplierAddr> ApSupplierAddr { get; set; }
    public DbSet<ApBank> ApBank { get; set; }
    public DbSet<InvMaster> InvMaster { get; set; }
    public DbSet<InvWarehouse> InvWarehouse { get; set; }
    public DbSet<InvBuyer> InvBuyer { get; set; }
    public DbSet<InvWhControl> InvWhControl { get; set; }
    public DbSet<PorSupStkInfo> PorSupStkInfo { get; set; }
    public DbSet<TblCurrency> TblCurrency { get; set; }
    
    // Custom Views
    public DbSet<CG_InventoryOrdering_View> CG_InventoryOrdering_View { get; set; }
    public DbSet<CG_v_SalesOrderJobDemand> CG_v_SalesOrderJobDemand { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(ConnectionString);
            optionsBuilder.EnableSensitiveDataLogging();
            optionsBuilder.EnableServiceProviderCaching();
        }
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure entity mappings
        modelBuilder.Entity<CG_InventoryOrdering_View>()
            .HasNoKey()
            .ToView("CG_InventoryOrdering_View");
            
        modelBuilder.Entity<InvWarehouse>()
            .HasKey(e => new { e.StockCode, e.Warehouse });
    }
}

Core Data Service Implementation

The InvOrderingService demonstrates comprehensive data access patterns:

// Services/InvOrderingService.cs (lines 22-93)
public IQueryable<WarehouseOrderingListItem> GetWarehouseQueryable(WarehouseSearchFilter searchFilter)
{
    try
    {
        var queryable = from x in _sysproDataContext.CG_InventoryOrdering_View
                        select new WarehouseOrderingListItem
                        {
                            Warehouse = x.Warehouse,
                            WarehouseDescription = x.WarehouseDesc,
                            Supplier = x.Supplier,
                            SupplierName = x.SupplierName,
                            StockCode = x.StockCode,
                            StockDescription = x.Description,
                            StockOnHold = x.StockOnHold == "F" || x.StockOnHold == "P",
                            SelectedOrderQty = x.QtyToOrder == null ? 0 : x.QtyToOrder.Value,
                            OrderQty = x.QtyToOrder == null ? 0 : x.QtyToOrder.Value,
                            QtyOnHand = x.QtyOnHand,
                            QtyOnOrder = x.OnOrder == null ? 0 : x.OnOrder.Value,
                            MinQty = x.MinQty,
                            SafetyQty = x.SafetyQty,
                            Demand = x.Demand == null ? 0 : x.Demand.Value,
                            ReOrderQty = x.ReOrderQty,
                            AllocatedSales = x.AllocatedSales,
                            WipAllocated = x.WipAllocated,
                            LastPricePaid = x.LastPricePaid,
                            LastPriceUom = x.LastPrcUom,
                            StockUom = x.StockUom,
                            AlternateUom = x.AlternateUom,
                            OtherUom = x.OtherUom,
                        };

        if (searchFilter != null)
        {
            // Apply filters
            if (!string.IsNullOrWhiteSpace(searchFilter.SelectedSupplier))
            {
                var supplier = searchFilter.SelectedSupplier.Trim().ToUpper();
                queryable = queryable.Where(x => x.Supplier.ToUpper().Contains(supplier) || 
                                                 x.SupplierName.ToUpper().Contains(supplier));
            }

            if (!string.IsNullOrWhiteSpace(searchFilter.SelectedWarehouse?.Warehouse))
            {
                queryable = queryable.Where(x => x.Warehouse == searchFilter.SelectedWarehouse.Warehouse);
            }

            if (!string.IsNullOrWhiteSpace(searchFilter.SearchString))
            {
                var searchstring = searchFilter.SearchString.Trim().ToUpper();
                queryable = queryable.Where(x =>
                    x.Warehouse.ToUpper().Contains(searchstring) ||
                    x.WarehouseDescription.ToUpper().Contains(searchstring) ||
                    x.Supplier.ToUpper().Contains(searchstring) ||
                    x.SupplierName.ToUpper().Contains(searchstring) ||
                    x.StockCode.ToUpper().Contains(searchstring) ||
                    x.StockDescription.ToUpper().Contains(searchstring));
            }

            if (!searchFilter.ShowStockOnHold)
            {
                queryable = queryable.Where(x => !x.StockOnHold);
            }
        }

        return queryable.OrderBy(x => x.Warehouse).ThenBy(x => x.StockCode);
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in GetWarehouseQueryable");
        throw;
    }
}

Dapper Integration for Performance

High-performance queries using Dapper:

// Services/InvOrderingService.cs (lines 95-107)
public async Task<IEnumerable<BuyerListItem>> GetBuyers()
{
    var sSql = "select Buyer, Name, Email from InvBuyer";
    try
    {
        return await _sysproDataContext.Database.Connection.QueryAsync<BuyerListItem>(sSql);
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in GetBuyers {TSql}", sSql);
        throw;
    }
}

public async Task<IEnumerable<SupplierListItem>> GetSuppliers()
{
    var sSql = @"select
        sup.Supplier,
        sup.SupplierName,
        isnull(cur.Currency, '$') Currency,
        isnull(cur.BuyExchangeRate, 1) ExchangeRate,
        isnull(cur.BuyMulDiv, 'M') MulDiv
    from ApSupplier sup
        left outer join TblCurrency cur on cur.Currency = sup.Currency
    where sup.OnHold = 'N' and sup.PurchOrdAllowed = 'Y'";
    
    try
    {
        return await _sysproDataContext.Database.Connection.QueryAsync<SupplierListItem>(sSql);
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in GetSuppliers {TSql}", sSql);
        throw;
    }
}

Complex Query Composition

Building complex queries with LINQ:

// ViewModels/SupplierSelectionViewModel.cs (lines 88-134)
public IQueryable<SupplierSelectionListItem> GetQueryable()
{
    try
    {
        var queryable = from s in _pluginSysproDataContext.ApSupplier
                        join ad in _pluginSysproDataContext.ApSupplierAddr
                        on s.Supplier equals ad.Supplier
                        
                        join b in _pluginSysproDataContext.ApBank
                        on s.Bank equals b.Bank
                        into bankJoin
                        
                        from bank in bankJoin.DefaultIfEmpty()
                        
                        select new SupplierSelectionListItem
                        {
                            Supplier = s.Supplier,
                            SupplierName = s.SupplierName,
                            Bank = s.Bank,
                            BankDescription = bank == null ? "" : bank.Description,
                            CurrentBalance = s.CurrentBalance ?? 0,
                            Address1 = ad.SupAddr1,
                            Address2 = ad.SupAddr2,
                            Address3 = ad.SupAddr3,
                            Address4 = ad.SupAddr4,
                            Address5 = ad.SupAddr5,
                            PostalCode = ad.SupPostalCode,
                            OnHold = s.OnHold == "Y" ? "Y" : string.Empty
                        };

        if (!string.IsNullOrEmpty(SearchString))
        {
            var searchstring = SearchString.ToUpper();
            queryable = queryable.Where(x => 
                x.Supplier.ToUpper().Contains(searchstring) ||
                x.SupplierName.ToUpper().Contains(searchstring) ||
                x.Address1.ToUpper().Contains(searchstring) ||
                x.Address2.ToUpper().Contains(searchstring) ||
                x.Address3.ToUpper().Contains(searchstring) ||
                x.Address4.ToUpper().Contains(searchstring) ||
                x.Address5.ToUpper().Contains(searchstring) ||
                x.PostalCode.ToUpper().Contains(searchstring) ||
                x.Bank.ToUpper().Contains(searchstring) ||
                x.BankDescription.ToUpper().Contains(searchstring));
        }

        return queryable.OrderBy(x => x.Supplier);
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Failed to get SupplierSelectionListItem queryable");
        throw;
    }
}

Transaction Handling

Managing database transactions for data consistency:

// Example transaction pattern
public async Task<bool> ProcessOrderBatch(IEnumerable<Order> orders)
{
    using var transaction = await _sysproDataContext.Database.BeginTransactionAsync();
    
    try
    {
        foreach (var order in orders)
        {
            // Create order header
            var header = new PorMasterHdr
            {
                PurchaseOrder = order.OrderNumber,
                Supplier = order.Supplier,
                OrderDate = DateTime.Now
            };
            _sysproDataContext.PorMasterHdr.Add(header);
            
            // Create order lines
            foreach (var line in order.Lines)
            {
                var detail = new PorMasterDetail
                {
                    PurchaseOrder = order.OrderNumber,
                    Line = line.LineNumber,
                    StockCode = line.StockCode,
                    OrderQty = line.Quantity
                };
                _sysproDataContext.PorMasterDetail.Add(detail);
            }
        }
        
        await _sysproDataContext.SaveChangesAsync();
        await transaction.CommitAsync();
        
        return true;
    }
    catch (Exception ex)
    {
        await transaction.RollbackAsync();
        _logger.LogError(ex, "Failed to process order batch");
        throw;
    }
}

Custom SQL View Management

The system manages custom database views dynamically:

// Services/DatabaseValidationService.cs (lines 104-129)
public async Task CreateMissingDatabaseViewsAsync()
{
    try
    {
        if (DatabaseValidationObjects == null)
        {
            _logger.LogError("DatabaseValidationObjects object is NULL");
            throw new Exception("DatabaseValidationObjects object is NULL");
        }

        foreach (var view in DatabaseValidationObjects.Where(
            x => x.ObjectType.Trim().ToUpper() == "VIEW" && 
                 !string.IsNullOrWhiteSpace(x.Name)))
        {
            await CreateViewIfNotExistsSysproAsync(view.Name, view.ResourceFile)
                .ConfigureAwait(true);
        }
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in CreateMissingDatabaseViewsAsync");
    }
}

Data Validation at Service Level

Implementing business rule validation:

public class DataValidationService
{
    public async Task<ValidationResult> ValidateOrderData(Order order)
    {
        var errors = new List<string>();
        
        // Validate supplier
        var supplier = await _context.ApSupplier
            .FirstOrDefaultAsync(s => s.Supplier == order.Supplier);
            
        if (supplier == null)
        {
            errors.Add($"Supplier {order.Supplier} not found");
        }
        else if (supplier.OnHold == "Y")
        {
            errors.Add($"Supplier {order.Supplier} is on hold");
        }
        
        // Validate stock items
        foreach (var line in order.Lines)
        {
            var stock = await _context.InvMaster
                .FirstOrDefaultAsync(i => i.StockCode == line.StockCode);
                
            if (stock == null)
            {
                errors.Add($"Stock code {line.StockCode} not found");
            }
            else if (stock.StockOnHold == "Y")
            {
                errors.Add($"Stock code {line.StockCode} is on hold");
            }
        }
        
        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }
}

Caching Strategies

Implementing caching for frequently accessed data:

public class CachedDataService
{
    private readonly IMemoryCache _cache;
    private readonly PluginSysproDataContext _context;
    
    public async Task<IEnumerable<WarehouseListItem>> GetWarehousesAsync()
    {
        return await _cache.GetOrCreateAsync("warehouses", async entry =>
        {
            entry.SlidingExpiration = TimeSpan.FromMinutes(30);
            
            var sSql = "SELECT Warehouse, Description FROM InvWhControl";
            var warehouses = await _context.Database.Connection
                .QueryAsync<WarehouseListItem>(sSql);
                
            return warehouses;
        });
    }
    
    public void InvalidateWarehouseCache()
    {
        _cache.Remove("warehouses");
    }
}

Examples

Example 1: Implementing a Repository Method

public async Task<IEnumerable<WarehouseOrderingListItem>> GetOrderingItemsAsync(
    string warehouse, 
    string supplier,
    bool includeOnHold = false)
{
    var query = _context.CG_InventoryOrdering_View.AsQueryable();
    
    // Apply filters
    if (!string.IsNullOrEmpty(warehouse))
        query = query.Where(x => x.Warehouse == warehouse);
        
    if (!string.IsNullOrEmpty(supplier))
        query = query.Where(x => x.Supplier == supplier);
        
    if (!includeOnHold)
        query = query.Where(x => x.StockOnHold != "Y");
    
    // Project to DTO
    var items = await query.Select(x => new WarehouseOrderingListItem
    {
        Warehouse = x.Warehouse,
        Supplier = x.Supplier,
        StockCode = x.StockCode,
        OrderQty = x.QtyToOrder ?? 0,
        // ... other mappings
    }).ToListAsync();
    
    return items;
}

Example 2: Batch Operations

public async Task<int> BulkUpdateStockLevels(
    Dictionary<string, decimal> stockUpdates)
{
    var sql = @"
        UPDATE InvWarehouse 
        SET QtyOnHand = QtyOnHand + @Adjustment
        WHERE StockCode = @StockCode AND Warehouse = @Warehouse";
    
    var parameters = stockUpdates.Select(kvp => new
    {
        StockCode = kvp.Key.Split('|')[0],
        Warehouse = kvp.Key.Split('|')[1],
        Adjustment = kvp.Value
    });
    
    var rowsAffected = await _context.Database.Connection
        .ExecuteAsync(sql, parameters);
        
    return rowsAffected;
}

Example 3: Complex Aggregation Query

public async Task<OrderSummaryStats> GetOrderSummaryStatsAsync(DateTime fromDate)
{
    var sql = @"
        SELECT 
            COUNT(DISTINCT Supplier) as SupplierCount,
            COUNT(DISTINCT StockCode) as ItemCount,
            SUM(QtyToOrder) as TotalQuantity,
            SUM(QtyToOrder * LastPricePaid) as TotalValue
        FROM CG_InventoryOrdering_View
        WHERE QtyToOrder > 0";
    
    var stats = await _context.Database.Connection
        .QuerySingleAsync<OrderSummaryStats>(sql);
        
    return stats;
}

Best Practices

Query Optimization

• Use appropriate indexes on frequently queried columns
• Avoid N+1 query problems with Include() or eager loading
• Use projection (Select) to retrieve only needed columns
• Consider using compiled queries for frequently executed queries

Connection Management

• Let Entity Framework manage connections automatically
• Use async methods for all database operations
• Dispose DbContext properly using dependency injection scopes
• Avoid long-running DbContext instances

Error Handling

• Log SQL queries and parameters for debugging
• Provide meaningful error messages
• Handle concurrency conflicts appropriately
• Implement retry logic for transient failures

Performance Considerations

• Use Dapper for read-heavy operations
• Implement pagination for large result sets
• Consider using stored procedures for complex operations
• Monitor query performance with logging

Common Pitfalls

Lazy Loading Issues

// BAD: Can cause N+1 queries
foreach (var order in orders)
{
    Console.WriteLine(order.Supplier.Name); // Lazy loads each supplier
}

// GOOD: Eager load related data
var orders = await _context.Orders
    .Include(o => o.Supplier)
    .ToListAsync();

Improper Disposal

// BAD: Manual DbContext creation without disposal
var context = new PluginSysproDataContext();
var data = context.InvMaster.ToList();
// Context not disposed!

// GOOD: Let DI handle lifecycle
public MyService(PluginSysproDataContext context)
{
    _context = context; // DI manages disposal
}

SQL Injection

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

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

Related Documentation

• Service Architecture
• Business Logic Services
• SYSPRO Data Models
• Database Validation Service

Summary

The data services layer provides a robust, performant foundation for all database operations in the MiniMrpOrderCreation dashboard. Through the combination of Entity Framework for complex operations and Dapper for performance-critical queries, the system achieves both flexibility and speed. Proper transaction management, validation, and caching strategies ensure data integrity and optimal performance throughout the application.