GenericQueryEngine 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)
var filters = new Dictionary<string, (string Operator, object Value)>
{
    { "OrderStatus", ("=", "Completed") },
    { "OrderDate", (">=", DateTime.UtcNow.AddMonths(-3)) }, // Orders from the last 3 months
    { "ProductCategory", ("IN", new[] { "Electronics", "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
);