DataGridView分页问题
DGV是绑定的数据源“DataTable”，如果“dt”也就是数据库中的表中的数据量比较大的话，如果还只是直接绑定，而不进行分页。是不是会浪费资源，而影响性能呢？如果要进行分页，请问应该怎样做？
能给点实例或是源码吗？ --------------------编程问答-------------------- 前台
用apsnetpager控件，上网上自己下一个
--------------------编程问答-------------------- 顶。。。 --------------------编程问答-------------------- 你是c#吗？那我做了一个分页控件，有源码 --------------------编程问答-------------------- 用存储过程在数据库中实现分页查询
<webdiyer:AspNetPager ID="AspNetPager1" CssClass="anpager" CurrentPageButtonClass="current"
runat="server" FirstPageText="首页" LastPageText="尾页" NextPageText="后页" OnPageChanging="AspNetPager1_PageChanging"
PageIndexBoxType="DropDownList" PrevPageText="前页" ShowPageIndexBox="Always" SubmitButtonText="Go"
TextAfterPageIndexBox="页" TextBeforePageIndexBox="转到">
</webdiyer:AspNetPager>
--------------------编程问答-------------------- 后台
--------------------编程问答-------------------- 比如先定好每页100条记录，那么通过存储过程每次就只查询100条绑定到gridview，点击下页或者页数时候再进行一次查询绑定，在存储过程中需要传递一个第几页的输入参数 --------------------编程问答-------------------- 数据库存储过程
protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
AspNetPager1.CurrentPageIndex = e.NewPageIndex;
this.gvBind(txtSeachArea.Text.Trim());
}
//load方法里
if (!Page.IsPostBack)
{
this.AspNetPager1.PageSize = 10;
gvBind("");//数据邦定方法
}
public void gvBind("")
{
string parName = "Proc_SplitPage";//存储过程名
SqlParameter[] param = {
new SqlParameter("@RowsCount",SqlDbType.Decimal,32),//[总条数]
new SqlParameter("@PageCount",SqlDbType.Decimal,32),//[总页数]
new SqlParameter("@PageIndex",SqlDbType.Decimal,32),//[要显示的页码]
new SqlParameter("@TopNum",SqlDbType.Decimal,32),//[每页显示的条数]
new SqlParameter("@ColsName",SqlDbType.VarChar,5000),//[列名]
new SqlParameter("@ColsNameFact",SqlDbType.VarChar,5000),//[构造后的列名]
new SqlParameter("@TableName",SqlDbType.VarChar,5000),//[表名]
new SqlParameter("@Term",SqlDbType.VarChar,5000),//[条件]
new SqlParameter("@Group",SqlDbType.VarChar,5000),//[是否分组]
new SqlParameter("@OrderCols",SqlDbType.VarChar,5000),//[排序 排序方式]
};
this.AspNetPager1.RecordCount = pagecount;
param[0].Value = pagecount;
if (pagecount % int.Parse(this.dropRowsPage.SelectedValue) > 0)
{
param[1].Value = pagecount / int.Parse(this.dropRowsPage.SelectedValue) + 1;
}
else
{
param[1].Value = pagecount / int.Parse(this.dropRowsPage.SelectedValue);
}
param[2].Value = this.AspNetPager1.CurrentPageIndex;
param[3].Value = this.AspNetPager1.PageSize;
param[...].Value = ....;
dtFiles = Data.SQLHelper.ProQuery(parName, param).Tables[0];//给你的dgv赋数据源
}
--------------------编程问答-------------------- 在页面上分页会占用系统内存，如果数据量巨大估计加载数据的时间都够呛的 --------------------编程问答-------------------- SELECT TOP 每页记录数 * FROM 表名
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[Proc_SplitPage] Script Date: 11/10/2010 16:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*测试语句
EXEC Proc_SplitPage
@RowsCount=22,@PageCount=5,@PageIndex=4,@TopNum=5,
@ColsName='u.ID,u.UserID,u.UserName,u.UserShowName,l.Remark',
@TableName='tb_UserInfo u left join tb_UserLog l on u.UserID=l.UserID',
@Term='u.ID>''1''',
@Group='1',
@OrderCols='u.ID ASC'
EXEC Proc_SplitPage
@RowsCount=22,@PageCount=5,@PageIndex=4,@TopNum=5,
@ColsName=' u.UserName,u.UserShowName,ci.ImgUrl,
vfs.FileID,vfs.SharingCount, v.CreateUser ',
@TableName='view_Files v
left join view_FileSharing vfs on v.directID=vfs.FileID
left join dbo.tb_UserInfo u on v.CreateUser=u.UserID
left join dbo.tb_ClsImg ci on LOWER(v.fileExtension)=LOWER(ci.cls)',
@Term='v.parentID=''1546''',
@Group='1',
@OrderCols='v.Sort DESC,v.dirOrder DESC,v.UpdateTime DESC'
GO
select u.ID,u.UserID,u.UserName,u.UserShowName,l.Remark
from tb_UserInfo u left join tb_UserLog l on u.UserID=l.UserID
WHERE u.UserID>1
GROUP BY u.ID,u.UserID,u.UserName,u.UserShowName,l.Remark
ORDER BY U.ID ASC
SELECT * FROM (SELECT TOP 5 ID,UserID,UserName,UserShowName,Remark FROM (SELECT TOP 7 u.ID,u.UserID,u.UserName,u.UserShowName,l.Remark FROM tb_UserInfo u left join tb_UserLog l on u.UserID=l.UserID WHERE u.ID>'1' GROUP BY u.ID,u.UserID,u.UserName,u.UserShowName,l.Remark ORDER BY u.ID DESC) AS tb_DL ORDER BY ID ASC) AS tb_Return ORDER BY ID ASC
GO
*/
ALTER PROC [dbo].[Proc_SplitPage]
@RowsCount DECIMAL(32,3), --[总条数]
@PageCount DECIMAL(32,3), --[总页数]
@PageIndex DECIMAL(32,3), --[要显示的页码]
@TopNum DECIMAL(32,3), --[每页显示的条数]
@ColsName VARCHAR(5000), --[列名]
@ColsNameFact VARCHAR(5000),--[]
@TableName VARCHAR(5000), --[表名]
@Term VARCHAR(5000), --[条件]
@Group VARCHAR(5000), --[是否分组]
@OrderCols VARCHAR(5000) --[排序 排序方式]
AS
DECLARE
@Exec VARCHAR(5000), --[要执行的SQL字符串]
@ExecPart VARCHAR(5000), --[截取的部分SQL字符串]
@ASC VARCHAR(50), --[正常排序字段]
@ASCPart VARCHAR(50), --[相反排序字段]
@TopNumPart DECIMAL(32,3), --[核心层筛查出的条数]
@PageMide DECIMAL(32,3), --[中间页码]
@LastPageRows DECIMAL(32,3) --[最后一页的条数]
/*设置 [要执行的SQL字符串][截取的部分SQL字符串]的默认值*/
SET @Exec=''
SET @ExecPart=''
/*计算[中间页码]*/
SET @PageMide=(@RowsCount/cast(@TopNum as decimal(32,3))/2)+1
if(@PageIndex<=0)
Begin
set @PageIndex=1
end
if(@PageIndex>@PageCount)
begin
set @PageIndex=@PageCount
end
/*
取之顺序：
如果[要显示的页码]大于[中间页码]，则 倒取--正取--排序；
如果[要显示的页码]小于[中间页码]，则 正取--倒取--排序
*/
IF CAST(@PageIndex AS DECIMAL(32,3))>@PageMide
BEGIN
SET @TopNumPart=@RowsCount-((@PageIndex-1)*@TopNum)
--PRINT '倒序'+CAST(@TopNumPart AS VARCHAR(500))
SET @ASCPart=@OrderCols
SET @ASC=@OrderCols
SET @ASC=REPLACE(@ASC,'ASC','@@@')
SET @ASC=REPLACE(@ASC,'DESC','ASC')
SET @ASC=REPLACE(@ASC,'@@@','DESC')
END
ELSE
BEGIN
SET @TopNumPart=@PageIndex*@TopNum
--PRINT '正序'+CAST(@TopNumPart AS VARCHAR(500))
SET @ASC=@OrderCols
SET @ASCPart=@OrderCols
SET @ASCPart=REPLACE(@ASCPart,'ASC','@@@')
SET @ASCPart=REPLACE(@ASCPart,'DESC','ASC')
SET @ASCPart=REPLACE(@ASCPart,'@@@','DESC')
END
IF LEN(@ColsName)>0
BEGIN
/*构造核心层的SQl语句*/
SET @ExecPart='SELECT TOP '+CAST(CAST(@TopNumPart AS INT) AS VARCHAR(500))+' '
SET @ExecPart=@ExecPart+@ColsName+' FROM '+@TableName
SET @ExecPart=@ExecPart+' WHERE 1=1 '+@Term
IF @Group='1'
BEGIN
SET @ExecPart=@ExecPart+' GROUP BY '+@ColsName
END
SET @ExecPart=@ExecPart+' ORDER BY '+@ASC
/*构造中间层的SQL语句*/
SET @Exec='SELECT TOP '+CAST(CAST(@TopNum AS INT) AS VARCHAR(500))+' '+dbo.FUN_OnlyColsName(@ColsName)
SET @Exec=@Exec+' FROM ('+@ExecPart+') AS tb_DL'
SET @Exec=@Exec+' ORDER BY '+ dbo.FUN_OnlyColsName(@ASCPart)
/*构造最外层的SQL语句*/
SET @Exec='SELECT '+@ColsNameFact+' FROM ('+@Exec+') AS tb_Return ORDER BY '+dbo.FUN_OnlyColsName(@OrderCols)
END
PRINT @Exec
EXECUTE (@Exec)
GO
WHERE id NOT IN
(
SELECT TOP 每页记录数 * 当前页码 id
)
如果用LinqToSQL查询就用.Take(每页记录数).Skip(页数) --------------------编程问答--------------------
--------------------编程问答--------------------
dbo.FUN_OnlyColsName()这个Function的代码呢？
补充：.NET技术 , C#
