ExecuteSqlNonQuery Function

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;" 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;" providerName="System.Data.Odbc" />
  </connectionStrings>
</configuration>

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

Supported Version

2.17.xxxx.x (to be supported in a future release)

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]