Cbam.Email.Utility 1.0.6

Cbam.Email.Utility

A reusable .NET library for sending import pipeline email notifications. It loads SMTP settings and recipients from SQL Server, renders HTML/text templates, sends mail via MailKit, and writes an audit log — all with minimal code in your application.

What this package does

You do not pass connection strings or SMTP details in code. Configure the database once, wire up DI in Program.cs, then call SendAsync with import details only.

How it works

Your import code
    │
    ▼
ImportNotificationService.SendAsync(request)
    │
    ├── Load template from EmailTemplates/ (disk)
    ├── Read recipients from SQL  (usp_Get_Email_Recipients)
    ├── Read SMTP config from SQL (usp_Get_Email_Config)
    ├── Insert audit log row      (Sys_Import_Notification_Log)
    ├── Send email via MailKit
    └── Update log status         (Sent / Failed / Skipped)

Prerequisites

• .NET 10.0 (or compatible target of the published package)
• Microsoft SQL Server
• A SQL connection string pointing to a database where you have run the setup script
• SMTP credentials stored in Sys_Email_Config

Step 1 — Install the package

dotnet add package Cbam.Email.Utility

Or add to your .csproj:

<PackageReference Include="Cbam.Email.Utility" Version="1.0.0" />

Step 2 — Set up the database (one-time)

The package uses a fixed SQL Server schema. Do not create custom tables — run the script shipped with the NuGet package:

content/database/Import_Notification_Email.sql

This creates:

Configure SMTP

UPDATE dbo.Sys_Email_Config
SET
    SmtpHost       = N'smtp.office365.com',
    SmtpPort       = 587,
    UseSsl         = 1,
    Username       = N'noreply@yourcompany.com',
    PasswordSecret = N'your-smtp-password',
    FromAddress    = N'noreply@yourcompany.com',
    FromDisplayName = N'Your App Name',
    SupportContact = N'support@yourcompany.com',
    IsEnabled      = 1
WHERE Environment = N'DEV';

Add recipients

-- All data types, success emails
INSERT INTO dbo.Sys_Email_Recipients (TemplateCode, DataType, EmailAddress, IsActive)
VALUES (N'IMPORT_SUCCESS', N'*', N'ops@yourcompany.com', 1);

-- Specific data type only
INSERT INTO dbo.Sys_Email_Recipients (TemplateCode, DataType, EmailAddress, IsActive)
VALUES (N'IMPORT_SUCCESS', N'Electricity', N'electricity-team@yourcompany.com', 1);

-- Error notifications
INSERT INTO dbo.Sys_Email_Recipients (TemplateCode, DataType, EmailAddress, IsActive)
VALUES (N'IMPORT_ERROR', N'*', N'ops@yourcompany.com', 1);

-- Warning notifications
INSERT INTO dbo.Sys_Email_Recipients (TemplateCode, DataType, EmailAddress, IsActive)
VALUES (N'IMPORT_WARNING', N'*', N'ops@yourcompany.com', 1);

Recipient matching rules:

• TemplateCode — IMPORT_SUCCESS, IMPORT_WARNING, or IMPORT_ERROR
• DataType — your pipeline identifier (e.g. Electricity, Heat, MaterialMovements), or * for all types
• Multiple rows are combined into one email (semicolon-separated)

Step 3 — Configure appsettings.json

Add only the default connection string.

{
  "ConnectionStrings": {
    "Default": "Server=your-server;Database=your-db;User Id=...;Password=...;Encrypt=True;TrustServerCertificate=True;"
  }
}

Note: The library resolves SQL connection only from ConnectionStrings:Default.

You can also pass a custom key or raw connection string from Program.cs (examples below).

Step 4 — Register in Program.cs

ASP.NET Core / Worker Service

using ExcelMerger.EmailNotifications.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddEmailNotifications(builder.Configuration);

var app = builder.Build();
app.Services.UseEmailNotifications();

app.Run();

Configure cache minutes directly in Program.cs:

builder.Services.AddEmailNotifications(builder.Configuration, options =>
{
    options.ConfigCacheMinutes = 10;
});

Use a custom connection-string key:

builder.Services.AddEmailNotifications(
    builder.Configuration,
    connectionStringName: "MainDb",
    options => { options.ConfigCacheMinutes = 10; });

Use a raw connection string (manually resolved):

var cs = builder.Configuration.GetConnectionString("MainDb")
    ?? throw new InvalidOperationException("MainDb connection string missing.");

builder.Services.AddEmailNotifications(cs, options =>
{
    options.ConfigCacheMinutes = 10;
});

Console application (with DI)

using ExcelMerger.EmailNotifications.Extensions;
using Microsoft.Extensions.DependencyInjection;

var configuration = /* load appsettings.json */;

var services = new ServiceCollection();
services.AddEmailNotifications(configuration);

var provider = services.BuildServiceProvider();
provider.UseEmailNotifications();

Console application (without full DI)

If your app does not use a service container, call this once at startup:

using ExcelMerger.EmailNotifications.Extensions;

configuration.EnsureEmailNotificationsConfigured();

This builds a minimal internal service provider and activates the notification facade.

Step 5 — Send notifications from your code

Option A — Static facade (simplest)

using ExcelMerger.EmailNotifications;

