How To: Examples of Data Providers with Conditions

(16/02/2009-17:42)

Learn how to create Data Providers with output with conditions.

In GeneXus X we've incorporated a new type of object called Data Provider. By way of introduction for those who don't know them, Data Providers are declarative objects that replace a certain class of procedures: those that receive an input, perform a process and whose final objective is to return a set of data in a structured format, which means that the focus is placed on the output.

In this way, the format of the Data Provider's output is defined, thus transforming it into a more declarative object than a procedure because the emphasis is on "what" to do instead of "how" to do it.

The basic components of Data Providers are Groups, Elements, Variables, Subgroups and advanced control options for the Groups (Default clause, Paginate clause, NoOutput clause, OutputIfDetail clause and Input clause).

For more details read the Documentation CommunityWiki

The objective of this How To is to show two interesting examples.

The first one shows that in the groups' Where conditions we can add conditions in which the left side of the comparison can be either an attribute or even variables.

Let's suppose that we want to obtain a list of employees with their details. In this case, the DP would be as shown below:

EmployeeCollection
{
       EmployeeItem
      {
                    Id = EmployeeId
                    Name = EmployeeDsc
                    Address = EmployeeAddress
       }
}

If we needed to condition the output depending on whether the user is authorized to view this information or not, we could add the following condition:

EmployeeCollection
{
             EmployeeItem
             Where &UsrAut=”S”
             {
                        Id = EmployeeId
                        Name = EmployeeDsc
                        Address = EmployeeAddress
              }
}

In this case, the filter is added to the generated SQL sentence, which would be as follows:

SELECT [EmployeeId], [EmployeeDsc], [EmployeeAddress] FROM [Employee] WITH (NOLOCK)
WHERE @AV1UsrAut = “S” ORDER BY
[EmployeeId]

If I wanted to always return this data but display the address to authorized users only, I could write the following:

EmployeeCollection
{
           EmployeeItem
          {
                  EmployeeId
                  EmployeeDsc
                  EmployeeAddress [NoOutput]
                  Where &UsrAut=”S”
                  {
                            EmployeeAddress = EmployeeAddress;
                            EmployeeSalary = EmployeeSalary;
                   }
           }
}

In this case, a select sentence is generated without the filter condition, which is transformed into an IF in the DP source:

Select a ejecutar:
SELECT [EmployeeId], [EmployeeDsc], [EmployeeAddress], [EmployeeSalary]
FROM [Employee] WITH (NOLOCK)
ORDER BY [EmployeeId]

Y además en el fuente del DP:
if ( AV1UsrAut = “S”)
{
Gxm1employee.gxTpr_Employeeaddress = A363Employee ;
Gxm1employee.gxTpr_Employeesalary = A364Employee ;
}

Note: If, instead of a group of attributes (Address, Salary, etc.), we want to filter only one value we can write:
EmployeeAddress = EmployeeAddress if &UsrAut=”S”;

Let's see another interesting example:

Let's suppose that we have an application that handles Clients and Prospects (Clients are in one table and Prospects are in another). We want to write a Data Provider that will return a Collection containing the Active Clients or Prospects.

The Data Provider will receive as parameter the order of whether to display Clients or Prospects.

Below is one way to write it:

Parm(&FiltrarPor)

SDTCliProsCollection

{

SDTCliProsCollectionItem using DSClientesActivos()

where &FiltrarPor = ‘CLIENTE’

{

PartnerId = ClienteId

PartnerName = ClienteName

}

SDTCliProsCollectionItem using DSProspectsActivos()

where &FiltrarPor = ‘PROSPECT’

{

PartnerId = ProspectId

PartnerName = ProspectName

}

DSClientesActivos defines the filter over Clients and DSProspectsActivos does the same in the Prospects table.

Note that this will always execute the two select clauses but will add to the where clause the &FilterBy value received by parameter in the Data Provider (therefore, one of them will return data and the other will not).

This generates the following:

SELECT [ClienteTipo], [ClienteId], [ClienteNombre] FROM [Cliente] WITH (NOLOCK)
WHERE (@AV4Filtrar = 'CLIENTE') AND
([ClienteStatus] = “Activo”) ORDER BY [ClienteId]

SELECT [ProspectTipo], [ProspectId], [ProspectNombre] FROM [Prospect] WITH (NOLOCK)
WHERE (@AV4Filtrar = 'PROSPECT') AND
([ProspectStatus] = “Activo”) ORDER BY [ProspectId]

However, we can write the Data Provider as shown below, so that it doesn't always execute the two select clauses, but only the corresponding one according to the &FiltrarPor parameter (the select to be executed is chosen in the generated source).

SDTCliProsCollection

{

SDTCliProsCollectionDummy [NoOutput]

where &FiltrarPor = 'CLIENTE'

{

SDTCliProsCollectionItem using DSClientesActivos()

{

PartnerId = ClienteId

PartnerName = ClienteNombre

}

SDTCliProsCollectionDummy [NoOutput]

where &FiltrarPor = 'PROSPECT'