Thursday, 12 November 2015

Union Selects in BQL

Hi Everyone,

Sometimes you want to select and combine data from different tables in database. There is no standard way to do it in BQL, but we can go with several workarounds:
  • Create a SQL server view. In this case you just create a view on SQL Server side with selecting, calculating and combining all data into one big select. After this you can create/generate a DAC that will be associated to the view. In the code you can easily use new data access class as you wish. System will always generate selects to SQL server view as it is normal table.
  • Use data view delegate, where you can execute several selects to database. When you have received data you can calculate and combine data with programming code as you wish. This way can be slower on big amount of data, but also here you have more control.
Here I want to describe in details the second way to do it.

What is delegate
Delegate is a special method that is associated to the DataView.
public class SomeMaint : PXGraph<SomeMaint>
{
public PXSelect<UnionTable> UnionSelect;
protected IEnumerable unionSelect()
{
.....
}
}
It always has the same name as DataView, differs only in casing of the first letter. System will execute delegate instead of generating SQL query to the database server. This can be very helpfull, if you need:
  • Modify select query dynamically with Where<>,Join<>, …
  • You want to perform additional calculations and don’t want/can’t do it declaratively
  • You want to return ‘fake’ data records, which are not stored in the database, but are constructed in code.
Here is schema, how system will handle the delegate.

How to do Union Dynamically
In the delegate we can do whatever we want, including selecting database, so we need to select data from 2 different tables and combine it into one result. Data View have to return only one DAC class as a result, so we should create a definition for virtual DAC, that will represents the combined union data.

Here I provide you a code example:
Of course, you have to understand that it is not completed code snippet. For example you need to aware about duplicate keys. May be it will be good idea, to use dictionary instead of list and check that you returning only unique data.

No comments: