Auto generate columns for bootstrap table grid in MVC
5.00/5 (2 votes)
Use bootstrap tables with minimal coding on .Net Core 2.0!
Introduction
In this blog I explain how you can automatically generate bootstrap table columns. Table grids are an import part of web application however coding them can be boring and error prone. In a MVC application the controller and view with the table grid are tightly coupled. The controller delivers the the table grid data so it must be possible to extract metadata from the controller. This metadata is the source for the column generation. The column generation reduces the lines of code in a view an takes away the repetitive coding task and results in faster and better development with more fun. The generation is based on the Bootstrap table
Manual Coding
The required grid coding:
...
$(document).ready(function () {
var $table = $('#table');
// old style
$table.bootstrapTable({
toolbar: '#toolbar',
classes: 'table table-condensed table-hover table-striped',
showRefresh: true,
search: true,
pagination: true,
pageSize: 10,
pageList: [10, 25, 50, 100, 250],
url: window.location.pathname + '/load',
sortName: 'Email',
sortOrder: 'asc',
sidePagination: 'server',
cache: false,
uniqueId: 'Id',
columns: [
{
title: "Edit",
width: "40",
halign: "center",
align: "center",
"formatter": "editFormatter"
},
{
field: "Id",
title: "Id",
sortable: true,
visible: false
},
{
field: "UserName",
title: "User",
sortable: false,
visible: true
},
{
field: "Email",
title: "Email",
sortable: true,
visible: true
},
{
field: "EmailConfirmed",
title: "Confirmed",
sortable: true,
visible: true,
halign: "center",
align: "center",
formatter: "checkboxFormatter"
},
{
field: "PhoneNumber",
title: "Phone",
sortable: true,
visible: true
},
{
field: "PhoneNumberConfirmed",
title: "Confirmed",
sortable: true,
visible: true,
halign: "center",
align: "center",
formatter: "checkboxFormatter"
},
{
field: "LockoutEnd",
title: "Lockout End",
sortable: true,
visible: true
},
{
field: "LockoutEnabled",
title: "Lockout Enabled",
sortable: true,
visible: true,
halign: "center",
align: "center",
formatter: "checkboxFormatter"
},
{
field: "AccessFailedCount",
title: "Access Failed Count",
sortable: true,
visible: true,
align: "right"
},
{
title: "Del",
width: "40",
halign: "center",
align: "center",
formatter: "delFormatter"
}]
});
...
Setting up a grid manually takes a lot of effort, especially the columns.
Automatic coding
With automatic columns generation the coding becomes:
$(document).ready(function () {
var $table = $('#table');
// retrieve metadata from controller and apply results for initialization
$.ajax({
url: window.location.pathname + '/metadata',
success: function (settings) {
// apply settings from controller
$table.bootstrapTable({
sortName: settings.SortName,
sortOrder: settings.SortOrder,
sidePagination: settings.Pagination,
cache: settings.UseCache,
uniqueId: settings.Key,
columns: settings.Columns,
// init manual settings
url: window.location.pathname + '/load',
toolbar: '#toolbar',
classes: 'table table-condensed table-hover table-striped',
showRefresh: true,
search: true,
pagination: true,
pageSize: 10,
pageList: [10, 25, 50, 100, 250]
});
},
});
...
As you can the see the required coding is now strongly reduced.
How it works
After the document is loaded an Ajax request is made to the controller with url <controller>/MetaData. The controller collects the metadata and sends it back to the browser. The metadata is not only about columns, it also sets other properties to configure the grid correctly.
- sortName sets sorting column.
- sortOrder sets sorting direction (ascending or descending).
- sidePagination sets where paging occurs, client or server side.
- cache sets if data is cached or not.
- uniqueId denotes the column that uniquely identify a row.
- columns as you already guessed, the column definition.
URL configuration
The controller hosts the MetaData and Load method. The complete URL is created with the window.location.pathname parameter. Please note that in this ways the controller name is not hard coded and makes the code reusable for other views without modification.
MetaData result
With Swagger we can test and examine the MetData call. Run the solution the alter to URL to 'http://localhost:49842/swagger/' and invoke the MetaData api.
Controller MetaData function
The Controller host the MetaData function. The BootStrapTableCreator does all the hard work. In this example the BootStrapTableCreator scans the AccountListModel class with reflection for attributes. Attributes control the table grids behaviour. The controller also knows if it has CRUD (CReate, Update Delete) capablities. If applicable and security allows it you can add CRUD columns. The security part is not coded here for simplicity.
HttpGet()]
[Route("[controller]/[action]")]
public IActionResult MetaData()
{
var tableCreator = new BootStrapTableCreator<AccountListModel>()
{
// add edit column
AllowEdit = true,
// add delete column
AllowDelete = true
};
return tableCreator.Serialize();
}
The Serialize() method creates the JSON result with 200 (OK) as result code.
Attributes
The BootStrapTableCreator scans for several attributes:
- Key Denotes the uniqueId field.
- CrudTitle Denotes the title field for CRUD dialogs, not used in this blog.
- HiddenInput Hides a column.
- DisableSorting (yes you guessed it all ready) disables sorting on a column.
- OrderBy Sets field sortName and sortOrder
- Display set column title. Param usage
- ShortName is used for column title, if not set Name becomes column title. If property is absence the property name becomes the column title.
- AutoGenerateFilter = false skips the column during filtering
Attribute example
The class AccountListModel gives an example on how you can use the attributes
public class AccountListModel
{
[Key]
[HiddenInput(DisplayValue = false)]
[Display(Name = "User Id", ShortName = "Id", AutoGenerateFilter = false)]
public String Id { get; set; }
[CrudTitle]
[DisableSorting]
[Display(Name = "User name", ShortName = "User")]
public String UserName { get; set; }
[CrudTitle]
[OrderByAttributeAttribute(ListSortDirection.Ascending)]
public String Email { get; set; }
[Display(Name = "Email confirmed", ShortName = "Confirmed")]
public Boolean EmailConfirmed { get; set; }
[Display(Name = "Phone number", ShortName = "Phone")]
public String PhoneNumber { get; set; }
[Display(Name = "Phone number confirmed", ShortName = "Confirmed")]
public Boolean PhoneNumberConfirmed { get; set; }
[Display(Name = "Lockout ends at", ShortName = "Lockout end")]
public DateTimeOffset? LockoutEnd { get; set; }
[Display(Name = "Lockout enabled")]
public Boolean LockoutEnabled { get; set; }
public Int32 AccessFailedCount { get; set; }
}
The attributes Key, CrudTitle, and OrderBy may occur only once. If used multiple times, only the first occurrence is used.
BootStrapTableCreator
The StrapTableCreator creates the metadata based the found attributes and internal rules:
- Boolean type is rendered as a read only checkbox.
- Numbers (Int, float, double, decimal) are right aligned.
/// <summary>
/// based on http://bootstrap-table.wenzhixin.net.cn/documentation/
/// </summary>
public class BootStrapTableColumn
{
public String field { get; set; }
public String title { get; set; }
public Boolean? sortable { get; set; }
public Boolean? visible { get; set; }
public String width { get; set; }
public String halign { get; set; }
public String align { get; set; }
public String formatter { get; set; }
}
public class BootStrapTableCreator<ListModel> where ListModel : class
{
private IQueryable<PropertyInfo> ListModelProperties { get; set; }
public Boolean AllowEdit { get; set; }
public Boolean AllowDelete { get; set; }
public IList<BootStrapTableColumn> Columns { get; private set; }
public BootStrapTableCreator()
{
ListModelProperties = typeof(ListModel).GetTypeInfo().GetProperties(BindingFlags.Public | BindingFlags.Instance).AsQueryable();
Columns = CreateColumns();
}
private IList<BootStrapTableColumn> CreateColumns()
{
var result = new List<BootStrapTableColumn>();
foreach (var property in ListModelProperties)
{
var displayAttrib = property.GetCustomAttribute<DisplayAttribute>();
var hiddenAttrib = property.GetCustomAttribute<HiddenInputAttribute>();
var disableSortingAttrib = property.GetCustomAttribute<DisableSortingAttribute>();
if (displayAttrib == null)
{
displayAttrib = new DisplayAttribute()
{
Name = property.Name,
ShortName = property.Name
};
}
var column = new BootStrapTableColumn()
{
field = property.Name,
title = (displayAttrib.ShortName ?? displayAttrib.Name) ?? property.Name,
sortable = disableSortingAttrib == null,
visible = hiddenAttrib?.DisplayValue ?? true
};
if (property.PropertyType.IsNumericType())
column.align = "right";
if (property.PropertyType == typeof(Boolean))
{
column.formatter = "checkboxFormatter";
column.halign = "center";
column.align = "center";
}
result.Add(column);
}
return result;
}
public ContentResult Serialize()
{
if (AllowEdit)
{
Columns.Insert(0, new BootStrapTableColumn()
{
title = "Edit",
formatter = "editFormatter",
halign = "center",
align = "center",
width = "40"
});
}
if (AllowDelete)
{
Columns.Add(new BootStrapTableColumn()
{
title = "Del",
formatter = "delFormatter",
halign = "center",
align = "center",
width = "40"
});
}
// Get column for title CRUD dialog
var crudTitleProperty = ListModelProperties.FirstOrDefault(p => p.GetCustomAttribute<CrudTitleAttribute>() != null);
// Only one field can be key, take the first one found
var keyProperty = ListModelProperties.FirstOrDefault(p => p.GetCustomAttribute<KeyAttribute>() != null);
// Only one field for sorting, take the first one found
var sortProperty = ListModelProperties.FirstOrDefault(p => p.GetCustomAttribute<OrderByAttributeAttributeAttribute>() != null);
// Get sortdirection
var sortAttrib = sortProperty?.GetCustomAttribute<OrderByAttributeAttributeAttribute>();
var settings = new
{
CrudTitleFieldName = crudTitleProperty?.Name,
Pagination = "server",
UseCache = false,
Key = keyProperty == null ? "" : keyProperty.Name,
SortName = sortAttrib == null ? "" : sortProperty.Name,
SortOrder = sortAttrib == null ? "" : (sortAttrib.Direction == ListSortDirection.Ascending ? "asc" : "desc"),
Columns = Columns
};
// NullValueHandling must be "ignore" to prevent errors with null value in the bootstrap table
var content = JsonConvert.SerializeObject(settings, new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore, Formatting = Formatting.Indented });
return new ContentResult()
{
StatusCode = HttpStatusCode.OK.ToInt32(),
Content = content,
ContentType = "application/json"
};
}
}
The Serialize() method encapsulates all the logic to create metadata that is understood by the bootstrap table. Null values must be ignored during serialization. The bootstrap table crashes setting with null as value.
Customize MetaData
You can customize the Metadata result if you have specific need without altering the BootStrapTableCreator.
[HttpGet()]
[Route("[controller]/[action]")]
public IActionResult MetaData()
{
var tableCreator = new BootStrapTableCreator<AccountListModel>()
{
// add edit column
AllowEdit = true,
// add delete column
AllowDelete = true
};
// customize phone column
// find column in collection
var column = tableCreator.Columns.FirstOrDefault(c => c.field.EqualsEx(nameof(AccountListModel.PhoneNumber)));
// set value(s)
column.title = "Phone";
return tableCreator.Serialize();
}
Controller Load function
The Controller Load function delivers the actual grid data and must match the MetaData. In this blog example the datasource is the UserManager<ApplicationUser> object. It handles the user accounts and is configured during startup with DI (Dependency Injection). The ApplicationUser class has more fields then I want to show so I created the AccountListModel class with all the the desired fields. The field selection is arbitrary and acts as an example. AutoMapper handles the mapping from ApplicationUser to AccountListModel and is configured with an profile:
using AutoMapper;
using Security.Models;
namespace Models.Mappings
{
public class AccountMapping : Profile
{
public AccountMapping()
{
// Only 1 way mapping
CreateMap<ApplicationUser, AccountListModel>();
}
}
}
The mapping is ready for action after registration the Controller constructor.
public class UserController : Controller
{
private readonly UserManager<ApplicationUser> userManager;
private readonly IMapper mapper;
// Constructor
public UserController(UserManager<ApplicationUser> userManager)
{
this.userManager = userManager;
// Setup AutoMapper between ApplicationUser and AccountListModel
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.AddProfiles(typeof(AccountMapping).GetTypeInfo().Assembly);
});
mapper = config.CreateMapper();
}
[HttpGet()]
[Route("[controller]/[action]")]
public IActionResult Load(String sort, String order, Int32 offset, Int32 limit, String search)
{
// apply filtering paging and mapping on datasource
var tableData = new BootStrapTableData<AccountListModel>(userManager.Users, sort, order, offset, limit, search, mapper);
// send table data to client
return tableData.Serialize();
}
...
The Load(params ...) receives the Ajax calls from the table grid. The parameters are about sorting, paging and the search text entered by the user. The BootStrapTableData<T> creates table data in JSON format. The controller sends this JSON data back to Ajax client.
BootStrapTableData Implementation
The search method inside BootStrapTableData is simple. All fields except for:
- Hidden fields.
- Fields with the Display.AutoGenerateFilter == false.
- Byte array fields.
are converted to string values. When a string value contains the search text is considered as a hit. The search is case insensitive. The search method works fine on small to medium sized datasets. On large data sets the performance will drop because indexes can not be used and you have to implement a smarter search pattern.
namespace BootstrapTable.Wenzhixi
{
/// <summary>
/// Filter,page and map items for http://bootstrap-table.wenzhixin.net.cn/
/// </summary>
public class BootStrapTableData<ListModel> where ListModel : class
{
private IQueryable items;
private String sortName;
private String sortDirection;
private Int32 skip;
private Int32 take;
private String search;
private IMapper mapper;
public Int32 MaxPageSize { get; set; } = 500;
public BootStrapTableData(IQueryable items, String sort, String order, Int32 skip, Int32 take, String search, IMapper mapper)
{
this.items = items;
this.sortName = sort;
this.sortDirection = order;
this.skip = skip;
this.take = take;
this.search = search;
this.mapper = mapper;
}
/// <summary>
/// Valid columns:
/// - visible
/// - AutoGenerateFilter!= false
/// - No Byte array
/// </summary>
/// <param name="recordType"></param>
/// <returns></returns>
private IEnumerable<String> ValidSearchFields<T>()
{
var ListModelProperties = typeof(T).GetTypeInfo().GetProperties(BindingFlags.Public | BindingFlags.Instance).AsQueryable();
var nonAutoGenProps = ListModelProperties.Where(p => p.GetCustomAttribute<DisplayAttribute>() != null &&
p.GetCustomAttribute<DisplayAttribute>().GetAutoGenerateFilter().HasValue).ToList();
var hiddenInputProps = ListModelProperties.Where(p => p.GetCustomAttribute<HiddenInputAttribute>() != null &&
p.GetCustomAttribute<HiddenInputAttribute>().DisplayValue == false).ToList();
var byteArrayTypes = ListModelProperties.Where(p => p.PropertyType == typeof(Byte[])).ToList();
// Extract invalid types
var validProperties = ListModelProperties.Except(nonAutoGenProps);
validProperties = validProperties.Except(hiddenInputProps);
validProperties = validProperties.Except(byteArrayTypes);
var result = validProperties.Select(p => p.Name).ToList();
return result;
}
private IQueryable Search(IQueryable items, out Int32 count)
{
var itemType = items.ElementType;
var propertyNames = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance).Select(p => p.Name).ToList();
// Apply filtering to all visible column names
if (!String.IsNullOrEmpty(search))
{
var sb = new StringBuilder();
// create for valid search fields dynamic Linq expression
foreach (String fieldName in ValidSearchFields<ListModel>())
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);
}
// apply count after filtering
count = items.Count();
// Skip requires sorting, so make sure there is always sorting
String sortExpression = "";
if (propertyNames.Any(c => c == sortName))
{
sortExpression += String.Format("{0} {1}", sortName, sortDirection);
items = items.OrderBy(sortExpression);
}
// save server and client resources
if (take <= 0)
take = MaxPageSize;
items = items.Skip(skip).Take(take);
return items;
}
public IActionResult Serialize()
{
// filter and map items
var mappedItems = mapper.Map<IList<ListModel>>(Search(items, out var count));
var tableData = new
{
// Make sure paging and pagecount is in sync with filtered items
total = count,
rows = mappedItems
};
// Prepare JSON content
return new ContentResult()
{
StatusCode = HttpStatusCode.OK.ToInt32(),
Content = JsonConvert.SerializeObject(tableData, new JsonSerializerSettings() { Formatting = Formatting.Indented, ContractResolver = new DefaultContractResolver() }),
ContentType = "application/json"
};
}
}
}
The Serialize() wraps it all up and returns JSON data, suitable to populate the table grid.
Conclusion
Attributes and reflection clears the way for auto generation columns. The generation process has hooks for modifying the columns without altering the generation engine. Auto generation takes away the boring task of defining the manually, reduces the lines of code and speeds up the development.