Table of Contents

Class BaseSqlProvider

Namespace
Dynamicweb.DataIntegration.Integration
Assembly
Dynamicweb.DataIntegration.dll

Base class for data integration providers that read from or write to a Microsoft SQL Server database. It extends BaseProvider with SQL-specific services: schema extraction from INFORMATION_SCHEMA (GetSqlSourceSchema(SqlConnection, IEnumerable<string>?)), foreign-key-aware ordering of a job's table mappings (OrderTablesInJob(Job, bool) and the protected ordering helpers), tracking of the rows a job affected (TotalRowsAffected) and SQL-oriented error logging (LogRunJobError(Exception, Dictionary<string, object>?)). The built-in SqlProvider, DynamicwebProvider, EcomProvider, OrderProvider and UserProvider derive from this class.

[ResponseMapping(true)]
public abstract class BaseSqlProvider : BaseProvider, ISource, IDestination, IParameterizedDestination
Inheritance
BaseSqlProvider
Implements
Derived
Inherited Members
Extension Methods

Remarks

How to create a custom SQL provider: Subclass this class instead of BaseProvider when the data lives in SQL Server, and implement the usual provider contract: build the schema with GetSqlSourceSchema(SqlConnection, IEnumerable<string>?), return a BaseSqlReader-derived reader from GetReader(Mapping), and write through a BaseSqlWriter-derived writer in RunJob(Job). See the BaseProvider documentation for how providers are discovered, configured and executed.

