Cenário
O queryVO é a possibilidade de na Framework poder efetuar Queries à base de dados em VB.NET, seja nas Análises, Regras de Negócio, Autocomplete ou construção de Parâmetros.
Este artigo tem como objetivo mostrar a construção de um QueryVO em vários exemplos práticos.
Como configurar
Toda a programação de seguida apresentada foi efetuada numa Análise com a opção "É um programa VB.NET"
Select de todos os registos
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("*")
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select * from [phcgokit].[dbo].[CL]
Select definindo algumas colunas:
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select nome,no from [phcgokit].[dbo].[CL]
Select utilizando um sub select
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL" query.SelectItems.add("nome")
query.SelectItems.add("no")
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select (Select Count(*) From ft (Nolock)
Where ft.no = cl.no) Total,* from [phcgokit].[dbo].[CL]
Select com condição Where simples - Possibilidade 1
Código em VB.NET
Dim query = New queryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
query.filterItems.add(new FilterItem("inactivo", Comparison.Equal, 0))
Dim result = SDK.query.getEntityData(query)
return result
Resultado em SQL
Select nome,no from [phcgokit].[dbo].[CL] where IsNull([Cl].[inactivo],0)=0
Select com condição Where simples - Possibilidade 2
Código em VB.NET
Dim query = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
Dim filtroCL = New FilterItem()
filtroCL.filterItem = "inactivo"
filtroCL.comparison = Comparison.Equal
filtroCL.valueItem = 0
query.filterItems.add(filtroCL)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select nome,no,esaldo from [phcgokit].[dbo].[CL]
where IsNull([Cl].[inactivo],0)=0 and IsNull([Cl].[esaldo],0)>0
Select com condição Where, com And entre dois campos
Código em VB.NET
Dim query = New queryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
query.SelectItems.add("esaldo")
Dim filtroCL = New FilterItem()
filtroCL.filterItem = "inactivo"
filtroCL.comparison = Comparison.Equal
filtroCL.valueItem = 0
Dim filtroCL2 = New FilterItem()
filtroCL2.filterItem = "esaldo"
filtroCL2.comparison = Comparison.Greater
filtroCL2.valueItem = 0
query.filterItems.add(filtroCL)
query.filterItems.add(filtroCL2)
Dim result = SDK.query.getEntityData(query)
return result
Resultado em SQL
Select nome,no,esaldo from [phcgokit].[dbo].[CL]
where IsNull([Cl].[inactivo],0)=0 and IsNull([Cl].[esaldo],0)>0
Select com condição Where, com Or entre dois campos
Código em VB.NET
Dim query = New queryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
query.SelectItems.add("esaldo")
Dim filtroCL = New FilterItem()
filtroCL.filterItem = "naoencomenda"
filtroCL.comparison = Comparison.Equal
filtroCL.valueItem = 1
filtroCL.groupItem = FilterGroupItem.Or
Dim filtroCL2 = New FilterItem()
filtroCL2.filterItem = "nocredit"
filtroCL2.comparison = Comparison.Equal
filtroCL2.valueItem = 1
query.filterItems.add(filtroCL)
query.filterItems.add(filtroCL2)
Dim result = SDK.query.getEntityData(query)
return result
Resultado em SQL
Select nome,no,esaldo from [phcgokit].[dbo].[CL]
where IsNull([Cl].[naoencomenda],0)=1 or IsNull([Cl].[nocredit],0)=1
Select com condição Where com 1 campo And e 2 campos OR
Código em VB.NET
Dim query = New queryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
query.SelectItems.add("esaldo")
Dim filtroCL = New FilterItem()
filtroCL.filterItem = "inactivo"
filtroCL.comparison = Comparison.Equal
filtroCL.valueItem = 0
filtroCL.groupItem = FilterGroupItem.AndBkt
Dim filtroCL2 = New FilterItem()
filtroCL2.filterItem = "naoencomenda"
filtroCL2.comparison = Comparison.Equal
filtroCL2.valueItem = 1
filtroCL2.groupItem = FilterGroupItem.Or
Dim filtroCL3 = New FilterItem()
filtroCL3.filterItem = "nocredit"
filtroCL3.comparison = Comparison.Equal
filtroCL3.valueItem = 1
filtroCL3.groupItem = FilterGroupItem.CloseBkt
query.filterItems.add(filtroCL)
query.filterItems.add(filtroCL2)
query.filterItems.add(filtroCL3)
Dim result = SDK.query.getEntityData(query)
return result
Resultado em SQL
Select nome,no,esaldo from [phcgokit].[dbo].[CL]
where IsNull([Cl].[inactivo],0)=0
and (IsNull([Cl].[naoencomenda],0)=1 or IsNull([Cl].[nocredit],0)=1)
Select com condição Where com 2 campo2 And e 2 campos OR
Código em VB.NET
Dim query = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("nome")
query.SelectItems.add("no")
query.SelectItems.add("esaldo")
Dim filtroCL0 = New FilterItem()
filtroCL0.groupItem = FilterGroupItem.OpenBkt
Dim filtroCL1 = New FilterItem()
filtroCL1.filterItem = "inactivo"
filtroCL1.comparison = Comparison.Equal
filtroCL1.valueItem = 0
Dim filtroCL2 = New FilterItem()
filtroCL2.filterItem = "esaldo"
filtroCL2.comparison = Comparison.Greater
filtroCL2.valueItem = 0
filtroCL2.groupItem = FilterGroupItem.BktAndBkt
Dim filtroCL3 = New FilterItem()
filtroCL3.filterItem = "naoencomenda"
filtroCL3.comparison = Comparison.Equal
filtroCL3.valueItem = 1
filtroCL3.groupItem = FilterGroupItem.Or
Dim filtroCL4 = New FilterItem()
filtroCL4.filterItem = "nocredit"
filtroCL4.comparison = Comparison.Equal
filtroCL4.valueItem = 1
filtroCL4.groupItem = FilterGroupItem.CloseBkt
query.filterItems.add(filtroCL0)
query.filterItems.add(filtroCL1)
query.filterItems.add(filtroCL2)
query.filterItems.add(filtroCL3)
query.filterItems.add(filtroCL4)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select nome,no,esaldo from [phcgokit].[dbo].[CL]
where (IsNull([Cl].[inactivo],0)=0 and IsNull([Cl].[esaldo],0)>0)
and (IsNull([Cl].[naoencomenda],0)=1 or IsNull([Cl].[nocredit],0)=1)
Select com 1 inner Join
Código em VB.NET
Dim query = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
query.SelectItems.add("ft.nmdoc")
query.SelectItems.add("ft.fdata")
query.SelectItems.add("ft.etotal")
Dim join = New JoinEntity() 'Create the Join object
Dim joinFilter = New FilterItem() 'Create the Join object filter
join.TableName = "FT"
joinFilter.filterItem = ft.no
joinFilter.comparison = Comparison.Equal
joinFilter.valueItem = cl.no
join.joinExp.Add(joinFilter)
query.joinEntities.Add(join)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL]
inner join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no]
Select com 1 inner Join e um And no mesmo Join
Código em VB.NET
Dim query = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
query.SelectItems.add("ft.nmdoc")
query.SelectItems.add("ft.fdata")
query.SelectItems.add("ft.etotal")
Dim join = New JoinEntity() 'Create the Join object
join.TableName = "FT"
Dim joinFilter = New FilterItem() 'Create the Join object filter
joinFilter.filterItem = ft.no
joinFilter.comparison = Comparison.Equal
joinFilter.valueItem = cl.no
join.joinExp.Add(joinFilter)
Dim joinFilter2 = New FilterItem() 'Create the Join object filter
joinFilter2.filterItem = ft.estab
joinFilter2.comparison = Comparison.Equal
joinFilter2.valueItem = cl.estab
join.joinExp.Add(joinFilter2)
query.joinEntities.Add(join)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL]
inner join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]
Select com 2 inner Join
Código em VB.NET
Dim query = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
query.SelectItems.add("ft.nmdoc")
query.SelectItems.add("ft.fdata")
query.SelectItems.add("ft.etotal")
Dim join = New JoinEntity() 'Create the Join object
join.TableName = "FT"
Dim joinFilter = New FilterItem() 'Create the Join object filter
joinFilter.filterItem = ft.no
joinFilter.comparison = Comparison.Equal
joinFilter.valueItem = cl.no
join.joinExp.Add(joinFilter)
Dim joinFilter2 = New FilterItem() 'Create the Join object filter
joinFilter2.filterItem = ft.estab
joinFilter2.comparison = Comparison.Equal
joinFilter2.valueItem = cl.estab
join.joinExp.Add(joinFilter2)
query.joinEntities.Add(join)
Dim join2 = New JoinEntity() 'Create the Join object
join2.TableName = "CC"
Dim joinFilter3 = New FilterItem() 'Create the Join object filter
joinFilter3.filterItem = cc.ftstamp
joinFilter3.comparison = Comparison.Equal
joinFilter3.valueItem = "<#var>ft.ftstamp"
join2.joinExp.Add(joinFilter3)
query.joinEntities.Add(join2)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL]
inner join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]
inner join phcgokit.dbo.Cc on [Cc].[ftstamp]=ft.ftstamp
Select com 1 left Join
Código em VB.NET
Dim query = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
query.SelectItems.add("ft.nmdoc")
query.SelectItems.add("ft.fdata")
query.SelectItems.add("ft.etotal")
Dim join = New JoinEntity() 'Create the Join object
Dim joinFilter = New FilterItem() 'Create the Join object filter
join.TableName = "FT"
join.joinType = joinsType.Left
joinFilter.filterItem = ft.no
joinFilter.comparison = Comparison.Equal
joinFilter.valueItem = cl.no
join.joinExp.Add(joinFilter)
Dim joinFilter2 = New FilterItem() 'Create the Join object filter
joinFilter2.filterItem = ft.estab
joinFilter2.comparison = Comparison.Equal
joinFilter2.valueItem = cl.estab join.joinExp.Add(joinFilter2)
query.joinEntities.Add(join)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL]
left join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]
Select com 1 right Join
Código em VB.NET
Dim query = New queryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
query.SelectItems.add("ft.nmdoc")
query.SelectItems.add("ft.fdata")
query.SelectItems.add("ft.etotal")
Dim join = New JoinEntity() 'Create the Join object
Dim joinFilter = New FilterItem() 'Create the Join object
filter join.TableName = "FT"
join.joinType = joinsType.Right
joinFilter.filterItem = ft.no
joinFilter.comparison = Comparison.Equal
joinFilter.valueItem = cl.no join.joinExp.Add(joinFilter)
Dim joinFilter2 = New FilterItem() 'Create the Join object
filter joinFilter2.filterItem = ft.estab
joinFilter2.comparison = Comparison.Equal
joinFilter2.valueItem = cl.estab join.joinExp.Add(joinFilter2)
query.joinEntities.Add(join)
Dim result = SDK.query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL]
right join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]
Select com 1 Order by
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
query.orderByItems.add(New OrderByItem("cl.esaldo", OrderTypes.Descending))
Dim result = SDK.query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo from [phcgokit].[dbo].[CL]
order by cl.esaldo desc
Select com 2 Order by
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.SelectItems.add("cl.no")
query.SelectItems.add("cl.esaldo")
Dim orderCL = new OrderByItem
orderCL.OrderItem = "cl.esaldo"
orderCL.OrderType = OrderTypes.Descending
query.orderByItems.add(orderCL)
Dim orderCL2 = new OrderByItem
orderCL2.OrderItem = "cl.nome"
orderCL2.OrderType = OrderTypes.Ascending
query.orderByItems.add(orderCL2)
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select cl.nome,cl.no,cl.esaldo from [phcgokit].[dbo].[CL]
order by cl.esaldo desc,cl.nome asc
Select com Top 10
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL" query.SelectItems.add("*")
query.limit = 10
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select top 10 * from [phcgokit].[dbo].[CL]
Select com Distinct
Código em VB.NET
Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")
query.distinct = true
Dim result = SDK.Query.getEntityData(query)
return result
Resultado em SQL
Select distinct cl.nome from [phcgokit].[dbo].[CL]
Resultado
Todos os exemplos permitem executar queries à base de dados, desde queries simples, a queries com condições where, joins e orders.