Coolite中GridPanel真实分页(储存过程方式)
Coolite中GridPanel自带的分页与GridView的分页一样，都是把所有数据全部读出放至内存中。
本文使用数据库中储存过程的方式与GridPanel配合完成，每次访问仅读出分页的数据。
前台：
<ext:Store ID="Store1" OnRefreshData="Store1_RefershData" runat="server" AutoLoad="true">
<Proxy>
<ext:DataSourceProxy>
</ext:DataSourceProxy>
</Proxy>
<Reader>
<ext:JsonReader ReaderID="id">
<Fields>
<ext:RecordField Name="id" Type="Int" />
<ext:RecordField Name="SortNode" />
<ext:RecordField Name="Coding" />
</Fields>
</ext:JsonReader>
</Reader>
</ext:Store>
<ext:GridPanel ID="GridPanel1" AutoHeight="true" StoreID="Store1" runat="server" >
<TopBar>
<ext:Toolbar ID="Toolbar1" runat="server">
<Items>
<ext:ComboBox ID="cboSelectCol" runat="server" AllowBlank="false" Editable="false"
FieldLabel="检索条件" EmptyText="请选择检索条件" >
<Items>
<ext:ListItem Text="按标题检索" Value="Title"/>
<ext:ListItem Text="按编码检索" Value="Coding" />
<ext:ListItem Text="按关键字检索" Value="Keyword" />
</Items>
</ext:ComboBox>
<ext:TextField ID="txtSelectValue" runat="server" FieldLabel="检索内容" AllowBlank="false"
Width="300" EmptyText="请输入检索内容" BlankText="请输入检索内容!"/>
<ext:Button ID="butSelectList" runat="server" Text=" 高级检索 " Icon="Accept">
<AjaxEvents>
<Click OnEvent="butSelectList_Click">
</Click>
</AjaxEvents>
</ext:Button>
</Items>
</ext:Toolbar>
</TopBar>
<ColumnModel ID="ColumnModelTitle" IDMode="Legacy" Height="30" runat="server">
<Columns>
<ext:Column ColumnID="id" DataIndex="id" Header="知识编码" Sortable="true"/>
<ext:Column ColumnID="SortNode" DataIndex="SortNode" Header="类型" Sortable="true"/>
<ext:Column ColumnID="Title" DataIndex="Title" Header="标题" Sortable="true" Width="200" />
<ext:Column ColumnID="Keyword" DataIndex="Keyword" Header="关键字" Sortable="true"/>
</Columns>
</ColumnModel>
<SelectionModel>
<ext:RowSelectionModel SelectedRecordID="id" ID="RowSelectionModel1" runat="server">
<CustomConfig>
<ext:ConfigItem Name="checkOnly" Value="true" Mode="Raw" />
</CustomConfig>
</ext:RowSelectionModel>
</SelectionModel>
<LoadMask ShowMask="true" Msg="Load..."/>
<BottomBar>
<ext:PagingToolbar ID="pagecut" runat="server" StoreID="Store1" PageSize="5"></ext:PagingToolbar>
</BottomBar>
</ext:GridPanel>
后台：
protected void butSelectList_Click(object sender, AjaxEventArgs e)//高级检索
{
if (this.txtSelectValue.Text == "")
{ Ext.Msg.Alert("消息", "请输入检索内容！").Show(); return;}
this.Store1.DataBind();//重新绑定
}
protected void Store1_RefershData(object sender, StoreRefreshDataEventArgs e)
{
int PageSize = this.pagecut.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值
int Count = 0;
int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页
string strWhere="";
if (this.txtSelectValue.Text != "")//查询条件
{
strWhere= this.cboSelectCol.SelectedItem.Value + " like '%" + this.txtSelectValue.Text + "%'";
}
DataTable dt = CommonQuery.m_QueryPagination("kno_Knowledge", "id,SortNode,Coding,Title,Keyword,Describe,Link,Author,CreateDate", strWhere, PageSize, CurPage, out Count);
e.TotalCount = Count;
if (Count > 0)
{
this.Store1.DataSource = dt;//绑定数据
this.Store1.DataBind();
}
else
{
Ext.Msg.Show(new MessageBox.Config { Title = "查询结果", Message = "<font style='color:red;'>没有找到任何数据!</font>", Buttons = MessageBox.Button.OK, Icon = MessageBox.Icon.INFO });
}
}
//访问储存过程的方法
public static DataTable m_QueryPagination(string TableName, string ReturnFields, string strwhere, int PageSize, int CurPage, out int RowCount)
{
SqlParameter[] parameters = {
new SqlParameter("@TableName", TableName),//表名
new SqlParameter("@SelectFieldName", ReturnFields),//要显示的字段名(不要加select)
new SqlParameter("@strWhere", strwhere),//查询条件(注意: 不要加 where)
new SqlParameter("@OrderFieldName", "id"),//排序索引字段名
new SqlParameter("@OrderType", 0),//设置排序类型, 非 0 值则降序
new SqlParameter("@PageSize", PageSize),//页码
new SqlParameter("@PageIndex", CurPage),//页大小
new SqlParameter("@iRowCount", SqlDbType.Int) };//返回记录总数
parameters[7].Direction = ParameterDirection.Output;
DataSet ds = DbHelperSQL.RunProcedure("QueryPagination", parameters, "ListTable"); //DbHelperSQL微软通用的SQL访问类
RowCount = Convert.ToInt32(parameters[7].Value.ToString()); //返回的总页数
return ds.Tables[0];
}
储存过程：
CREATE procedure QueryPagination
@TableName varchar(1000), -- 表名
@SelectFieldName varchar(4000), -- 要显示的字段名(不要加select)
@strWhere varchar(4000), -- 查询条件(注意: 不要加 where)
@OrderFieldName varchar(255), -- 排序索引字段名
@OrderType bit = 0 , -- 设置排序类型, 非 0 值则降序
@PageSize int , -- 页大小
@PageIndex int = 1, -- 页码
@iRowCount int output -- 返回记录总数
AS
declare @strSQL varchar(4000) -- 主语句
declare @strTmp varchar(4000) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strRowCount nvarchar(4000) -- 用于查询记录总数的语句
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @TableName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @TableName + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @TableName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @TableName + ' 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) + @SelectFieldName+' from '
+ @TableName + @strTmp + ' ' + @strOrder
end
exec(@strSQL)
if @strWhere!=''
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @TableName+' where '+@strWhere
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @TableName
end
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out
GO