Things to be careful of:

  • The class is decorated with [ResponseMapping(true)], so every derived provider is treated as supporting response mappings; a job only enables response mappings when both its source and destination provider types carry the attribute.
  • Maintain TotalRowsAffected in RunJob (reset it at the start, accumulate the writers' row counts). When a job runs with cache clearing disabled, the engine uses the counter to decide whether service caches and repository indexes still need to be refreshed after the run.
  • OrderTablesInJob(Job, bool) opens a connection to the Dynamicweb database. Providers that target another database must override it and call OrderTablesByConstraints(Job, SqlConnection, bool) with their own connection, as the built-in providers do.
  • The constraint-based ordering rebuilds job.Mappings: mappings whose table name cannot be resolved are removed, and tables with circular foreign key constraints are appended at the end once loop detection gives up.
  • The schema and ordering helpers embed the provided table names in the SQL text or use long-running commands (one hour timeout); pass trusted configuration values and reuse the results instead of calling them per row.
The skeleton of a custom SQL source provider:
using Microsoft.Data.SqlClient;
using Dynamicweb.DataIntegration;
using Dynamicweb.DataIntegration.Integration;
using Dynamicweb.DataIntegration.Integration.Interfaces;
using Dynamicweb.Extensibility.AddIns;
using Dynamicweb.Extensibility.Editors;

[AddInName("MySqlServerProvider"), AddInLabel("My SQL Server provider")]
public class MySqlServerProvider : BaseSqlProvider, ISource
{
    private Schema? _schema;
    private SqlConnection? _connection;

    [AddInParameter("ConnectionString"), AddInParameterEditor(typeof(TextParameterEditor), "")]
    public string ConnectionString { get; set; } = "";

    private SqlConnection Connection => _connection ??= new SqlConnection(GetConnectionString(ConnectionString));

    public override Schema GetOriginalSourceSchema() => GetSqlSourceSchema(Connection);

    public override Schema GetSchema() => _schema ??= GetOriginalSourceSchema();

    public override void OverwriteSourceSchemaToOriginal() => _schema = GetOriginalSourceSchema();

    public override ISourceReader GetReader(Mapping mapping) =>
        new Dynamicweb.DataIntegration.Providers.SqlProvider.SqlSourceReader(mapping, Connection);

    public override string ValidateSourceSettings() =>
        string.IsNullOrEmpty(ConnectionString) ? "Connection string is required." : "";

    public override void Close()
    {
        _connection?.Dispose();
        _connection = null;
    }
}

Properties

TotalRowsAffected

Gets or sets the total number of destination rows the current job run has affected. The provider's RunJob(Job) implementation is expected to reset it when the run starts and accumulate the writers' row counts; when a job runs with cache clearing disabled, the job engine only clears service caches and updates repository indexes after the run when this counter is positive.

public virtual long TotalRowsAffected { get; set; }

Property Value

long

Methods

GetConnectionString(string?)

Normalizes a SQL Server connection string for use by the provider: appends Encrypt=False when the connection string does not already specify an Encrypt setting, preserving the pre-encryption default behavior of the SQL client.

protected static string GetConnectionString(string? connectionString)

Parameters

connectionString string

The connection string to normalize.

Returns

string

The normalized connection string; an empty string when connectionString is null or empty.

GetSqlSourceSchema(SqlConnection, IEnumerable<string>?)

Builds a Schema from the database's INFORMATION_SCHEMA: every table (except the system tables sysdiagrams and dtproperties) with its columns, including type, length, identity and primary key information, plus the tables' foreign key references. Providers typically call this from GetOriginalSourceSchema(). Opens the connection when needed and closes it when done; the schema queries run with a one-hour command timeout.

public static Schema GetSqlSourceSchema(SqlConnection connection, IEnumerable<string>? tableNames = null)

Parameters

connection SqlConnection

The connection to the database to read the schema from.

tableNames IEnumerable<string>

The tables to include; when null or empty, all tables are read. The names are embedded in the schema query, so pass trusted configuration values only.

Returns

Schema

The schema describing the requested tables.

GetTableName(Mapping, bool)

Resolves the table name of a mapping's source or destination side, preferring the resolved Table object and falling back to the table name persisted on the mapping.

protected static string? GetTableName(Mapping map, bool isSource)

Parameters

map Mapping

The mapping to resolve the table name for.

isSource bool

true to resolve the source table name; otherwise the destination table name.

Returns

string

The table name, or null when neither side is set.

GetTableSchema(Mapping, bool)

Resolves the SQL schema name of a mapping's source or destination table, preferring the resolved Table object and falling back to the schema persisted on the mapping.

protected static string? GetTableSchema(Mapping map, bool isSource)

Parameters

map Mapping

The mapping to resolve the schema for.

isSource bool

true to resolve the source table schema; otherwise the destination table schema.

Returns

string

The schema name, or null when neither side is set.

LogRunJobError(Exception, Dictionary<string, object>?)

Logs a job execution failure to Logger with SQL-aware diagnostics: the message for "Subquery returned more than 1 value" errors is extended with a hint about duplicate key values, bulk copy failures have their individual failed rows logged, and the failed source row is included when provided. Call from the catch blocks of a RunJob(Job) implementation. No-op when ex is null.

protected void LogRunJobError(Exception ex, Dictionary<string, object>? sourceRow)

Parameters

ex Exception

The exception that failed the job.

sourceRow Dictionary<string, object>

The source row being processed when the failure occurred, or null when not applicable.

OrderItemsByJobMappings<T>(IList<T>, Job, Func<T, string?>, bool)

Reorders a list of items in place so they follow the order of the job's table mappings — used, for example, to line destination writers up with the mapping order after OrderTablesInJob(Job, bool) has run. One item is consumed per mapping occurrence of its table; items whose table is not mapped keep their relative order after the matched items, and items without a table name are placed last.

protected static void OrderItemsByJobMappings<T>(IList<T> entries, Job job, Func<T, string?> tableNameSelector, bool isSource = false)

Parameters

entries IList<T>

The items to reorder; cleared and refilled in the new order.

job Job

The job whose mapping order the items should follow.

tableNameSelector Func<T, string>

Returns the table name an item belongs to; items yielding null or an empty name are placed last.

isSource bool

true to match against source table names; otherwise destination table names.

Type Parameters

T

The type of the items to reorder.

OrderTablesByConstraints(Job, SqlConnection, bool)

Reorders the job's table mappings so foreign key parent tables come before their children, based on the referential constraints read from the database on connection. Delegates to OrderTablesByConstraints(Job, SqlConnection, bool, Func<string, string?, Table?>?) without metadata-based ordering.

protected static void OrderTablesByConstraints(Job job, SqlConnection connection, bool isSource = false)

Parameters

job Job

The job whose mappings are reordered in place.

connection SqlConnection

The connection to the database whose constraints define the order; opened when not already open.

isSource bool

true to order by source table names; otherwise by destination table names.

OrderTablesByConstraints(Job, SqlConnection, bool, Func<string, string?, Table?>?)

Reorders the job's table mappings so foreign key parent tables come before their children. When getTable is supplied, the ordering is first attempted from persisted schema metadata via TryOrderMappingsBySchemaDependencies(Job, bool, Func<string, string?, Table?>?); only when that is not possible are the referential constraints queried from the database. No-op when fewer than two distinct tables are mapped. Note that job.Mappings is rebuilt: mappings whose table name cannot be resolved are removed, and tables whose constraints cannot be satisfied (circular references) are appended at the end once loop detection gives up.

protected static void OrderTablesByConstraints(Job job, SqlConnection connection, bool isSource, Func<string, string?, Table?>? getTable)

Parameters

job Job

The job whose mappings are reordered in place.

connection SqlConnection

The connection to the database whose constraints define the order; opened when not already open.

isSource bool

true to order by source table names; otherwise by destination table names.

getTable Func<string, string, Table>

Resolves a table (by name and optional schema) from the provider's persisted schema metadata; pass null to always query the database.

OrderTablesInJob(Job, bool)

Reorders the job's table mappings so tables referenced by foreign keys are processed before the tables that reference them. Called by the administration UI when table mappings are saved. The base implementation opens a connection to the Dynamicweb database and delegates to OrderTablesByConstraints(Job, SqlConnection, bool); override it to use the provider's own connection, or to enforce a provider-specific order as the Ecom, Order and User providers do.

public virtual void OrderTablesInJob(Job job, bool isSource)

Parameters

job Job

The job whose mappings are reordered in place.

isSource bool

true to order by source table names; otherwise by destination table names.

TryOrderMappingsBySchemaDependencies(Job, bool, Func<string, string?, Table?>?)

Attempts to order the job's mappings topologically from persisted foreign key metadata, without querying the database. Ties are broken by the tables' first occurrence in the current mapping order, so the existing order is preserved where the dependencies allow it.

protected static bool TryOrderMappingsBySchemaDependencies(Job job, bool isSource, Func<string, string?, Table?>? getTable)

Parameters

job Job

The job whose mappings are reordered in place when the ordering succeeds.

isSource bool

true to order by source table names; otherwise by destination table names.

getTable Func<string, string, Table>

Resolves a table (by name and optional schema) from the provider's persisted schema metadata.

Returns

bool

true when the mappings were ordered (or the current order is already valid) using metadata alone; false when getTable is null, a mapped table lacks foreign key metadata, or a dependency cycle prevents a complete order — in which case the caller falls back to database-based ordering.

TryOrderTablesInJobUsingSchemaMetadata(Job, bool)

Attempts to order mapped tables by using provider-specific persisted schema metadata instead of querying the database.

public virtual bool TryOrderTablesInJobUsingSchemaMetadata(Job job, bool isSource)

Parameters

job Job

The job whose mappings should be ordered.

isSource bool

true to order source tables; otherwise destination tables.

Returns

bool

true when the provider could order the mappings from persisted metadata alone; otherwise false so the caller can fall back to database-based ordering.

Remarks

The base implementation intentionally returns false. Providers that persist foreign-key metadata, such as SQL-backed providers, override this method to enable in-memory ordering without an extra database lookup during job execution.

To top