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".
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.
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
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.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.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);
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
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 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.
IF @editdate IS NOT NULL
SELECT @editid = updaterId FROM [table]
WHERE rowid=@rowid AND editdate <= @editdate
IF @editid IS NOT NULL AND @editid <> @userid
SELECT @newdate =NULL
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.