◷ 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:
- Using database task in Flow: Database Tasks
- Using procedural logic: Database Commands
- Expose logic as a function
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
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.
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>
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.
- Flow Logic: When FlexRule Designer is installed, the sample Car Insurance using DMN has a flow model named Car Flow.xml
- Procedural Logic: When you install FlexRule Runtime in the Samples folder, there is a project called FlexRule.Samples.Database.MsAccess