Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

AJAX Demystified - Part Two - The AJAX DataGrid

, 21 Jun 2007 CPOL
Create an AJAX DataGrid that binds, sorts and pages with no post-backs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;

namespace ajaxDG
{
	/// <summary>
	/// Receives an SQL statement, and processed it into a pageable, sortable HTML grid
	/// </summary>
	public class processSQL : System.Web.UI.Page
	{
		private void Page_Load(object sender, System.EventArgs e)
		{
			string query = Request.QueryString["query"];
			int rows = int.Parse(Request.QueryString["rows"]);

			//Get the initial dataset using the connection string in the web.config
			SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connectionString"]);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandText = query;
			cmd.CommandType = CommandType.Text;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataTable dt = new DataTable();			
			try
			{
				cn.Open();
				da.Fill(dt);
			}
			catch (Exception exc) //Write the expression as a paragraph tag
			{
				Response.Clear();
				Response.Write("<p>" + exc.ToString() + "</p>");
				Response.End();
			}
			finally
			{
				cn.Close();
			}
			//If the gridpage isn't null, we're paging
			if (Request.QueryString["gridpage"] != null)
			{
				Response.Clear();
				Response.Write(processTableSubset(dt, rows, int.Parse(Request.QueryString["gridpage"]), 
					Request.QueryString["expression"]));
				Response.End();
			}
			else
			{
				Response.Clear();
				Response.Write(processTable(dt, rows));
				Response.End();
			}
		}

		#region Generic Table Processing - No Paging

		/// <summary>
		/// Creates a HTML table from a datatable
		/// </summary>
		/// <param name="dt">System.Data.DataTable</param>
		/// <returns>HTML String</returns>
		private string processTable(DataTable dt)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append("<table id='queryTable' width='100%' cellpadding=0 cellspacing=0>");
			
			//Write the header
			sb.Append("<tr>");
			for (int i=0; i<dt.Columns.Count; i++)
			{
				sb.Append("<th>" + dt.Columns[i].ColumnName + "</th>");
			}
			sb.Append("</tr>");

			//Write the cells
			foreach (DataRow r in dt.Rows)
			{
				sb.Append("<tr>");
				for (int x=0; x<dt.Columns.Count; x++)
				{
					if (r[x].ToString().Length > 0)
						sb.Append("<td>" + r[x].ToString() + "</td>");
					else
						sb.Append("<td>&nbsp;</td>");
				}
				sb.Append("</tr>");
			}
			sb.Append("</table>");
			return sb.ToString();
		}

		#endregion

		/// <summary>
		/// Creates a pageable HTML table from a datatable
		/// </summary>
		/// <param name="dt">System.Data.DataTable</param>
		/// <param name="rowCount">Rows to display</param>
		/// <returns>HTML String</returns>
		private string processTable(DataTable dt, int rowCount)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append("<table id='queryTable' width='100%' cellpadding=0 cellspacing=0>");
			
			//Write the header
			sb.Append("<tr>");
			for (int i=0; i<dt.Columns.Count; i++)
			{
				sb.Append("<th><a href = '#' onclick='sortGrid(\""+dt.Columns[i].ColumnName+"\");'>" + dt.Columns[i].ColumnName + "</a></th>");
			}
			sb.Append("</tr>");

			//Write the cells
			bool alt=false;
			for (int k=0; k<rowCount;k++)
			{
				if (k >= dt.Rows.Count)
					break;
				sb.Append("<tr>");
				if (alt)
				{
					alt=false;
					for (int x=0; x<dt.Columns.Count; x++)
					{
						if (dt.Rows[k][x].ToString().Length > 0)
							sb.Append("<td class='alt'>" + dt.Rows[k][x].ToString() + "</td>");
						else
							sb.Append("<td class='alt'>&nbsp;</td>");
					}
				}
				else
				{
					alt=true;
					for (int x=0; x<dt.Columns.Count; x++)
					{
						if (dt.Rows[k][x].ToString().Length > 0)
							sb.Append("<td class='itm'>" + dt.Rows[k][x].ToString() + "</td>");
						else
							sb.Append("<td class='itm'>&nbsp;</td>");
					}
				}
				sb.Append("</tr>");
			}

