Database

◷ Reading Time: 3 minutes

Connection to Database

Any database can be used as part of your business logic in FlexRule (i.e., MsSql, Oracle, MsAccess, ODBC, or even custom databases), by providing your custom library (e.g., PostgreSQL (Npgsql.dll)). Then you can use any database commands that are compatible with your database and send them to the database for execution and eventually return the result back to your logic.

There are multiple ways of reading and/or manipulating data:

1. Using database task in Flow: Database Tasks
2. Using procedural logic: Database Commands
3. Expose logic as a function

Flow Sample

Using this approach, you orchestrate your data before processing it, then load it into memory and use it as needed.

• Connection String: The connection string to the database
• Dynamic Connection String: Indicates if the connection string requires evaluation at runtime.
• Database Type: different types of databases with which to connect
• Custom Database Type: Full type name (namespace.typename) of the connection object of custom database
• Custom Database Assembly: Assembly file of the custom database
• Queries: Set of queries
  • Select List: Any query that selects rows from a table (i.e., SELECT)
  • Execute Query: Any query that manipulates data or the structure of tables (i.e., INSERT, DELETE, UPDATE, etc.)
  • Get Value: When a query returns a single value

Managing Queries

Open the Queries editor and you can see the window below:

Use Parameters to create a parameterized query and map between the query’s parameter’s value and your logic value.

XML Declaration

When the model is built, a Database node will be embedded into an Activity.

<Activity name="Activity5">
   <Database connection="Data Source=.\SqlExpress;Initial Catalog=Car-Insurance;User ID=sa;Password=123;MultipleActiveResultSets=True" type="MsSql">
           <SelectList command="select * from Thefts" return="thefts" multi="True" expando="True" />
   </Database>
   <Transition name="Transition12" to="Activity6" />
</Activity>

Procedural Sample

In this approach, you create a procedural logic that loads the data into memory. You can either use this database logic directly or expose it as a function for other parts of the model.

<Database connection-ref="cnn" type="MsAccess">
   <SelectValue command="select FName from person where id = @id" return="name">
      <Param name="id" value="itemId"/>
   </SelectValue>
</Database>

Expose Logic as Function

This allows you to create a database logic in a Procedural form and then use it in other models as a function.

Samples

1. Flow Logic: When FlexRule Designer is installed, the sample Car Insurance using DMN has a flow model named Car Flow.xml
2. Procedural Logic: When you install FlexRule Runtime in the Samples folder, there is a project called FlexRule.Samples.Database.MsAccess

Tutorial

Applying rules on database records