DatabaseActions & ExpressionDataProvider

Date: 20. Jul 2016

With the realeases of MatchPoint 4.0.12 and 5.0.0 we introduced DatabaseActions and the ExpressionDataAdapter. This blog post describes the functionality of these two concepts.

DatabaseActions

DatabaseActions are arbitrary SQL statements which can be defined within ModelConfigurations and can be used in various places:

• Whenever expressions can be used (e.g. expression console, expression fields, as (Post)Actions in FormConfigurations or WorkflowConfigurations, as Commands (button / ribbon actions) in FormConfigurations, etc.)
• In the ExpressionDataProvider
• In the ModelActionDataProvider (the main advantages in contrast to the ExpressionDataProvider are, that it's possible to cache the result of an action and that it's easier to override / set parameters)
• In the ExpressionDataAdapter

If you want to use a return value your queries have to return a data set / data table, i.e. the end of your query has to be a SELECT statement.

When defining your database actions you can use two new types of expression variables: Parameters and ReturnValues.

Parameters

Parameters can be accessed using the Parameters variable. The values for these parameters are defined "outside" (in a seperate node) of the actual sql statement. You can define DefaultParameterValues which are used by default. When you later execute the database action you can set or override (if you've defined default parameters) your values. The parameters are defined "on the fly", i.e. there's no need for a parameter definition elsewhere.

An example for a database action with parameters is the following:

SELECT * FROM MyTable WHERE ID = {MPUtility.EscapeSql(Parameters.Id)}

Note that it's your own responsibility to properly escape parameters to prevent SQL injection and similar threats. MatchPoint offers some helper methods (as used above) for Transact SQL.

ReturnValues

In your query you can define ReturnValues. The return values are "pseudo" variables which will be replaced with the same name as you define them. They are only used to tell other MatchPoint components which fields / columns will be available in the result set. Let's illustrate this in an example:

SELECT {ReturnValues.Field1}, {ReturnValues.Field2} FROM MyTable

-- will look like this when evaluated:
SELECT Field1, Field2 FROM MyTable

The fields which are defined here, will be displayed as field propositions in other components, e.g. in a DataGrid:

Note that you can also declare your fields as comments (or as whatever you like), if you don't want to use them directly in the select:

SELECT Field1, Field2 FROM MyTable
--{ReturnValues.Field1}{ReturnValues.Field2}

Execution

If you want to execute your database actions via the ModelActionDataProvider it should be self-explanatory.

However executing them via Expressions may need a little of explanation: The database actions can be accessed via the expression variable Models.<ModelName>.DataBaseActions.<DbActionName>. If you start typing this statement in an expression, all available models and database actions will be proposed, and you can simply select them from there. If your action has no parameters you can directly execute it by calling execute at the end of the statement, if it has parameters you have different possibilities to specify these and execute the action afterwards.

// Direct execution
Models.MyModel.DatabaseActions.MySimpleAction.Execute()

// Execute by setting parameters via "proposed" names
Models.MyModel.DatabaseActions.MyParameterAction.Parameters.Id.Set(1).Name.Set("Foo").Execute()

// Execute by setting parameters via "custom" names
Models.MyModel.DatabaseActions.MyParameterAction.AddParameter("Id", 1).AddParameter("Name", "Foo").Execute()

// Execute by setting all parameters via a Dictionary
Models.MyModel.DatabaseActions.MyParameterAction.AddParameters(myDictionary).Execute()

For other ways to add parameters and to execute the database actions have a look at the auto completion in the expression console.

ExpressionDataAdapter

As all data adapters the ExpressionDataAdapter can be used within FormConfigurations.

The idea of this adapter is, that it can work with any expressions and therefore can be used to create, read, update and delete (CRUD) data of an arbitrary data source.

This means that the adapter will also work with the DatabaseActions. Here's an example of a fully configured adapter which works with database actions:

Note that I deleted the descriptions of the configuration, so the screenshot gets not too big.

All fields in the configuration are optional. This means that you can use the adapter respectively the form only for some of the CRUD operations if you choose so. For example you can configure the ReadExpression and the DeleteExpression if you only want to be able to view and delete items. Or you could only configure the CreateExpression if you only want to be able to create new items.

Some explanations to the different fields:

CreateExpression

This expression is used to create a new item. The 'FormValues' (which is a Dictionary<string, object>) can be used to access the values from the form. Noteworthy is maybe also that we can directly use this dictionary in the case of DatabaseActions since the overloads of AddParameters accept a dictionary as parameter.

ReadExpression

If the read expression returns null a new item will be created (an empty form is displayed). Note that the returned object must either have a string-indexer or fields / properties to get and update the relevant field values. If an IUpdatable is returned, all functionality of the updatable is used and the update expression is ignored.

UpdateExpression / DeleteExpression

In these expressions you get the same item instance passed in as returned by the specified 'ReadExpression'. This instance can be used by accessing the 'Updatable' variable.

ChangeTokenExpression

This expression can be used to determine whether or not the item has changed before an actual update happens (to prevent save conflicts). For example if a user starts editing a specific item and then leaves the form open for one hour, it's possible that another user already changed the same item in the meantime. When the user then finally submits his changes, the ChangeTokenExpression is used to determine if the item is still the same or was updated in between. To do that the value of the ChangeTokenExpression is saved when the user initially opened the form and compared with the new value (by executing the ReadExpression once more) when the form is submitted. If the two values are not equal (e.g. LastModified = 20.07.2016_14:00 and LastModified = 20.07.2016_14:02) the item will not be updated and an appropriate message will be displayed to the user.

That's it!

Thank you for reading, I hope I could give you a good overview over the two concepts. Feel free to ask questions in the comments below.