Database and Rules

◷ Reading Time: 9 minutes

This tutorial shows how to load filtered data from the database and apply some rules to the data.

1. How to connect to a Database
2. Filter data on the database side
3. Apply some rules to the data

In this tutorial, we are going to calculate a discount for each individual car based on the year of the car

Scenario

We want to apply discount business rules to some data that are in the database.

The idea is

1. a flow loads the data from the database by passing dateStart and dateEnd to the database query
2. Then iterates on the loaded data, and sets discount for each individual car based on the below business rules

If Year is between 2012 and 2015 then Discount is 10%
If Year is between 2015 and 2017 then Discount is 4%
If Year is 2017 then Discount is 1%
Otherwise no Discount

Main Flow

The idea is

1. A flow loads the relevant amount of data from the database by passing dateStart and dateEnd input parameters. So not all the data is loaded.
2. Using a Loop to iterate on the loaded data, and sets Discount for each individual car

New Generic Flow

Create a new project and add a Flow as below:

Use Toolbox and Drag and Drop tools from it to the surface of Flow to create the above diagram.

The above Flow below nodes:

• a Generic Flow
  • Start
  • Database
  • Loop
    • Decision Table
  • End

Add Flow Parameters

Add the below parameters to flow

1. cars (Out): List of the cars read from the database
2. car (Local): Individual car to set the discount
3. dateStart (In, Integer): Start year to filter data
4. dateEnd (In, Integer): End year to filter data

Database Access

You need to

1. Set connection string
2. Add a query to load data

Connection String

Connect string can be static inside the flow model or can be passed on by your application. In this example, we use the static method.

Select the Database node in the flow and the Properties Window will be updated.

Click on the button in front of the property value, and the entry window will pop up.

Add the connection string to the database, and click the OK button.

Note that we connect to the MsSql database here. You can choose other databases by changing the Database Type property.

Add Query

In the database’s Properties Window.

In the Properties Window of the Database node, select the Queries property and Click on the editor button.

The Query collection manager allows you to add multiple queries. In this case, click on the Add button to add our select query.

In the right panel, select Command property and click on the editor … button

Write your query in the SQL editor. In this example, we are writing a parameterized query that filters data based on the date on the database side. Then click OK to close the editor.

You are back on the query collection manager screen. Select the Parameters property and click the editor button.

Add two-parameter. Name is the parameter name of the query. And Value is the value of the parameter. Add two below parameters. With Name to start and Value of dateStart. And with Name set to end and Value of dateEnd.

And you finally have the query ready:

The property Copy Value To of query should be set to Flow parameters cars. This will store the result of the query in cars.

Rules

In this example, we use a Decision Table for the above rules. To model the above rules:

In the Flow, Right-click on the Decision Table node, and select Create & Link Document.

The new document selector appears, Rename your Decision Table to DiscountDT.XML and press OK

To open the new document (Decision Table), in your Project Explorer double click on the file named DiscountDT.xml

And you will have an empty Decision Table as below:

Our Decision Table is going to set a Discount on a Car

1. Define and Input Parameter called car for this decision table
2. And we will have
   1. One condition (year ranges)
   2. One action (discount assignment)

The final DT is like the below:

Expressions settings are:

1. Condition Year: car.Year
2. Action Discount: car.Discount

Finalizing Flow

The last step is to finalize the flow to iterate over the cars and pass them to the Decision Table. Now we are going to link the Loop node to flow parameters:

Select the loop and properties windows will be updated. We have to set up the Iterator properties.

In the loop properties, select List Source and press the editor button

Press Control+Space and the suggestion box pops up, select cars and press OK button

Do the same for Item Name and select car and you will have the below properties:

Run

In the Flow, press F5, and the JSON Composer screen appears. There you can assign dateStart and dateEnd as below:

• dateStart = 2014
• dateEnd = 2017

or use Logic Run Template to run/ debug the project.

When the Flow is run, you can use the data viewer to explore your data. Before going to the Decision Table you can view the below values:

And after running the Decision Table containing the running business rules against the loaded data, you see the below result:

As you see in the cars the Decision Table assigned Discount to each car.

Setup Your Database

In this example, we use MsSql, Create a new database called Car-Insurance, and use the below scripts to set up the table and data:

Setup Cars Table

Sample Project

Use the attachment at the end of the page to download the sample project.