# Database plugin

Executes SQL queries against configured databases and stores results as JSON in SharePoint lists. Enables scheduled database synchronization and integration with external database systems.

DLL: Epona.Database.dll

# What it does

Query execution — runs SQL queries against configured databases
Result storage — saves query results as JSON in SharePoint lists
Scheduled execution — runs queries on configurable intervals
Credential management — encrypts and securely manages database passwords
Multiple sources — supports multiple database connections and queries per configuration
Version tracking — stores last-modified timestamps for incremental synchronization
Write-back — after a matter is provisioned, can write MS Graph identifiers (site, drive, Microsoft 365 group) and other matter values back into the source database

# Prerequisites

Network connectivity to target database server
Database credentials with execute permissions
SQL Server, Oracle, or other ADO.NET supported database
Target SharePoint list to store query results

# Configuration

# Database job settings

# Name

Type: string | Required: Yes

Unique name for this database job

# Disabled

Type: boolean | Required: No

Disable this job without removing configuration

# ConnectionString

Type: string | Required: Yes

Database connection string (host, port, database)

# Password

Type: string | Required: No

Database password (encrypted; extracted automatically from connection string)

# ConnectionStringProviderName

Type: string | Required: Yes

Database provider (e.g., System.Data.SqlClient, Oracle.ManagedDataAccess.Client)

# ConnectionStringProviderNameFactory

Type: string | Required: No

Override factory type name for custom database provider instantiation

# Query settings

# CommandText

Type: string | Required: Yes

SQL query to execute, or path to .sql file relative to provisioning service bin directory

# CommandTimeout

Type: int | Required: No

Query timeout in seconds (default: 30)

# Execution schedule

# IntervalInMinutes

Type: int | Default: — | Required: No

Execution interval in minutes

# StartTime

Type: TimeSpan | Default: — | Required: No

Optional: earliest time of day to run (e.g., 09:00:00)

# EndTime

Type: TimeSpan | Default: — | Required: No

Optional: latest time of day to run (e.g., 17:00:00)

# UseUTCDateTime

Type: boolean | Default: false | Required: No

Use UTC time (true) or local time (false)

# LastRunDateTime

Type: DateTime | Default: — | Required: No

Timestamp of last successful execution (read-only)

# Testing mode

# TestMapping

Type: boolean | Default: false | Required: No

Store result in test directory instead of production; does not update LastRunDateTime

# PostHandler settings

These settings configure the optional write-back behavior on the same job — see Write-back (PostHandler) below.

# PostHandlerEnabled

Type: boolean | Default: false | Required: No

Enables write-back for this job. PostHandlerCommandText and PostHandlerUniqueIdPropertyName are required when this is true.

# PostHandlerUniqueIdPropertyName

Type: string | Required: Conditional (when PostHandlerEnabled is true)

Name of the property (or MatterProperties key) on the provisioned matter that holds the id of the database record to update. Used as the @uniqueId parameter in PostHandlerCommandText. Depends on how the job's own CommandText maps the source column — see Unique id resolution.

# PostHandlerCommandText

Type: string | Required: Conditional (when PostHandlerEnabled is true)

SQL command executed after a matter is created or updated, to write values back into this job's database.

# Query parameters

# Last-run date parameter

Queries can reference @LastRunDateTime to fetch only new or changed records:

SELECT * FROM Contacts WHERE ModifiedDate > @LastRunDateTime

The parameter is automatically set to the LastRunDateTime of the previous successful execution.

# SQL file references

Instead of embedding SQL in the configuration, reference a file path:

CommandText: queries/customer-sync.sql

The provisioning service looks for the file relative to its bin directory. This also applies to PostHandlerCommandText (see Write-back (PostHandler)).

# Example configuration

Property	Example value
`Jobs[0].Name`	`DailyCustomerSync`
`Jobs[0].ConnectionString`	`Server=sqlserver.example.com;Database=SourceDB`
`Jobs[0].Password`	`p@ssw0rd`
`Jobs[0].ConnectionStringProviderName`	`System.Data.SqlClient`
`Jobs[0].CommandText`	`SELECT CustomerCode, CustomerName, ModifiedDate FROM Customers WHERE ModifiedDate > @LastRunDateTime`
`Jobs[0].CommandTimeout`	`60`
`Jobs[0].IntervalInMinutes`	`1440`
`Jobs[0].StartTime`	`08:00:00`
`Jobs[0].EndTime`	`18:00:00`

# Scheduling behavior

The Database plugin schedules execution based on interval and time-of-day constraints:

Interval: job runs every N minutes
StartTime: job only runs after this time of day (optional)
EndTime: job only runs before this time of day (optional)
Example: with interval=60, StartTime=09:00, EndTime=17:00, the job runs hourly during business hours

# Security

Database passwords are encrypted using the provisioning service's encryption key:

