DataEngine for .NET Standard | ComponentOne
Walkthrough / Creating Queries using QueryFactory
In This Topic
    Creating Queries using QueryFactory
    In This Topic

    This walkthrough depicts a scenario where the user is provided with a user interface to select the table, table fields and operators, which would be used to create the query and display the query results using the client side controls. Note that the C1DataEngine library includes a class named QueryFactory, which provides methods to create a C1DataEngine Query from JSON string or RuntimeQuery objects. This is helpful when we need to choose the query fields and operators at runtime. The selected values can be used to create the query using the QueryFactory class.

    The GIF below depicts the DataEngine in action while extracting the count of animals (species-wise) from a dataset of Pet Licenses issued for the city of Seattle.

    query execute

    To learn how to create queries using QueryFactory in detail, follow the steps given below:

    1. Create a new ASP.NET Core Web Application using C#.NEt.
    2. Make sure that the project is configured with the following settings:

      Settings

    3. Install C1.DataEngine and C1.DataEngine.Api packages using the Nuget Package manager.
    4. Generate the license key for the application using the following link: https://developer.mescius.com/login.
    5. Apply the generated license to the application. Here is the sample snippet:

      C1.DataEngine.LicenseManager.Key = “XXXXXX”;

    6. Add the json files from where the DataEngine will fetch the data. For this sample, you can copy paste the two json files namely “seattle-pet-licenses.json” and “washington-zip-codes.json” that can be found in the “DataEngineDesigner” product sample located at the following location: "Documents\ComponentOne Samples\DataEngine\CS\DataEngineDesigner".
    7. Create two new classes namely “PetLicenses” and “Locations”. These classes are created to read the appropriate data from the json files to create the tables in the DataEngine. Add the following code to Startup.cs file for defining the classes: 
      public class PetLicense
{
    [JsonProperty("License Issue Date")]
    public DateTime IssueDate { get; set; }
    [JsonProperty("License Number")]
    public string LicenseNumber { get; set; }
    [JsonProperty("Animal's Name")]
    public string AnimalName { get; set; }
    public string Species { get; set; }
    [JsonProperty("Primary Breed")]
    public string PrimaryBreed { get; set; }
    [JsonProperty("Secondary Breed")]
    public string SecondaryBreed { get; set; }
    [JsonProperty("ZIP Code")]
    [JsonConverter(typeof(ZipCodeConverter))]
    public int? ZipCode { get; set; }
}

public class Location
{
    public int Zip { get; set; }
    public string City { get; set; }
    public string County { get; set; }
}

public class ZipCodeConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(string);
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }

    public override Object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        string zip = (string)reader.Value;
        if (zip.Length < 5) return null;
        return Convert.ToInt32(zip.Substring(0, 5));
    }
}
    8. Replace the “Main” method in the Program.cs file with the following code used to set up the DataEngine Workspace and populate it with the tables created using the included json files: 
      private static Workspace workspace;
public static Workspace Workspace
{
    get { return workspace; }
}
public static void Main(string[] args)
{
    // Apply license key
    C1.DataEngine.LicenseManager.Key = License.Key;

    // Create and initialize a new workspace folder relative to the project root
    workspace = new Workspace();
    workspace.KeepFiles = KeepFileType.Results;
    workspace.Init("workspace");

    // Uncomment the following line to clear the workspace before each run
    // workspace.Clear();            

    // Import the main license table
    if (!workspace.TableExists("PetLicenses"))
    {
        List<PetLicense> collection1 = JsonConvert.DeserializeObject<List<PetLicense>>(System.IO.File.ReadAllText("seattle-pet-licenses.json"));
        ObjectConnector<PetLicense> connector = new ObjectConnector<PetLicense>(workspace, collection1);
        connector.GetData("PetLicenses");
        workspace.Save();
    }

    // Import the secondary location table
    if (!workspace.TableExists("Locations"))
    {
        List<Location> collection2 = JsonConvert.DeserializeObject<List<Location>>(System.IO.File.ReadAllText("washington-zip-codes.json"));
        ObjectConnector<Location> connector = new ObjectConnector<Location>(workspace, collection2);
        connector.GetData("Locations");
        workspace.Save();
    }
    CreateWebHostBuilder(args).Build().Run();
}
    9. Add a new razor page named “DemoQuery” to the project.
    10. Design the UI of the application by adding the following HTML code to the DemoQuery.cshtml file: 
      <form method="post"></form>
