(untagged)

.NET Core Datagrid

Bart-Jan Brouwer

0.00/5 (No votes)

18 Jan 2017

.Net Core datagrid with server side paging, sorting and filtering

Showing data in grid format is an important task for many web applications. This blog gives a demo of how to display data with the Bootstrap Table plug in. The demo shows advanced features like server side paging, filtering and sorting.

With the demo application, I cover these aspects in more detail:

Setup Bootstrap Table plug in
Setup DataSource
Table definition in cshtml file
Custom cell rendering
Server side paging, sorting and filtering
Highlight selected row
Custom Toolbar
Additional Load parameters

Bootstrap Table plug in

There are many data grid packages available, all with their own strengths and weaknesses. In this demo, I use the Bootstrap Table plug in. It's a free plug in with useful features:

Ajax enabled
Server side paging, filtering and sorting
Easy to use
Lightweight and fast
Bootstrap support
Third party packages are available for extra functionality

Setup Bootstrap Table plug in

Start Visual Studio and create a new .NET Core Project with no authentication.

Bootstrap Table is a JavaScript library and you only need to include the library files in the application. It has two files, a JavaScript and a CSS file. The rendersection is set at the end of the _Layout.cshtml file. The rendersection provides a hook to an individual cshtml page. This hook is executed during the rendering of the individual cshtml page.

  ...
  @RenderSection("scripts", required: false)
</body>
</html>

