|
|
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> </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'> </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'> </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() + " ");
else
sb.Append("<a href='#' onclick='pageGrid(" + j.ToString() + ")'>" + j.ToString() + "</a> ");
}
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'> </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'> </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() + " ");
else
sb.Append("<a href='#' onclick='pageGrid(" + j.ToString() + ")'>" + j.ToString() + "</a> ");
}
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.
I have worked professionally in IT since 2004, and as a software architect since 2008, specializing in user interface design and experience, something I am still extremely passionate about. In 2013 I moved into management, and since then I've held positions as Director of Product Development, Director of Engineering, and Practice Director.
Submit an article or tip