ExecuteDataReader

The ExecuteDataReader activity enables you to create workflows that perform two steps:

  1. Execute an SQL query.
  2. Execute activities in the query result.

If the query successfully connects to the data source, it queries the database and executes the activities in the body once per data row returned. For more information, see ExecuteDataReader Example 1.

Execute Data Reader

alert

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

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

Note: Supply an SQL query that will only return one set of rows from one table. Do not attempt to return multiple sets of data since this activity will only utilize the first set of data rows returned.

Example

"Select * from Messages"
ConnectionString InArgument<String> No 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"
DisplayName String No Specify a name for the activity or accept the default.

ExecuteDataReader Example 1

This example retrieves rows from the database and writes the results to the console.

  1. Open a workflow or create a new workflow.

  2. Drag the ExecuteDataReader activity into your workflow.

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

    execute data reader example

  4. Add activities into the body of this activity.

    write line

    Tip: The activities in the body of this activity will be executed once per every row returned from the database query.

    You can access the data in each row as a variable called CurrentRow.

    You can then use the data in each row using the format: CurrentRow(“ColumnName”).

  5. Run the workflow.

    Result:

    The query successfully connects to the data source, queries the database, and executes the activities in the body once per data row returned.

ExecuteDataReader Example 2

This example retrieves a value from a single row in the database and uses the retrieved value in an assignment statement.

  1. Open a workflow or create a new workflow.

  2. Create two variables to hold the query statement and the value retrieved from the database.

    • query
    • studentIdVar

    Variables

  3. Drag an Assign activity into a sequence.

    Assign the following value to a string named query:

    “select * from systudent where systudentid = “& studentIdVar

    Assign Query

  4. Drag the ExecuteDataReader activity into your sequence.

  5. In the Query field of the ExecuteDataReader activity, specify query (the name of the string assigned in the previous step).

  6. Drop an Assign activity into the body of the ExecuteDataReader activity.

    Assign the following value to a string named First:

    CurrentRow("FirstName").toString()

    Assign Current Row

    Note:

    The data type returned by the query must be specified in the assignment.

    • To get a string field value from a database row, the expression.ToString() is needed.

    • To get an integer value, the assignment would be like this: Convert.ToInt32(CurrentRow(“dbIntegerField”))

    Without the type conversion, the assignment statement fails with the following error:

    Return type error

    The following image shows the completed workflow section:

    ExceuteDataReader Example 2

To see how this activity can be used in a workflow, refer to