GenericQueryEngine.DTOs 1.0.0

How to Add QueryEngine

To use QueryEngine in your class, simply inject IQueryEngine it via the constructor like this:

public class JobService
{
    private readonly IQueryEngine _queryEngine;

    public JobService(IQueryEngine queryEngine)
    {
        _queryEngine = queryEngine;
    }
}

Example: Executing a Stored Procedure with QueryEngine

To execute a stored procedure using QueryEngine, follow the example below:

string storedProcedureName = "USP_GetJobLocations";

var jobLocations = await _queryEngine.ExecuteStoredProcedureAsync(
    storedProcedureName: storedProcedureName,
    parameters: new
    {
        JobTitle = "Software Engineer",
        IsRemote = true,
        LocationId = 123,
        DatabaseName = "cor_main",
    });

Example: Fetching Data with Filters and Aggregates using QueryEngine

To retrieve data from a table with filtering, pagination, order and aggregation, use the GetAsync method as shown below:

// Define the filters (e.g., filter orders by status and date range. AND with OR (Nested Conditions))
var filters = new FilterGroup
{
    LogicalOperator = "AND",
    Conditions = new List<object>
    {
        new FilterCondition { "OrderDate", (">=", DateTime.UtcNow.AddMonths(-3)) },
        new FilterCondition { "ProductCategory", ("IN", new[] { "Electronics", "Books" }) },
        new FilterGroup
        {
            LogicalOperator = "OR",
            Conditions = new List<object>
            {
                new FilterCondition { Column = "ProductCategory", Operator = "=", Value = "Electronics" },
                new FilterCondition { Column = "ProductCategory", Operator = "=", Value = "Books" }
            }
        }
    }
};

// Define the sorting columns (e.g., sort by OrderDate and CustomerName)
var orderByColumns = new List<string> { "OrderDate", "CustomerName" };

// Define pagination (page 1, 10 results per page)
var pageSize = 10;
var pageNumber = 1;

// Define the aggregate functions (e.g., calculate the total sum of TotalAmount)
var aggregates = new Dictionary<string, string>
{
    { "TotalAmount", "SUM" }  // Sum of the TotalAmount column
};

// Call GetAsync with all parameters
var queryResult = await _queryEngine.GetAsync(
    tableName: "Orders",   // The name of the table we're querying
    filters: filters,      // Filters to apply
    orderByColumns: orderByColumns,  // Sorting columns
    pageSize: pageSize,    // Results per page
    pageNumber: pageNumber,  // Page number to fetch
    aggregates: aggregates // Aggregate functions
);