自定义分页控件用于DataGrid（使用SQL储存过程）
分页储存过程：
网上找的一个改了一下！
/*
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明:
@tblName 包含数据的表名
@PKName 关键字段名
@strGotFields 要获取的字段
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件 (注意: 不要加 where)
@isCount 是否取得记录条数 , 0 - 不取 , 1 - 获取
@strSort 排序字段
*/
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@PKName varchar(255), -- 字段名
@strGotFields varchar(1000) = '*' , --查询字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '' , -- 查询条件 (注意: 不要加 where)
@isCount bit = 1, --取得记录条数
@strSort varchar(255) = '' --排序字段
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
declare @strCount varchar(1000)
declare @fldName varchar(255)
declare @sortName varchar(255)
declare @countSQL varchar(1000)
set @fldName = @PKName
if @strSort != ''
begin
set @sortName = @strSort
end
else
begin
set @sortName = @PKName
end
if @isCount = 1
begin
if @strWhere != ''
begin
set @countSQL = 'select count(' + @fldName + ') from ' + @tblName + ' where ' + @strWhere
end
else
begin
set @countSQL = 'select count(' + @fldName + ') from ' + @tblName
end
exec (@countSQL)
return
end
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @sortName + ' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @sortName +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from '
+ @tblName + '' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
end
GO
分页控件
在网上也查看了一些分页控件代码，大多都是做成通用的，对我所用的项目不太适合
自已写了一个功能不多，只分页与排序，刚合适合当前项目！
using System;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
namespace WebAppInc
{
/// <summary>
/// /*
///函数名称: GetRecordFromPage
///函数功能: 获取指定页的数据
///参数说明:
///@tblName 包含数据的表名
///@PKName 关键字段名
///@strGotFields 要获取的字段
///@PageSize 每页记录数
///@PageIndex 要获取的页码
///@OrderType 排序类型, 0 - 升序, 1 - 降序
///@strWhere 查询条件 (注意: 不要加 where)
///@isCount 是否取得记录条数 , 0 - 不取 , 1 - 获取
/// @strSort 排序字段
///*/
/// </summary>
[DefaultProperty("EnableCustomerStyle"),
ToolboxData("<{0}:CusPageCtrl runat=server></{0}:CusPageCtrl>")]
public class CusPageCtrl:System.Web.UI.Control,IPostBackDataHandler, IPostBackEventHandler
{
private int _PageSize;
private int _PageCount;
private string _TableName;
private string _SelectFields;
private string _SortField;
private int _CurPageIndex;
private string _btDGrid;
private DataView _datasource;
private OrderType itype;
private string _SelectWhere;
private string _DataKeyField;
private string _selectClass;
private bool isCount = true;
private string _itemClass;
public enum OrderType
{
Asc,Desc
}
public string PageCssClass
{
get{ return _itemClass; }
set{ _itemClass = value;}
}
public string CurPageCssClass
{
get{ return _selectClass; }
set{ _selectClass = value;}
}
/// <summary>
/// 排序方式
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public OrderType Order
{
get{ return itype; }
set{ itype = value;}
}
/// <summary>
/// 分页数
/// </summary>
public int PageCount
{
get{ return _PageCount; }
}
/// <summary>
/// 关键字段
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public string SelectKeyField
{
get{ return _DataKeyField; }
set{ _DataKeyField = value;}
}
/// <summary>
/// 杳询条件
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public string SelectWhere
{
get{ return _SelectWhere; }
set{ _SelectWhere = value; }
}
/// <summary>
/// 数据源
/// </summary>
public DataView DataSource
{
get{ return _datasource; }
//set{ _datasource = value;}
}
/// <summary>
/// 要绑定的 DataGrid 控件
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public string BindToDataGrid
{
get{ return _btDGrid; }
set{ _btDGrid = value; }
}
/// <summary>
/// 当前页码索引
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public int CurPageIndex
{
get{ return _CurPageIndex; }
set{ _CurPageIndex = value; }
}
/// <summary>
/// 排序字段
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public string SortField
{
get{ return _SortField; }
set{ _SortField = value;}
}
/// <summary>
/// 分页记录条数
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("25")]
public int PageSize
{
get{ return _PageSize; }
set{ _PageSize = value; }
}
/// <summary>
/// 查询数据来源表格
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public string TableName
{
get{ return _TableName; }
set{ _TableName = value;}
}
/// <summary>
/// 查询字段
/// </summary>
[Bindable(true),Category("Appearance"),DefaultValue("")]
public string SelectFields
{
get{ return _SelectFields; }
set{ _SelectFields = value; }
}
public bool LoadPostData(string postDataKey, NameValueCollection values)
{
_CurPageIndex = Int32.Parse(values[this.UniqueID]);
return false;
}
public void RaisePostDataChangedEvent()
{
// IPostBackDataHandler 协定的一部分。如果曾经从 LoadPostData 方法返回真
// （表示需要引发更改通知），则被调用。由于
// 始终返回假，则此方法只是一个空操作。
}
public void RaisePostBackEvent(string eventArgument)
{
this.CurPageIndex = Convert.ToInt32(eventArgument);
GetDataSource();
}
protected override void OnPreRender(EventArgs e)
{
//Page.RegisterPostBackScript();
//Page.Response.Write("2");
}
protected override void OnInit(EventArgs e)
{
DataGrid dGrid = (DataGrid)this.Parent.FindControl(this.BindToDataGrid);
dGrid.SortCommand += new DataGridSortCommandEventHandler(dGrid_SortCommand);
GetDataSource(); //获取数据源
GetDataSourcePageCount(); //获取页码数量
base.OnInit (e);
}
protected override void Render(HtmlTextWriter output)
{
//Page.Response.Write("3：" + this.PageCount.ToString());
int max;
int min;
int i,len;
int ShowSize = 10;
string tmp = null;
min = Convert.ToInt32(Math.Floor((this.CurPageIndex + 1) / ShowSize)) * ShowSize;
max = min + ShowSize;
len = this.PageCount;
if(max > len)
{
max = len;
}
output.Write("<Div class=\"" + PageCssClass + "\">");
if(min >= ShowSize)
{
output.Write("<a style=\"cursor:hand\" onClick=\"jscript:"+ Page.GetPostBackEventReference(this, (min-2).ToString())+ "\" title=\"向前翻页\"><font face=webdings>9</font></a> ");
}
for(i = min;i < max ;i++ )
{
if(i == this.CurPageIndex)
{
tmp = "<font class=\"" + this.CurPageCssClass + "\">" + (i+1).ToString() + "</font>";
}
tmp = (i+1).ToString();
if(i < max - 1)
{
output.Write("<a style=\"cursor:hand\" onclick=\"jscript:"+ Page.GetPostBackEventReference(this, i.ToString())+ "\">" + tmp + "</a>");
}
if(i < max - 2)
{
output.Write(" | ");
}
else if(i == max - 1 && (max == min + 10))
{
output.Write(" <a style=\"cursor:hand\" onClick=\"jscript:"+ Page.GetPostBackEventReference(this, i.ToString())+ "\" title=\"向后翻页\"><font face=webdings>:</font></a>");
}
}
output.Write("</Div>");
output.Write("<input type=\"hidden\" name=" + this.UniqueID + " type=text value=" + this.CurPageIndex + ">");
if(max <= 0)
{
output.Write("无数据！");
}
}
private void GetDataSourcePageCount()
{
if(ViewState["CurPageCount"] == null)
{
this.isCount = true;
}
else
{
return;
}
try
{
SqlParameter[] arParams = new SqlParameter[9];
arParams[0] = new SqlParameter("@tblName",SqlDbType.VarChar,255);
arParams[0].Value = this.TableName;
arParams[1] = new SqlParameter("@strGotFields",SqlDbType.VarChar,1000);
arParams[1].Value = this.SelectFields;
arParams[2] = new SqlParameter("@strSort",SqlDbType.VarChar,200);
arParams[2].Value = this.SelectKeyField;
arParams[3] = new SqlParameter("@OrderType",SqlDbType.Bit);
arParams[3].Value = true;
arParams[4] = new SqlParameter("@PageSize",SqlDbType.Int);
arParams[4].Value = this.PageSize;
arParams[5] = new SqlParameter("@PageIndex",SqlDbType.Int);
arParams[5].Value = 1;
arParams[6] = new SqlParameter("@strWhere",SqlDbType.VarChar,2000);
arParams[6].Value = this.SelectWhere;
arParams[7] = new SqlParameter("@isCount",SqlDbType.Bit);
arParams[7].Value = this.isCount;
arParams[8] = new SqlParameter("@PKName",SqlDbType.VarChar,200);
arParams[8].Value = this.SelectKeyField;
ViewState["CurPageCount"] = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(ForDataBase.ConnectionString,CommandType.StoredProcedure,"GetRecordFromPage",arParams).ToString();
this._PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToInt32(ViewState["CurPageCount"]) / this.PageSize));
this._PageCount = Convert.ToInt32(ViewState["CurPageCount"]) / this.PageSize;
}
catch(Exception ex)
{
Page.Response.Write(ex.Message);
}
//Page.Response.Write(ViewState["CurPageCount"].ToString());
}
private void GetDataSource()
{
bool tmp = true;
DataSet ds = new DataSet();
if(itype == OrderType.Desc)
{
tmp = false;
}
this.isCount = false;
SqlParameter[] arParams = new SqlParameter[9];
arParams[0] = new SqlParameter("@tblName",SqlDbType.VarChar,255);
arParams[0].Value = this.TableName;
arParams[1] = new SqlParameter("@strGotFields",SqlDbType.VarChar,1000);
arParams[1].Value = this.SelectFields;
arParams[2] = new SqlParameter("@strSort",SqlDbType.VarChar,200);
arParams[2].Value = this.SortField;
arParams[3] = new SqlParameter("@OrderType",SqlDbType.Bit);
arParams[3].Value = tmp;
arParams[4] = new SqlParameter("@PageSize",SqlDbType.Int);
arParams[4].Value = this.PageSize;
arParams[5] = new SqlParameter("@PageIndex",SqlDbType.Int);
arParams[5].Value = this.CurPageIndex + 1;
arParams[6] = new SqlParameter("@strWhere",SqlDbType.VarChar,2000);
arParams[6].Value = this.SelectWhere;
arParams[7] = new SqlParameter("@isCount",SqlDbType.Bit);
arParams[7].Value = this.isCount;
arParams[8] = new SqlParameter("@PKName",SqlDbType.VarChar,200);
arParams[8].Value = this.SelectKeyField;
ds = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(WebAppInc.ForDataBase.ConnectionString,CommandType.StoredProcedure,"GetRecordFromPage",arParams);
this._datasource = ds.Tables[0].DefaultView;
Page.Response.Write(Parent.FindControl(this.BindToDataGrid).ToString());
DataGrid dGrid = (DataGrid)this.Parent.FindControl(this.BindToDataGrid);
if(this.SortField != String.Empty)
{
ViewState["Sort"] = this.SortField;
if(tmp == true)
{
ViewState["Order"] = "asc";
}
else
{
ViewState["Order"] = "desc";
}
//DataGrid 控件排序样式
if(ViewState["Sort"] != null)
{
int i=0;
string strtmp = null;
for(i=0;i<dGrid.Columns.Count;i++)
{
strtmp = dGrid.Columns[i].HeaderText;
strtmp = strtmp.Replace("<font face=\"Webdings\">6</font>","");
strtmp = strtmp.Replace("<font face=\"Webdings\">5</font>","");
dGrid.Columns[i].HeaderText = strtmp;
if(dGrid.Columns[i].SortExpression == ViewState["Sort"].ToString())
{
if(ViewState["Order"].ToString() == "desc")
{
dGrid.Columns[i].HeaderText = strtmp + "<font face=\"Webdings\">6</font>";
}
else
{
dGrid.Columns[i].HeaderText = strtmp + "<font face=\"Webdings\">5</font>";
}
}
}
}
}
dGrid.DataSource = this._datasource;
dGrid.DataBind();
//Page.Response.Write("<br>Exe Sort：" + SortField);
}
private void dGrid_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
this.SortField = e.SortExpression;
string sort = e.SortExpression;
if(ViewState["Sort"] != null)
{
if(ViewState["Sort"].ToString() != sort)
{
ViewState["Sort"] = sort;
ViewState["Order"] = "desc";
}
else
{
if(ViewState["Order"].ToString() == "desc")
{
ViewState["Order"] = "asc";
}
else
{
ViewState["Order"] = "desc";
}
}
}
else
{
ViewState["Sort"] = sort;
ViewState["Order"] = "desc";
}
if(ViewState["Order"].ToString() == "desc")
{
this.Order = OrderType.Desc;
}
else
{
this.Order = OrderType.Asc;
}
GetDataSource();
//Page.Response.Write("<br>Sort：" + SortField);
}
}
}
