ExecuteSqlNonQuery Function

NileshGhodekar edited this page Mar 18, 2018 · 7 revisions

Syntax

Dictionary<string, object> ExecuteSqlNonQuery(sqlConnectionStringConfigKey:string, sqlStatement:string [, parameter1:SqlParameter, parameter2:SqlParameter, ...])

Description

Executes a SQL stored procedure or insert, update, delete statements against a SQL Server database or an ODBC data source. Returns a dictionary of output parameter names and their values which can then be read by using ValueByKey function. The dictionary also contains an "@RowsAffected" item that stores the value of number of rows affected by the execution of non-query.

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

Activity Display Name Query Location Details
Updates
Value Expression Target Allow Null
CreateSqlParameter2("AuxDB", "@LocationID", [//Target/xLocationID]) $LocationID
CreateSqlParameter2("AuxDB", "@LocationOU", "Output", "NVarChar", 1000) $LocationOU
CreateSqlParameter2("AuxDB", "@HomeMDB", "Output", "NVarChar", 1000) $HomeMDB
CreateSqlParameter2("AuxDB", "@HomeServer", "Output", "NVarChar", 100) $HomeServer
ExecuteSqlNonQuery("AuxDB", "SP_GetLocationDetails", $LocationID, $LocationOU, $HomeMDB, $HomeServer) $LocationDetails
ValueByKey($LocationDetails, "@LocationOU") [//Target/xOU]
ValueByKey($LocationDetails, "@HomeMDB") [//Target/xHomeMDB]
ValueByKey($LocationDetails, "@HomeServer") [//Target/xHomeServer]
Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.