Bulk import & handlers

A table view can declare an <ImportConfig> to accept uploaded CSV/XLSX data. The toolbar then shows an Import button (beside Export) that opens a dialog: it lists the expected columns, offers a downloadable template (XLSX or CSV with the right headers), takes the upload, and shows a live progress checklist (Uploaded → Processing data → Saving records → Completed) driven over SignalR.

The two built-in importers

<!-- Per-row INSERT (QueryImporter): each file row binds @columns -->
<ImportConfig>
  <BeforeSql>/* optional setup, supports {GUID} for temp tables */</BeforeSql>
  <ImportSql>
    INSERT INTO POS.Customers (Number, FirstName, Email, CreatedById)
    VALUES (@Number, @FirstName, @Email, @SessionUserId);
  </ImportSql>
  <AfterSql>/* optional post-processing */</AfterSql>
</ImportConfig>

<!-- Bulk insert (BulkCopyImporter): SqlBulkCopy / PostgreSQL COPY -->
<ImportConfig>
  <BulkCopy TargetTable="POS.Suppliers" Columns="Number,Name,CreatedById,CreatedAt,CompanyId,IsDeleted">
    <Expression Column="CreatedById" Expression="@SessionUserId" />
    <Expression Column="CompanyId"   Expression="@SessionCompanyId" />
    <Expression Column="CreatedAt"   Expression="SYSDATETIME()" />
    <Expression Column="IsDeleted"   Expression="0" />
  </BulkCopy>
</ImportConfig>

BulkCopy <Expression> grammar

BulkCopy <Expression> values are a small server-side function grammar, evaluated per row before the copy and resolved to typed values (so they store into bit/bigint/datetime columns cleanly — a bare string can’t convert to a bit). The element is <Expression Column="…" Expression="…"/> (a child of <BulkCopy>; note it is <Expression>, not <Column>). Supported:

• @Session… parameters — any session parameter (@SessionUserId, @SessionCompanyId, @SessionCartId, plus host-registered ones). Usable standalone or as a function argument. Unknown → NULL.
• @Guid[N] — N uppercase-hex characters (a fresh GUID per row; N clamped 1–32). Handy as a uniqueness key inside Sequence(...).
• Functions (case-insensitive):
  • Sequence(format, objectName [, company [, key]]) — builds the sequence token the DB trigger expands, {objectName}|{format}|{company}|{key}. company defaults to @SessionCompanyId, key to @Guid[7]. format must contain # — #N zero-pads to N digits, and a bare # defaults to width 2 (SKU-#2 → SKU-01). If the file already provides a value for the column it is kept (a hand-entered SKU wins). E.g. Sequence('SKU-#2','Product','@SessionCompanyId','@Guid[7]').
  • Convert(type) — coerce the column’s value to int|long|decimal|double|bool|datetime; blank → NULL.
  • TRIM(), NULLIF_TRIM() / NULLIF_TRIM('sentinel') — trim the column’s own value (empty/sentinel → NULL).
  • SYSDATETIME()/GETDATE() (now), NEWID() (GUID string).
• Literals — anything else is a constant, parsed to its narrowest type (0/1 → integer, 1.5 → decimal, true/false → boolean, else string). Use a literal for audit/tenant defaults the uploader shouldn’t supply, e.g. Expression="0" for a bit IsDeleted.

Function argument resolution

Function args are resolved uniformly (unquote → @-token → literal), so @SessionCompanyId / @Guid[7] work inside any argument. New functions are added in ImportExpressionEvaluator (one registry entry). No part of a Sequence token may contain a literal |.

Template & required columns

Template columns are derived server-side (the import SQL is stripped from client metadata): for a QueryImporter, the ImportSql @parameters (framework-owned @Session*/@Parent__/@Form__ and any DECLARE @local variables are excluded — so you can pre-cook values in the batch without them showing up as columns); for a BulkCopy, Columns minus the <Expression> columns. Each column’s type/required hint comes from the matching EditorField.

Required vs optional columns. A file must contain every column that backs a required editor field; any other column is optional — if the file omits it, a QueryImporter binds the @parameter as NULL, and a BulkCopy pads the missing column to an empty (NULL) column before the copy. So optional or framework-generated columns may be left out. This is how a <Sequence> import works: leave the sequence column out of the file and pre-cook it (ISNULL(NULLIF(@Sku,''), @SkuFormat)), so it’s auto-assigned when absent but kept when the row provides one. The whole import (BeforeSql → rows → AfterSql) runs in one transaction.

