Configuration

In addition to the Mapping and SearchParameters configuration, which all DataProviders of the Data interface require, the SqlDataProvider can be configured via the Option element:

<SqlDataProvider DisplayName="Customer addresses">
  <Options>
    <ConnectionString>{ConnectionString}</ConnectionString>
    <ConnectionProvider>System.Data.SqlClient</ConnectionProvider>
    <Query>
        SELECT * FROM Users WHERE FirstName Like '%' + @firstName + '%'
      </Query>
  </Options>
    ...
</SqlDataProvider>

Via Options the connection and the database query is defined.

ConnectionString: ConnectionString, can be encrypted.
ConnectionProvider: Defines the type of the database - supported types:
- System.Data.Odbc
- System.Data.SqlClient
- MySql.Data.MySqlClient
- Oracle.ManagedDataAccess.Client
Query: Defines the SQL query, here the SearchParameters are treated as SQL parameters.

The SQL query must be in the format for the respective target database. Since all search parameters are treated as SQL parameters by the SearchParameters configuration, the respective SQL parameter syntax of the target database must be followed.

Examples

MS SQL

SELECT FirstName, LastName FROM Users
WHERE FirstName LIKE '%' + @firstName + '%' AND LastName LIKE '%' + @lastName + '%'

MySQL

SELECT FirstName, LastName FROM Users
WHERE FirstName LIKE Concat('%', @firstName, '%') AND LastName LIKE Concat('%', @lastName, '%')

CAUTION
For Oracle, Odbc and PostgreSQL the order of the placeholders in the query must match the order of the "SearchParameters!

Oracle

Odbc

PostgreSQL

CAUTION
The restriction of the order has the consequence that a parameter can be used in the query exactly once.

The following MS SQL query cannot be translated directly to Oracle or Odbc:

If a parameter needs to be compared on more than one column, then these columns can be combined in the SELECT using the respective function of the database system: