A lightweight AJAX.NET-enabled grid control

• Download sample DB - 221 KB
• Download source - 948 KB

Introduction

Managing lists of items from databases is the bread and butter of most web applications, but creating a user-friendly grid can be problematic. The obvious solution of creating Microsoft grid controls in an AJAX.NET UpdatePanel can create very large HTML pages with long response times when a substantial amount of data is being worked with. Also, every grid has to be created individually on each page, and that makes it hard to create and maintain a common "look and feel".

This lightweight grid outputs very compact HTML, and uses JavaScript to dynamically generate input or select boxes when updating. It includes built-in AJAX-ed sorting and filtering which is very user-friendly, and throws in CSV export for free. It has a fixed table header and scrolling contents, to facilitate use with large amounts of data.

Many different grids can be very quickly generated in a standard way, using an XML control file and a single common style sheet. Multiple AjaxGrids can be defined and used, even on the same page.

You can customise grid column data to include links, images, etc., and to support your own custom validations.

Background

Most developers will dip their toes into the seductive water of AJAX.NET by using the UpdatePanel, but calling AJAX methods directly can be much more flexible and efficient.

Any HTML element that is updated server-side by ASP.NET code must contain a long and "decorated" ID, and these are used extensively in a typical grid and UpdatePanel. But, if you generate your HTML directly, then you can rely on JavaScript to walk through the DOM to assign events to the element, to pop up HTML elements dynamically for the user to edit into a request, and to identify any element that has changed. AJAX allows you to do most of the heavy lifting server-side, so that you don't find yourself attempting to write an entire application in JavaScript (a real performance "gotcha").

Because the HTML is so much more compact, there is much less data to transfer. Large numbers of rows can be returned efficiently to users. This implementation includes a way of side-stepping paging in most cases and under user control, even for large amounts of data. With the fixed grid header and scrolling data section, the user's scrolling experience can really take advantage of the smooth scrolling and background updating, which browsers are optimised for. This approach is especially kind to your dial-up browsers.

This control remembers the user's preferences for each grid during the session, and can be set up to remember them between sessions too. Thanks to AJAX.NET, the user's data in the browser can also be always available server-side; you could extend the control to remember all the data that the user enters (even on a keystroke-by-keystroke basis), and even allow switching off the browser mid-session and resume working later at the same point - just as if it was a local application. Naive users especially will appreciate the security of not losing entered data by mistake.

Using the Code

The source files are set up as a complete ASP.NET 2.0 website.

This code uses two tables of a database set up by the open source "Tackle" application for management of the SCRUM process, which itself uses AJAX.NET using an UpdatePanel. It is an interesting comparison. You can download it from here. (I have also included a minimal database .mdf file for a sample database, if you don't want to bother with this.)

You will need to edit the connection string in AjaxGrid.db.cs. It assumes a SQLExpress implementation of the database.

Your own implementation of this control will almost certainly include an edited stylesheet to match your own application branding (you might like to improve the graphics too!).

Lastly, you will want to edit ajaxgrid.xml to define and format each grid in your web application.

The Source Files

This control doesn't work well as a completely stand-alone assembly, because you need to include its .css and .js files and ajaxgrid.xml in the containing website. You will probably also want to adapt the core source files to the needs of your web application.

• Default.aspx and app.css are dummies for your own target web application. Default.aspx.cs shows how to serialise and deserialise "profile" data and how to register custom validation and postback methods for each page.
• ajaxgrid.xml specifies the language-specific texts and the format and contents of each grid. Each grid is named, and the SQL table, primary key, and basic query string are defined. Each column of the grid is then defined, to include the data type, the displayable header, and the SQL column name. Where a column is restricted in the available options, a static or database-generated list can be specified. You can define as many grids as you like, and they will all look and act the same. This file is loaded into static arrays when the web application loads to ensure efficient access at runtime.
• web.config shows how to set up the AJAX.NET subsystem to use web services in JSON format for AJAX.
• AjaxGrid.Webservices.cs contains the actual AJAX methods called by JavaScript.
• AjaxGrid.db.cs implements database access for the grids. Any production web application should implement MultipleActiveResultSets, defend against SQL injection, and implement asynchronous I/O, so this control does all this. However, asynchronous I/O is a problem for AJAX.NET because there is currently no async handler for JSON available, and AJAX callbacks are still restricted to synchronous I/O only. Don't confuse asynchronous AJAX calls from the client (no problem) with asynchronous handling of the server requests (required for efficient use of server threads). There is a discussion of this here.
• AjaxGrid.main.cs is the core code for this control. It contains methods to paint or repaint the control, or just the data part of the control, by generating HTML as a string. The HTML can be used to populate the control server-side (initial rendering of page or postback) or client-side (in response to an AJAX call). Each row can contain many cells of different types (as defined by the XML control file), and there is scope for creating new methods for handling other data types - such as data, currency, or images.
• AjaxGrid.js is the supporting JavaScript for the control. It shows how to use JavaScript to walk through the table cells and their contents, and how to position pop up edit controls to overlay the grid cells.
• AjaxGrid.css is the supporting stylesheet for the control. By including this stylesheet as a separate file, the control dramatically reduces the style information that needs to be included in each page.
• AjaxGrid.ascx/AjaxGrid.ascx.cs is the actual control. It is of interest for showing how an asynchronous IO operation is launched from an ASP.NET page.

What the Code Does

The server "manually" generates a string containing HTML using a StringBuilder object. The HTML can then be used to populate the control server-side in C#:

divGrid.InnerHtml = AjaxGrid.Paint(Session["GridProfile"], gridId);

or to populate the control client-side from an AJAX callback in JavaScript:

divGrid.innerHTML =  response.html;

When the "add new row" image is clicked, an extra row is added to the header table. This allows the user to see and edit the new row wherever the data table is scrolled to; once the columns have been filled in and the new row is accepted, the tables will automatically be repainted and the new row will then appear in the data table in its proper sort order.

Points of Interest

This control illustrates an interesting technique for making texts set up by JavaScript language-independently, by calling back to the server via AJAX. In many cases, the same thing can be accomplished by customising the JavaScript file to each individual user, but calling back to the server on demand reduces the amount of unnecessary data sent to the browser and can be easier to maintain.

An AJAX application will present differently on your local machine (where there is little latency) and on the Internet. Although the application correctly handles multiple outstanding AJAX calls, it can be helpful to the user to show an animated GIF file while an AJAX response is outstanding. There are no definite rules about whether to initiate an AJAX call on every key-press or when the "onchange" event handler fires (except beware if some other JavaScript event click handler is called by (say) clicking a button, you may lose notification of the onchange operation). Where a new row is to be created, all columns must be default or filled in before creating a new row in the database, and it is necessary to have a button to indicate that the row is now ready to be created. Nevertheless, it is convenient to AJAX each column up to the server as it is completed.

One possible implementation of updates from the grid is to save them up in the user's Session data and only actually action the updates when the user explicitly requests with a "Save" command. This mimics many desktop applications in allowing the user to see how the updates look before committing them to the database. There are implications to this if multiple users can see the same data, especially if the saved updates are stored between Sessions. But even without these refinements, there is always going to be a window between the user reading and writing row data.

Using database locks (possibly supplemented by SQL Server notifications when row data changes) are complex to implement, and may significantly impact performance. A lightweight solution is to record when and who the data was last changed by, and notify the user if that differs from when the row data was read. In many cases, users can be quite tolerant of being told that they must repeat the edit and update, because in the real world, it is often an error if two people mistakenly work on the same task at the same time. In the following SQL snippet from a stored procedure to update a row, contention is detected before the update is action-ed, and the update is abandoned if someone else has edited the row since the original data was read.

-- @editdate DATETIME parameter is the date that
--  the record was last edited (remembered from read)
-- @userid INT parameter is the caller's id
-- @newdate DATETIME OUT parameter is the new date
--  that the record was last edited (or null if not updated)
-- @editid INT OUT parameter is the person who made that last edit

-- each table so protected needs two new columns:
-- editdate DATETIME is the date last edited
-- updaterId INT is the person who last updated the record

IF @editdate IS NOT NULL
BEGIN
    SELECT @editid = updaterId FROM [table]
        WHERE rowid=@rowid AND editdate <= @editdate
    IF @editid IS NOT NULL AND @editid <> @userid
    BEGIN
        SELECT @newdate =NULL
        RETURN
    END
END
SELECT @newdate = GETDATE()
SELECT @editid = @userid

By the way, if the last editor was the caller, then that is considered okay. It is possible that the user has created problems for themselves by working from two different machines or two different browsers, but most likely, they will be quite annoyed if the website prevents them from updating their own edits.

You could also change the AJAX callback when each cell is updated to include both the new and the original values (the original value is still present despite being overlaid by the edit popup). This allows a more fine-grained approach where the update is refused only if a particular column of the row has been changed by someone else, rather than the entire record.

One major source of difficulty in a web application is the management of postbacks and the random use of the Back button. This is much less of a problem for AJAX-enabled applications, because the user cannot initiate the same operation twice by refreshing while waiting a response, and the Back button will take the user right out of the current page. If you have a button which mustn't be pushed twice, add some custom JavaScript to disable it when pressed.