Skip to main content

Example 02: Custom Database Validation Framework

Overview​

The dashboard implements a sophisticated database validation framework that ensures required database objects exist and are correctly structured before the application runs. This prevents runtime errors and provides clear feedback about missing or outdated database components.

Implementation Details​

DatabaseValidation.json Configuration​

[
  {
    "Name": "CG_InventoryOrdering_View",
    "ObjectType": "VIEW",
    "ResourceFile": "SQL.CREATE VIEW CG_InventoryOrdering_View.txt",
    "Version": "1.2.0"
  },
  {
    "Name": "CG_InventoryOrdering_View.QtyToOrder",
    "ObjectType": "VIEWCOLUMN",
    "Required": true
  }
]

Validation Service Implementation​

public class DatabaseValidationService : IDatabaseValidationService
{
    public async Task InitializeAsync()
    {
        // Load validation configuration
        string validationJson = ResourceHelper.GetResourceFileText(
            "DatabaseValidation.json");
        DatabaseValidationObjects = JsonConvert
            .DeserializeObject<List<DatabaseValidationObject>>(validationJson);
    }

    public async Task CreateMissingDatabaseViewsAsync()
    {
        foreach (var view in DatabaseValidationObjects
            .Where(x => x.ObjectType == "VIEW"))
        {
            if (!await ViewExistsSysproAsync(view.Name))
            {
                // Create view from embedded SQL script
                await RunScriptResourceSysproAsync(view.ResourceFile);
            }
        }
    }

    public async Task ValidateAndUpdateExistingViewsAsync()
    {
        foreach (var col in DatabaseValidationObjects
            .Where(x => x.ObjectType == "VIEWCOLUMN"))
        {
            var parts = col.Name.Split('.');
            var viewName = parts[0];
            var columnName = parts[1];
            
            if (!await ViewColumnExistsSysproAsync(viewName, columnName))
            {
                // Update view to add missing column
                await UpdateViewAsync(viewName);
            }
        }
    }
}

Startup Validation​

// MainViewModel.cs
public async Task InitializeAsync()
{
    try
    {
        await _databaseValidationService.InitializeAsync();
        await _databaseValidationService.CreateMissingDatabaseViewsAsync();
        await _databaseValidationService.ValidateAndUpdateExistingViewsAsync();
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Database validation failed");
        ShowDatabaseValidationDialog();
    }
}

Validation Rules​

Object Existence Checks​

  1. Views must exist in sys.views
  2. Columns must exist in INFORMATION_SCHEMA.COLUMNS
  3. Stored procedures in sys.procedures
  4. Functions in sys.objects

Version Validation​

  • Track object versions in extended properties
  • Compare against required versions
  • Apply update scripts if outdated

Dependency Validation​

  • Check referenced tables exist
  • Verify column data types match
  • Validate foreign key relationships

Error Recovery​

Missing Object Creation​

  1. Detect missing object
  2. Load creation script from resources
  3. Execute script
  4. Verify creation success
  5. Log actions for audit

Failed Validation Handling​

  • Display detailed error dialog
  • Provide manual fix instructions
  • Option to continue with warnings
  • Export validation report

Benefits​

  • Prevents runtime errors
  • Simplifies deployment
  • Enables automatic updates
  • Provides clear diagnostics
  • Maintains consistency across environments