Welcome to part two of my series on Demystifying AJAX for .NET. If you haven't yet tried the first article about creating an AJAX Shoutbox using AJAX and ASP.NET, it's a good place to start for AJAX beginners. It also covers some of the introductory parts regarding what AJAX is, as well as a brief background on Microsoft's AJAX implementation for AJAX called ATLAS. If you've already covered that article and are looking to sink your teeth into some practical applications regarding the use of AJAX and SQL, you've come to the right place.
In Visual Studio 2002, Microsoft provided us with the Datagrid control, spawning off a zillion related topics concerning the use of the thing. The Datagrid is an extremely powerful tool and a great way to display SQL result sets to users. It implemented paging and sorting, and could be styled in many different ways. The only major drawback to utilizing a Datagrid in an ASP.NET application was the problem of post-backs. Binding, sorting and paging all required server post-backs. At many times, this created that unwanted "flicker" effect on websites. This article will illustrate how to create your own AJAX-powered data grid that can be styled, sorted, paged and bound with no server post-back.
I've provided a fairly complete demonstration project that should have enough comments to give you a relative roadmap of what's going on. Also, if you're already familiar with the first article, there's no reason for me to go over the
As I said before, this complete example does the following functions with NO POST-BACK:
- Binds the data
- Pages the grid
- Sorts the grid
For this example, I've used the oh-so-famous Northwind database (as pictured). The connection string is accessed from within the web.config file, so be sure you change that to your local MSSQL database before attempting to run the application. The HTML input on the main page handles the SQL query, so you can change that to whatever you like. The row input will return X number of rows from your data table.
Using the code
There are several different steps to creating and using the code. Because AJAX uses multiple technologies to accomplish its tasks, I'll break it down here with a brief explanation using my included example:
- Web.Config - Contains the MSSQL server connection string.
- ajaxDatagrid.css - Style sheet. This spruces up the data grid and makes it extremely customizable.
- processSQL.aspx - The HTML page is nothing. We're only interested in the code-behind. This is the server side script that handles the XML request, does all of the processing and returns the formatted HTML.
Once you crack open the processSQL.cs file, you'll see that a lot of text manipulation is going on. Now, for the example, I'm returned everything as a string. If you're not uber-comfortable using HTML, you might want to use the
HTMLTextWriter object from within .NET to create your HTML page. I prefer the old fashioned way of formatting my own text.
I have one area "regioned out" that reads: "Generic Table Processing - No Paging." This was the first iteration that helped me create the table. It's not used in the demonstration, but I kept it in there in case you have the desire to just display a grid, with no paging or sorting. The meat of the code is housed in the
processSubsetTable() methods. Each of these methods returns a string -- a large, formatted HTML string, but still just a string -- that is dumped into the "results" div on the main page. The process flow works something like this:
- The SQL query, row count, page and sort expression (last two initially null) are passed to the server via the
- The server receives the query and pulls the data from the SQL server, storing it in a data table.
- The formatted HTML text is sent back to the client and dumped into the div tag. The CSS picks it up and formats it on the fly.
Points of interest
In the processSQL.cs file, classes are applied to each TD element in the HTML. They are coded as either "itm" or "alt" and can be used in your CSS file to have alternating colors for the rows, if you so choose. ALL of the data grid styling is handled solely by the CSS file, making it very easy to modify in order to change the look and feel of your grid.
When the grid is sorted, ONLY the visible page is sorted, NOT the entire back-end data table. This can of course be changed, with some technical effort, but when I coded this application I only wanted one page sorted at a time, not the whole set.
With this demonstration I have tried as much as possible to keep the look and feel -- as well as the standard functionality -- of the ASP.NET Datagrid intact. However, you will notice the speed of performance difference since there are no post-backs.
It is a BAD IDEA to use this function to execute a long-running query. If you choose to go this route, I would definitely suggest some sort of user feedback, such as an hourglass, an animated "please wait" bar or something along those lines.
The query strings and parameters for this application are NOT encoded. They use raw SQL to illustrate the demonstration. This is by no means ready for deployment. I have created this only as a demonstration and not for use as an enterprise application. If you wish to use it as such, by all means, but please be sure you address your own security concerns before deploying!
- 20 September, 2006 -- Original version posted.
- 21 June, 2007 -- Article edited and moved to the main CodeProject.com article base.