ExecuteSqlScalar Function

Syntax

object ExecuteSqlScalar(sqlConnectionStringConfigKey:string, sqlStatement:string [, parameter1:DbParameter, parameter2:DbParameter, ...])

Description

Executes a SQL stored procedure or a SQL query against a SQL Server database or an ODBC data source. Returns the value of the first column of the first row in the result set.

Remarks

To use this function, first define a Connection String in the FIMService application config file (Microsoft.ResourceManagement.Service.exe.config) and restart the FIMService. Only Native SQL Server and ODBC .Net Framework data providers are supported. An example of Native SQL Server and ODBC connection strings is listed below:

<configuration>
   <connectionStrings>
    <add name="AuxDB" connectionString="Data Source=IDSYS.CONTOSO.COM;Initial Catalog=FIMAuxiliaryDB;Integrated Security=SSPI;Application Name=MIMWAL;Connection Timeout=15;" providerName="System.Data.SqlClient" />

    <add name="AuxDB_ODBC" connectionString="DRIVER={SQL Server Native Client 11.0};SERVER=IDSYS.CONTOSO.COM;DATABASE=FIMAuxiliaryDB;Trusted_Connection=Yes;APP=MIMWAL;Connection Timeout=15;" providerName="System.Data.Odbc" />
  </connectionStrings>
</configuration>

You can control the connection timeout by specifying it in your connection string. It is 15 sec by default for SQL Server. The command timeout has been coded to default to 30 seconds. Increasing the connection or command timeout values to a much larger value is not recommended.

If the connection strings contain SQL User's password, it is highly recommended that you do not leave them unencrypted. The connection strings section can be encrypted using the helper script EncryptConnectionString.ps1 included in the project source code.

Please be aware that your workflows may run in parallel, so it will be your responsibility to make sure that the SQL queries you execute don't cause DB locks leading to workflow failures or performance issues.

Use CreateSqlParameter or CreateSqlParameter2 function to create parameters for the ExecuteSqlScalar and ExecuteSqlNonQuery functions.

Supported Version

2.17.0414.0

Examples

ExecuteSqlScalar("AuxDB", "SP_GetAccountName", CreateSqlParameter("@FirstName", [//Target/FirstName]), CreateSqlParameter("@LastName", [//Target/LastName]), CreateSqlParameter("@UniquenessKey", [//Value]))