...
@section scripts { 
  @await Html.PartialAsync("bootstraptable")
 
  <script type="text/javascript">
  ...

This chain renders the shared bootstraptable.cshtml, located in the shared views folder. This makes re-use in other pages easy and the files are just in time loaded for optimal performance. Dot Net Core offers a neat solution to differentiate between production and development files. In development, you can use the larger, easy readable files, while for production, you automatically switch to the smaller and faster, minified files.

<environment names="Development">
  <link rel="stylesheet" href="~/css/bootstrap-table.css">
  <script src="~/lib/bootstrap-table/bootstrap-table.js"></script>
</environment>
<environment names="Staging,Production">
  <link rel="stylesheet" 
   href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-table/1.11.0/bootstrap-table.min.css">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-table/1.11.0/bootstrap-table.min.js">
  </script>
</environment>

Setup DataSource

In most cases, the data is retrieved from database server or a REST-api. This demo focuses on how to show data and less on how to get data. In this case, the data is fetched from a simple JSON file, located at the web server. This keeps the datasource simple and easy to setup. The controller has a private IList<Country> countries property. Country is a simple POCO class:

public class Country
{
  [Key()]
  public Int32 Code { get; set; }

  [StringLength(2, MinimumLength =2)]
  [Display(Name="2 Digit ISO")]
  public String ISO2{ get; set; }

  [StringLength(3, MinimumLength = 3)]
  [Display(Name = "3 Digit ISO")]
  public String ISO3 { get; set; }

  public String Name { get; set; }
}

Entity Framework Support

This sample uses IList<Country> as datasource, but it can easily be replaced by a DbSet<Country> delivered by the Entity Framework. The demo works with every kind of datasource, provided the datasource can be cast to the none generic IQueryable type.

Table Definition in cshtml File

You can setup the table definition in either HTML or jquery. I prefer HTML because I find it easier to read. A combination is also possible. In this demo, HTML sets the table layout and the table events are handled in jquery. Data attributes in the <table> section sets the table behavior, the columns and rows are configured in the <tr> section.

<table id="table"
         data-unique-id="Code"
         data-sort-name="Code"
         data-sort-order="asc"
         data-classes="table table-condensed table-hover table-striped"
         data-toggle="table"
         data-side-pagination="server"
         data-url="Load"
         data-pagination="true"
         data-search="true"
         data-show-refresh="true"
         data-toolbar="#toolbar"
         data-page-size="20"
         data-page-list="[5,10,20,50,100,All]">
    <thead>
      <tr>
        <th data-field="ISO2" data-sortable="false" 
        data-halign="center" data-align="center" 
         data-formatter="flagFormatter">Flag</th>
        <th data-field="Code" data-sortable="true" 
        data-halign="center" data-align="center">Code</th>
        <th data-field="ISO3" data-sortable="true">ISO 3</th>
        <th data-field="Name" data-sortable="true">Name</th>
      </tr>
    </thead>
  </table>

Column settings like alignment, width, date and number formats can be configured with attributes. The data-unique-id="Code" sets the primary key column. During rendering, each table row gets a data-uniqueid attribute with its key value.

<tr data-uniqueid="4" data-index="0">
  ...
</tr>

This key attribute is vital for CRUD (Create, Retrieve, Update and Delete) operations. With jquery, you can retrieve the key value from a row with little effort. If the data-unique-id is empty or not set, the table will still be rendered. The data-sort-name and data-sort-order attributes set the initial sort column and order. These values are passed to the controller during the data request.

Custom Cell Rendering

Bootstrap Table supports custom cell rendering with the data-formatter attribute. This offers maximum flexibility if the standard configuration options are not enough.

function flagFormatter(value, row) {
     return '<img src="/images/flags/' + value.toLowerCase() + '.png" >';
   }

The value parameter is the column value and row parameter contains all the row values.

Server Side Paging, Sorting and Filtering

It takes only a few attributes to setup server side paging, sorting and filtering.

data-side-pagination="server"
data-url="Load"
data-pagination="true"
data-search="true"

The data-url="Load" attribute specifies that the controller class has a public Load function. The Load function handles the input parameters and returns a JSON document.

[HttpGet]
public virtual ActionResult Load(String sort, String order, String search, Int32 limit, Int32 offset)
{
   // Get entity fieldnames
   List<String> columnNames = typeof(Country).GetProperties(BindingFlags.Public | 
                              BindingFlags.Instance).Select(p => p.Name).ToList();

   // Create a separate list for searchable field names   
   List<String> searchFields = new List<String>(columnNames);

   // Exclude field Iso2 for filtering 
   searchFields.Remove("ISO2");

   // Perform filtering
   IQueryable items = SearchItems(countries.AsQueryable(), search, searchFields);

   // Sort the filtered items and apply paging
   return Content(ItemsToJson
   (items, columnNames, sort, order, limit, offset), "application/json");
}

Input parameters:

sort: sort column name
order: sort direction, asc or desc
search: search argument entered by user
limit: page size
offset: number of records to skip before fetching the data page

JSON Output:

total: number of records available after filtering
rows: array with country records

The array capacity is equal to or less than the limit page size.

"total": 193,
"rows": [
  {
    "Code": 4,
    "ISO2": "AF",
    "ISO3": "AFG",
    "Name": "Afghanistan"
  },
  {
    "Code": 8,
    "ISO2": "AL",
    "ISO3": "ALB",
    "Name": "Albania"
  },
 ...

Exclude Filter Fields

Field ISO2 is used for rendering the flag image, the code itself is not visible for the user. In this GUI design, the search parameter applies only for visible data. This means that the ISO2 property must be excluded from searchable fields.

// Get entity fieldnames
List<String> columnNames = typeof(Country).GetProperties(BindingFlags.Public | 
                           BindingFlags.Instance).Select(p => p.Name).ToList();

// Create a separate list for searchable field names   
List<String> searchFields = new List<String>(columnNames);

// Exclude field Iso2 for filtering 
searchFields.Remove("ISO2");

// Perform filtering
IQueryable items = SearchItems(countries.AsQueryable(), search, searchFields);

Reusable Filtering with Dynamic Linq

Linq is a great innovation. It gives the ability to execute queries on an enumerable collection. Unlike SQL, Linq has compile time syntax checking. This is helpful in most cases. It already detects errors during compile time instead of runtime. If I want to create a reusable filter method, the compile time syntax becomes an obstacle. Different entities has different fieldnames, so how to pass different field names to one reusable method? Runtime parsing instead of compile time parsing is the solution. The Dynamic Linq Core packages does just this. It smoothly integrates with Linq and provides extra overload functions for where clauses, sorting and other operations. Dynamic Linq is used in SearchItems to create a searchExpression at runtime.

protected virtual IQueryable SearchItems(IQueryable items, String search, List<String> columnNames)
{
  // Apply filtering to all visible column names
  if (search != null && search.Length > 0)
  {
    StringBuilder sb = new StringBuilder();

    // create dynamic Linq expression
    foreach (String fieldName in columnNames)
      sb.AppendFormat("({0} == null ? 
      false : {0}.ToString().IndexOf(@0, @1) >=0) or {1}", 
                                      fieldName, Environment.NewLine);

    String searchExpression = sb.ToString();
    // remove last "or" occurrence
    searchExpression = searchExpression.Substring
    (0, searchExpression.LastIndexOf("or"));

    // Apply filtering, 
    items = items.Where
    (searchExpression, search, StringComparison.OrdinalIgnoreCase);
  }
  return items;
}

Country searchExpression generated by SearchItems:

(Code == null ? false : Code.ToString().IndexOf(@0, @1) >=0) or 
(ISO3 == null ? false : ISO3.ToString().IndexOf(@0, @1) >=0) or 
(Name == null ? false : Name.ToString().IndexOf(@0, @1) >=0)

Please note the ISO2 field is not in the searchExpression as expected. In this demo, the SearchItems implementation is pretty straightforward. If a more complicated filtering is required, the SearchItems can be overridden to meet the new needs.

Generating JSON Document

The ItemsToJson function creates the JSON document that is consumed by the Bootstrap Table.

protected String ItemsToJson(IQueryable items, List<String> columnNames, 
                             String sort, String order, Int32 limit, Int32 offset)
{
  try
  {
	// where clause is set, count total records
	Int32 count = items.Count();

	// Skip requires sorting, so make sure there is always sorting
	String sortExpression = "";
   
	if (sort != null && sort.Length > 0)
	  sortExpression += String.Format("{0} {1}", sort, order);

	// show all records if limit is not set
	if (limit == 0)
	  limit = count;

	// Prepare json structure
	var result = new
	{
	  total = count,
	  rows = items.OrderBy(sortExpression).Skip(offset).Take(limit).Select
             ("new (" + String.Join(",", columnNames) + ")")
	};

	return JsonConvert.SerializeObject(result, Formatting.None, new JsonSerializerSettings() 
                  { MetadataPropertyHandling = MetadataPropertyHandling.Ignore });
  }
  catch (Exception ex)
  {
	Console.WriteLine(ex.Message);
	return null;
  }
}

Input parameters:

items: unsorted, filtered entity set
columnNames: fields included in JSON document
sort: sort column name
order: sort direction, asc or desc
search: search argument entered by user
limit: page size
offset: number of records to skip before fetching the data page

The columnNames variable limits the number of properties exposed in the JSON document. This can be useful if you don't want to show all available entities properties for performance or security reasons. The paging requires sorting and is provided by Dynamic Linq. The paging is implemented with the standard Linq Skip and Take functions. The option Formatting.None reduces the JSON document size, and increases performance, but makes it more difficult to read. I only use the option Formatting.Indented for debugging purposes.

Highlight Selected Row

Bootstrap Table has several row selection options. It can even remember selected rows on a previous page, which I think is pretty cool. You can read the documentation on how this can be done. In the demo application, I use jquery and CSS. On forums, there are many questions about this topic, so let's give it some attention here. First, I modified the CSS style to make the selected row more apparent. I could overwrite the bootstrap CSS file, but all the work would be lost in case of an update. Setting the new style in the site.css file frees you from this risk.

/* selected row style */
.table-striped tbody .highlight td {
  background-color: #b5b5b5;
}

The next step is to attach the Bootstrap Table row click event to the highLightRow function.

// register row-click event
$('#table').on('click-row.bs.table', function ($element, row, $tr) {
  highLightRow($tr);
});

The highLightRow function assigns the highlight CSS class to the selected row and removes the CSS class from all other rows. This makes sure that only one row at a time is selected.

function highLightRow($tr) {
   $tr.addClass('highlight').siblings().removeClass('highlight');
}

Custom Toolbar

With Bootstrap Table, you can customize the toolbar with just plain HTML. In some other packages, you need to know a lot about the grid details and its API. Bootstrap Table is breathtakingly simple. Create your toolbar buttons and other controls inside a div with an id. Assign this id to the data-toolbar attribute and that's all it takes!

<div id="toolbar">
  <button id="btninfo" title="Show selected row info" class="btn btn-default" type="button">
  <i class="glyphicon glyphicon-info-sign"></i>&nbsp;row info</button>
</div>>

<table id="table"
 ...
 data-toolbar="#toolbar"
 ...

Additional Load Parameters

Sometimes, the GUI requires that additional parameters are sent to the controller. This takes only a few simple steps. First, set what function injects the extra parameter with the data-query-params attribute.

<table id="table"
 ...
 data-query-params="extraServerParams"
 ...

In this demo, the extra parameter is fixed, normally, you would use the value of an input control.

function extraServerParams(params) {
   params.ExtraParam = 2;
   return params;
}

The last step is modifying the Load function on the controller to process the extra parameter.

Conclusion

Displaying data in a grid is an important requirement for many applications. Bootstrap Table does an excellent job at this. It's easy to setup and use and works well with Bootstrap and Dot Net Core. Dynamic Linq makes the solution highly reusable. I added the demo application so you can play with it. If you have any comments or questions, please let me know.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here