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.
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
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
connectionStringstringThe connection string to normalize.
Returns
- string
The normalized connection string; an empty string when
connectionStringisnullor 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
connectionSqlConnectionThe connection to the database to read the schema from.
tableNamesIEnumerable<string>The tables to include; when
nullor 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
mapMappingThe mapping to resolve the table name for.
isSourcebooltrueto resolve the source table name; otherwise the destination table name.
Returns
- string
The table name, or
nullwhen 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
mapMappingThe mapping to resolve the schema for.
isSourcebooltrueto resolve the source table schema; otherwise the destination table schema.
Returns
- string
The schema name, or
nullwhen 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
exExceptionThe exception that failed the job.
sourceRowDictionary<string, object>The source row being processed when the failure occurred, or
nullwhen 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
entriesIList<T>The items to reorder; cleared and refilled in the new order.
jobJobThe job whose mapping order the items should follow.
tableNameSelectorFunc<T, string>Returns the table name an item belongs to; items yielding
nullor an empty name are placed last.isSourcebooltrueto match against source table names; otherwise destination table names.
Type Parameters
TThe 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
jobJobThe job whose mappings are reordered in place.
connectionSqlConnectionThe connection to the database whose constraints define the order; opened when not already open.
isSourcebooltrueto 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
jobJobThe job whose mappings are reordered in place.
connectionSqlConnectionThe connection to the database whose constraints define the order; opened when not already open.
isSourcebooltrueto order by source table names; otherwise by destination table names.getTableFunc<string, string, Table>Resolves a table (by name and optional schema) from the provider's persisted schema metadata; pass
nullto 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
jobJobThe job whose mappings are reordered in place.
isSourcebooltrueto 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
jobJobThe job whose mappings are reordered in place when the ordering succeeds.
isSourcebooltrueto order by source table names; otherwise by destination table names.getTableFunc<string, string, Table>Resolves a table (by name and optional schema) from the provider's persisted schema metadata.
Returns
- bool
truewhen the mappings were ordered (or the current order is already valid) using metadata alone;falsewhengetTableisnull, 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
jobJobThe job whose mappings should be ordered.
isSourcebooltrueto order source tables; otherwise destination tables.
Returns
- bool
truewhen the provider could order the mappings from persisted metadata alone; otherwisefalseso 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.