SqlDataProvider

---

note

SqlDataProvider will be available in the next primedocs version.

Via the SqlData provider arbitrarily configurable SQL databases can be accessed.

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 ADO.NET provider of the target database. The value is passed straight to the ADO.NET provider factory. The supported providers and their requirements are listed below.
• Query: Defines the SQL query, here the SearchParameters are treated as SQL parameters.

On the Desktop Client (.NET Framework 4.8), the supported providers have different requirements:

Provider	Target database	Requirement on the client
System.Data.Odbc	Generic ODBC access	Included in the .NET Framework — works without any additional installation. For the actual connection, a matching ODBC driver/DSN for the target database must be present on the machine (operating-system level).
System.Data.SqlClient	Microsoft SQL Server	Included in the .NET Framework — works without any additional installation.
MySqlConnector	MySQL / MariaDB	Shipped with the client — no additional installation required. The older name MySql.Data.MySqlClient is still accepted as an alias (points to the same library) so that existing configurations keep working unchanged.
Oracle.ManagedDataAccess.Client	Oracle	Not shipped with the client. It requires the Oracle Data Provider for .NET (ODP.NET, managed) to be installed on the client; its installer registers the provider on the machine. Without this installation, the provider is not available.

If the configured provider is not available, the client logs a meaningful message (naming the missing provider); the affected data source is then unavailable.

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, '%')

note

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('%' || ? || '%')

note

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>