Introduction
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.
Background
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.
Getting started
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 XMLHttpRequest object that I use in the example. I plan to focus more on the way the data grid is rendered back to the website and what you can do to add functionality to the application. If you are a little lost, please be sure to go back and read the first article. It gives a fairly complete overview of what is going on in the Javascript.
Overview
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:
- ajaxDatagrid.aspx - Honestly, doesn't even need to be an ASP.NET page. This could be just a standard HTML file. It contains the HTML that we will use to activate the Javascript and display the response.
- ajaxDatagrid.js - This is the Javascript file -- referenced from the ajaxDatagrid.aspx page -- that handles all of our request processing.
- 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 processTable() and 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
XMLHttpRequest object. - The server receives the query and pulls the data from the SQL server, storing it in a data table.
- The data table is processed on the server, converting all of the columns and rows into an HTML table. Javascript sort and page functions are added here.
- 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.
Security concerns
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!
Happy coding!
History
- 20 September, 2006 -- Original version posted.
- 21 June, 2007 -- Article edited and moved to the main CodeProject.com article base.
