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

• Postgres https://github.com/npgsql/npgsql/releases
• MySQL https://downloads.mysql.com/archives/get/p/6/file/mysql-connector-net-6.10.9.msi
• Oracle https://www.oracle.com/technetwork/topics/dotnet/index-085163.html
• Devart dotConnect https://www.devart.com/dotconnect/
• Cdata https://www.cdata.com/ado/

info

You need to use the older driver version for MySQL otherwise the driver does not register in Windows and will not show in the list in Data Sync.

We have specific connectors available to download and install for Oracle, MySQL, and Postgres as an alternative to ADO.Net.

Connection

To connect start by opening the connection window, expand the SQL Database folder, and then select the ADO.NET connector.

You then need to select the .NET driver to use and enter in the connection string and details for the datasource you are connecting to.

Once you are done click Connect & Create Library Connection to save the connection so that you can use other tables found within this database in future projects.

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

Property	Description
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);
}