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');
$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');
$.ajax({
url: window.location.pathname + '/metadata',
success: function (settings) {
$table.bootstrapTable({
sortName: settings.SortName,
sortOrder: settings.SortOrder,
sidePagination: settings.Pagination,
cache: settings.UseCache,
uniqueId: settings.Key,
columns: settings.Columns,
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>()
{
AllowEdit = true,
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.
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"
});
}
var crudTitleProperty = ListModelProperties.FirstOrDefault(p => p.GetCustomAttribute<CrudTitleAttribute>() != null);
var keyProperty = ListModelProperties.FirstOrDefault(p => p.GetCustomAttribute<KeyAttribute>() != null);
var sortProperty = ListModelProperties.FirstOrDefault(p => p.GetCustomAttribute<OrderByAttributeAttributeAttribute>() != null);
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
};
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>()
{
AllowEdit = true,
AllowDelete = true
};
var column = tableCreator.Columns.FirstOrDefault(c => c.field.EqualsEx(nameof(AccountListModel.PhoneNumber)));
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()
{
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;
public UserController(UserManager<ApplicationUser> userManager)
{
this.userManager = userManager;
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)
{
var tableData = new BootStrapTableData<AccountListModel>(userManager.Users, sort, order, offset, limit, search, mapper);
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
{
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;
}
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();
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();
if (!String.IsNullOrEmpty(search))
{
var sb = new StringBuilder();
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();
searchExpression = searchExpression.Substring(0, searchExpression.LastIndexOf("or"));
items = items.Where(searchExpression, search, StringComparison.OrdinalIgnoreCase);
}
count = items.Count();
String sortExpression = "";
if (propertyNames.Any(c => c == sortName))
{
sortExpression += String.Format("{0} {1}", sortName, sortDirection);
items = items.OrderBy(sortExpression);
}
if (take <= 0)
take = MaxPageSize;
items = items.Skip(skip).Take(take);
return items;
}
public IActionResult Serialize()
{
var mappedItems = mapper.Map<IList<ListModel>>(Search(items, out var count));
var tableData = new
{
total = count,
rows = mappedItems
};
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.
Further reading
Bootstrap table
AutoMapper