Query Service

The Web Configurator Service offers the ability for a connected client application to issue direct queries against the server. This feature is not enabled by default and must be enabled via the Settings Editor by setting the Query Authentication Mode setting to something other than Off.

The two supported authentication modes are AsConfigured and Sql. These modes identify the credentials used to connect to the back-end database. AsConfigured identifies that the connection will be made using the settings in the Experlogix web.config file. This is the preferred setting when connecting to the Experlogix configurator database as this account has very limited, read-only access in the database. The Sql value identifies that the caller will be providing SQL Server credentials to connect. The database server must be configured with Mixed Mode Authentication enabled for this type of authentication to succeed.

Caution must be exercised when enabling the Query Service as you’re effectively opening the database up for direct query access. A user can gain access to any database their credentials allow them to, so a path of least privilege is preferred to not expose information unwittingly. If you do not need to leverage the query services, the recommendation is to not enable them, particularly on public-facing endpoints.

ExecuteQueryParameters

The ExecuteQueryParameters class defines the type of object that is passed to the ExecuteQuery web service method, providing the server with the query to run. Currently, the only valid instance type is a ConfigDbExecuteQueryParameters, which instructs the server to connect to the Experlogix configurator database.

ConfigDbExecuteQueryParameters	Description
Sql	The SQL statement to execute. See additional details below.
CommandTimeout	The number of seconds the server should wait for a response to be received before the query is aborted. This value, if provided, overrides the setting from the web.config file
UserName	The user name used to authenticate to the database. This value is ignored if the Query Authentication Mode is AsConfigured, but required if set to Sql.
Password	The password used to authenticate to the database.
Parameters	A list of QueryParameter objects used for the query

The Sql property identifies the SQL statement to execute on the server. This string may contain tokens, similar to those used by Option Queries, where a value is unknown to the client, and can be substituted by the server. An example of this is when you need to query a List or a Lookup Table. These tables have names that can vary from environment to environment since they are dynamically created during the publish process.

Tokens are only valid within the context of a configurator session. If you include these tokens an Exception will be thrown by the service unless you have an active configurator session.

The following examples illustrate retrieving data from a List and from a Lookup Table:

SELECT Value FROM {LIST:[Colors]}
SELECT TankHeight, Thickness FROM {LOOKUP:[Glass]} WHERE Material = @material

The following example demonstrates the code needed to issue a query to the server:

using ( var client = getClient() ) {
 client.AuthenticateClient(getConnectInfo());
 try {
    string sql = "SELECT TankHeight, Thickness FROM {LOOKUP:[Glass]}"
            + " WHERE Material = @material"
    var queryParams = new ConfigDbExecuteQueryParameters();
    queryParams.Parameters.Add(new SqlQueryParameter("@material", "Glass"));
    
    var queryResult = ( ExecuteQueryResultJson )client.ExecuteQuery(queryParams);
    if ( queryResult.ResultCode == ConfigurationResultCode.Success ) {
        string json = queryResult.JsonResult;
        // TODO: process result
    }
 }
 finally {
    client.CancelConfiguration();
 }
}

Similar to how tokens may be embedded within the query directly for server-side substitution, there are a few

objects that can be passed as QueryParameters for server-side resolution. These are enumerated below:

ParamValuePublishSetId
ParamValueSeriesId
ParamValueModelId
ParamValueVariantId

Example usage:

queryParams.Parameters.Add(new ParamValueSeriesId("@seriesId"));

This removes the need for the client to know what the current values are that should be passed to the

server, leaving their implementation up to the server to fulfill.