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

SELECT FirstName, LastName from Users
WHERE UPPER(NVL(FirstName, ' ')) Like UPPER('%' || :firstName || '%') AND UPPER(NVL(LastName, ' ')) Like UPPER('%' || :lastName || '%')

Odbc

SELECT FirstName, LastName FROM Users
WHERE FirstName LIKE '%' + ? + '%' AND LastName LIKE '%' + ? + '%'

PostgreSQL

SELECT FirstName, LastName FROM Users
WHERE UPPER(FirstName) LIKE UPPER('%' || ? || '%') AND UPPER(LastName) LIKE UPPER('%' || ? || '%')

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:

SELECT FirstName, MiddleName, LastName FROM Users
WHERE FirstName LIKE '%' + firstName + '%' OR MiddleName LIKE '%' + firstName + '%'

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:

Odbc

SELECT STRING(FirstName, ' ', MiddleName) AS CompleteName, LastName FROM Users
WHERE CompleteName LIKE '%' + ? + '%'

Example

<SqlDataProvider DisplayName="Database">
  <SearchParameters>
    <Text Id="Name" Label="Name" />
  </SearchParameters>
  <Options>
    <ConnectionString>Data Source=localhost;Initial Catalog=Customers;User Id={user};Password={pwd};</ConnectionString>
    <ConnectionProvider>System.Data.SqlClient</ConnectionProvider>
    <Query>
      SELECT * FROM Customers WHERE Name Like '%' + @Name + '%'
    </Query>  
  </Option>
  <Mapping>
    <Map Source="Name" Target="CompanyName" />
    <Map Source="Street" Target="Street" />
    <Map Source="Zip" Target="PostalCode" />
    <Map Source="City" Target="City" />
    <Map SourceValue="CH" Target="Country" />
  </Mapping>
</SqlDataProvider>

primedocs: technical documentation

SqlDataProvider

Configuration

Examples

MS SQL

MySQL

Oracle

Odbc

PostgreSQL

Odbc

Example

Related content