ExecuteQuery

The ExecuteQuery activity enables you to create workflows that perform SQL queries into an ADO.NET data source to return a result set of data from a given data source.

If the query result is not empty, the workflow can be programmed to iterate over the result set and execute logic for each data record by using a ForEach<T> activity. For more information, see ExecuteQuery Example.

In general, the connection strings used during workflow execution are retrieved from the web.config of the product that triggers workflow execution. Only if you want to run a workflow with ExecuteDataReader, ExecuteNonQuery, or ExecuteQuery activity in test mode using the Run option in Workflow Composer, would you need to manually add the connection string to the Workflow Composer web.config file.

Properties

ExecuteQuery Properties

Property	Value	Required	Notes
CommandText	InArgument<String>	Yes	Enter a command that specifies the query to perform on the target data source and is expected to return a result set. Example: "Select * from Messages"
ConnectionString	InArgument<String>	Yes	Enter the name of a connection string that has been configured in the CONFIG file of the host application that is executing the workflows (see Connection Strings). If none is specified, this activity attempts to connect to a connection string named “DbConnection”. Connection String Example: "dbSampleData"
Data	OutArgument<Int32>	No	The output argument contains the data returned by the query. It may return one or more System.Data.DataTable objects depending on the results of the query execution.
DisplayName	String	No	Specify a name for the activity or accept the default.

ExecuteQuery Example

1. Open a workflow or create a new workflow.

2. Drag the ExecuteQuery activity into your workflow.

3. Specify the values for the input arguments or map them to workflow variables.

   

4. Create a workflow variable of data type System.Data.DataSet that will be mapped to the OutArgument of the query.

   

5. Map the OutArgument named Data to the new workflow variable.

   

6. Import the following namespaces into the workflow:

   • System.Data
   • System.Linq.Expression
   • System.Xml

   These namespaces are needed to allow the ForEach<T> activity to easily iterate over the results in each System.Data.DataTable object returned.

   To import the namespaces:

   1. Click the Imports pane in the Workflow Designer.
   2. Click on the right side of the "Enter or Select namespace" field.
   3. Type the name of the namespace you want to import.
   4. Select the namespace and press Enter.

   

7. Add a ForEach<T> activity to your workflow.

   Configure TypeArgument = System.Data.DataRow.

   You can assign the Values variable to each DataTable returned as shown below.

   

8. Configure the ForEach<T> activity to assign a name to each row as it iterates through the rows returned from the database.

   In the example shown here, each row is assigned the variable name of item. Access the values returned in each row by using the format: item(“ColumnName”)

   

9. Run the workflow.

   Result:

   • If the query successfully connects to the data source, the activity populates your local variable with the rows returned by the query.

   • The ForEach<T> activity iterates over each row stored in the local variable. It executes the activities within the body of the ForEach activity per each row in the DataTable.