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.

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"
CommandTimeout	InArgument<Int32>	No	You can adjust the CommandTimeout value if the activity needs to execute long-running SQL statements.   The default and minimum command timeout is 30 seconds. The maximum is 1800 (30 minutes).
ConnectionStringName	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. Note: Forms Builder 3.6 introduces the "CrmConnection" string in the web.config of Forms Renderer (see Renderer Connection Strings). If you have created workflows with ExecuteQuery activities, ensure that connection strings in the activities match the updated web.config of Forms Renderer.
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

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.

ExecuteQuery Example 2

The following example uses the ExecuteQuery activity in the context of a Forms Builder sequence. In the first form, the ExecuteQuery activity queries the database for a student's registration bill details by term and displays the data in a grid where each row represents a term.

1. The LookupUser activity captures the UserName from the formInstance.UserName argument and returns the SyStudentID value.

2. The GetEntity activity takes the SyStudentID and returns the studentEntity.

3. The Assign activity assigns the value studentEntity.Ssn.Remove(1,7) to studentEntity.Ssn. This formats the SSN to display only the last 4 digits. It starts at 1 and removes 7 digits. This includes the dashes (111-11-1111). So that leaves the last 4 of the SSN.

   

4. The ExecuteQuery activity queries the Anthology Student database for the student's registration billl details using the following SQL statement:

   STRING.Format("Select Distinct AdTerm.Descrip AS termSelect, SaTrans.AdTermId AS termSelectID from SaTrans join AdTerm on SaTrans.AdTermId = AdTerm.AdTermId WHERE SyStudentID = {0}",studententity.Id)

   The ExecuteQuery activity stores the retrieved data in a variable named "RegistrationBill". The variable type is a DataSet.

   

5. Next, a ForEach activity parses the data output from the ExecuteQuery using the value RegistrationBill.Tables(0).AsEnumerable with the TypeArgument System.Data.DataRow.

   We are basically using the returned RegistrationBill DataSet, and in the ForEach activity we are looping through each row and then doing something with the data (e.g., assigning values).

6. The Body section of the ForEach activity includes three Assign activities that assign the following values to variables:

   Variable	Value
   varList	New NameIdObject
   varListId	item("termSelect").ToString
   varListName	CINT(item("termSelectId"))

   

7. The AddToCollection activity associates the varList variable with the NamedIdObject.

8. The Assign activity below the AddToCollection activity assigns the value varTermSelect.toArray to the myTerms argument. The value of this argument will be passed back to the form sequence and displayed in a grid row on the form.

   

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 1.

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.

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 1.

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.