Reading Data

Overview

Your appRules projects may require reading data from files, data bases, SAAS applications and other sources. appConnector activity modules include activities for initializing Sources for reading data. For example, the appConnector SQL Server module features the InitializeMssqlSource activity. Once initialized, you can use the GetNextMssqlRecord activity to read the next record from the result set in the Source.

NB: The BaseData module features the common activities, for all the connectors available. (InitializeSouce, IntializeTarget, GetNextRecord, SaveRecord...). The dedicated connector module can have additional specific activities (Ex: ExecuteStoredProcedure in Microsoft SQL Server).

InitializeSource Activity

The appRules InitializeSource activity is used for initializing the Source. For example, to read data from Microsoft SQL Server, you will need to initialize the Source of the data, select the datasource and entity (table) to use and define filters for specifying the records to read. At design time, you can also get a count and view the records defined by the Source that you have defined.

To define the Source for reading SQL Server data source, drag the InitializeSource activity from the SQL Server module activities on to the designer and configure the properties as follows:

Property

Description

QueryType

Configuration (user defines fields to read, filters ...) or CustomQuery (user defines a SQL query to read the data)

QueryType

Configuration

DataSource

Select the SQL server Datasource from the List of defined datasources:

Next to the list, you can choose to define a new datasource, test the connection or browse the datasource

PrimaryEntity

Select the primary SQL Server table from the list.

SourceRecords

Click to access the following properties

AutoRecordCount

Whether to automatically count the number of records.

Datafields

Click to select the data fields to be returned from the PrimaryEntity. If no data fields are specified, all fields are returned.

FilterItems

You can optionally use this property to define the criteria for selecting the entity records to be included in the result set.

SelectDistinctRecords

True or False

SelectTopNRecords

option to specify a number of records to load (for testing purpose)

SortItems

You can optionally use this property to define how the records in the Source are to be sorted.

UseWithNoLock

RecordCount

Displays a count of the records that meet the criteria defined by the FilterItems property. If no filter is defined, the count of the total number of records in the table is displayed. This is a design time feature that gives you the number of records in the list.

Records

Displays a list of the records that meet the criteria defined by the FilterItems property. If no filter is defined, browsing starts at the first record in the list. This is a design time property for viewing table records before running your project.

ViewQuery

Displays the current query depending on the selected datafields, filters, sorts defined (see below)

AutoLoadFirstRecord

Set to True to automaticaly read the first record from the list of records that meet the filter criteria.

PreloadAllRecords

If Set to True, allows you to utilize preloaded records for tables with a small number of records. This option is especially useful if you will be performing a lot of lookups from the table.

QueryType

CustomQuery

Select only the datasource to use and In this case clicking on SourceRecords will display the CustomQuery editor

Type the sql query to load the data, you can mix data from several tables (using SQL statement with JOIN or WHERE CLAUSE).

Use the Tables and DataFields button to select the correct names from the datasource definitions.

ViewQuery, viewrecords and viewcount buttons to test your query

See samples section of the documentation for learning more about the use of customquery

GetNextRecord Activity

To read the next record from an open connection in appRules, you use the GetNextRecord activity from the appropriate activity module. For example, to read the next record from a SQL Server data source, drag and drop the GetNextRecord activity on to the designer. The only property required is the Source (An InitializeSource activity defined previously in the project).

Looking up Data

appRules includes activities for looking up data from data sources. Lookups provide a convenient means of reading a specific record from the data source. You can also use the Lookup activities to verify existence of a record in the data source.

LookupRecord Activity

The LookupRecord activity returns a specific record from a data source. It uses the same properties as the as the InitializeSource activity discussed in the previous chapter.

LookupPreloadedRecord Activity

The appConnector InitializeSource activities can be configured to load records into memory. If an activity module supports preloading of records, a record can be loaded from the list by using a LookupPreloadedRecord activity.