await ImportNotificationService.SendAsync(
    ImportNotificationRequest.ForSuccess(
        dataType: "Electricity",
        pipelineName: "DOLVI Electricity Import",
        sourceFileName: "report.xlsx",
        trackId: Guid.NewGuid(),
        importDate: DateOnly.FromDateTime(DateTime.UtcNow),
        stagingRowCount: 100,
        finalRowCount: 300));

No connection string is passed — it is read from configuration automatically.

Option B — Dependency injection (recommended for new code)

using ExcelMerger.EmailNotifications;

public sealed class MyImporter
{
    private readonly IImportNotificationService _notifications;

    public MyImporter(IImportNotificationService notifications)
    {
        _notifications = notifications;
    }

    public async Task ImportAsync(...)
    {
        // ... perform import ...

        await _notifications.SendAsync(
            ImportNotificationRequest.ForSuccess(
                dataType: "Electricity",
                pipelineName: "My Import Pipeline",
                sourceFileName: fileName,
                trackId: trackId,
                importDate: importDate,
                stagingRowCount: rowCount,
                finalRowCount: insertedCount));
    }
}

Notification types

Success — ImportNotificationRequest.ForSuccess(...)

Sent when an import completes without errors.

await ImportNotificationService.SendAsync(
    ImportNotificationRequest.ForSuccess(
        dataType: "Heat",
        pipelineName: "Heat Import Pipeline",
        sourceFileName: "heat_data.xlsx",
        trackId: trackId,
        importDate: DateOnly.FromDateTime(DateTime.UtcNow),
        stagingRowCount: 50,
        finalRowCount: 150));

Template: IMPORT_SUCCESS

Error — ImportNotificationRequest.ForError(...)

Sent when an import fails (e.g. transaction rolled back).

await ImportNotificationService.SendAsync(
    ImportNotificationRequest.ForError(
        dataType: "Heat",
        pipelineName: "Heat Import Pipeline",
        sourceFileName: "heat_data.xlsx",
        errorMessage: ex.Message,
        trackId: trackId,
        sourceRowNumber: ImportNotificationService.TryParseRowNumber(ex),
        stagingRowCount: rowsAttempted));

Template: IMPORT_ERROR

TryParseRowNumber extracts a row number from SQL exception messages when available.

Warning — custom request

Sent for partial failures or skipped files in a batch run.

await ImportNotificationService.SendAsync(
    new ImportNotificationRequest
    {
        Severity = ImportNotificationSeverity.Warning,
        DataType = "CBAMJSW",
        PipelineName = "CBAM Templates Import",
        WarningSummary = "2 failures, 1 skipped",
        WarningsTextList = "file1.xlsx: invalid column\nfile2.xlsx: timeout",
        SkippedFilesTextList = "file3.xlsx — unrecognized template",
        FilesSucceeded = 5,
        FilesTotal = 8,
        FinalRowCount = 1200
    });

Template: IMPORT_WARNING

Email templates

Three templates ship with the package under EmailTemplates/:

Templates are copied to your application output directory automatically. To customize, copy the EmailTemplates folder into your project and edit the files — placeholders use {{TokenName}} syntax:

Audit log

Every send attempt is recorded in Sys_Import_Notification_Log:

SELECT TOP 10
    NotificationId,
    Severity,
    DataType,
    SourceFileName,
    Subject,
    Recipients,
    SendStatus,
    ErrorMessage,
    CreatedAtUtc,
    SentAtUtc
FROM dbo.Sys_Import_Notification_Log
ORDER BY NotificationId DESC;

Email failures are caught internally and never propagate to your import code.

Complete integration checklist

Use this when adding the package to a new project:

[ ] 1. dotnet add package ExcelMerger.EmailNotifications
[ ] 2. Run content/database/Import_Notification_Email.sql on SQL Server
[ ] 3. UPDATE Sys_Email_Config with SMTP credentials and PasswordSecret
[ ] 4. INSERT rows into Sys_Email_Recipients for each template + data type
[ ] 5. Add ConnectionStrings entry to appsettings.json
[ ] 6. Add EmailNotifications section to appsettings.json
[ ] 7. Call services.AddEmailNotifications(configuration) in Program.cs
[ ] 8. Call provider.UseEmailNotifications() after BuildServiceProvider()
[ ] 9. Call ImportNotificationService.SendAsync() after import success/failure
[ ] 10. Verify a row appears in Sys_Import_Notification_Log with SendStatus = Sent

Troubleshooting

Architecture (for contributors)

Cbam.Email.Utility/
├── IImportNotificationService.cs       Public send interface
├── ImportNotificationService.cs        Static facade for legacy/static callers
├── ImportNotificationRequest.cs        Payload DTO
├── Extensions/
│   └── ServiceCollectionExtensions.cs  AddEmailNotifications(), UseEmailNotifications()
├── Options/
│   └── EmailNotificationsOptions.cs    appsettings binding
├── Abstractions/                       Internal interfaces (repos, SMTP, templates)
├── SqlServer/                          Fixed-schema SQL repositories
├── Services/                           Preparer, MailKit sender, orchestrator
├── EmailTemplates/                     File-based HTML/text templates
└── database/
    └── Import_Notification_Email.sql   Schema setup script (packaged)

Publishing the package

To build a .nupkg locally:

dotnet pack src/EmailNotifications/Cbam.Email.Utility.csproj -c Release

Output: Cbam.Email.Utility.1.0.0.nupkg

License

MIT