Importing

17 Feb 202215 minutes to read

Importing allows you to view or edit the predefined conditions. You can import the conditions by using methods and initial rendering.

Initial Rendering

To apply conditions initially, you can define the Rule. Here, you can import structured JSON object by defining the Rule property.

@model List<QueryBuilderSample.Controllers.EmployeeView>

@Html.EJS().QueryBuilder("querybuilder").Width("72%").Columns(col =>
{
   col.Field("EmployeeID").Label("Employee ID").Type("number").Add();
   col.Field("FirstName").Label("First Name").Type("string").Add();
   col.Field("TitleOfCourtesy").Label("Title Of Courtesy").Type("boolean").Values(new List<string> { "Mr.", "Mrs." }).Add();
   col.Field("Title").Label("Title").Type("string").Add();
   col.Field("HireDate").Label("Hire Date").Type("date").Format("dd/MM/yyyy").Add();
   col.Field("Country").Label("Country").Type("string").Add();
   col.Field("City").Label("City").Type("string").Add();
}).DataSource(Model).Render()
public ActionResult Index()
    {
          QueryBuilderRule rule = new QueryBuilderRule()
            {
                Condition = "and",
                Rules = new List<QueryBuilderRule>()
                {
                    new QueryBuilderRule { Label="Employee ID", Field="EmployeeID", Type="number", Operator="equal", Value = 1 },
                    new QueryBuilderRule { Label="Title", Field="Title", Type="string", Operator="equal", Value = "Sales Manager" }
                }
            };

            ViewBag.rule = rule;
            ViewBag.dataSource = EmployeeView.GetAllRecords();
            return View();
    }

public class EmployeeView
    {
        public EmployeeView()
        {

        }
        public EmployeeView(int EmployeeID, string FirstName, string LastName, string Title, DateTime BirthDate, DateTime HireDate, int ReportsTo, string Address, string PostalCode, string Phone, string City, string Country)
        {
            this.EmployeeID = EmployeeID;
            this.FirstName = FirstName;
            this.LastName = LastName;
            this.Title = Title;
            this.BirthDate = BirthDate;
            this.HireDate = HireDate;
            this.ReportsTo = ReportsTo;
            this.Address = Address;
            this.PostalCode = PostalCode;
            this.Phone = Phone;
            this.City = City;
            this.Country = Country;

        }
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Title { get; set; }
        public DateTime BirthDate { get; set; }
        public DateTime HireDate { get; set; }

        public int ReportsTo { get; set; }

        public string Address { get; set; }
        public string PostalCode { get; set; }
        public string Phone { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public static List<EmployeeView> GetAllRecords()
        {
            List<EmployeeView> Emp = new List<EmployeeView>();
            Emp.Add(new EmployeeView(1, "Nancy", "Davolio", "Sales Representative", new DateTime(1948, 12, 08), new DateTime(1992, 05, 01), 2, "507 - 20th Ave. E.Apt. 2A ", " 98122", "(206) 555-9857 ", "Seattle ", "USA"));
            Emp.Add(new EmployeeView(2, "Andrew", "Fuller", "Vice President, Sales", new DateTime(1952, 02, 19), new DateTime(1992, 08, 14), 4, "908 W. Capital Way", "98401 ", "(206) 555-9482 ", "Kirkland ", "USA"));
            Emp.Add(new EmployeeView(3, "Janet", "Leverling", "Sales Representative", new DateTime(1963, 08, 30), new DateTime(1992, 04, 01), 3, " 4110 Old Redmond Rd.", "98052 ", "(206) 555-8122", "Redmond ", "USA "));
            Emp.Add(new EmployeeView(4, "Margaret", "Peacock", "Sales Representative", new DateTime(1937, 09, 19), new DateTime(1993, 05, 03), 6, "14 Garrett Hill ", "SW1 8JR ", "(71) 555-4848 ", "London ", "UK "));
            Emp.Add(new EmployeeView(5, "Steven", "Buchanan", "Sales Manager", new DateTime(1955, 03, 04), new DateTime(1993, 10, 17), 8, "Coventry HouseMiner Rd. ", "EC2 7JR ", " (206) 555-8122", "Tacoma ", " USA"));
            Emp.Add(new EmployeeView(6, "Michael", "Suyama", "Sales Representative", new DateTime(1963, 07, 02), new DateTime(1993, 10, 17), 2, " 7 Houndstooth Rd.", " WG2 7LT", "(71) 555-4444 ", "London ", "UK "));
            Emp.Add(new EmployeeView(7, "Robert", "King", "Sales Representative", new DateTime(1960, 05, 29), new DateTime(1994, 01, 02), 7, "Edgeham HollowWinchester Way ", "RG1 9SP ", "(71) 555-5598 ", "London ", " UK"));
            Emp.Add(new EmployeeView(8, "Laura", "Callahan", "Inside Sales Coordinator", new DateTime(1958, 01, 09), new DateTime(1994, 03, 05), 9, "722 Moss Bay Blvd. ", "98033 ", " (206) 555-3412", "Seattle ", "USA "));
            Emp.Add(new EmployeeView(9, "Anne", "Dodsworth", "Sales Representative", new DateTime(1966, 01, 27), new DateTime(1994, 11, 15), 5, "4726 - 11th Ave. N.E. ", "98105 ", "(71) 555-5598 ", " London", "UK "));
            return Emp;
        }
    }

Post Rendering

Import from JSON

You can set the conditions from structured JSON object through the setRules method.

@Html.EJS().QueryBuilder("querybuilder").Width("72%").Columns(col =>
   {
       col.Field("EmployeeID").Label("Employee ID").Type("number").Add();
       col.Field("FirstName").Label("First Name").Type("string").Add();
       col.Field("TitleOfCourtesy").Label("Title Of Courtesy").Type("boolean").Values(new List<string> { "Mr.", "Mrs." }).Add();
       col.Field("Title").Label("Title").Type("string").Add();
       col.Field("HireDate").Label("Hire Date").Type("date").Format("dd/MM/yyyy").Add();
       col.Field("Country").Label("Country").Type("string").Add();
       col.Field("City").Label("City").Type("string").Add();
   }).Render()
 @Html.EJS().Button("importrules").CssClass("e-primary").Content("Import Rule").Render()
 
 <script>
  document.getElementById('importrules').onclick = function () {
        var querybuilderObj = ej.base.getInstance(document.getElementById("querybuilder"), ej.querybuilder.QueryBuilder);
        querybuilderObj.setRules({ 'condition': 'or', 'rules': [{ 'label': 'Employee ID', 'field': 'EmployeeID', 'type': 'number', 'operator': 'equal', 'value': '1001' }] });
    }
 </script>
public ActionResult Index()
{
     return View();
}

Import From SQL

You can set the conditions from SQL query through the setRulesFromSql method.

@Html.EJS().QueryBuilder("querybuilder").Width("72%").Columns(col =>
   {
       col.Field("EmployeeID").Label("EmployeeID").Type("number").Add();
       col.Field("FirstName").Label("First Name").Type("string").Add();
       col.Field("TitleOfCourtesy").Label("Title Of Courtesy").Type("boolean").Values(new List<string> { "Mr.", "Mrs." }).Add();
       col.Field("Title").Label("Title").Type("string").Add();
       col.Field("HireDate").Label("Hire Date").Type("date").Format("dd/MM/yyyy").Add();
       col.Field("Country").Label("Country").Type("string").Add();
       col.Field("City").Label("City").Type("string").Add();
   }).Render()
   @Html.EJS().Button("importrules").CssClass("e-primary").Content("Import Rule").Render()
    <script>
        document.getElementById('importrules').onclick = function () {
            var querybuilderObj = ej.base.getInstance(document.getElementById("querybuilder"), ej.querybuilder.QueryBuilder);
            querybuilderObj.setRulesFromSql("EmployeeID = 1");
    }
 </script>
public ActionResult Index()
{
    return View();
}

Exporting

Exporting allows you to save or maintain the created conditions through the Query Builder. You can export the defined conditions by the following ways.

JSON Export

You can export the defined conditions to structured JSON object through the getRules method.

SQL Support

You can export the defined conditions to SQL query through the getRulesFromSQL method.

@Html.EJS().QueryBuilder("querybuilder").Width("72%").Columns(col =>
   {
       col.Field("EmployeeID").Label("Employee ID").Type("number").Add();
       col.Field("FirstName").Label("First Name").Type("string").Add();
       col.Field("TitleOfCourtesy").Label("Title Of Courtesy").Type("boolean").Values(new List<string> { "Mr.", "Mrs." }).Add();
       col.Field("Title").Label("Title").Type("string").Add();
       col.Field("HireDate").Label("Hire Date").Type("date").Format("dd/MM/yyyy").Add();
       col.Field("Country").Label("Country").Type("string").Add();
       col.Field("City").Label("City").Type("string").Add();
   }).Render()
    
@Html.EJS().Dialog("defaultdialog").Target(".e-query-builder").Width("50%").Header("Query builder").Visible(false).ShowCloseIcon(true).Render()

@Html.EJS().Button("getsql").CssClass("e-primary").Content("Get Sql").Render()
@Html.EJS().Button("getrule").CssClass("e-primary").Content("Get Rule").Render()

<script>
 document.getElementById('getsql').onclick = function () {
        var querybuilderObj = ej.base.getInstance(document.getElementById("querybuilder"), ej.querybuilder.QueryBuilder);
        var dialogObj = ej.base.getInstance(document.getElementById("defaultdialog"), ej.popups.Dialog);
        dialogObj.content = querybuilderObj.getSqlFromRules(querybuilderObj.getRules());
        dialogObj.show();
 }
 document.getElementById('getrule').onclick = function () {
        var querybuilderObj = ej.base.getInstance(document.getElementById("querybuilder"), ej.querybuilder.QueryBuilder);
        var dialogObj = ej.base.getInstance(document.getElementById("defaultdialog"), ej.popups.Dialog);
        dialogObj.content = '<pre>' + JSON.stringify(querybuilderObj.getValidRules(querybuilderObj.rule), null, 4) + '</pre>';
        dialogObj.show();
    }
</script>
public ActionResult Index()
{
    QueryBuilderRule rule = new QueryBuilderRule()
    {
        Condition = "and",
        Rules = new List<QueryBuilderRule>()
        {
            new QueryBuilderRule { Label="Employee ID", Field="EmployeeID", Type="number", Operator="equal", Value = 1 },
            new QueryBuilderRule { Label="Title", Field="Title", Type="string", Operator="equal", Value = "Sales Manager" }
        }
    };
    ViewBag.rule = rule;
    return View();
}