Password entered in configuration UI is automatically extracted from connection string
Password is encrypted with the service account key
Encrypted password is stored in configuration file
At runtime, password is decrypted before opening connection

# Multiple jobs per configuration

A single Database configuration can contain multiple jobs:

{
  "Jobs": [
    { "Name": "Job1", ... },
    { "Name": "Job2", ... },
    { "Name": "Job3", ... }
  ]
}

Each job runs on its own schedule independently.

# Write-back (PostHandler)

In addition to reading from a database, a job can write values back to it after a matter is created or updated in SharePoint. This reuses the same job's connection settings (ConnectionString, ConnectionStringProviderName, CommandTimeout) — set PostHandlerEnabled to true and provide PostHandlerUniqueIdPropertyName and PostHandlerCommandText (see PostHandler settings above).

# Available parameters

PostHandlerCommandText is a plain SQL statement (typically an UPDATE) that can reference these named parameters. Like CommandText, it can also reference a file path instead of embedding SQL directly — see SQL file references.

Parameter	Value
`@uniqueId`	The value read from `PostHandlerUniqueIdPropertyName` (see below)
`@msGraphSiteId`	The Microsoft Graph site id of the provisioned matter site
`@msGraphDriveId`	The Microsoft Graph drive id of the matter's document library
`@msGraphGroupId`	The id of the Microsoft 365 group backing the site, or `NULL` when the matter is not a Teams/Group-backed site
`@siteUrl`	The matter's SharePoint site URL
`@matterUrl`	The matter's SharePoint URL
`@matterCode`	The matter's code

# Unique id resolution

PostHandlerUniqueIdPropertyName must match wherever the job's own CommandText (the read query) placed the value, via the plugin's standard field mapping:

A column aliased to a known property (e.g. select AKTNR as MatterID from ...) → use the property name (MatterID).
A column aliased with the Matter. prefix (e.g. select AKTNR as [Matter.AKTNR] from ...) → use the name after the prefix (AKTNR).
A column with no alias, or one that doesn't match a known property → falls back to a __-prefixed key (e.g. __AKTNR).

# Example

{
  "Jobs": [
    {
      "Name": "JurXpertAkt",
      "ConnectionString": "Server=jurxpert-sql;Database=JurXpert;Trusted_Connection=True;",
      "CommandText": "select ID as MatterID, ... from AKT where LastModified > @LastRunDateTime",
      "IntervalInMinutes": 15,
      "PostHandlerEnabled": true,
      "PostHandlerUniqueIdPropertyName": "MatterID",
      "PostHandlerCommandText": "update AKT set MS_GRAPH_SITE_ID = @msGraphSiteId, MS_GRAPH_DRIVE_ID = @msGraphDriveId, MS_GRAPH_GROUP_ID = @msGraphGroupId, matterurl = @matterUrl, matterCode = @matterCode where ID = @uniqueId"
    }
  ]
}

A failed write-back is logged and does not affect the SharePoint provisioning result; it is attempted again the next time the matter is provisioned or updated (there is no dedicated retry queue).

# Troubleshooting

# Common issues

Issue	Cause	Resolution
Cannot connect	Invalid connection string or credentials	Verify connection string syntax, server name, and password
Query timeout	Query too slow for configured timeout	Increase CommandTimeout or optimize SQL query
@LastRunDateTime not working	Parameter name misspelled or case mismatch	Use exact parameter name `@LastRunDateTime` (case-sensitive)
File not found	SQL file path incorrect	Verify relative path from provisioning service bin directory
Password not persisting	Password contains semicolon	Enclose password in quotation marks: `Password="p@ss;word"`
Write-back not running	`PostHandlerEnabled` is `false`, or `PostHandlerCommandText`/`PostHandlerUniqueIdPropertyName` missing	Set `PostHandlerEnabled` to `true` and fill in both properties
`@uniqueId` not found	`PostHandlerUniqueIdPropertyName` doesn't match how the read query mapped the column	See Unique id resolution

Configuration reference — provisioning configuration reference
Plugins index — list of available plugins

← DataLEX plugin Datev plugin →

# Database plugin

# What it does

# Prerequisites

# Configuration

# Database job settings

# Name

# Disabled

# ConnectionString

# Password

# ConnectionStringProviderName

# ConnectionStringProviderNameFactory

# Query settings

# CommandText

# CommandTimeout

# Execution schedule

# IntervalInMinutes

# StartTime

# EndTime

# UseUTCDateTime

# LastRunDateTime

# Testing mode

# TestMapping

# PostHandler settings

# PostHandlerEnabled

# PostHandlerUniqueIdPropertyName

# PostHandlerCommandText

# Query parameters

# Last-run date parameter

# SQL file references

# Example configuration

# Scheduling behavior

# Security

# Multiple jobs per configuration

# Write-back (PostHandler)

# Available parameters

# Unique id resolution

# Example

# Troubleshooting

# Common issues

# Related