Grid Data Bind
The Data Grid component can be bound to multiple types of data collections including arrays, xml, json, tsv, csv or remote data. To data bind the Grid to a data source you need to set its dataSource
property. The first option is to set it to a Javascript Array instance or URL string and configure the data binding through the dataSourceSettings
property. The second option is to set the property to an instance of Smart.DataAdapter
Topics
Array
The following example shows how to populate the Grid with Javascript Array. The Grid'sdataSource
is set to an instance of Smart.DataAdapter. The DataAdapter is a powerful plugin which enables data binding to local and remote data, parses the data to specific data types and smoothly integrates with Grid, Chart, Scheduler, CardView, Kanban and Gantt Chart components.
We set the DataAdapter's dataSource and dataFields properties. The dataFields property is used to define the data source bound fields and their data types.
function getData() { let data = new Array(); let firstNames= [ "Andrew", "Nancy", "Shelley", "Regina", "Yoshi", "Antoni", "Mayumi", "Ian", "Peter", "Lars", "Petra", "Martin", "Sven", "Elio", "Beate", "Cheryl", "Michael", "Guylene" ]; let lastNames = [ "Fuller", "Davolio", "Burke", "Murphy", "Nagase", "Saavedra", "Ohno", "Devling", "Wilson", "Peterson", "Winkler", "Bein", "Petersen", "Rossi", "Vileid", "Saylor", "Bjorn", "Nodier" ]; let productNames = [ "Black Tea", "Green Tea", "Caffe Espresso", "Doubleshot Espresso", "Caffe Latte", "White Chocolate Mocha", "Cramel Latte", "Caffe Americano", "Cappuccino", "Espresso Truffle", "Espresso con Panna", "Peppermint Mocha Twist" ]; let priceValues = [ "2.25", "1.5", "3.0", "3.3", "4.5", "3.6", "3.8", "2.5", "5.0", "1.75", "3.25", "4.0" ]; for (let i = 0; i < 5000; i++) { let row = {}; let productindex = Math.floor(Math.random() * productNames.length); let price = parseFloat(priceValues[productindex]); let quantity = 1 + Math.round(Math.random() * 10); row["id"] = 1 + i; row["firstName"] = firstNames[Math.floor(Math.random() * firstNames.length)]; row["lastName"] = lastNames[Math.floor(Math.random() * lastNames.length)]; row["productName"] = productNames[productindex]; row["available"] = (productindex % 2 === 0); row["price"] = price; row["quantity"] = quantity; row["total"] = price * quantity; data[i] = row; } return data; } const grid = new Smart.Grid('#grid', { dataSource: new Smart.DataAdapter( { dataSource: getData(), dataFields: [ 'id: number', 'firstName: string', 'lastName: string', 'productName: string', 'available: bool', 'quantity: number', 'price: number', 'total: number' ] }), columns: [ { label: 'First Name', dataField: 'firstName' }, { label: 'Last Name', dataField: 'lastName' }, { label: 'Product', dataField: 'productName' }, { label: 'Available', dataField: 'available', template: 'checkBox' }, { label: 'Quantity', dataField: 'quantity' }, { label: 'Unit Price', dataField: 'price', cellsFormat: 'c2' } ] });
JSON
The following example shows how to create a Smart.DataAdapter instance and set the dataSource property to url.dataSource: new Smart.DataAdapter({ dataSource: '../../scripts/beverages.json', dataFields: [ { name: 'name', dataType: 'string' }, { name: 'type', dataType: 'string' }, { name: 'calories',dataType: 'int' }, { name: 'totalfat', dataType: 'string' }, { name: 'protein', dataType: 'string' } ] })Data Adapter properties:
- data: Data to be sent to the server. Expects JSON object.
- dataSourceType: the data's type. Possible values: 'xml', 'json', 'jsonp', 'tsv', 'csv', 'xlsx', 'array', 'ics' type: The type of request to make ("POST" or "GET"), default is "GET".
- id: A string containing the Id data field. When you set this, the Grid rows id will use the data source row id.
- root: A string describing where the data begins and all other loops begin from this element. This is used in XML data binding
- record: A string describing the information for a particular record. This is used in XML data binding
- childrenDataField - Sets or gets a children data field like 'children', 'items' in the data source. When this property is set, the dataAdapter will look for this data field when looping through the items. If it is found a hierarchical data source would be created.
- sanitizeHTML - Possible values: "all" | "blackList" | "none". The property specifies how html in the data bind is escaped.
- keyDataField - Sets or gets the key data field to be used for building the hierarchy. It is used in combination with the parentDataField property. Usually the 'id' field is used as key data field and 'parentId' as parent data field'.
- parentDataField - Sets or gets the parent data field to be used for building the hierarchy. It is used in combination with the keyDataField property. Usually the 'id' field is used as key data field and 'parentId' as parent data field'
The beverages.json has the following content:
Let's extend the above example, by adding Filter, Sorting and Paging. That can be achieved by using the
[{ "id": "1", "name": "Hot Chocolate", "type": "Chocolate Beverage", "calories": "370", "totalfat": "16g", "protein": "14g" }, { "id": 2, "name": "Peppermint Hot Chocolate", "type": "Chocolate Beverage", "calories": "440", "totalfat": "16g", "protein": "13g" }, { "id": "3", "name": "Salted Caramel Hot Chocolate", "type": "Chocolate Beverage", "calories": "450", "totalfat": "16g", "protein": "13g" }, { "id": "4", "name": "White Hot Chocolate", "type": "Chocolate Beverage", "calories": "420", "totalfat": "16g", "protein": "12g" }, { "id": "5", "name": "Caffe Americano", "type": "Espresso Beverage", "calories": "15", "totalfat": "0g", "protein": "1g" }, { "id": "6", "name": "Caffe Latte", "type": "Espresso Beverage", "calories": "190", "totalfat": "7g", "protein": "12g" }, { "id": "7", "name": "Caffe Mocha", "type": "Espresso Beverage", "calories": "330", "totalfat": "15g", "protein": "13g" }, { "id": "8", "name": "Cappuccino", "type": "Espresso Beverage", "calories": "120", "totalfat": "4g", "protein": "8g" }, { "id": "9", "name": "Caramel Brulee Latte", "type": "Espresso Beverage", "calories": "420", "totalfat": "9g", "protein": "8g" }, { "id": "10", "name": "Caramel Macchiato", "type": "Espresso Beverage", "calories": "240", "totalfat": "11g", "protein": "10g" }, { "id": "11", "name": "Peppermint Hot Chocolate", "type": "Espresso Beverage", "calories": "440", "totalfat": "10g", "protein": "13g" }, { "id": "12", "name": "Cinnamon Dolce Latte", "type": "Espresso Beverage", "calories": "260", "totalfat": "6g", "protein": "10g" }, { "id": "13", "name": "Eggnog Latte", "type": "Espresso Beverage", "calories": "460", "totalfat": "16g", "protein": "13g" }, { "id": "14", "name": "Espresso", "type": "Espresso Beverage", "calories": "5", "totalfat": "1g", "protein": "1g" }, { "id": "15", "name": "Espresso Con Panna", "type": "Espresso Beverage", "calories": "30", "totalfat": "1g", "protein": "0g" }, { "id": "16", "name": "Espresso Macchiato", "type": "Espresso Beverage", "calories": "100", "totalfat": "1g", "protein": "0g" }, { "id": "17", "name": "Flavored Latte", "type": "Espresso Beverage", "calories": "250", "totalfat": "6g", "protein": "12g" }, { "id": "18", "name": "Gingerbread Latte", "type": "Espresso Beverage", "calories": "320", "totalfat": "13g", "protein": "12g" }, { "id": "19", "name": "White Chocolate Mocha", "type": "Espresso Beverage", "calories": "470", "totalfat": "18g", "protein": "15g" }, { "id": 20, "name": "Skinny Peppermint Mocha", "type": "Espresso Beverage", "calories": 130, "totalfat": "15g", "protein": "13g" }, { "id": "21", "name": "Skinny Flavored Latte", "type": "Espresso Beverage", "calories": "120", "totalfat": "0g", "protein": "12g" }, { "id": "22", "name": "Pumpkin Spice Latte", "type": "Espresso Beverage", "calories": "380", "totalfat": "13g", "protein": "14g" }, { "id": "23", "name": "Caffe Vanilla Frappuccino", "type": "Frappuccino Blended Beverage", "calories": "310", "totalfat": "3g", "protein": "3g" }, { "id": "24", "name": "Caffe Vanilla Frappuccino Light", "type": "Frappuccino Blended Beverage", "calories": "180", "totalfat": "0g", "protein": "3g" }, { "id": "25", "name": "Caramel Brulee Frappuccino", "type": "Frappuccino Blended Beverage", "calories": "410", "totalfat": "13g", "protein": "4g" }, { "id": "26", "name": "Caramel Brulee Frappuccino Light", "type": "Frappuccino Blended Beverage", "calories": "190", "totalfat": "0g", "protein": "3g" }, { "id": "27", "name": "Eggnog Frappuccino", "type": "Frappuccino Blended Beverage", "calories": "420", "totalfat": "18g", "protein": "7g" }, { "id": "28", "name": "Mocha Frappuccino", "type": "Frappuccino Blended Beverage", "calories": "400", "totalfat": "15g", "protein": "5g" }, { "id": "29", "name": "Tazo Green Tea Creme Frappuccino", "type": "Frappuccino Blended Beverage", "calories": "430", "totalfat": "16g", "protein": "6g" }]Setting JSON data source can be done like that:
dataSource: [ { "EmployeeID": 2, "FirstName": "Andrew", "LastName": "Fuller", "Country": "USA", "Title": "Vice President, Sales", "HireDate": "1992-08-14 00:00:00", "BirthDate": "1952-02-19 00:00:00", "City": "Tacoma", "Address": "908 W. Capital Way"}, { "EmployeeID": 8, "FirstName": "Laura", "LastName": "Callahan", "Country": "USA", "Title": "Inside Sales Coordinator", "HireDate": "1994-03-05 00:00:00", "BirthDate": "1958-01-09 00:00:00", "City": "Seattle", "Address": "4726 - 11th Ave. N.E." }, { "EmployeeID": 1, "FirstName": "Nancy", "LastName": "Davolio", "Country": "USA", "Title": "Sales Representative", "HireDate": "1992-05-01 00:00:00", "BirthDate": "1948-12-08 00:00:00", "City": "Seattle", "Address": "507 - 20th Ave. E.Apt. 2A" }, { "EmployeeID": 3, "FirstName": "Janet", "LastName": "Leverling", "Country": "USA", "Title": "Sales Representative", "HireDate": "1992-04-01 00:00:00", "BirthDate": "1963-08-30 00:00:00", "City": "Kirkland", "Address": "722 Moss Bay Blvd." }, { "EmployeeID": 4, "FirstName": "Margaret", "LastName": "Peacock", "Country": "USA", "Title": "Sales Representative", "HireDate": "1993-05-03 00:00:00", "BirthDate": "1937-09-19 00:00:00", "City": "Redmond", "Address": "4110 Old Redmond Rd." }, { "EmployeeID": 5, "FirstName": "Steven", "LastName": "Buchanan", "Country": "UK", "Title": "Sales Manager", "HireDate": "1993-10-17 00:00:00", "BirthDate": "1955-03-04 00:00:00", "City": "London", "Address": "14 Garrett Hill"} ]From JSON, you can build Tree Grid, by using Nested JSON. In that case, the
childrenDataField
property should be set to specify the field that defines the sub-level items.
dataSource: new Smart.DataAdapter( { dataSource: [ { "EmployeeID": 2, "FirstName": "Andrew", "LastName": "Fuller", "Country": "USA", "Title": "Vice President, Sales", "HireDate": "1992-08-14 00:00:00", "BirthDate": "1952-02-19 00:00:00", "City": "Tacoma", "Address": "908 W. Capital Way", "expanded": "true", items: [ { "EmployeeID": 8, "FirstName": "Laura", "LastName": "Callahan", "Country": "USA", "Title": "Inside Sales Coordinator", "HireDate": "1994-03-05 00:00:00", "BirthDate": "1958-01-09 00:00:00", "City": "Seattle", "Address": "4726 - 11th Ave. N.E." }, { "EmployeeID": 1, "FirstName": "Nancy", "LastName": "Davolio", "Country": "USA", "Title": "Sales Representative", "HireDate": "1992-05-01 00:00:00", "BirthDate": "1948-12-08 00:00:00", "City": "Seattle", "Address": "507 - 20th Ave. E.Apt. 2A" }, { "EmployeeID": 3, "FirstName": "Janet", "LastName": "Leverling", "Country": "USA", "Title": "Sales Representative", "HireDate": "1992-04-01 00:00:00", "BirthDate": "1963-08-30 00:00:00", "City": "Kirkland", "Address": "722 Moss Bay Blvd." }, { "EmployeeID": 4, "FirstName": "Margaret", "LastName": "Peacock", "Country": "USA", "Title": "Sales Representative", "HireDate": "1993-05-03 00:00:00", "BirthDate": "1937-09-19 00:00:00", "City": "Redmond", "Address": "4110 Old Redmond Rd." }, { "EmployeeID": 5, "FirstName": "Steven", "LastName": "Buchanan", "Country": "UK", "Title": "Sales Manager", "HireDate": "1993-10-17 00:00:00", "BirthDate": "1955-03-04 00:00:00", "City": "London", "Address": "14 Garrett Hill", "expanded": "true", items: [ { "EmployeeID": 6, "FirstName": "Michael", "LastName": "Suyama", "Country": "UK", "Title": "Sales Representative", "HireDate": "1993-10-17 00:00:00", "BirthDate": "1963-07-02 00:00:00", "City": "London", "Address": "Coventry House Miner Rd." }, { "EmployeeID": 7, "FirstName": "Robert", "LastName": "King", "Country": "UK", "Title": "Sales Representative", "HireDate": "1994-01-02 00:00:00", "BirthDate": "1960-05-29 00:00:00", "City": "London", "Address": "Edgeham Hollow Winchester Way" }, { "EmployeeID": 9, "FirstName": "Anne", "LastName": "Dodsworth", "Country": "UK", "Title": "Sales Representative", "HireDate": "1994-11-15 00:00:00", "BirthDate": "1966-01-27 00:00:00", "City": "London", "Address": "7 Houndstooth Rd." } ] } ] } ], childrenDataField: 'items', id: 'EmployeeID', dataFields: [ 'EmployeeID: number', 'ReportsTo: number', 'FirstName: string', 'LastName: string', 'Country: string', 'City: string', 'Address: string', 'Title: string', 'HireDate: date', 'BirthDate: date' ] })Tree Grid with Nested JSON
Tree Grid from flat JSON
Parent-child relationship can be created from flat data. In that case, the dataAdapter and the dataSourceSettings havekeyDataField
and parentDataField
properties. Look at the example below. We have a flat data and the tree hierarchy is defined by using a pair of properties - EmployeeID and ReportsTo
dataSource: new Smart.DataAdapter( { dataSource: [ { "EmployeeID": 1, "FirstName": "Nancy", "LastName": "Davolio", "ReportsTo": 2, "Country": "USA", "Title": "Sales Representative", "HireDate": "1992-05-01 00:00:00", "BirthDate": "1948-12-08 00:00:00", "City": "Seattle", "Address": "507 - 20th Ave. E.Apt. 2A" }, { "EmployeeID": 2, "FirstName": "Andrew", "LastName": "Fuller", "ReportsTo": null, "Country": "USA", "Title": "Vice President, Sales", "HireDate": "1992-08-14 00:00:00", "BirthDate": "1952-02-19 00:00:00", "City": "Tacoma", "Address": "908 W. Capital Way" }, { "EmployeeID": 3, "FirstName": "Janet", "LastName": "Leverling", "ReportsTo": 2, "Country": "USA", "Title": "Sales Representative", "HireDate": "1992-04-01 00:00:00", "BirthDate": "1963-08-30 00:00:00", "City": "Kirkland", "Address": "722 Moss Bay Blvd." }, { "EmployeeID": 4, "FirstName": "Margaret", "LastName": "Peacock", "ReportsTo": 2, "Country": "USA", "Title": "Sales Representative", "HireDate": "1993-05-03 00:00:00", "BirthDate": "1937-09-19 00:00:00", "City": "Redmond", "Address": "4110 Old Redmond Rd." }, { "EmployeeID": 5, "FirstName": "Steven", "LastName": "Buchanan", "ReportsTo": 2, "Country": "UK", "Title": "Sales Manager", "HireDate": "1993-10-17 00:00:00", "BirthDate": "1955-03-04 00:00:00", "City": "London", "Address": "14 Garrett Hill" }, { "EmployeeID": 6, "FirstName": "Michael", "LastName": "Suyama", "ReportsTo": 5, "Country": "UK", "Title": "Sales Representative", "HireDate": "1993-10-17 00:00:00", "BirthDate": "1963-07-02 00:00:00", "City": "London", "Address": "Coventry House Miner Rd." }, { "EmployeeID": 7, "FirstName": "Robert", "LastName": "King", "ReportsTo": 5, "Country": "UK", "Title": "Sales Representative", "HireDate": "1994-01-02 00:00:00", "BirthDate": "1960-05-29 00:00:00", "City": "London", "Address": "Edgeham Hollow Winchester Way" }, { "EmployeeID": 8, "FirstName": "Laura", "LastName": "Callahan", "ReportsTo": 2, "Country": "USA", "Title": "Inside Sales Coordinator", "HireDate": "1994-03-05 00:00:00", "BirthDate": "1958-01-09 00:00:00", "City": "Seattle", "Address": "4726 - 11th Ave. N.E." }, { "EmployeeID": 9, "FirstName": "Anne", "LastName": "Dodsworth", "ReportsTo": 5, "Country": "UK", "Title": "Sales Representative", "HireDate": "1994-11-15 00:00:00", "BirthDate": "1966-01-27 00:00:00", "City": "London", "Address": "7 Houndstooth Rd." } ], keyDataField: 'EmployeeID', parentDataField: 'ReportsTo', id: 'EmployeeID', dataFields: [ 'EmployeeID: number', 'ReportsTo: number', 'FirstName: string', 'LastName: string', 'Country: string', 'City: string', 'Address: string', 'Title: string', 'HireDate: date', 'BirthDate: date' ] })Tree Grid
Data Bind with PHP
dataSource: new Smart.DataAdapter({ dataSource: 'grid_data.php', dataSourceType: 'json', dataFields: [ 'CompanyName: string', 'ContactName: string', 'ContactTitle: string', 'Address: string', 'City: string', 'Country: string' ] })The above code shows how to populate the Grid with data from MySQL server. grid_data.php
<?php // Include the connect.php file include ('../../../scripts/connect.php'); // Connect to the database // connection String $mysqli = new mysqli($hostname, $username, $password, $database); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } // get data and store in a json array $from = 0; $to = 100; $query = "SELECT CompanyName, ContactName, ContactTitle, Country, Address, City FROM Customers LIMIT ?,?"; $result = $mysqli->prepare($query); $result->bind_param('ii', $from, $to); $result->execute(); /* bind result variables */ $result->bind_result($CompanyName, $ContactName, $ContactTitle, $Country, $Address, $City); /* fetch values */ while ($result->fetch()) { $orders[] = array( 'CompanyName' => $CompanyName, 'ContactName' => $ContactName, 'ContactTitle' => $ContactTitle, 'Country' => $Country, 'Address' => $Address, 'City' => $City ); } echo json_encode($orders); /* close statement */ $result->close(); /* close connection */ $mysqli->close(); ?>connect.php
<?php # FileName="connect.php" $hostname = "; $database = ""; $username = ""; $password = ""; error_reporting(E_ERROR | E_PARSE); ?>
Let's extend the above example, by adding Filter, Sorting and Paging. That can be achieved by using the
virtualDataSource
property.
appearance: { alternationStart:0, alternationCount: 2 }, sorting: { enabled: true }, filtering: { enabled: true }, pager: { visible: true }, paging: { enabled: true }, dataSource: new Smart.DataAdapter( { virtualDataSource: function ( resultCallbackFunction: any, details: any ) { const sqlQuery = details.query; new Smart.Ajax({ url: 'grid_data.php', dataSourceType: 'json', data: details.query }, (response: any) => { resultCallbackFunction( { dataSource: JSON.parse(response.data), virtualDataSourceLength: parseInt(response.length) } ); }); }, dataFields: [ 'CompanyName: string', 'ContactName: string', 'ContactTitle: string', 'Address: string', 'City: string', 'Country: string' ] } ), columns: [ { label: 'Company Name', dataField: 'CompanyName' }, { label: 'Contact Name', dataField: 'ContactName' }, { label: 'Contact Title', dataField: 'ContactTitle' }, { label: 'Address', dataField: 'Address' }, { label: 'City', dataField: 'City' }, { label: 'Country', dataField: 'Country' } ] };In the above code, whenever the Grid needs data, it requests it by calling the the 'virtualDataSource' function. The function has two arguments - details which is a JSON object and 'resultCallbackFunction', which we call when we are ready to provide the new data to the Grid. Online example: Grid with Mysql on demand
<?php // Include the connect.php file include ('../../../scripts/connect.php'); // Connect to the database // connection String $mysqli = new mysqli($hostname, $username, $password, $database); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } // get data and store in a json array $query = "SELECT CompanyName, ContactName, ContactTitle, Country, Address, City FROM Customers" . $_GET[ 'where' ] . $_GET[ 'groupBy' ] . $_GET[ 'orderBy' ] . $_GET[ 'limit' ]; $sqlCount = "SELECT COUNT(CustomerID) as length from Customers" . $_GET[ 'where' ]; if (!$_GET[ "groupBy" ]) { // $sqlCount = "SELECT COUNT(DISTINCT " . $_GET[ "groupBy" ] . ") as length from ? " . $_GET[ "where" ]; } $result = $mysqli->prepare($query); $result->execute(); /* bind result variables */ $result->bind_result($CompanyName, $ContactName, $ContactTitle, $Country, $Address, $City); /* fetch values */ while ($result->fetch()) { $data[] = array( 'CompanyName' => $CompanyName, 'ContactName' => $ContactName, 'ContactTitle' => $ContactTitle, 'Country' => $Country, 'Address' => $Address, 'City' => $City ); } $result = $mysqli->prepare($sqlCount); $result->execute(); /* bind result variables */ $result->bind_result($length); /* fetch values */ $count = $result->fetch(); $serverData["data"] = json_encode($data); $serverData["length"] = json_encode($length); echo json_encode($serverData); /* close statement */ $result->close(); /* close connection */ $mysqli->close(); ?>
Data Mapping
An important feature in the data binding is the 'map' property. The dataFields array may contain not only stings, but Objects with the following structure { name, map, dataType }. The 'map' property allows you to define fields which get data from sub-level fields. For example:dataSource: new Smart.DataAdapter( { dataSource: [{ "empName": "test", "age": "67", "department": { "id": "1234", "name": "Sales" }, "author": "ravi"}], dataSourceType: 'json', dataFields: [ { name: 'empName', datAType: 'string' }, { name: 'age', dataType: 'number' }, { name: 'id', dataType: 'number', map: 'department.id' }, { name: 'name', dataType: 'string', map: 'department.name' }, { name: 'author', dataType: 'string' } ] })
![Grid mapping](./grid-mapping.png)
Date Parsing
The Grid works with Date objects and so the data source expects a Date object for the date fields. However, in many cases, the data comes from the backend as a string and we internally deserialize the data by using: new Date(yourDataString). This is problematic when your data does not contain time-zone information. The new Date(yourDataString) is creating a local date specific to your time-zone settings. In order to avoid that, you can use the 'formatString' property of the dataField.formatString - Sets the data formatting. By setting the format, the DataAdapter will try to parse the data in the specific date format. Example: { name: 'SubmitDate', dataType: 'date', formatString: "yyyy-MM-ddTHH:mm:ss-HH:mm" }
Another useful case is to parse a specific date using the specific format. For example, if we have a date like: "01/06/1954". We can read the date as 1th June 1954 or 6th January 1954. To deserialize the date in the specific format, we use the 'formatString' property and pass the date format.
dataSource: new Smart.DataAdapter({ dataSource: [{ "C1": "1", "C2": "Hot Chocolate", "C4”": "SEAVISION", "C6": "01/06/1954", "C5": "true", "C3": "14g" }], dataFields: [ { name: 'C1', dataType: 'number' }, { name: 'C2', dataType: 'string' }, { name: 'C4', dataType: 'string' }, { name: 'C5', dataType: 'bool' }, { name: 'C3', dataType: 'string' }, { name: 'C6', dataType: 'date', formatString: 'dd/MM/yyyy' } ] })
CSV/TSV
To bind to CSV/TSV data, just set the dataSource property to CSV/TSV URL and define the dataFields array. dataSource: new Smart.DataAdapter({ dataSource: '../../scripts/employees.csv', dataFields: [ { name: 'EmployeeKey', dataType: 'number' }, { name: 'ParentEmployeeKey', dataType: 'number' }, { name: 'FirstName', dataType: 'string' }, { name: 'LastName', dataType: 'string' }, { name: 'Title', dataType: 'string' }, { name: 'HireDate', dataType: 'date' }, { name: 'BirthDate', dataType: 'date' }, { name: 'Phone', dataType: 'string' }, { name: 'Gender', dataType: 'string' }, { name: 'DepartmentName', dataType: 'string' } ] })
XML
To bind to XML data, you need to set the dataSource, dataFields, root and record properties of the dataAdapter or dataSourceSettings.dataSource: new Smart.DataAdapter({ dataSource: '../../scripts/customers.xml', root: 'entry', record: 'content', id: 'CustomerID', dataFields: [ { name: 'CompanyName', dataType: 'string' }, { name: 'ContactName', dataType: 'string' }, { name: 'ContactTitle', dataType: 'string' }, { name: 'City', dataType: 'string' }, { name: 'PostalCode',dataType: 'string' }, { name: 'Country', dataType: 'string' } ] }), columns: [ { label: 'Company Name', dataField: 'CompanyName', width: 250 }, { label: 'Contact Name', dataField: 'ContactName', width: 150 }, { label: 'Contact Title', dataField: 'ContactTitle', width: 180 }, { label: 'City', dataField: 'City', width: 120 }, { label: 'Postal Code', dataField: 'PostalCode', width: 90 }, { label: 'Country', dataField: 'Country' } ]
customers.xml
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <feed xml:base="http://services.odata.org/Northwind/Northwind.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom"> <title type="text">Customers</title> <updated>2011-11-30T11:39:28Z</updated> <link rel="self" title="Customers" href="Customers" /> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>ALFKI</d:CustomerID> <d:CompanyName>Alfreds Futterkiste</d:CompanyName> <d:ContactName>Maria Anders</d:ContactName> <d:ContactTitle>Sales Representative</d:ContactTitle> <d:Address>Obere Str. 57</d:Address> <d:City>Berlin</d:City> <d:Region m:null="true" /> <d:PostalCode>12209</d:PostalCode> <d:Country>Germany</d:Country> <d:Phone>030-0074321</d:Phone> <d:Fax>030-0076545</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>ANATR</d:CustomerID> <d:CompanyName>Ana Trujillo Emparedados y helados</d:CompanyName> <d:ContactName>Ana Trujillo</d:ContactName> <d:ContactTitle>Owner</d:ContactTitle> <d:Address>Avda. de la Constitución 2222</d:Address> <d:City>México D.F.</d:City> <d:Region m:null="true" /> <d:PostalCode>05021</d:PostalCode> <d:Country>Mexico</d:Country> <d:Phone>(5) 555-4729</d:Phone> <d:Fax>(5) 555-3745</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>ANTON</d:CustomerID> <d:CompanyName>Antonio Moreno Taquería</d:CompanyName> <d:ContactName>Antonio Moreno</d:ContactName> <d:ContactTitle>Owner</d:ContactTitle> <d:Address>Mataderos 2312</d:Address> <d:City>México D.F.</d:City> <d:Region m:null="true" /> <d:PostalCode>05023</d:PostalCode> <d:Country>Mexico</d:Country> <d:Phone>(5) 555-3932</d:Phone> <d:Fax m:null="true" /> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>AROUT</d:CustomerID> <d:CompanyName>Around the Horn</d:CompanyName> <d:ContactName>Thomas Hardy</d:ContactName> <d:ContactTitle>Sales Representative</d:ContactTitle> <d:Address>120 Hanover Sq.</d:Address> <d:City>London</d:City> <d:Region m:null="true" /> <d:PostalCode>WA1 1DP</d:PostalCode> <d:Country>UK</d:Country> <d:Phone>(171) 555-7788</d:Phone> <d:Fax>(171) 555-6750</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BERGS</d:CustomerID> <d:CompanyName>Berglunds snabbköp</d:CompanyName> <d:ContactName>Christina Berglund</d:ContactName> <d:ContactTitle>Order Administrator</d:ContactTitle> <d:Address>Berguvsvägen 8</d:Address> <d:City>Luleå</d:City> <d:Region m:null="true" /> <d:PostalCode>S-958 22</d:PostalCode> <d:Country>Sweden</d:Country> <d:Phone>0921-12 34 65</d:Phone> <d:Fax>0921-12 34 67</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BLAUS</d:CustomerID> <d:CompanyName>Blauer See Delikatessen</d:CompanyName> <d:ContactName>Hanna Moos</d:ContactName> <d:ContactTitle>Sales Representative</d:ContactTitle> <d:Address>Forsterstr. 57</d:Address> <d:City>Mannheim</d:City> <d:Region m:null="true" /> <d:PostalCode>68306</d:PostalCode> <d:Country>Germany</d:Country> <d:Phone>0621-08460</d:Phone> <d:Fax>0621-08924</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BLONP</d:CustomerID> <d:CompanyName>Blondesddsl père et fils</d:CompanyName> <d:ContactName>Frédérique Citeaux</d:ContactName> <d:ContactTitle>Marketing Manager</d:ContactTitle> <d:Address>24, place Kléber</d:Address> <d:City>Strasbourg</d:City> <d:Region m:null="true" /> <d:PostalCode>67000</d:PostalCode> <d:Country>France</d:Country> <d:Phone>88.60.15.31</d:Phone> <d:Fax>88.60.15.32</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BOLID</d:CustomerID> <d:CompanyName>Bólido Comidas preparadas</d:CompanyName> <d:ContactName>Martín Sommer</d:ContactName> <d:ContactTitle>Owner</d:ContactTitle> <d:Address>C/ Araquil, 67</d:Address> <d:City>Madrid</d:City> <d:Region m:null="true" /> <d:PostalCode>28023</d:PostalCode> <d:Country>Spain</d:Country> <d:Phone>(91) 555 22 82</d:Phone> <d:Fax>(91) 555 91 99</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BONAP</d:CustomerID> <d:CompanyName>Bon app'</d:CompanyName> <d:ContactName>Laurence Lebihan</d:ContactName> <d:ContactTitle>Owner</d:ContactTitle> <d:Address>12, rue des Bouchers</d:Address> <d:City>Marseille</d:City> <d:Region m:null="true" /> <d:PostalCode>13008</d:PostalCode> <d:Country>France</d:Country> <d:Phone>91.24.45.40</d:Phone> <d:Fax>91.24.45.41</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BOTTM</d:CustomerID> <d:CompanyName>Bottom-Dollar Markets</d:CompanyName> <d:ContactName>Elizabeth Lincoln</d:ContactName> <d:ContactTitle>Accounting Manager</d:ContactTitle> <d:Address>23 Tsawassen Blvd.</d:Address> <d:City>Tsawassen</d:City> <d:Region>BC</d:Region> <d:PostalCode>T2F 8M4</d:PostalCode> <d:Country>Canada</d:Country> <d:Phone>(604) 555-4729</d:Phone> <d:Fax>(604) 555-3745</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>BSBEV</d:CustomerID> <d:CompanyName>B's Beverages</d:CompanyName> <d:ContactName>Victoria Ashworth</d:ContactName> <d:ContactTitle>Sales Representative</d:ContactTitle> <d:Address>Fauntleroy Circus</d:Address> <d:City>London</d:City> <d:Region m:null="true" /> <d:PostalCode>EC2 5NT</d:PostalCode> <d:Country>UK</d:Country> <d:Phone>(171) 555-1212</d:Phone> <d:Fax m:null="true" /> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>CACTU</d:CustomerID> <d:CompanyName>Cactus Comidas para llevar</d:CompanyName> <d:ContactName>Patricio Simpson</d:ContactName> <d:ContactTitle>Sales Agent</d:ContactTitle> <d:Address>Cerrito 333</d:Address> <d:City>Buenos Aires</d:City> <d:Region m:null="true" /> <d:PostalCode>1010</d:PostalCode> <d:Country>Argentina</d:Country> <d:Phone>(1) 135-5555</d:Phone> <d:Fax>(1) 135-4892</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>CENTC</d:CustomerID> <d:CompanyName>Centro comercial Moctezuma</d:CompanyName> <d:ContactName>Francisco Chang</d:ContactName> <d:ContactTitle>Marketing Manager</d:ContactTitle> <d:Address>Sierras de Granada 9993</d:Address> <d:City>México D.F.</d:City> <d:Region m:null="true" /> <d:PostalCode>05022</d:PostalCode> <d:Country>Mexico</d:Country> <d:Phone>(5) 555-3392</d:Phone> <d:Fax>(5) 555-7293</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>CHOPS</d:CustomerID> <d:CompanyName>Chop-suey Chinese</d:CompanyName> <d:ContactName>Yang Wang</d:ContactName> <d:ContactTitle>Owner</d:ContactTitle> <d:Address>Hauptstr. 29</d:Address> <d:City>Bern</d:City> <d:Region m:null="true" /> <d:PostalCode>3012</d:PostalCode> <d:Country>Switzerland</d:Country> <d:Phone>0452-076545</d:Phone> <d:Fax m:null="true" /> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>COMMI</d:CustomerID> <d:CompanyName>Comércio Mineiro</d:CompanyName> <d:ContactName>Pedro Afonso</d:ContactName> <d:ContactTitle>Sales Associate</d:ContactTitle> <d:Address>Av. dos Lusíadas, 23</d:Address> <d:City>Sao Paulo</d:City> <d:Region>SP</d:Region> <d:PostalCode>05432-043</d:PostalCode> <d:Country>Brazil</d:Country> <d:Phone>(11) 555-7647</d:Phone> <d:Fax m:null="true" /> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>CONSH</d:CustomerID> <d:CompanyName>Consolidated Holdings</d:CompanyName> <d:ContactName>Elizabeth Brown</d:ContactName> <d:ContactTitle>Sales Representative</d:ContactTitle> <d:Address>Berkeley Gardens 12 Brewery</d:Address> <d:City>London</d:City> <d:Region m:null="true" /> <d:PostalCode>WX1 6LT</d:PostalCode> <d:Country>UK</d:Country> <d:Phone>(171) 555-2282</d:Phone> <d:Fax>(171) 555-9199</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>DRACD</d:CustomerID> <d:CompanyName>Drachenblut Delikatessen</d:CompanyName> <d:ContactName>Sven Ottlieb</d:ContactName> <d:ContactTitle>Order Administrator</d:ContactTitle> <d:Address>Walserweg 21</d:Address> <d:City>Aachen</d:City> <d:Region m:null="true" /> <d:PostalCode>52066</d:PostalCode> <d:Country>Germany</d:Country> <d:Phone>0241-039123</d:Phone> <d:Fax>0241-059428</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>DUMON</d:CustomerID> <d:CompanyName>Du monde entier</d:CompanyName> <d:ContactName>Janine Labrune</d:ContactName> <d:ContactTitle>Owner</d:ContactTitle> <d:Address>67, rue des Cinquante Otages</d:Address> <d:City>Nantes</d:City> <d:Region m:null="true" /> <d:PostalCode>44000</d:PostalCode> <d:Country>France</d:Country> <d:Phone>40.67.88.88</d:Phone> <d:Fax>40.67.89.89</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>EASTC</d:CustomerID> <d:CompanyName>Eastern Connection</d:CompanyName> <d:ContactName>Ann Devon</d:ContactName> <d:ContactTitle>Sales Agent</d:ContactTitle> <d:Address>35 King George</d:Address> <d:City>London</d:City> <d:Region m:null="true" /> <d:PostalCode>WX3 6FW</d:PostalCode> <d:Country>UK</d:Country> <d:Phone>(171) 555-0297</d:Phone> <d:Fax>(171) 555-3373</d:Fax> </m:properties> </content> </entry> <entry> <title type="text"></title> <updated>2011-11-30T11:39:28Z</updated> <author> <name /> </author> <content type="application/xml"> <m:properties> <d:CustomerID>ERNSH</d:CustomerID> <d:CompanyName>Ernst Handel</d:CompanyName> <d:ContactName>Roland Mendel</d:ContactName> <d:ContactTitle>Sales Manager</d:ContactTitle> <d:Address>Kirchgasse 6</d:Address> <d:City>Graz</d:City> <d:Region m:null="true" /> <d:PostalCode>8010</d:PostalCode> <d:Country>Austria</d:Country> <d:Phone>7675-3425</d:Phone> <d:Fax>7675-3426</d:Fax> </m:properties> </content> </entry> </feed>Example: Data bind to XML
Server side Model
Smart.Grid supports data bind on demand. When a new row is added, removed or updated a request with CRUD data object is made via the 'virtualDataSource' dataAdapter or dataSourceSettings property on the Server-Side Datasource. The server uses the SQL query sent by the Server-Side Datasource to get the rows. The type of query is stored in the request's action property. Theaction
property could be 'dataBind', 'filter', 'sort', 'group', 'add', 'remove', 'update', 'expand', 'pageIndexChange', 'pageSizeChange' or 'scroll'. details.first and details.last contains the first and last index of requested rows in the view. Other properties of the details JSON object are:
'edit', 'sorting', 'filtering', 'grouping', 'row', which contain the current state of the Grid.
Example with sorting object:
sorting: [{ CustomerName: { sortIndex: null sortOrder: "asc" } }]The sorting can be by one or multiple columns and that is why it is an Array. Each object in the Array is a column field with sortIndex and sortOrder properties.
Similarly, the filtering object contains array with objects where the key is the column field.
const firstFilter = details.filtering['CustomerName'].filters[0]; const secondFilter = details.filtering['CustomerName'].filters[1];Each of these have 'value' and 'condition' properties. To learn more about filtering and sorting, please, visit: Filtering and Sorting
Example: Server-side cell edit and Server-side CRUD
Load data on expand with Grouping
dataSource: new Smart.DataAdapter({ groupBy: ['Country'], virtualDataSourceOnExpand: function (resultCallbackFunction, details) { const result = demoServer.getData(details); resultCallbackFunction({ dataSource: result.data, virtualDataSourceLength: result.length }); }, virtualDataSource: function (resultCallbackFunction, details) { const that = this; if (details.action === 'dataBind') { fetch('https://raw.githubusercontent.com/HTMLElements/smart-webcomponents/master/sampledata/customers.json').then(response => response.json()) .then(data => { window.demoServer = DemoServer(data); const result = window.demoServer.getData(details); resultCallbackFunction({ dataSource: result.data, virtualDataSourceLength: result.length }); }); } else { const result = window.demoServer.getData(details); resultCallbackFunction({ dataSource: result.data, virtualDataSourceLength: result.length }); } }, id: 'CustomerID', dataFields: [ 'CustomerID: string', 'CompanyName: string', 'ContactName: string', 'ContactTitle: string', 'Address: string', 'City: string', 'Country: string' ] }), columns: [ { label: 'Id', dataField: 'CustomerID' }, { label: 'Company Name', dataField: 'CompanyName' }, { label: 'Contact Name', dataField: 'ContactName' }, { label: 'Contact Title', dataField: 'ContactTitle' }, { label: 'Address', dataField: 'Address' }, { label: 'City', dataField: 'City' }, { label: 'Country', dataField: 'Country' } ]Example: Server-side grouping
Load data on expand with TreeGrid
virtualDataSourceOnExpand: function ( resultCallbackFunction, details ) { const result = (window.demoServer).getData( details ); // when you set the 'leaf' property to true, the row's toggle button is not displayed. for(let i = 0; i < result.data.length; i++) { if (result.data[i].EmployeeID !== 5) { result.data[i]!.leaf = true; } } resultCallbackFunction( { dataSource: result.data, virtualDataSourceLength: result.length } ); }, virtualDataSource: function ( resultCallbackFunction, details ) { if ( details.action === 'dataBind' ) { window.demoServer = DemoServer( ); const result = (window.demoServer).getData( details ); window.data = (window.demoServer).executeQuery( 'SELECT * FROM Employees' ); resultCallbackFunction( { dataSource: result.data, virtualDataSourceLength: result.length } ); } else { const that = this; let data = window.data as any; window.query.innerHTML = ''; // Sorts the data. if (details.sorting.length > 0 && (details.action === 'sort' || details.action === 'dataBind')){ let sortColumns = []; let sortOrders = []; for(let dataField in details.sorting) { const sortOrder = details.sorting[dataField].sortOrder; sortColumns.push(dataField); sortOrders.push(sortOrder); } that.sortedData = window.Smart.DataAdapter.Sort(data, sortColumns, sortOrders); } else if (details.sorting.length === 0) { that.sortedData = null; } if (that.sortedData) { data = that.sortedData; } // Filters the data. if (details.filtering.length > 0 && (details.action === 'sort' || details.action === 'filter' || details.action === 'dataBind')) { let filterColumns = []; let filters = []; for(let dataField in details.filtering) { const filter = details.filtering[dataField]; filterColumns.push(dataField); filters.push(filter); } that.filteredData = window.Smart.DataAdapter.Filter(data, filterColumns, filters); } else if (details.filtering.length === 0) { that.filteredData = null; } if (that.filteredData) { data = that.filteredData; } // when you set the 'leaf' property to true, the row's toggle button is not displayed. for(let i = 0; i < data.length; i++) { if (data[i].EmployeeID !== 5 && data[i].EmployeeID !== 2) { data[i].leaf = true; } } resultCallbackFunction( { dataSource: data, virtualDataSourceLength: data.length } ); } }Example: Server-side Tree Grid.
To sync your DataGrid with backend, you can alternatively use callback functions. The Smart.Grid component has the following callbacks for CRUD: onRowRemoved(ids), onRowInserted(indexes, rows, callback), onRowUpdate(indexes, rows, oldDatas, newDatas, callback), onCellUpdate(indexes, oldValues, values, callback) Each row argument has 'id'. In the 'onRowInserted' you can set each row.id to the ID which comes from your backend and then call the callback() to confirm the action. onRowUpdate and onCellUpdate are called before the changes are saved so you can use callback(true) or callback(false) to confirm or deny the edit changes. Note that if you subscribe to 'onCellUpdate', the 'onRowUpdate' would not be called, becuase 'onCellUpdate' is more specific and is for each cell in the row.