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 the one 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.