Connectors

ADO.NET

The ADO.NET connector uses the ADO.NET interface to connect to datasources that support the standard ADO.NET protocol.

The ADO.NET drivers will need to be installed on the machine and this usually requires the .NET assemblies to be registered in the Global Assembly Cache (GAC). The Installed providers are listed based on the result of the .NET static function DbProviderFactories.GetFactoryClasses().

The ADO.NET connector supports schema browsing via the ADO.NET Schema Tables, Connection Library, Lookups, Lookup Functions, Incremental Lookup, Incremental Data Load and INSERT/UPDATE/DELETE target actions. Some of these features are subject to the driver implementation.

ADO.NET Drivers

Connection

The connection requires the selection of the .NET driver and a connection string for the Data source.

Connect to ADO.NET

For more details on connecting to Oracle please see our Oracle Connection pages.

Example Connection Strings

MySQL or MariaDB

server=myserver;database=mydatabase;user id=myuserid;password=mypassword;port=3306

StartQuoteChar=` EndQuoteChar=` ParameterMarker=?

Postgres

host=myserver;database=mydatabase;username=myuserid;password=mypassword;port=5432

StartQuoteChar=" EndQuoteChar=" ParameterMarker=:{0}

Connection Properties

CommandTimeout The number of seconds a command will run before a Timeout exception is thrown.

ProviderName The name of the ADO.NET provider to use.

ConnectionString The connection string used to connect to the ADO.NET Data Source.

SourceTable The Datasource Table name.

StartQuoteChar A character used as the start character to escape identifiers.

EndQuoteChar A character used as the end character to escape identifiers.

ParameterMarker The format specifier for SQL Parameters. i.e. for MySQL its ? and for Postgres its :{0}

IncrementalMode For Incremental Mode whether to use an IN or OR clause in the query.

IncrementalBatchSize Total number of records to query in each SQL request for incremental mode.

Command SQL Command to run overrides SourceTable.

CommandWhere SQL WHERE command to append to Query.

CommandOrderBy SQL ORDER BY command to append to Query.

Transaction The number of SQL commands to Execute in a Transaction. 0=No Transaction.

CommandBatchSize Total number of SQL commands to send in each request to the SQL database.

Project Automation

The ADO.NET provider also exposes helper functions that can be called from Project Automation to update the Data source ExecuteScalar, ExecuteNonQuery and UpdateSourceRow.

ExecuteScalar

Method ExecuteScalar creates a SQL Command from the supplied SQL and Parameters values and calls ExecuteScalar against the database.

int ExecuteScalar(string sql, params object[] parameters)

ExecuteNonQuery

Method ExecuteNonQuery creates a SQL Command from the supplied SQL and Parameters values and calls ExecuteNonQuery against the database.

int ExecuteNonQuery(string sql, params object[] parameters)

UpdateSourceRow

Method UpdateSourceRow creates a SQL Command to update a single column in the Data source using the key from the DataCompareItem

bool UpdateSourceRow(string column, object value, DataCompareItemInvariant item)

Used to update the source row from project automation, for example setting a Sync flag once a record has been synchronised.

For example calling this method in the AfterUpdateItem item event to mark a record in the source as synchronised.

public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
{
    DataSourceA.UpdateSourceRow("Sync", true, item);
}