			//Write the page footer
			sb.Append("<tr>");
			sb.Append("<th colspan='" + dt.Columns.Count + "'>");
			int pages = (dt.Rows.Count / rowCount) + 1;
			for (int j=1; j<=pages; j++)
			{
				if (j==1)
					sb.Append(j.ToString() + "&nbsp;&nbsp;");
				else
					sb.Append("<a href='#' onclick='pageGrid(" + j.ToString() + ")'>" + j.ToString() + "</a>&nbsp;&nbsp;");
			}
			sb.Append("</th>");
			sb.Append("</tr>");
			sb.Append("</table>");
			return sb.ToString();
		}

		/// <summary>
		/// Creates a pageable HTML table from a datatable
		/// </summary>
		/// <param name="dt">System.Data.DataTable</param>
		/// <param name="rowCount">Rows to display</param>
		/// <returns>HTML String</returns>
		private string processTableSubset(DataTable dt, int rowCount, int page, string expression)
		{
			//Create a subset of the data, and process that table
			DataTable subset = new DataTable();
			
			// Duplicate the columns
			foreach (DataColumn dc in dt.Columns)
			{
				subset.Columns.Add(new DataColumn(dc.ColumnName));
			}

			int maxRange = (rowCount * page) - 1;
			int minRange = (maxRange - rowCount) + 1;

			// Check the range, make sure we don't go too far
			if (maxRange > dt.Rows.Count-1)
				maxRange = dt.Rows.Count-1;

			// Import our subset
			for (int x=minRange;x<=maxRange;x++)
			{
				DataRow r = dt.Rows[x];
				subset.ImportRow(r);
			}

			// Sort the grid
			DataRow[] subRows;
			if (expression != "")
				subRows = subset.Select("1=1", expression + " ASC");
			else
				subRows = subset.Select();

			// Process the table now

			StringBuilder sb = new StringBuilder();
			sb.Append("<table id='queryTable' width='100%' cellpadding=0 cellspacing=0>");
			
			//Write the header
			sb.Append("<tr>");
			for (int i=0; i<subset.Columns.Count; i++)
			{
				sb.Append("<th><a href = '#' onclick='sortGrid(\""+dt.Columns[i].ColumnName+"\");'>" + dt.Columns[i].ColumnName + "</a></th>");
			}
			sb.Append("</tr>");

			//Loop through the rows and write out the cells
			bool alt=false;

			for (int k=0; k<rowCount;k++)
			{
				if (k >= subRows.Length)
					break;

				sb.Append("<tr>");
				if (alt)
				{
					alt=false;
					for (int x=0; x<subset.Columns.Count; x++)
					{
						if (subRows[k][x].ToString().Length > 0)
							sb.Append("<td class='alt'>" + subRows[k][x].ToString() + "</td>");
						else
							sb.Append("<td class='alt'>&nbsp;</td>");
					}
				}
				else
				{
					alt=true;
					for (int x=0; x<subset.Columns.Count; x++)
					{
						if (subRows[k][x].ToString().Length > 0)
							sb.Append("<td class='itm'>" + subRows[k][x].ToString() + "</td>");
						else
							sb.Append("<td class='itm'>&nbsp;</td>");
					}
				}
				sb.Append("</tr>");
			}

			//Write the page footer
			sb.Append("<tr>");
			sb.Append("<th colspan='" + dt.Columns.Count + "'>");
			int pages = (dt.Rows.Count / rowCount) + 1;
			for (int j=1; j<=pages; j++)
			{
				if (j==page)
					sb.Append(j.ToString() + "&nbsp;&nbsp;");
				else
					sb.Append("<a href='#' onclick='pageGrid(" + j.ToString() + ")'>" + j.ToString() + "</a>&nbsp;&nbsp;");
			}
			sb.Append("</th>");
			sb.Append("</tr>");
			sb.Append("</table>");
			return sb.ToString();
			
		}

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.Load += new System.EventHandler(this.Page_Load);
		}
		#endregion
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

UsualDosage
Architect
United States United States
I have been an ASP.NET/C# Programmer/Software Architect for about 10 years, specializing in web architecture, user interface, and user experience. I formerly wrote business applications for mortgage banking front-ends in C++ before switching to the .NET Framework many years ago, and I've never looked back. I'm an evangelist of HTML5 and web standards, and spend the majority of my time working on front end design, performance and scale.

My primary website is located at http://www.usualdosage.com

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 21 Jun 2007
Article Copyright 2006 by UsualDosage
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid