Data binding
22 Dec 202224 minutes to read
The Query Builder uses DataManager to bind the data source, which supports both RESTful JSON data services binding and local JavaScript object array binding. The DataSource property can be assigned either with the instance of DataManager or JavaScript object array collection. It supports two kinds of data binding method.
- Local data
- Remote data
Local Data
To bind local data to the query builder, you can assign the DataSource property with a JavaScript object array. The local data source can also be provided as an instance of the DataManager.
@using Syncfusion.EJ2.QueryBuilder
@{
...
List<string> values = new List<string> { "Mr.", "Mrs." };
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" }
}
};
var dataSource = EmployeeView.GetAllRecords();
}
<ejs-querybuilder id="querybuilder" width="73%" rule="rule" dataSource="dataSource">
<e-querybuilder-columns>
<e-querybuilder-column field="EmployeeID" label="Employee ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="FirstName" label="First Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean" values="values"></e-querybuilder-column>
<e-querybuilder-column field="Title" label="Title" type="string"></e-querybuilder-column>
<e-querybuilder-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-querybuilder-column>
<e-querybuilder-column field="Country" label="Country" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>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;
}
}NOTE
By default, DataManager uses JsonAdaptor for local data-binding.
Remote data
To bind remote data to the query builder, assign service data as an instance of DataManager to the dataSource property. To interact with remote data source, provide the endpoint url.
<ejs-querybuilder id="querybuilder" width="73%" rule="ViewBag.rule" dataSource="ViewBag.dataSource" maxGroupCount=5>
<e-datamanager url="https://services.syncfusion.com/aspnet/production/api/orders" adaptor="ODataV4Adaptor" crossdomain="true"></e-datamanager>
<e-querybuilder-columns>
<e-querybuilder-column field="EmployeeID" label="Employee ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="FirstName" label="First Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean" values="ViewBag.values"></e-querybuilder-column>
<e-querybuilder-column field="Title" label="Title" type="string"></e-querybuilder-column>
<e-querybuilder-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-querybuilder-column>
<e-querybuilder-column field="Country" label="Country" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>public IActionResult Index()
{
QueryBuilderRule rule = new QueryBuilderRule()
{
Condition = "and",
Rules = new List<QueryBuilderRules>()
{
new QueryBuilderRules { Label="Employee ID", Field="EmployeeID", Type="number", Operator="equal", Value = 1 },
new QueryBuilderRules { Label="Title", Field="Title", Type="string", Operator="equal", Value = "Sales Manager" }
}
};
ViewBag.rule = rule;
return view();
}NOTE
By default,
DataManagerusesODataAdaptorfor remote data-binding.
Binding with OData services
OData is a standardized protocol for creating and consuming data. You can retrieve data from OData service using the DataManager. Refer to the following code example for remote Data binding using OData service.
<ejs-querybuilder id="querybuilder" width="73%" rule="ViewBag.rule" dataSource="ViewBag.dataSource" maxGroupCount=5>
<e-datamanager url="https://services.syncfusion.com/aspnet/production/api/orders" adaptor="ODataAdaptor" crossdomain="true"></e-datamanager>
<e-querybuilder-columns>
<e-querybuilder-column field="EmployeeID" label="Employee ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="FirstName" label="First Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean" values="ViewBag.values"></e-querybuilder-column>
<e-querybuilder-column field="Title" label="Title" type="string"></e-querybuilder-column>
<e-querybuilder-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-querybuilder-column>
<e-querybuilder-column field="Country" label="Country" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>public IActionResult Index()
{
QueryBuilderRule rule = new QueryBuilderRule()
{
Condition = "and",
Rules = new List<QueryBuilderRules>()
{
new QueryBuilderRules { Label="Employee ID", Field="EmployeeID", Type="number", Operator="equal", Value = 1 },
new QueryBuilderRules { Label="Title", Field="Title", Type="string", Operator="equal", Value = "Sales Manager" }
}
};
ViewBag.rule = rule;
return view();
}Binding with ODatav4 services
The ODatav4 is an improved version of OData protocols, and the DataManager can also retrieve and consume ODatav4 services. For more details on ODatav4 services, refer to the odata documentation. To bind ODatav4 service, use the ODataV4Adaptor.
<ejs-querybuilder id="querybuilder" width="73%" rule="ViewBag.rule" dataSource="ViewBag.dataSource" maxGroupCount=5>
<e-datamanager url="https://services.syncfusion.com/aspnet/production/api/orders" adaptor="ODataV4Adaptor" crossdomain="true"></e-datamanager>
<e-querybuilder-columns>
<e-querybuilder-column field="EmployeeID" label="Employee ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="FirstName" label="First Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean" values="ViewBag.values"></e-querybuilder-column>
<e-querybuilder-column field="Title" label="Title" type="string"></e-querybuilder-column>
<e-querybuilder-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-querybuilder-column>
<e-querybuilder-column field="Country" label="Country" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>public IActionResult Index()
{
QueryBuilderRule rule = new QueryBuilderRule()
{
Condition = "and",
Rules = new List<QueryBuilderRules>()
{
new QueryBuilderRules { Label="Employee ID", Field="EmployeeID", Type="number", Operator="equal", Value = 1 },
new QueryBuilderRules { Label="Title", Field="Title", Type="string", Operator="equal", Value = "Sales Manager" }
}
};
ViewBag.rule = rule;
return view();
}Web API
You can use WebApiAdaptor to bind query builder with Web API created using OData endpoint.
<ejs-querybuilder id="querybuilder" width="73%" rule="ViewBag.rule" dataSource="ViewBag.dataSource" maxGroupCount=5>
<e-datamanager url="api/OrderAPI" adaptor="WebApiAdaptor" crossdomain="true"></e-datamanager>
<e-querybuilder-columns>
<e-querybuilder-column field="EmployeeID" label="Employee ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="FirstName" label="First Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean" values="ViewBag.values"></e-querybuilder-column>
<e-querybuilder-column field="Title" label="Title" type="string"></e-querybuilder-column>
<e-querybuilder-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-querybuilder-column>
<e-querybuilder-column field="Country" label="Country" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>public IActionResult Index()
{
QueryBuilderRule rule = new QueryBuilderRule()
{
Condition = "and",
Rules = new List<QueryBuilderRules>()
{
new QueryBuilderRules { Label="Employee ID", Field="EmployeeID", Type="number", Operator="equal", Value = 1 },
new QueryBuilderRules { Label="Title", Field="Title", Type="string", Operator="equal", Value = "Sales Manager" }
}
};
ViewBag.rule = rule;
return view();
}Url adaptor
You can use the UrlAdaptor of DataManager when binding data source from remote data. In the initial load of querybuilder, data are fetched from remote data and bound to the querybuilder using url property of DataManager.
<div>
<ejs-querybuilder id="querybuilder" width="100%" >
<e-data-manager url="Home/QBDataSource" adaptor="UrlAdaptor" crossDomain="true"></e-data-manager>
<e-querybuilder-columns>
<e-querybuilder-column field="EmployeeID" label="Employee ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="FirstName" label="FirstName" type="string">
</e-querybuilder-column>
<e-querybuilder-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean"></e-querybuilder-column>
<e-querybuilder-column field="Title" label="Title" type="string"></e-querybuilder-column>
<e-querybuilder-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-querybuilder-column>
<e-querybuilder-column field="Country" label="Country" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>
</div>public IActionResult QBDatasource([FromBody]DataManagerRequest dm)
{
IEnumerable DataSource = employeeData;
DataOperations operation = new DataOperations();
if (dm.Where != null && dm.Where.Count > 0) //Filtering
{
DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);
}
int count = DataSource.Cast<OrdersDetails>().Count();
if (dm.Skip != 0)
{
DataSource = operation.PerformSkip(DataSource, dm.Skip); //Paging
}
if (dm.Take != 0)
{
DataSource = operation.PerformTake(DataSource, dm.Take);
}
return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource);
}Support with Data Manager
You can use the created conditions in DataManager through the getPredicate method, which results the filtered records.
<ejs-querybuilder id="querybuilder" width="100%" rule="ViewBag.rule">
<e-querybuilder-columns>
<e-querybuilder-column field="TaskID" label="Task ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="Name" label="Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="Category" label="Category" type="string"></e-querybuilder-column>
<e-querybuilder-column field="SerialNo" label="Serial No" type="string"></e-querybuilder-column>
<e-querybuilder-column field="InvoiceNo" label="Invoice No" type="string"></e-querybuilder-column>
<e-querybuilder-column field="Status" label="Status" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>
<ejs-button id="getpredicate" cssClass='e-primary' content="Get Predicate"></ejs-button>
<script>
document.getElementById('getpredicate').onclick = function () {
var querybuilderObj = ej.base.getInstance(document.getElementById("querybuilder"), ej.querybuilder.QueryBuilder);
var template = '<tr><td>${TaskID}</td><td>${Name}</td><td>${Category}</td></tr>';
var compileFunction = ej.base.compile(template);
var table = (document.getElementById('datatable'));
table.className = 'e-table';
var predicate = querybuilderObj.getPredicate(querybuilderObj.getValidRules(querybuilderObj.rule));
dataManagerQuery = new ej.data.Query().select(['TaskID', 'Name', 'Category', 'SerialNo', 'InvoiceNo', 'Status']).where(predicate);
new ej.data.DataManager(@Html.Raw(JsonConvert.SerializeObject(@ViewBag.DataSource)))
.executeQuery(dataManagerQuery)
.then(function (e) {
e.result.forEach(function (data) {
table.appendChild(compileFunction(data)[0]);
});
});
}
</script>
<style type="text/css">
.e-hide {
display: none;
}
</style>
<body>
<div id="container">
<div id="Grid"></div>
<table id="datatable" class="e-table e-hide">
<thead>
<tr><th>Task ID</th><th>Category</th><th>Name</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
</body>public ActionResult Index()
{
QueryBuilderRule rule = new QueryBuilderRule()
{
Condition = "and",
Rules = new List<QueryBuilderRule>()
{
new QueryBuilderRule { Label="Category", Field="Category", Type="string", Operator="equal", Value = "Laptop" }
}
};
ViewBag.rule = rule;
// hardwareData is referred from MVC sample browser.
ViewBag.DataSource = QueryBuilderData.hardwareData;
return View();
}Grid Integration with QueryBuilder
This section explains how to integrate Grid with QueryBuilder. We have used UrlAdaptor to load dataSource to Grid, and the dataSource property is optional for QueryBuilder. So, you can create QueryBuilder with only columns. QueryBuilder interacts with Grid through the Query property of a Grid. You can get the query from QueryBuilder and update the Grid Query property in the ruleChange event of QueryBuilder. The UrlAdaptor works based on the on-demand concept, it will load only current page records from the server and request the next page records when navigating to the next page. Also, we have sent the request to the server for every action performed in the Grid.
<ejs-querybuilder id="querybuilder" width="73%" ruleChange="updateRule">
<e-querybuilder-columns>
<e-querybuilder-column field="CustomerID" label="Customer ID" type="number"></e-querybuilder-column>
<e-querybuilder-column field="CompanyName" label="Company Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="ContactName" label="Contact Name" type="string"></e-querybuilder-column>
<e-querybuilder-column field="City" label="City" type="string"></e-querybuilder-column>
</e-querybuilder-columns>
</ejs-querybuilder>
<ejs-grid id="grid" allowPaging="true">
<e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager>
<e-grid-columns>
<e-grid-column field="CustomerID" headerText="Customer ID" width="120"> </e-grid-column>
<e-grid-column field="CompanyName" headerText="Company Name" width="120"></e-grid-column>
<e-grid-column field="ContactName" headerText="Contact Name" width="100"></e-grid-column>
<e-grid-column field="City" headerText="City" width="120"></e-grid-column>
</e-grid-columns>
</ejs-grid>
<script>
function updateRule(args) {
var dataManagerQuery;
var qryBldrObj = ej.base.getComponent(document.getElementById("querybuilder"), 'query-builder');
var grid = ej.base.getComponent(document.getElementById("grid"), 'grid');
var predicate = qryBldrObj.getPredicate(args.rule);
if (ej.base.isNullOrUndefined(predicate)) {
dataManagerQuery = new ej.data.Query().select(['CustomerID', 'CompanyName', 'ContactName', 'City']);
} else {
dataManagerQuery = new ej.data.Query().select(['CustomerID', 'CompanyName', 'ContactName', 'City']).where(predicate);
}
grid.query = dataManagerQuery;
grid.refresh();
}
</script>public class HomeController : Controller
{
private NORTHWNDContext _context;
public HomeController(NORTHWNDContext context)
{
_context = context;
}
public IActionResult Index()
{
return View();
}
private IQueryable<Records> GetDataSource()
{
IQueryable<Records> transactions = _context.Records;
return transactions;
}
public IActionResult UrlDatasource([FromBody] DataManagerRequest dm)
{
IQueryable<Records> DataSource = GetDataSource();
QueryableOperation operation = new QueryableOperation();
if (dm.Where != null && dm.Where.Count > 0) //Filtering
{
DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Condition); //filtering
}
if (dm.Search != null && dm.Search.Count > 0)
{
DataSource = operation.PerformSearching(DataSource, dm.Search); //Search
}
int count = DataSource.Cast<Records>().Count();
if (dm.Skip != 0)
{
DataSource = operation.PerformSkip(DataSource, dm.Skip);
}
if (dm.Take != 0)
{
DataSource = operation.PerformTake(DataSource, dm.Take);
}
return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource);
}
}