Data Querying

MYOB Acumatica Framework provides a custom language called BQL (business query language) that developers can use for writing database queries. BQL is written in C# and based on generic class syntax, but is still very similar to SQL syntax.

MYOB Acumatica Framework provides two dialects of BQL: traditional BQL and fluent BQL. We recommend that you use fluent BQL because statements written in fluent BQL are simpler and shorter than the ones written with traditional BQL. Further in this topic, the examples are written in fluent BQL.

Note:

You can also use LINQ to select records from the database or to apply additional filtering to the data of a BQL query. For details, see Creating LINQ Queries.

BQL has almost the same keywords as SQL does, and they are placed in the same order as they are in SQL, as shown in the following example of BQL.

SelectFrom<Product>.Where<Product.availQty.IsNotNull.
        And<Product.availQty.IsGreater<Product.bookedQty>>>

If the database provider is Microsoft SQL Server, the framework translates this expression into the following SQL query.

SELECT * FROM Product 
WHERE Product.AvailQty IS NOT NULL 
AND Product.AvailQty > Product.BookedQty

BQL extends several benefits to the application developer. It does not depend on the specifics of the database provider, and it is object-oriented and extendable. Another important benefit of BQL is compile-time syntax validation, which helps to prevent SQL syntax errors.

Because BQL is implemented on top of generic classes, you need data types that represent database tables. In the context of MYOB Acumatica Framework, these types are called data access classes (DACs). As an example of a DAC, you would define the Product data access class as shown in the following code fragment to execute the SQL query from the previous code example.

using System;
using PX.Data;

[PXCacheName("Product")]
public class Product : PX.Data.PXBqlTable, PX.Data.IBqlTable
{
    // The property holding the ProductID value in a record
    [PXDBIdentity(IsKey = true)]
    public virtual int? ProductID { get; set; }
    // The type used in BQL statements to reference the ProductID column
    public abstract class productID : PX.Data.BQL.BqlInt.Field<productID> { }

    // The property holding the AvailQty value in a record
    [PXDBDecimal(2)]
    public virtual decimal? AvailQty { get; set; }
    // The type used in BQL statements to reference the AvailQty column
    public abstract class availQty : PX.Data.BQL.BqlDecimal.Field<availQty> { }

    // The property holding the BookedQty value in a record
    [PXDBDecimal(2)]
    public virtual decimal? BookedQty { get; set; }
    // The type used in BQL statements to reference the BookedQty column
    public abstract class bookedQty : PX.Data.BQL.BqlDecimal.Field<bookedQty> { }
}

Each table field is declared in a data access class in two different ways, each for a different purpose:

As a public virtual property (which is also referred to as a property field) to hold the table field data
As a public abstract class (which is also referred to as a class field or BQL field) to reference a field in the BQL command

If the DAC is bound to the database, it must have the same class name the database table has. DAC fields are bound to the database by means of data mapping attributes (such as PXDBIdentity and PXDBDecimal), using the same naming convention as fields in the database.

The following code demonstrates an example of how to obtain data records from the database.

// Select Product records
PXResultset<Product> res = SelectFrom<Product>.Where<Product.availQty.IsNotNull.
        And<Product.availQty.IsGreater<Product.bookedQty>>>.View.Select(graph);
// You can iterate through the result set
foreach(PXResult<Product> rec in res)
{
    // A record from the result set can be cast to the DAC
    Product p = (Product)rec;
    ...
}