Introduction
Selecting and querying data from a list is one of the core parts of any web application, most commercial web applications will have dozens or even hundreds of lists and a pressing need to develop them quickly and efficiently.
There are a number of different ways to implement such lists: traditionally templates have been implemented server-side on
.aspx pages and bound to data controls; more recently MVC has supported the paradigm of templating client-side, supported by JSON feeds from the server.
This project extends an approach pioneered by my previous implementation in CodeProject for ASP.NET A lightweight AJAX.NET-enabled grid control of combining the definition of the data source and the templating in a single XML configuration file.
The AjaxGrid uses AJAX (of course) to manage the flow of large amounts of grid data in the most efficient way possible - building HTML server-side and copying the resultant string directly into
innerHTML. In this way, data transfer size and JavaScript runtime are both minimised.
Furthermore, the grids are optimised for progressively providing large amounts of information server-side and supporting user strategies to quickly drill-down to the required subset by easy-to-use sort and filter functionality.
Lastly, the grids also provide the ability to generate simple, user-customisable reports which can be exported for further work or future reference.
The grid supports the following user functionality:
- Built-in sort and filter
- "Auto-complete" and free-text filtering options
- "Infinite scroll" support for large datasets, without paging
- Export to CSV and JSON
- Optional create new row, either in-place or through MVC routing
- Optional data edit, either in-place or through MVC routing
- Built-in undo/redo for in-place edits
- Integrated with MVC routing
There is a lot of functional detail in supporting an effective user interface and some sort of user control structurally guarantees a compatible user interface throughout
the application and that usability enhancements are consistently applied. MVC/Razor does not really support user controls, so part of this project has been
to see how to integrate this functionality within the framework. Even if you are not going to use the grid functionality, you may be interested in how I have
worked with MVC to provide such shared functionality.
Background
The simple approach to implementing shared functionality in multiple views and Controller classes is to implement a class derived from
System.MVC.Controller
and to derive the "page controller" classes from that. This is simple and works well in a demonstration project but is not really practical in the real world due
to the unfortunate fact that you cannot derive a C# class from multiple classes, making it an administrative nightmare to derive selectively from more than one control.
A more sustainable approach is to use an HtmlHelper to delegate the shared functionality to a shared Controller, which is hidden from the end-user.
The task in hand is to generate HTML dynamically without having to configure or repeat boilerplate or custom code in each view and controller that wishes to use
the functionality; the configuration is entirely defined in an XML control file. The dynamic nature of this requirement has a number of consequences:
- The AjaxGrid Controller renders output dynamically - apart from a single containing DIV - and so does not use views to render (although it could)
- There are no models because the data is extracted dynamically as directed by the configuration file (although the code can generate and export JSON models dynamically)
- LINQ is not a suitable vehicle for dynamically generating data queries, so ADO.NET is used
- The shared functionality must be able to integrate with more "normal" MVC implementation and routing
Overall the functionality of the user control is managed by a static AjaGrid class. Its core functions are split into defining and loading data structures
from the XML file in AjaxGrid.cs, generating SQL queries in AjaxGrid.DataAccess.cs, rendering HTML from the resultant SQL data source and the current user options
in AjaxGrid.Render.cs and generating export formats for CSV and JSON in AjaxGrid.Export.cs.
I think that there are arguments that using Asynchronous Controllers for database operations is not cost-effective,
see: Should my database calls be asynchronous?.
However, it should be quite easy to upgrade the AjaxGridController to
asynchronous usage if you disagree.
Using the code
This implementation has been targeted at MVC4 on .NET4.5, but it should be easily rejigged to work with other versions of MVC. The attached sample project has had
its packages folder emptied to save space in the zip file - download the project and then copy in the packages files from one of your default projects.
To simplify, I have included a zip of the source files with a folder structure as laid out in a default project, and a sample project implementation based
on the default internet implementation without rewriting irrelevant parts such as the AccountController and Views.
I also don't want to prejudge application-specific issues such as authentication, use of Entity Framework, bundling strategies for JavaScript and CSS,
application build procedures or the inclusion of other frameworks - whether client-side or server-side.
Also, I haven't implemented Create or Edit Actions to the Home Controller of sample program (I don't want to presume about how you scaffold model objects)
and I changed the default font size to 60% for aesthetic reasons.
I have also included two grids based on the NorthWind sample database, one very simple and one with a bit more complexity in the SQL and supporting inline edits.
For the web application as a whole, you will need to include the following files:
- App_Data/AjaxGrid.xml: the configuration file for your application
- Controllers/AjaxGridController.cs: the Controller
- AjaxGrid folder: contains the AjaxGrid class in four partial files for clarity
- Scripts/AjaxGrid.js: you will need to include this (and jQuery+jQueryUI) in relevant views or bundle them in
App_Start/Bundle.config (NET 4.5)
- Content/AjaxGrid.css: you will need to include this in relevant views (and datepicker CSS for jQueryUI) or bundle it in
App_Start/Bundle.config (NET 4.5)
- Images/ajaxgrid: folder holding the image files
- global.asax.cs: add
AjaxGrid.Load(); to Application_Start(). - web.config:Include a connection string "DataConnection" in web.config (as below)
add name="DataConnection" connectionString="Trusted_Connection=true;
MultipleActiveResultSets=True;Server=.\SQLEXPRESS;Database=NorthWind"
providerName="System.Data.SqlClient"
...note MultipleActiveResultSets=True. Ideally share this connection string with the rest of your application for effective caching of SQL connections.
Each participating view can then render its grid with a one-liner HtmlHelper as follows:
@Html.DisplayAjaxGrid(@Url, 1, "Edit","Create",(AjaxGrid.ValidateEventHandler)ViewBag.Validator)
where @Url is the current URL, 1 is the gridId (as set in
AjaxGrid.xml), "Edit" and "Create" are the optional calling Controller's Action
Methods and ViewBag.Validator contains an optional delegate for an editable grid to perform custom data validation as below (note that an explicit cast was required).
AjaxGrid.ValidateEventHandler v = (fieldname, value) =>
{
if (fieldname == "book" && value.Length == 0)
return fieldname + ": A valid string must be entered\r\n";
return "";
};
ViewBag.Validator = v;
It can also be useful to set the gridId as a ViewBag parameter, if you want to vary the grid shown dynamically depending on user permissions or application state.
The XML configuration file
AjaxGrid.xml contains two main sections, a section defining system texts (such as static arrays to populate
SELECT options) and a section to define the grids themselves.
The XML format should make it quick to create large numbers of grids and reports.
Grids count up from zero as each one is defined, so you shouldn't move the grid definitions around if you want easy maintenance. Each grid is defined with the following attributes:
Id: numeric grid id for information only (counts up from zero)Name: for documentation purposes onlyDbTable: the core database table for the queryDbKey: the column containing the primary key for the DbTableHeight: to contain the grid in a scrolling divNewRow: indicates if an icon to create a new row is desired
Within each grid are elements defining the query SQL and a list of column definitions.
The query SQL is the core definition of the grid contents, once that is in place it is straightforward to manage the column definitions.
The query SQL should begin with either SELECTx or SELECTDISTINCTx, automatically converted to "SELECT TOP n" or "SELECT DISTINCT TOP n" to support infinite scroll.
The SQL should also terminate with a WHERE clause to which extra filters can be tacked on, even if the clause is a dummy such as WHERE 1=1.
Each table column selected in the SQL is then defined (in the order determined by the SELECT statement) with the following attributes or sub-elements:
Header: the user-displayed header (also the column name in the database by default)ColumnName: used where user-displayable header does not match the column name (for example, "Company Name" vs. "CompanyName")SortName: used where a custom Sort column is requiredWidth: gives a fixed layout for the columnEditable: optional for in-place edit for this columnSelectOptionsArray: for SELECT dropdowns, name of static options array in Systems Text sectionSelectOptionsSQL: for SELECT dropdowns, SQL to generate dataset of Id and Value for non-static options to be read from the databaseColumnDbKey: optional for in-place edit where the edited column is connected by a JOIN to another tableUpdateTable: optional for in-place edit where the edited column is connected by a JOIN to another tableCustomUpdateSQL: optional for in-place edit where custom update SQL is requiredFreeTextSql: I haven't included a free text user interface, but this defines the free-text search columns in the database
Most columns don't need all of these parameters, a quick look at the two sample templates will give you a feel how all this works in practice.
Points of Interest
Enabling fast user sort and filter
As soon as a sort or filter operation has been performed by the end user, the operation is notified by an AJAX call and applied to the displayed grid. One desirable feature is the ability to "auto-complete" when a free-form input filter is being typed into; rather than provide a list of possible options, the grid is automatically filtered as the filter string is entered.
For this to work efficiently, two mechanisms are in force: firstly, there is a delay of half a second from the last keystroke before the filter is AJAX'ed to the server - this enables multiple keystrokes entered quickly to be packaged up and sent in a single AJAX request. Users quickly get a feel for this and type in only as many characters as are needed to create an efficient filter string. The exception is where a user presses TAB or ENTER, in those cases we can assume that filter input for that column is complete and the request is sent immediately. Secondly multiple AJAX filter calls are optimised at the client because once an AJAX filter request has been sent it cannot be recalled, even if more characters are subsequently entered. The grid
JavaScript maintains a counter for filter requests and only renders the response to the last filter to be sent, eliminating surplus client-side work so that the client quickly renders when the most recent data arrives.
Free-text search is also easy to implement if your database has been set up to support it, I haven't included an user interface although there is a sample definition in AjaxGrid.xml of how to define the target columns for a free-text search. Note that "auto-complete" doesn't really work for free-text search because it operates on whole words and so a conventional Search button is probably best to launch a free-text filter AJAX command with text from an associated input field.
Data Security
Two key areas of data security are required, preventing SQL injection and XSS (Cross-Site Scripting). Any data which is entered by a user and which will be included in a SQL statement, either by data entry or by being used as a filter, must be attached to the SQL query by a parameter.
XSS attacks are prevented by sanitising all outgoing user-generated data with HttpUtility.HtmlEncode.
Note that SQL is sanitised incoming, it must be to protect its SQL queries, while html is sanitised outgoing as is standard practice for MVC. It is probably more efficient, although maybe more risky, to sanitise user data one time incoming rather than many times outbound - but this depends on the confidence you have that text fields in your database are always pre-sanitised; the safer option of always sanitising outgoing user data is used here.
Most of the methods in AjaxGridController can only be called by AJAX, and this is enforced by a custom attribute [AjaxOnly], ensuring that the information that they deliver is restricted to the local referer.
All AjaxGrid methods also enforce whether a GET, POST or DELETE operations are used to access their functionality.
The CSV export method, however, must be obtained by a postback - which makes it potentially callable from any referer: checking the referer is not the solution because
referrers can be spoofed.
If data security is important and this is implemented in the calling controller, then it is recommended to apply the [Authorize]attribute and the [ValidateAntiForgeryToken]attribute to this method.
These attributes need application authentication and/or the view or controller setup (Html.AntiForgeryToken()) to work. Refer to this blog for a discussion of anti-forgery issues, including how to read anti-forgery tokens from jQuery, and here for information on how to automatically secure all controllers with the [Authorize] attribute.
AjaxGrid uses either strings or JSON to respond to its clients. However it is only in the case of Export to JSON that the JSON response
can contain an array and this is a potential security risk using a GET, prevention of XSS attacks requires that a POST operation is mandated for the AjaxGrid's JSON export action.
The use of jQuery
Using jQuery, in line with good MVC practice, has much simplified the
JavaScript for this module; you will need to include jQuery and jQueryUI (for the datepicker).
No assumptions have been made as to how you should bundle up and/or use CDNs to manage
JavaScript, that's very much an application choice.
Unobtrusive jQuery event management is great for documenting in one place where all the handled events are, but slightly fiddly for managing unbind without
memory leaks - especially for server-generated html; keeping C# and JavaScript event management
synchronized is a challenge (unless you were to generate
JavaScript dynamically at the server also). I have ensured that all events in the header row are added and removed dynamically, but some of the content
event handler interfaces are still server-created.
To compress the content data as much as possible, events are delegated to the content TD elements; column-specific flags are kept as "data-" attributes in the header
cells - these can be found by matching the cellindex of the TD with the cellindex of the header row TDs.
Design patterns for inline edits
Almost by definition, a web application will be active for multiple, simultaneous users.
Editing individual fields or inserting complete new rows are generally low-risk in this environment, although be aware that if editing individual fields amounts
to incrementing a count then you should generate some custom SQL to call a stored procedure implementing a lock-increment-unlock sequence to prevent races.
Updating multiple fields from a model which is potentially "stale" is more complex - let Entity Framework manage that, although perhaps not so efficiently.
Because end users can make updates directly onto the database as each field is changed, undo/redo functionality is essential for a good user experience.
This will automatically pop up when edits have been made.
If you configure the grid, or other unrelated applications using the same database tables, to allow inserts or deletes there is a risk that "pages" can become stale
while performing infinite scroll - leading to missed or duplicate entries at "page" boundaries. This is why each paging action (scrolling to the end of the grid) initiates
a complete re-rendering of the grid rather than just tacking a few more rows on each time. This is, I think, justified because users quickly learn that sorting and filtering
is a much more efficient way of extracting data than wading through pages of data. Nevertheless you could opt for the latter strategy, in which case I would recommend
the use of SQL Server 2012 OFFSET/FETCH rather than SELECT TOP.
Extending AjaxGrid
AjaxGrid is definitely built with extensions in mind. At the moment there are a relatively small range of column types supported (text, dropdown, date, link, delete, etc).
To create a custom column type simply add a new name to the AjaxGrid.columnType enum. The column type is then available to be selected in the XML file.
The new columntype will default to text format, but may vary in how it is filtered or rendered in the header or in the contents, the simplest way to catch each point
of customisation is to search for a type such as columnType.Date and add a new case into the switch statements where the
columnType appears where you
want to add a new behaviour or rendering for your new columnType. You may also have to look at
AjaxGrid.js if the column is to be editable and custom editing is required.
Conclusions
MVC has provided a framework which has enabled a surprisingly terse implementation of the AjaxGrid user control, given that it does not natively support user controls.
The main problem encountered to providing a clean generic interface is that the control depends on the calling Controller as to whether the [Authorize]
and/or the [ValidateAntiForgeryToken] attributes can (and therefore should) be applied.
History
Version 1.