<label>Select table</label><div id="theTables" style="margin-left:5px;"></div>
<br/>
<label>Enter Query name</label><input type="text" id="queryName" style="height:30px; margin-left:5px; margin-top:5px;" />
<br/>
<label>Choose Fields</label>
<div id="fieldOperatorGrid"></div>
<button onclick="ExecuteQuery()" style="margin-bottom:5px; margin-top:5px;">Execute Query</button>
<button onclick="FetchResults()" style="margin-bottom:5px; margin-top:5px;">Fetch Results</button>
<div id="gsFlexGrid" style="height:450px;"></div>
    11. The FlexGrid cells should display the list of fields and query operators for the user to select the query fields and operators. The default FlexGrid cell editor, that is a textbox must be replaced with ComboBox to display the list of fields and operators. 
      var CustomGridEditor = /** @class */ (function () {
    /**
     * Initializes a new instance of a CustomGridEditor.
     */
    function CustomGridEditor(flex, binding, edtClass, options) {
        var _this = this;
        // save references
        this._grid = flex;
        this._col = flex.columns.getColumn(binding);
        // create editor
        this._ctl = new edtClass(document.createElement('div'), options);
        // connect grid events
        flex.beginningEdit.addHandler(this._beginningEdit, this);
        flex.sortingColumn.addHandler(function () {
            _this._commitRowEdits();
        });
        flex.scrollPositionChanged.addHandler(function () {
            if (_this._ctl.containsFocus()) {
                flex.focus();
            }
        });
        flex.selectionChanging.addHandler(function (s, e) {
            if (e.row != s.selection.row) {
                _this._commitRowEdits();
            }
        });
        // connect editor events
        this._ctl.addEventListener(this._ctl.hostElement, 'keydown', function (e) {
            switch (e.keyCode) {
                case wijmo.Key.Tab:
                case wijmo.Key.Enter:
                    e.preventDefault(); // TFS 255685
                    _this._closeEditor(true);
                    _this._grid.focus();
                    // forward event to the grid so it will move the selection
                    var evt = document.createEvent('HTMLEvents');
                    evt.initEvent('keydown', true, true);
                    'altKey,metaKey,ctrlKey,shiftKey,keyCode'.split(',').forEach(function (prop) {
                        evt[prop] = e[prop];
                    });
                    _this._grid.hostElement.dispatchEvent(evt);
                    break;
                case wijmo.Key.Escape:
                    _this._closeEditor(false);
                    _this._grid.focus();
                    break;
            }
        });
        // close the editor when it loses focus
        this._ctl.lostFocus.addHandler(function () {
            setTimeout(function () {
                if (!_this._ctl.containsFocus()) {
                    _this._closeEditor(true); // apply edits and close editor
                    _this._grid.onLostFocus(); // commit item edits if the grid lost focus
                }
            });
        });
        // commit edits when grid loses focus
        this._grid.lostFocus.addHandler(function () {
            setTimeout(function () {
                if (!_this._grid.containsFocus() && !CustomGridEditor._isEditing) {
                    _this._commitRowEdits();
                }
            });
        });
        // open drop-down on f4/alt-down
        this._grid.addEventListener(this._grid.hostElement, 'keydown', function (e) {
            // open drop-down on f4/alt-down
            _this._openDropDown = false;
            if (e.keyCode == wijmo.Key.F4 ||
                (e.altKey && (e.keyCode == wijmo.Key.Down || e.keyCode == wijmo.Key.Up))) {
                var colIndex = _this._grid.selection.col;
                if (colIndex > -1 && _this._grid.columns[colIndex] == _this._col) {
                    _this._openDropDown = true;
                    _this._grid.startEditing(true);
                    e.preventDefault();
                }
            }
            // commit edits on Enter (in case we're at the last row, TFS 268944)
            if (e.keyCode == wijmo.Key.Enter) {
                _this._commitRowEdits();
            }
        }, true);
        // close editor when user resizes the window (unless it's a touch)
        window.addEventListener('resize', function () {
            if (_this._ctl.containsFocus() && !_this._ctl.isTouching) {
                _this._closeEditor(true);
                _this._grid.focus();
            }
        });
    }
    Object.defineProperty(CustomGridEditor.prototype, "control", {
        // gets an instance of the control being hosted by this grid editor
        get: function () {
            return this._ctl;
        },
        enumerable: true,
        configurable: true
    });
    // handle the grid's beginningEdit event by canceling the built-in editor,
    // initializing the custom editor and giving it the focus.
    CustomGridEditor.prototype._beginningEdit = function (grid, args) {
        var _this = this;
        // check that this is our column
        if (grid.columns[args.col] != this._col) {
            return;
        }
        // check that this is not the Delete key
        // (which is used to clear cells and should not be messed with)
        var evt = args.data;
        if (evt && evt.keyCode == wijmo.Key.Delete) {
            return;
        }
        // cancel built-in editor
        args.cancel = true;
        // save cell being edited
        this._rng = args.range;
        CustomGridEditor._isEditing = true;
        // initialize editor host
        var rcCell = grid.getCellBoundingRect(args.row, args.col), rcBody = document.body.getBoundingClientRect(), ptOffset = new wijmo.Point(-rcBody.left, -rcBody.top), zIndex = (args.row < grid.frozenRows || args.col < grid.frozenColumns) ? '3' : '';
        wijmo.setCss(this._ctl.hostElement, {
            position: 'absolute',
            left: rcCell.left - 1 + ptOffset.x,
            top: rcCell.top - 1 + ptOffset.y,
            width: rcCell.width + 1,
            height: grid.rows[args.row].renderHeight + 1,
            borderRadius: '0px',
            zIndex: zIndex,
        });
        // initialize editor content
        if (!wijmo.isUndefined(this._ctl['text'])) {
            this._ctl['text'] = grid.getCellData(this._rng.row, this._rng.col, true);
        }
        else {
            throw 'Can\'t set editor value/text...';
        }
        // start editing item
        var ecv = grid.editableCollectionView, item = grid.rows[args.row].dataItem;
        if (ecv && item && item != ecv.currentEditItem) {
            setTimeout(function () {
                grid.onRowEditStarting(args);
                ecv.editItem(item);
                grid.onRowEditStarted(args);
            }, 50); // wait for the grid to commit edits after losing focus
        }
        // activate editor
        document.body.appendChild(this._ctl.hostElement);
        this._ctl.focus();
        setTimeout(function () {
            // get the key that triggered the editor
            var key = (evt && evt.charCode > 32)
                ? String.fromCharCode(evt.charCode)
                : null;
            // get input element in the control
            var input = _this._ctl.hostElement.querySelector('input');
            // send key to editor
            if (input) {
                if (key) {
                    input.value = key;
                    wijmo.setSelectionRange(input, key.length, key.length);
                    var evtInput = document.createEvent('HTMLEvents');
                    evtInput.initEvent('input', true, false);
                    input.dispatchEvent(evtInput);
                }
                else {
                    input.select();
                }
            }
            // give the control focus
            if (!input && !_this._openDropDown) {
                _this._ctl.focus();
            }
            // open drop-down on F4/alt-down
            if (_this._openDropDown && _this._ctl instanceof wijmo.input.DropDown) {
                _this._ctl.isDroppedDown = true;
                _this._ctl.dropDown.focus();
            }
        }, 50);
    };
    // close the custom editor, optionally saving the edits back to the grid
    CustomGridEditor.prototype._closeEditor = function (saveEdits) {
        if (this._rng) {
            var grid = this._grid, ctl = this._ctl, host = ctl.hostElement;
            // raise grid's cellEditEnding event
            var e = new wijmo.grid.CellEditEndingEventArgs(grid.cells, this._rng);
            grid.onCellEditEnding(e);
            // save editor value into grid
            if (saveEdits) {
                ctl.onLostFocus(); // commit any pending changes (TFS 323317)
                if (!wijmo.isUndefined(ctl['value'])) {
                    this._grid.setCellData(this._rng.row, this._rng.col, ctl['value']);
                }
                else if (!wijmo.isUndefined(ctl['text'])) {
                    this._grid.setCellData(this._rng.row, this._rng.col, ctl['text']);
                }
                else {
                    throw 'Can\'t get editor value/text...';
                }
                this._grid.invalidate();
            }
            // close editor and remove it from the DOM
            if (ctl instanceof wijmo.input.DropDown) {
                ctl.isDroppedDown = false;
            }
            host.parentElement.removeChild(host);
            this._rng = null;
            CustomGridEditor._isEditing = false;
            // raise grid's cellEditEnded event
            grid.onCellEditEnded(e);
        }
    };
    // commit row edits, fire row edit end events (TFS 339615)
    CustomGridEditor.prototype._commitRowEdits = function () {
        var grid = this._grid, ecv = grid.editableCollectionView;
        this._closeEditor(true);
        if (ecv && ecv.currentEditItem) {
            var e = new wijmo.grid.CellEditEndingEventArgs(grid.cells, grid.selection);
            ecv.commitEdit();
            setTimeout(function () {
                grid.onRowEditEnding(e);
                grid.onRowEditEnded(e);
                grid.invalidate();
            });
        }
    };
    return CustomGridEditor;
}());

      To accomplish the same, add a custom js file named “CustomGridEditor” to the js folder found under the wwwroot folder in the project’s root folder. Refer the code snippet below:

      <script type="text/javascript" src="~/js/CustomGridEditor.js"></script>

    12. Reference the newly created js file in the DemoQuery razor page:

      <script type="text/javascript" src="~/js/CustomGridEditor.js"></script>

    13. Further, reference the Wijmo control library used to display the DataEngine query results, by adding the following scripts:
    14. Add the following javascript code to DemoQuery.cshtml file, to perform the following operations:
      • The handler defined for the window onload event initializes and sets up the two FlexGrid controls, one will be used to display the table fields and operators, while the other would be used to display the query results.
      • The “ExecuteQuery” method formulates the query data based on the user selection in the FlexGrid as a json string and makes an Ajax call to the server side “OnPostAsync” method. This method would be used to create and execute the query.
      • The “FetchResults” method has been defined to fetch the query results from the server side and display in the FlexGrid control on the client side. 
        <script type="text/javascript">
    var grid, theTables, PetLicenses_Fields, Locations_Fields, flex, Operators, fieldCombo;
    window.onload = function () {
        PetLicenses_Fields = ["AnimalName", "IssueDate", "LicenseNumber", "PrimaryBreed", "SecondaryBreed", "Species", "ZipCode"];
        Locations_Fields = ["City", "County", "Zip"];
        Operators = ["Sum", "Avg", "First", "Last", "Count", "Max", "Min", "VarP", "Var", "StdP", "Std"];

        //Populate combobox with tables list
        theTables = new wijmo.input.ComboBox('#theTables', {
            itemsSource: ["PetLicenses", "Locations"],
            selectedIndexChanged: function () {
                if (theTables.selectedValue == "PetLicenses") {
                    fieldCombo._ctl.itemsSource = PetLicenses_Fields;
                    flex.refresh();
                }
                else if (theTables.selectedValue == "Locations") {
                    fieldCombo._ctl.itemsSource = Locations_Fields
                    flex.refresh();
                }
            }
        });        

        //create FlexGrid to load fields and operators
        flex = new wijmo.grid.FlexGrid('#fieldOperatorGrid', {
            autoGenerateColumns: false,
            itemsSource: getData(7),
            columns: [
                { header: 'Fields', binding: 'fieldname', width: '.4*' },
                { header: 'Operator', binding: 'op', width: '*' },
            ]
        });

        //custom editor to choose fields
        fieldCombo = new CustomGridEditor(flex, 'fieldname', wijmo.input.ComboBox, {
            itemsSource: PetLicenses_Fields
        });

        //custom editor to choose operator
        new CustomGridEditor(flex, 'op', wijmo.input.ComboBox, {
            itemsSource: Operators
        });

        //Add FlexGrid to display query results
        grid = new wijmo.grid.FlexGrid('#gsFlexGrid');
    }

    //Add empty rows to field/operator gFlexGrid
    function getData(fieldCount) {
        var data = [];
        for (var i = 0; i < fieldCount; i++) {
            data.push({
                fieldname: "",
                op: null
            });
        }
        return data;
    }

    //Ajax call to execute query on server side
    function ExecuteQuery() {
        var queryCols = [];
        for (var r = 0; r < flex.rows.length; r++) {
            if (flex.getCellData(r, 0) != "") {
                queryCols.push({ names: [theTables.selectedValue + "." + flex.getCellData(r, 0)], op: flex.getCellData(r, 1), alias: null })
            }
        }

        var testQuery = {
            "name": $("#queryName").val(),
            "tables": [theTables.selectedValue],
            "columns": queryCols,
            "range": [],
            "joins": []
        };

        $.ajax({
            url: '/DemoQuery',
            type: 'POST',
            contentType: 'application/json; charset=utf-8',
            headers: {
                RequestVerificationToken:
                    $('input:hidden[name="__RequestVerificationToken"]').val()
            },
            success: function (response) {
                alert("Query executed successfully");
            },
            data: JSON.stringify(testQuery)
        });
    }

    //Ajax call to fetch query results
    function FetchResults() {
        $.ajax({
            url: '/DemoQuery?handler=Data',
            type: 'GET',
            data: {
                queryname: $("#queryName").val()
            },
            success: function (response) {
                grid.itemsSource = response;
            },
            error: function (error) {
                console.log("error: " + error);
            }
        });
    }