Staging pattern (BeforeSql/AfterSql)

For a robust bulk load, bulk-copy into a per-import temp table in BeforeSql, then AfterSql casts/cleans and inserts (or MERGE-upserts) into the real table and drops the temp — all in one transaction. Use the {GUID} placeholder (replaced everywhere) to make the temp name unique, and TRIM()/NULLIF_TRIM() (or plain LTRIM(RTRIM()) in AfterSql) to clean text. Worked example: sample/Inventory/models/views/Products.view.xml (insert-only, with a commented MERGE seam).

Live progress

The dialog shows four phases over the ImportProgress hub: Uploaded (client upload %), Processing (Expressions + BeforeSql), Saving (BulkCopy / MERGE / ImportSql), Completed (after AfterSql). A failed step is reported with an error status.

Upload size

The import endpoint accepts up to 1 GiB by default; override with Genie:Uploads:MaxImportBytes (bytes) in appsettings — it caps both the request body and the multipart form length.

Custom import handlers (any file type — XML, ZIP, …)

<ImportConfig> only handles CSV/XLSX. To import any other file type (XML, ZIP, JSON, a proprietary format), a host registers a custom import handler — the import mirror of the custom export handler. The handler receives the raw uploaded file and does its own parsing, running entirely separately from (and instead of) the built-in <ImportConfig> path.

public sealed class SupplierXmlImportHandler(GenieContext ctx, IAuthenticatedUserService user,
    ISessionCompanyService company) : ICustomImportHandler
{
    public string TableName => "Suppliers";          // the Genie view this handler applies to
    public string HandlerName => "SuppliersXml";      // explicit name sent back as ImportHandler
    public string DisplayName => "Import XML / ZIP";   // label in the import dropdown
    public string AcceptedFileTypes => ".xml,.zip";    // drives the client file picker's accept filter
    public bool OverrideDefault => false;              // true ⇒ replaces the plain "Import" action

    public async Task<ObjectActionResult> ImportAsync(ImportTableRequest request, IFormFile file, CancellationToken ct)
    { /* parse `file` (any format) and persist; return a success message */ }
}

Register it in the host: services.AddGenieImportHandler<SupplierXmlImportHandler>(); (additive, like AddGenieExportHandler). Registered handlers are auto-discovered per view (matched by TableName) and surfaced in the view metadata, so the import toolbar offers them as a split-button: the plain Import runs the OverrideDefault handler (or the built-in CSV/XLSX importer when none), and each additional handler is a named dropdown item. The API resolves the handler from the request’s ImportHandler name (scoped to the view) — the client never names a C# type. AcceptedFileTypes (comma-separated accept tokens; empty ⇒ the standard .csv,.xlsx) sets which files the picker allows for that handler. Worked example: sample/Inventory/api/Import/SupplierXmlImportHandler.cs (with sample/Inventory/sample-data/suppliers.xml).

Declaring the handler + accept on the view

Beyond DI discovery by TableName, a view can wire its import declaratively on <ImportConfig> with two optional attributes:

<!-- Handler-based import: no <ImportSql>/<BulkCopy>; the named handler does everything -->
<ImportConfig Accept=".xml,.zip" Handler="WarehousesXml" />

Attribute	Effect
Accept	The file-picker accept for this view’s import (comma-separated, e.g. .xml,.zip). View-level; empty ⇒ .csv,.xlsx. Also valid on a built-in <ImportConfig> to restrict its picker.
Handler	The HandlerName of a registered ICustomImportHandler — the view’s default import (the plain Import runs it, no dropdown choice).

Handler and a built-in importer (<ImportSql>/<BulkCopy>) are mutually exclusive (a parse error if both). A Handler-only <ImportConfig> has no template. Handler resolution precedence at import time: the request’s explicit ImportHandler (a UI dropdown choice) → the view’s ImportConfig.Handler → a code-side OverrideDefault handler → the built-in importer. Accept precedence for the picker: ImportConfig.Accept → the selected handler’s AcceptedFileTypes → .csv,.xlsx. Worked example: sample/Inventory/models/views/Warehouses.view.xml + sample/Inventory/api/Import/WarehouseXmlImportHandler.cs.