OleDB Database

The OleDb Database provider connects to a database using OleDB.

The following Databases are typically what you might connect with Data Sync via OleDB.

• Oracle
• SQL Server
• Microsoft Access
• AS/400
• IBM DB2

SELECT, INSERT, UPDATE and DELETE SQL Statements are automatically calculated from the Data Schema. Only those columns in the Schema Map are affected the Key(s) columns define the WHERE condition for UPDATE and DELETE.

In Incremental Mode the IN clause is used to return matching records based on the Key selection in groups of 500 across multiple CPU threads. It is advised that the Key column is an Indexed SQL Column.

CommandTimeout

The SQL Command timeout in seconds.

ConnectionString

The SQL Connection String that connects to your database.

The connection string could look similar to: Provider=SQLNCLI11;Data Source=(localdb)\v11.0;Persist Security Info=False;Integrated Security=SSPI

SourceTable

The TABLE or VIEW that the Data source is connected to.

Command

A user defined SQL Query to use instead of SourceTable setting this property overrides SourceTable and makes the Data source read-only.

CommandOrderBy

Appends an ORDER BY clause to the Query.

CommandWhere

Appends an WHERE clause to the Query, only when using SourceTable if you have a user defined Query in Command this value is ignored.

StartQuoteChar

The start character to use when quoting identifiers.

EndQuoteChar

The end character to use when quoting identifiers.

ParameterMarker

The character to use in parametrised queries, such as INSERT, UPDATE and DELETE statements.

Project Automation

The SQL Server 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);
}