</script>
    15. Define the following methods in the DemoQuery.cshtml.cs file to accomplish the server side DataEngine operations:

      The OnGetAsync method will be used to fetch the query results and send the same back to the client side as a JSON response:

      //Return the query results
public async Task<JsonResult> OnGetAsync(string queryname)
{
    JsonResult results = null;

    await Task.Run(() =>
    {                
        if (queryname != null)
        {
            if (Program.Workspace.QueryExists(queryname))
            {                        
                string className = "TestClass" + queryname;
                IDataList test = Program.Workspace.GetQueryData(queryname);                        
                var list = ClassFactory.CreateFromDataList(test, className);
                results = new JsonResult(list);
            }
        }
    });
    return results;

      The OnPostAsync method will be used to handle the query data send from the client side. The JSON query object is handled as a RuntimeQuery object on the server side. The method would create the query on the server side and execute the same to create the result tables.

      //Fetch query json to create and execute RuntimeQuery
public async Task<IActionResult> OnPostAsync([FromBody]RuntimeQuery query)
{
    try
    {
        await Task.Run(() =>
        {
            QueryFactory.Delete(Program.Workspace, query.name);
            //The CreateQueryFromRuntimeQuery method of the QueryFactory class 
            //is used to create the query
            dynamic test = QueryFactory.CreateQueryFromRuntimeQuery(Program.Workspace, query);
            test.Query.Execute();
        });
        return StatusCode(200);
    }
    catch (Exception e)
    {
        return StatusCode(500, e.ToString());
    }
}
    16. Configure the “DemoQuery” razor page as the start page for the application. Save and execute the project to see the DataEngine in action.