COMPANY
COMMUNITY
BLOG
HELP
MY ACCOUNT
EN PT ES

ht dt 286

How To: Examples of Data Providers with Conditions

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'
          {
                   SDTCliProsCollectionItem using DSProspectActivos()
                   {
                             PartnerId = ProspectId
                             PartnerName = ProspectNombre
                   }
          }

}

Select clause to execute in the event that the DP is invoked with &FiltrarPor = ‘CLIENTE’:
 
SELECT [ClienteTipo], [ClienteId], [ClienteNombre] FROM [Cliente] WITH (NOLOCK)
WHERE [ClienteStatus] =
“Activo” ORDER BY [ClienteId]
 
Also, in the Data Provider source:
         if ( ( String.CompareOrdinal(AV4Filtrar.TrimEnd(' '), "CLIENTE".TrimEnd(' ') ) == 0 ) )
         {
            AV10DSClie = AV2Cliente ;
            /* Using cursor P001R2 */
            pr_default.execute(0, new Object[] {AV10DSClie});
            while ( (pr_default.getStatus(0) != 101) )
            {
               ………………………. (select con filtro por [ClienteStatus] = “Activo”)
            }
            pr_default.close(0);
         }
         if ( ( String.CompareOrdinal(AV4Filtrar.TrimEnd(' '), "PROSPECT".TrimEnd(' ') ) == 0 ) )
         {
            AV12DSPros = AV3Prospec ;
            /* Using cursor P001R3 */
            pr_default.execute(1, new Object[] {AV12DSPros});
            while ( (pr_default.getStatus(1) != 101) )
            {
               ……………………….(select con filtro por [ProspectStatus] = “Activo”)
            }
            pr_default.close(1);
         }

 

         this.cleanup();
Related
How to: Data Providers
GeneXus X increases development productivity with Data Providers and Data Selectors
How to: Workflow
Data Providers and Business Components to initialize data
How to: GXflow in GeneXus X Evolution 1
How to: Enabling security in GXserver
Special Edition #GeneXusTheBestOf2014 :: Cloud Computing, the Internet of Things, GeneXus X Evolution 3, Success Stories, Smart Devices and ... Much More!
Release of U2 of GeneXus Evolution 3
Chopo Mobile introduces successful app in Mexico, developed with GeneXus and used for medical studies
GeneXus™ ERP Explorer and Connector for SAP® ERP Now Live on SAP Store
We have a new Download Center! The GeneXus download center has an enhanced appearance at its new location
Demand for Apple TV apps expected to double in 2016
What we've learned about GeneXus by working with Gartner (by Gabriel Simonet)
Agile or Waterfall development? (by Breogán Gonda)
We are pleased to share an interesting white paper about GeneXus, written by Kapil Ambwani from India!