SqlPageGrid.cs
using System;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.ComponentModel;
using System.Diagnostics;
using System.Reflection;
using System.Xml;
using System.Text;
using System.Data.SqlClient;
namespace YHLib
{
[
DefaultProperty("SQL")
]
public class SqlPageGrid : System.Web.UI.WebControls.DataGrid
{
// Fields
//private bool shouldRebind = false;
#region Properties
[
Bindable(true),
Category("Data"),
Description("sql string to retrieve data")
]
public string SQL
{
get
{
string s = (string)ViewState["SQL"];
if (s == null)
return String.Empty;
return s;
}
set
{
ViewState["SQL"] = value;
}
}
[
Bindable(true),
Category("Data"),
Description("Connection String for database")
]
public string ConnectionString
{
get
{
string s = (string)ViewState["ConnectionString"];
if (s == null)
return String.Empty;
return s;
}
set
{
ViewState["ConnectionString"] = value;
}
}
private bool dSortCommand=false;
#endregion
// Overridden DataGrid Methods
#region Overridden DataGrid Methods
// OnLoad() method -- Load the dataset and get field names and types.
protected override void OnLoad(EventArgs e)
{
// Test for Page to ensure this doesn't run at design time
if (!Page.IsPostBack)
{
this.AllowCustomPaging = true;
this.PagerStyle.Mode = PagerMode.NumericPages;
if (this.CurrentPageIndex==-1)
{
this.CurrentPageIndex=0;
}
// shouldRebind = true;
}
}
// OnPageIndexChanged() method -- handles datagrid paging
protected override void OnPageIndexChanged(DataGridPageChangedEventArgs e)
{
this.CurrentPageIndex = e.NewPageIndex;
// shouldRebind = true;
}
// OnItemCreated() method --Add the table name to the header row and
protected override void OnItemCreated(DataGridItemEventArgs e)
{
if ((ListItemType)e.Item.ItemType == ListItemType.Header)
{
// e.Item.Cells.RemoveAt(0);
// e.Item.Cells.RemoveAt(0);
// TableCell c = new TableCell();
// c.ColumnSpan = 2;
// c.Text = "<b>" + SQL + "</b>";
// e.Item.Cells.AddAt(0, c);
}
if ((ListItemType)e.Item.ItemType == ListItemType.Footer)
{
//base.ShowFooter=true;
}
if ((ListItemType)e.Item.ItemType == ListItemType.Pager)
{
base.PagerStyle.Mode = PagerMode.NumericPages;
TableCell pager = (TableCell) e.Item.Controls[0];
pager.HorizontalAlign = HorizontalAlign.Left;
for (int i=0;i < pager.Controls.Count; i +=2)
{
try
{
LinkButton h = (LinkButton) pager.Controls[i];
h.CausesValidation = true;
h.Text = "[" + h.Text + "]";
}
catch
{
Label l = (Label) pager.Controls[i];
l.Text = "第" + l.Text + "页";
}
}
TableCell c1=new TableCell();
c1.Text="共"+this.PageCount.ToString()+"页"+"[<font size='1'>共</font><i>"+this.VirtualItemCount.ToString()+"</i><font size='1'>条记录</font>]";
//"[<font size='2'>共<i>"+this.VirtualItemCount.ToString()+"</i>条记录</font>]";
e.Item.Cells.AddAt(0,c1);
if (this.Columns.Count>4)
{
if (this.Columns[0].HeaderStyle.Width.Value+this.Columns[1].HeaderStyle.Width.Value>149)
{
e.Item.Cells[0].ColumnSpan=2;
}
else if (this.Columns[0].HeaderStyle.Width.Value+this.Columns[1].HeaderStyle.Width.Value+this.Columns[2].HeaderStyle.Width.Value>149)
{
e.Item.Cells[0].ColumnSpan=3;
}
else
{
e.Item.Cells[0].ColumnSpan=4;
}
}
else
{
e.Item.Cells[0].ColumnSpan=2;
}
}
}
protected override void OnSortCommand(DataGridSortCommandEventArgs e)
{
string sSortFiled = e.SortExpression.ToString();
this.Attributes["SortExpression"] = sSortFiled;
if(this.Attributes["SortDirection"] == "asc")
{
this.Attributes["SortDirection"] = "desc";
}
else
{
this.Attributes["SortDirection"] = "asc";
}
this.CurrentPageIndex = 0;
dSortCommand=true;
this.BindData();
dSortCommand=false;
}
// OnPreRender() method -- We bind in this method since
// it's called only at runtime
protected override void OnPreRender(EventArgs e)
{
// if (shouldRebind)
// {
// BindData();
// }
BindData();
}
#endregion
// BindData() method -- binds datatable to grid
#region BindData() method -- binds datatable to grid
private void BindData()
{
int count = 0;
DataTable list = GetList(this.CurrentPageIndex,this.PageSize,ref count);
if (list.Rows.Count>0)
{
this.VirtualItemCount = count;
this.DataSource = list;
base.DataBind();
}
else
{
this.VirtualItemCount = 0;
this.DataSource = new ArrayList();
base.DataBind();
}
}
public override void DataBind()
{
return;
}
private DataTable GetList( int currentPage, int pageSize, ref int numResults) {
if(SQL == "")
return new DataTable();
numResults = 0;
int index = 0;
ArrayList results = new ArrayList();
SqlDataReader reader = null;
SqlConnection con = null;
if (this.ConnectionString == String.Empty)
{
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
}
else
{
con = new SqlConnection(this.ConnectionString);
}
con.Open();
SqlCommand cmd =null;
if (this.Attributes["SortExpression"]!=null&&dSortCommand)
{
if (SQL.ToLower().LastIndexOf("order")>0)
{
SQL=SQL.Substring(0,SQL.LastIndexOf("Order"))+" Order by "+this.Attributes["SortExpression"]+" "+this.Attributes["SortDirection"];
}
else
{
SQL=SQL+" Order by "+this.Attributes["SortExpression"]+" "+this.Attributes["SortDirection"];
}
}
cmd=new SqlCommand(SQL,con);
reader = cmd.ExecuteReader();
// now go through and pull out the items we want
int start = (int)(currentPage * pageSize);//int start = (int)((currentPage - 1) * pageSize);
// if (start <= 0) start = 1;
// skip
for (int i = 0; i < start ; i++) //for (int i = 0; i < start - 1; i++)
{
if (reader.Read()) numResults++;
}
// if (start > 1) reader.Read();
DataTable dt = new DataTable();
bool bDT = false;
// read the data we are interested in
while (reader.Read())
{
if (index < pageSize)
{
if (!bDT)
{
for (int i=0;i<reader.FieldCount;i++)
{
string sname=string.Empty;
string dataTypeName = reader.GetDataTypeName(i);
switch (dataTypeName)
{
case "int":
sname = "System.Int32";
break;
case "smallint":
sname = "System.Int16";
break;
case "decimal":
sname = "System.Decimal";
break;
case "string":
sname = "System.String";
break;
case "char":
sname = "System.String";
break;
case "varchar":
sname = "System.String";
break;
case "nvarchar":
sname = "System.String";
break;
case "smalldatetime":
case "datetime":
sname = "System.DateTime";
break;
case "bit":
sname = "System.Boolean";
break;
}
DataColumn col = new DataColumn(reader.GetName(i), Type.GetType(sname), null, System.Data.MappingType.Element);
dt.Columns.Add(col);
}
bDT = true;
}
DataRow _dr = dt.NewRow();
for (int i=0;i<reader.FieldCount;i++)
{
_dr[i] = reader.GetValue(i);
}
dt.Rows.Add(_dr);
index++;
}
numResults++;
}
reader.Close();
con.Close();
return dt;
}
#endregion
}
}
2. .ASPX中加入%@ Register TagPrefix="yh" Namespace="YHLib" Assembly="CK" %
protected System.Web.UI.WebControls.DataGrid dg;改为protected YHLib.SqlPageGrid dg; //
dg.SQL=strSql;
dg.DataKeyField="asnid";
dg.DataBind();
