Best practice: Fetching process data values in code-behind

[eddy.daouk]

This article is intended for educational purposes only. It explains the best strategy to retrieve data values in your forms. It also explains the drawbacks of other strategies.

Use case

Your workflow runs an SQL query to fetch a value and stores it in a process data. You would like to use that data value in your .NET code behind.

In the following example, the SQL_FETCH system action stores a value in a process data called DATA, and you want to reference that value in the INITIATES action in code-behind.

Best practice

1. Create a hidden section and add a Textbox to it.

   

2. Open the Mappings window.

3. Select the action where you would like to add the .NET code-behind.

4. Add the data called DATA as a VALUE IN to the hidden field.

5. You can then easily refer to the hidden field in code behind (e.g. HIDDEN_DATA.Text). You can also easily implement form validations since the value is already stored in the form and you can use the built-in validation features.

Other strategies to fetch process data from code-behind

Note: These are NOT RECOMMENDED. Only use them when the best practice solution is not possible.

Using a direct SQL connection

The following code fetches a process data called DATA and stores it in a field called REQUEST_SUBJECT.

    protected void Page_Load(object sender, EventArgs e)
    {
        base.Page_Load(sender, e);
        //Fetch value of a process data called DATA and store in the the REQUEST_SUBJECT fields
        REQUEST_SUBJECT.Text = fetchValue("DATA");
    }
    
    private string fetchValue(string data_name){
          string value = String.Empty;
          string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainDbSource"].ToString();
          string queryString = @"SELECT VALUE_TEXT, VALUE_NUMERIC, VALUE_DATETIME FROM WFDATASET_VALUE 
                                WHERE ID_DATASET IN 
                                (SELECT ID_DATASET FROM WFPROCESS_INST_RELDATA WHERE ID_PROCESS_INST = @PROCESS_INST_ID
                                 AND ID_RELDATA IN (SELECT ID_RELDATA FROM WFRELDATA WHERE NAME = @DATA_NAME))";
        
          
          using(System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString))
          {
              System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, sqlConnection);
              command.Parameters.AddWithValue("@PROCESS_INST_ID", Int32.Parse(CURRENT_REQUEST.Text));
              command.Parameters.AddWithValue("@DATA_NAME", data_name);
              sqlConnection.Open();
                using (System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()){
                        if(reader["VALUE_TEXT"]!= null) value = reader["VALUE_TEXT"].ToString();
                        else if(reader["VALUE_NUMERIC"]!= null) value = reader["VALUE_NUMERIC"].ToString();
                        else if(reader["VALUE_DATETIME"]!= null) value = reader["VALUE_DATETIME"].ToString();
                        break;
                    }
                }
          }
          
          return value;
    }

This is not recommended because:

1. You’ll need to maintain more code in code-behind.

2. If you want to implement validation logic, you’ll need to send that data to a hidden field regardless. Or, you can override the validation logic with your own custom validation logic, which is not simple.

3. Performance might be slow if you need to establish multiple database connections to fetch multiple values.

Using the hidden fields and macros is more performant and requires less maintenance.

Using the API

You can also use the GraphQL API to fetch data values in code behind but it’s still not a good solution for the same reasons mentioned in the strategy above.

Using the FORM_DATA XML file

You can retrieve a value from the FORM_DATA file using the following code:

this.FormData.Tables[Table1].Rows[0]["FIELD_ID"].ToString();

This code goes to the Table1 node and retrieves the value of child node FIELD_ID.

Before you use it, it’s important to understand when you can use it:

The FORM_DATA XML file is intended to be used by the form only. It helps keep track of form field values without the need to export every single field and map it back in each action.

This file only gets generated after a form is submitted. It reads all the field IDs and store their values in the XML file.

If you run a system action and open the first action, this.FormData will be null because the FORM_DATA file is not generated yet.

We don’t recommend using that approach because:

1. It doesn’t fetch the value from the database but from a file which is not necessarily up to date.

2. The code won’t work on the first EFORMASPX action because the file is not generated yet.

3. It’s not reliable since you might change the values in the form and this.FormData will not be updated until you submit the form, so you might retrieve incorrect values.