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.

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:

   

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:
   • C#

   • C#

   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:
   • C#

   • C#

   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:
    • CSHTML

    • CSHTML

    <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.
    • JS

    • JS

    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.
      • CSHTML

      • CSHTML

      <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:

    • C#

    • C#

    //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.

    • C#

    • C#

    //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.