Auto generate columns for bootstrap table grid in MVC

• Download Source code.zip - 2.6 MB

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.

Further reading

Bootstrap table

AutoMapper