πŸ“„ ODSUpdate.cs Documentation


File Path: IrbyImaging.BusinessLogic/Jobs/ODSUpdate.cs
Assembly: IrbyImaging.BusinessLogic
Author: kvamshi
Created: 03-31-2022
Last Modified: 03-31-2022


πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Architectural Context
  3. Dependencies & Technologies
  4. Key Functional Areas
  5. Class: ODSUpdate
  6. Usage & Integration
  7. Error Handling & Logging
  8. Sample Code Snippets
  9. Mermaid UML Diagram
  10. Conclusion

🎯 Overview & Purpose

The ODSUpdate job is designed to synchronize and refresh the Operational Data Store (ODS) within the IrbyImaging ecosystem.
By leveraging Quartz.NET scheduling and a combination of Entity Framework Core, SMO (SQL Management Objects), and raw SQL script execution, this job ensures that:

  • πŸ“‹ Jobs, Employees, Customers, Assets, Purchase Orders, and Work Orders data are up‑to‑date.
  • πŸ“‚ Custom SQL scripts (e.g., status updates) are applied to the production database.

This helps downstream reporting, analytics, and other systems to read from a consistent, denormalized ODS layer.


πŸ—οΈ Architectural Context

[Quartz Scheduler] 
       ↓ Execute()
   [ODSUpdate Job]
       ↓ UpdateODS()
   β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
   β”‚   IrbyContext (EF)   β”‚ ←─┐
   β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜   β”‚ Dependency Injection 
       ↓                     β”‚
   [ ODSManager Service ]     β”‚
   β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
       ↓ Calls: UpdateJob(), UpdateEmployee(), ...
       ↓
   [ SQL Server (Production) ]
       ↓ Executes .sql scripts via SMO
  • Quartz.NET triggers Execute().
  • DI container provides IConfiguration, IServiceProvider, IHttpContextAccessor, IServiceScopeFactory.
  • ODSManager encapsulates per-entity data-sync logic.
  • Raw scripts under wwwroot/Scripts/*.sql are executed using SMO.

πŸ”— Dependencies & Technologies

Category Package / Technology Purpose
Scheduling Quartz Job scheduling & trigger management
ORM / Data Access Microsoft.EntityFrameworkCore Database context (IrbyContext)
SQL Management Microsoft.SqlServer.Management.Smo & Common Execute raw SQL scripts via SMO
ADO.NET Microsoft.Data.SqlClient Low‑level SQL connection and statistics retrieval
Configuration Microsoft.Extensions.Configuration Load connection strings & settings
Dependency Injection Microsoft.Extensions.DependencyInjection ServiceProvider & scopes
HTTP Context Microsoft.AspNetCore.Http Access to incoming HTTP context if needed
CSV Processing CsvHelper (Unused in this file but available for potential CSV I/O)

πŸ”‘ Key Functional Areas

  1. Initialization & DI
    Injects required services:

    • IServiceProvider
    • IConfiguration
    • IHttpContextAccessor
    • IServiceScopeFactory
  2. Quartz Job Execution
    Implements IJob.Execute(IJobExecutionContext) to Kick‑off UpdateODS().

  3. Entity‑Based Sync
    Calls ODSManager to update:

    • Jobs
    • Employees
    • Customers
    • Assets
    • Purchase Orders
    • Work Orders
  4. Script Execution via SMO
    Loads ODS_D_EmployeeStatusUpdate.sql from wwwroot/Scripts and executes it against the target database.

  5. Logging & Error Handling
    Captures SQL info messages, warnings, and errors via OnInfoMessageGenerated.


🏷️ Class: ODSUpdate

public class ODSUpdate : IJob
{
    // DI-backed fields
    private readonly IServiceProvider _provider;
    private readonly IServiceScopeFactory _factory;
    private IConfiguration _configuration;
    private IHttpContextAccessor _httpContextAccessor;

    // Shared message log
    public static string message = string.Empty;

    // Constructor
    public ODSUpdate(IServiceProvider provider,
                     IConfiguration configuration,
                     IHttpContextAccessor httpContextAccessor,
                     IServiceScopeFactory factory) { ... }

    // Quartz entry point
    public Task Execute(IJobExecutionContext context) { ... }

    // Main ODS update routine
    public void UpdateODS() { ... }

    // Event handler for SQL messages
    private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args) { ... }
}

Properties

Name Type Description
_provider IServiceProvider .NET DI service provider
_factory IServiceScopeFactory For creating scoped services
_configuration IConfiguration Application configuration (e.g., connection strings)
_httpContextAccessor IHttpContextAccessor Provides access to HTTP context
message string (static) Aggregated log of SQL messages & errors

Constructor

public ODSUpdate(
    IServiceProvider provider,
    IConfiguration configuration,
    IHttpContextAccessor httpContextAccessor,
    IServiceScopeFactory factory)
{
    _provider = provider;
    _configuration = configuration;
    _httpContextAccessor = httpContextAccessor;
    _factory = factory;
}
  • Purpose: Capture all required dependencies via DI.
  • Usage: Registered as a Quartz job in Startup.

Methods

Method Return Type Description
Task Execute(IJobExecutionContext) Task Quartz entry point. Calls UpdateODS().
void UpdateODS() void Main routine to sync data & run scripts.
void OnInfoMessageGenerated(...) void (static) Handler for ADO.NET info messages & errors.

Execute

public Task Execute(IJobExecutionContext context)
{
    UpdateODS();
    return Task.CompletedTask;
}
  • Invoked by: Quartz scheduler.
  • Action: Triggers the full ODS sync routine.

UpdateODS

  1. Load Connection String
    var sqlConnectionString = _configuration["ConnectionStringsProduction"];
    
  2. Initialize EF Context & Manager
    var context = new IrbyContext(_configuration, _httpContextAccessor);
    var manager = new ODSManager(context);
    
  3. Perform Entity Updates
    manager.UpdateJob(sqlConnectionString);
    manager.UpdateEmployee(sqlConnectionString);
    manager.UpdateCustomer(sqlConnectionString);
    manager.UpdateAsset(sqlConnectionString);
    manager.UpdatePurchaseOrder(sqlConnectionString);
    manager.UpdateWorkOrder(sqlConnectionString);
    
  4. Execute Custom SQL Script
    var path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "Scripts", 
                            "ODS_D_EmployeeStatusUpdate.sql");
    string script = File.ReadAllText(path);
    using var conn = new SqlConnection(sqlConnectionString);
    var server = new Server(new ServerConnection(conn));
    server.ConnectionContext.ExecuteNonQuery(script);
    
  5. (Optional) Logging to File

    Commented out by default; can be enabled for detailed logging.

OnInfoMessageGenerated

  • Purpose: Build up the message log whenever SQL Server emits an InfoMessage or Error.
  • Registers with: conn.InfoMessage += OnInfoMessageGenerated; (commented out by default).

πŸš€ Usage & Integration

  1. Registering with DI & Quartz

    // In Startup.cs or wherever you configure services
    services.AddSingleton<ODSUpdate>();
    services.AddQuartz(q =>
    {
        q.UseMicrosoftDependencyInjectionJobFactory();
        var jobKey = new JobKey("ODSUpdateJob");
        q.AddJob<ODSUpdate>(opts => opts.WithIdentity(jobKey));
        q.AddTrigger(opts => opts
          .ForJob(jobKey)
          .WithIdentity("ODSUpdateTrigger")
          .WithSimpleSchedule(x => x
             .WithIntervalInHours(1)
             .RepeatForever()));
    });
    services.AddQuartzHostedService(q => q.WaitForJobsToComplete = true);
    
  2. Configuration

    // appsettings.json
    {
      "ConnectionStringsProduction": "Server=...;Database=IrbyImaging;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;"
    }
    
  3. Script Deployment

    • Place ODS_D_EmployeeStatusUpdate.sql under wwwroot/Scripts/.
    • Ensure folder & file are included in build/publish (set ).

πŸ› οΈ Error Handling & Logging

  • SMO Execution: Any script errors bubble up as exceptions.
  • InfoMessage Handler:
    • Captures warnings/info from SQL Server.
    • Aggregates into the static message field.
    • Can be written out to a .txt log if the commented code is enabled.

πŸ“ Sample Code Snippets

Triggering the Job Manually

// Obtain scheduler
var scheduler = await StdSchedulerFactory.GetDefaultScheduler();
await scheduler.Start();

// Trigger the ODSUpdate job immediately
await scheduler.TriggerJob(new JobKey("ODSUpdateJob"));

Reading the Log

string logContents = ODSUpdate.message;
Console.WriteLine(logContents);

πŸ“Š Mermaid UML Diagram

classDiagram
    class ODSUpdate {
        - IServiceProvider _provider
        - IServiceScopeFactory _factory
        - IConfiguration _configuration
        - IHttpContextAccessor _httpContextAccessor
        + static string message
        + ODSUpdate(provider, configuration, httpContextAccessor, factory)
        + Task Execute(IJobExecutionContext context)
        + void UpdateODS()
        - void OnInfoMessageGenerated(object, SqlInfoMessageEventArgs)
    }

    class ODSManager {
        + UpdateJob(string conn)
        + UpdateEmployee(string conn)
        + UpdateCustomer(string conn)
        + UpdateAsset(string conn)
        + UpdatePurchaseOrder(string conn)
        + UpdateWorkOrder(string conn)
    }

    ODSUpdate --> ODSManager : uses
    ODSUpdate --> IrbyContext : creates
    ODSUpdate --> SqlConnection : opens
    SqlConnection --> Server : used by

βœ… Conclusion

The ODSUpdate job is a central component for maintaining the Operational Data Store in the IrbyImaging platform. By orchestrating multiple entity‑based updates via ODSManager and executing custom SQL scripts, it ensures data consistency and freshness for reporting and analytics.

With Quartz.NET, EF Core, and SMO working in concert, this job demonstrates a robust pattern for scheduled, multi‑layered database synchronization tasks.

Feel free to extend or customize:

  • Add more scripts under wwwroot/Scripts.
  • Hook into OnInfoMessageGenerated for richer logging.
  • Adjust Quartz triggers for different schedules.

Happy coding! πŸš€