Class SqlProvider
- Namespace
- Dynamicweb.DataIntegration.Providers.SqlProvider
- Assembly
- Dynamicweb.DataIntegration.dll
[AddInName("Dynamicweb.DataIntegration.Providers.Provider")]
[AddInLabel("SQL Provider")]
[AddInDescription("SQL provider")]
[AddInIgnore(false)]
public class SqlProvider : BaseSqlProvider, IParameterizedDestination, ISource, IDestination, IDisposable
- Inheritance
-
SqlProvider
- Implements
- Inherited Members
- Extension Methods
Constructors
SqlProvider()
public SqlProvider()
SqlProvider(string)
public SqlProvider(string connectionString)
Parameters
connectionStringstring
SqlProvider(XmlNode)
public SqlProvider(XmlNode xmlNode)
Parameters
xmlNodeXmlNode
Properties
Catalog
protected string? Catalog { get; set; }
Property Value
Connection
protected SqlConnection Connection { get; set; }
Property Value
DestinationConnectionString
[AddInParameter("Sql destination connection string")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Destination")]
public string? DestinationConnectionString { get; set; }
Property Value
DestinationDatabase
[AddInParameter("Sql destination database")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Destination")]
public string? DestinationDatabase { get; set; }
Property Value
DestinationPassword
[AddInParameter("Sql destination server password")]
[AddInParameterEditor(typeof(TextParameterEditor), "password=true")]
[AddInParameterGroup("Destination")]
public string? DestinationPassword { get; set; }
Property Value
DestinationServer
[AddInParameter("Destination server")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Destination")]
public string? DestinationServer { get; set; }
Property Value
DestinationServerSSPI
[AddInParameter("Use integrated security to connect to destination server")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "")]
[AddInParameterGroup("Destination")]
public bool DestinationServerSSPI { get; set; }
Property Value
DestinationUsername
[AddInParameter("Sql destination server username")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Destination")]
public string? DestinationUsername { get; set; }
Property Value
DiscardDuplicates
[AddInParameter("Discard duplicates")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "Tooltip=When ON, duplicate rows are skipped")]
[AddInParameterGroup("Destination")]
public virtual bool DiscardDuplicates { get; set; }
Property Value
ManualConnectionString
protected string? ManualConnectionString { get; set; }
Property Value
Password
protected string? Password { get; set; }
Property Value
RemoveMissingAfterImport
[AddInParameter("Remove missing rows after import")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "Tooltip=Deletes rows from each destination table individually, based on whether they are present in the corresponding source table. This setting looks at each table separately and removes rows missing from the source for that specific table. This option takes precedence")]
[AddInParameterGroup("Destination")]
public bool RemoveMissingAfterImport { get; set; }
Property Value
RemoveMissingAfterImportDestinationTablesOnly
[AddInParameter("Remove missing rows after import in the destination tables only")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "Tooltip=Deletes rows not present in the import source - excluding related tabled")]
[AddInParameterGroup("Destination")]
[AddInParameterOrder(35)]
public bool RemoveMissingAfterImportDestinationTablesOnly { get; set; }
Property Value
RemoveMissingRows
[AddInParameter("Remove missing rows across all tables after import")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "Tooltip=Deletes rows from all destination tables and relation tables by considering the entire dataset in the import source. This setting evaluates all tables collectively and removes rows missing across the whole activity.")]
[AddInParameterGroup("Destination")]
public bool RemoveMissingRows { get; set; }
Property Value
Server
protected string? Server { get; set; }
Property Value
SkipFailingRows
[AddInParameter("Persist successful rows and skip failing rows")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "Tooltip=Checking this box allows the activity to do partial imports by skipping problematic records and keeping the succesful ones")]
[AddInParameterGroup("Destination")]
[AddInParameterOrder(100)]
public virtual bool SkipFailingRows { get; set; }
Property Value
SourceConnectionString
[AddInParameter("Sql source connection string")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Source")]
public string? SourceConnectionString { get; set; }
Property Value
SourceDatabase
[AddInParameter("Sql source database")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Source")]
public string? SourceDatabase { get; set; }
Property Value
SourcePassword
[AddInParameter("Sql source server password")]
[AddInParameterEditor(typeof(TextParameterEditor), "password=true")]
[AddInParameterGroup("Source")]
public string? SourcePassword { get; set; }
Property Value
SourceServer
[AddInParameter("Source server")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Source")]
public string? SourceServer { get; set; }
Property Value
SourceServerSSPI
[AddInParameter("Use integrated security to connect to source server")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "")]
[AddInParameterGroup("Source")]
public bool SourceServerSSPI { get; set; }
Property Value
SourceUsername
[AddInParameter("Sql source server username")]
[AddInParameterEditor(typeof(TextParameterEditor), "")]
[AddInParameterGroup("Source")]
public string? SourceUsername { get; set; }
Property Value
SqlConnectionString
protected string SqlConnectionString { get; set; }
Property Value
Transaction
public SqlTransaction Transaction { get; }
Property Value
Username
protected string? Username { get; set; }
Property Value
Methods
CheckMapping(Mapping)
Validate the provided mapping against this destination's schema and capabilities.
public List<SchemaComparerResult> CheckMapping(Mapping map)
Parameters
mapMappingMapping to validate.
Returns
- List<SchemaComparerResult>
Collection of SchemaComparerResult describing issues or an empty list if valid.
Close()
Close the provider and release resources (connections, file handles, etc.). Should be safe to call multiple times (idempotent).
public void Close()
CommitTransaction()
protected void CommitTransaction()
Dispose()
Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
public void Dispose()
Dispose(bool)
protected virtual void Dispose(bool disposing)
Parameters
disposingbool
GetOriginalDestinationSchema()
Gets the unmodified schema as read from the underlying destination, ignoring any user edits. Used by the job editor to reset or compare schemas. Must be overridden by destination providers; the base implementation throws NotImplementedException.
public override Schema GetOriginalDestinationSchema()
Returns
- Schema
The original destination schema.
GetOriginalSourceSchema()
Fetch the most recent schema directly from the source (unmodified, live).
This should return a fresh representation of the source structure and may perform expensive I/O. Use it when you explicitly need the authoritative source schema regardless of any cached/persisted schema.
public virtual Schema GetOriginalSourceSchema()
Returns
GetReader(Mapping)
Create and return an ISourceReader for the provided mapping.
The returned reader must be initialized and ready to enumerate rows for the mapping. Implementations are responsible for any resource allocation; callers will close the reader.
public ISourceReader GetReader(Mapping mapping)
Parameters
mappingMappingMapping that configures what/how to read.
Returns
- ISourceReader
An ISourceReader instance.
GetSchema()
Return the live Schema (tables + columns) for this source.
Implementer guidance:
- Return a thread-safe, non-mutating Schema instance (or a defensive copy).
- Prefer cached or lightweight responses to avoid repeated expensive I/O.
- This method should reflect the source structure at the time of the call.
Note: schema access is centralized via GetSchema(ISource?). That service consults the SchemaManagementFeature and may use a persisted schema instead of invoking this method. Call this directly only when you need the live schema.
public virtual Schema GetSchema()
Returns
LoadSettings(Job)
Called before the job runs to let the provider read settings from the job it participates in. Override to pick up job-level configuration; the default implementation does nothing.
public override void LoadSettings(Job job)
Parameters
jobJobThe job the provider is the source or destination of.
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 override 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.
OverwriteDestinationSchemaToOriginal()
Discards user edits to the destination schema and reverts to GetOriginalDestinationSchema(). Must be overridden by destination providers; the base implementation throws NotImplementedException.
public override void OverwriteDestinationSchemaToOriginal()
OverwriteSourceSchemaToOriginal()
Discards user edits to the source schema and reverts to GetOriginalSourceSchema(). Must be overridden by source providers; the base implementation throws NotImplementedException.
public override void OverwriteSourceSchemaToOriginal()
RollbackTransaction()
protected void RollbackTransaction()
RunJob(Job)
Executes the job with this provider as the destination. Called once by the job engine; the implementation
loops the active table mappings, reads the rows from job.Source via
GetReader(Mapping), processes each row with
ProcessInputRow(Dictionary<string, object>, Mapping) and writes it to the destination.
Must be overridden by destination providers; the base implementation throws
NotImplementedException.
public override bool RunJob(Job job)
Parameters
jobJobThe job to run.
Returns
SaveAsXml(XmlTextWriter)
Persist provider settings to the supplied System.Xml.XmlTextWriter.
Implementations should write their configuration elements but must not close or dispose the writer.
public virtual void SaveAsXml(XmlTextWriter textWriter)
Parameters
textWriterXmlTextWriterXml writer to write settings into.
Serialize()
Serializes the provider configuration to an XML fragment string, used when the provider settings are stored
as part of a job definition. Must round-trip with the Ctor(XmlNode) constructor. The base
implementation throws NotImplementedException.
public override string Serialize()
Returns
- string
The provider configuration as an XML string.
TryOrderTablesInJobUsingSchemaMetadata(Job, bool)
Attempts to order mapped tables by using provider-specific persisted schema metadata instead of querying the database.
public override 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.
UpdateDestinationSettings(IDestination)
Copies the settings of another instance of the same provider into this instance, used when the job editor saves updated destination settings onto an existing job. Must be overridden by destination providers; the base implementation throws NotImplementedException.
public override void UpdateDestinationSettings(IDestination destination)
Parameters
destinationIDestinationThe instance holding the new settings; cast to the concrete provider type.
UpdateSourceSettings(ISource)
Copies the settings of another instance of the same provider into this instance, used when the job editor saves updated source settings onto an existing job. Must be overridden by source providers; the base implementation throws NotImplementedException.
public override void UpdateSourceSettings(ISource source)
Parameters
sourceISourceThe instance holding the new settings; cast to the concrete provider type.
ValidateDestinationSettings()
Validates the destination settings. Used to validate properties when created by the AddinSelector.
public override string ValidateDestinationSettings()
Returns
ValidateSourceSettings()
Validates the source settings when the provider is configured as a source in the job editor. Override to check connectivity, file existence and similar; return an error description to show the user, or a non-error string when valid.
public override string ValidateSourceSettings()
Returns
- string
A validation message; the default implementation performs no validation.