Datasources


General

The DataSources can be used to inject a database query into the document parameter. These queries are called when the document parameter dialog is opened, depending on the defined "Loadbehavior" (see Selector). The data from the query can then be mapped to DataNodes using the Selector mapping.

The basic structure for a DataSource binding looks like this:

<DataSources> <DataSource> <ConnectionProvider /> <ConnectionString /> <Selector LoadBehavior="Value"> <Query /> <Result> <!-- Pro Selektor kann entweder ein SingleMap oder ein CollectionMap definiert werden. --> <SingleMap> <Map Source="ColumnName1" Target="CustomDataNode1" /> <Map Source="ColumnName2" Target="CustomDataNode2" /> </SingleMap> <CollectionMap id="IdOfCollection"> </CollectionMap> </Result> </Selector> </DataSource> </DataSources>

DataSource

The DataSourceNode can have different types. The name is identical in XML with the DataSource types listed here

Attributes and elements for each type of DataSource

Name

Description

Name

Description

Id (Optional, Attribute)

Gives the DataSource a unique Id.

ConnectionProvider
(Mandatory, Element)

Defines the ConnectionProvider for the corresponding database type. This provider is used to connect to the database.
Overview of the ConnectionProviders of the .NET Framework

ConnectionString
(Mandatory, Element)

The ConnectionString provides the necessary information to establish the connection to the database. Each database defines its own format for the ConnectionString.

Selector
(Mandatory, Element)

Defines the database query and the corresponding mapping to the DataNodes.

Database types and their type-specific attributes

Type

Attributes

Type

Attributes

SqlDataSource

SafeQuery
If the value is set to "true" (default), then any parameters (see Selector → Query) in the query are not replaced with the current values. Instead, a parameter list with the key-value pair is sent to the database. It then replaces the values in the query. This results in increased security regarding SQL injection.

Selector

The selector defines the execution time, the database query to be executed and the corresponding mapping to the DataNodes. A DataSource can contain any number of selectors.

Name

Description

Name

Description

Id (Optional, Attribute)

Unique within a DataSource

LoadBehavior
(Mandatory, Attribute)

Defines on which type of call of the document parameter the query should be triggered. The possible LoadBehaviors are:
OnlyOnce: when the document parameter is first initiated
Always: whenever the document parameter dialog is invoked (also if it is opened from the generated document).

Query
(Mandatory, Element)

The query that will be executed on the database. Placeholders can be used with {}, and thus e.g. refer to the value of a DataNode → {DocParam.ValueToInject}. If placeholders are used, it must be ensured that the CustomElements that are addressed have a valid default value. Otherwise, the query may fail. This may cause the opening of the document parameter dialog to fail. If a < or a > is used in the query, it must be in a <![CDATA[InsertQueryHere]]> tag.

Result
(Mandatory, Element)

The Result element of the selector contains the information how the values from the database should be mapped to the CustomElements.
A distinction is made between two types: CollectionMap and SingleMap. The exact application is listed below this table due to the complexity.

Mapping

To explain the mapping, the following basic configuration is used:

<Configuration> <CustomContentSection xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Name="{[DocParam.Config.WindowName]}" WindowHeight="450" WindowWidth="{[DocParam.Config.WindowWidth]}"> <DataNodes> {[DocParam.EnableDocParamButton]} <CustomDataNode xsi:type="TextNode" Id="DocParam.Name" LCID="1042" /> <CustomDataNode xsi:type="TextNode" Id="DocParam.FirstName" LCID="1042" /> <CustomDataNode xsi:type="TextNode" Id="DocParam.Function" LCID="1042" /> <CustomDataNode xsi:type="TextNode" Id="DocParam.Salary" LCID="1042" /> </DataNodes> </CustomContentSection> <Views> <DataNodes> <Collection Id="MAList"></Collection> </DataNodes> <View Id="main" Label="{[DocParam.LabelMainView]}"> <!-- Here is the view configuration --> </View> </Views> <DataSources> <SqlDataSource> <ConnectionProvider>System.Data.SqlClient</ConnectionProvider> <ConnectionString>Data Source=SomeServer; DataBase=SomeDatabase; Integrated Security=true; </ConnectionString> <SafeQuery>true</SafeQuery> <Selector LoadBehavior="OnlyOnce"> <Query><![CDATA[SELECT Name, Vorname, Funktion, Lohn FROM tblMitarbeiter WHERE Name LIKE '%something%' AND Lohn > 20000]]> </Query> <Result> <!-- Here is the configuration of the mapping --> </Result> </Selector> </SqlDataSource> </DataSources> </Configuration>

SingleMap

With the SingleMap, individual columns from the database query can be mapped to a DataNode. The goal is now to fill the result from the database query into the configured DataNodes. For the SingleMap the configuration within the <Result></Result>{:.language-xml} looks like this:

<Result> <SingleMap> <Map Source="Name" Target="DocParam.Name" /> <Map Source="FirstName" Target="DocParam.FirstName" /> <Map Source="Function" Target="DocParam.Function" /> <Map Source="Salary" Target="DocParam.Salary" /> </SingleMap> </Result>

The Source attribute contains the name of the column from the database query from which the value is to be read. The Target attribute contains the name of the DataNode to which the value is to be written. A view element can then also be created for the corresponding DataNodes in order to display the value. If a SingleMap is mapped to a TextNode, then only the first entry of the database query is considered, all other entries are ignored. If, on the other hand, a ComboBox is mapped, all values from the column are inserted into the ComboBox.

CollectionMap

With the CollectionMap, a list returned from the database query can be written to a collection. The collection is filled with the corresponding elements. This collection can then be accessed via a ComboBox. For the CollectionMap, the configuration within the <Result></Result> looks like this:

<Result> <CollectionMap Id="EmpList" /> </Result>

The CollectionMap only needs the Id of the corresponding collection to which the values are to be written. This collection must be defined in the ViewDataNodes. The collection is then filled as follows:

<Collection Id="EmpList"> <Element> <Text Id="Name">Doe</Text> <Text Id="FirstName">John</Text> <Text Id="Function">Administration clerk</Text> <Text Id="Salary">5100</Text> </Element> <Element> . . </Element> . . </Collection>

The Id of the text elements within an element of the collection contain the name of the column from the database query, and accordingly the value which the evaluated entry contains in the corresponding column of the query. This collection can then be accessed via a ComboBox as follows:

<ComboBox Id="EmpList" CollectionLabelMember="FirstName" CollectionPlaceholder="Select"> <CollectionSelectionMap Source="Name" Target="DocParam.Name"></CollectionSelectionMap> <CollectionSelectionMap Source="FirstName" Target="DocParam.FirstName"></CollectionSelectionMap> <CollectionSelectionMap Source="Function" Target="DocParam.Function"></CollectionSelectionMap> <CollectionSelectionMap Source="Salary" Target="DocParam.Salary"></CollectionSelectionMap> </ComboBox>

The value that is displayed in the ComboBox is defined in the CollectionLabelMember, here it is for example the first name. If a value is selected in the ComboBox, the corresponding values from the Collection (Source) are written to the DataNodes (Target) defined in the mapping. Here again, it is important to make sure that the Source corresponds to the Id of the text in the Collection, i.e. the name of the column from the database query. The same applies to the target of the corresponding DataNode.

PrimeSoft AG, Bahnhofstrasse 4, 8360 Eschlikon, Switzerland