想要查询100多万条记录,用DataGrid怎样做好分页呀,这里不能一次性把记录都取出来的,因为数据太多了。
高手帮忙呀!
高手帮忙呀!
解决方案 »
- 将word转换成html
- 下面代码是什么意思啊
- 帮忙,求一SQL语句。。
- DetailsView的编辑怎么进入?怎么用?
- 在线高分求助, asp.net2.0 的treeview实现右键菜单
- 如何部署使用了COM的asp.net应用程序?急需解决,期待解答!!!
- 这样的统计应该用什么东西做?
- *******我公司现招人,需精通ASP.NET+SQL SERVER2000+C#/VB.NET,曾经参与开发过.net较大项目上,并有作品*******
- 请大家谈谈Visual Studio 2002与Visual Studio 2003有异同点!
- 关于IIS工作线程数的设置
- 一个存储过程在sql server里就对的,在asp.net一调用就出错。
- 哪位大哥给个用C#写的3层结构的程序。
czheng能不能详细说说呀,我是新手呀。
也可用分页控件
AspNetPager
(
@targettable nvarchar(50), --目标表、视图..
@key nvarchar(50), --关键字段(无重复)
@swhere nvarchar(4000), --查询条件( where ....)
@pageSize int, --每页显示记录条数
@pageIndex int, --要显示第几页
@allnum int output
)
AS
set nocount on
begin
create table #t(id int identity(1,1),username nvarchar(50))
declare @PageLowerBound int
declare @PageUpperBound int
declare @table nvarchar(4000)
--declare @spnum int
set @table=N'select @num=count(*) from '+@targettable +@swhere
exec sp_executesql @table,
N'@num int output',
@allnum output
set @PageLowerBound=(@pageIndex-1)*@pageSize
set @PageUpperBound=@PageLowerBound+@pageSize
set rowcount @PageUpperBound
set @table=N'insert into #t(username) select '+@key+N' from ' +@targettable +@swhere
exec sp_executesql @table set @table=N'select R.* from '+@targettable+N' R,#t t where R.'+@key+N'=t.username'
+N' and t.id>'+cast(@PageLowerBound as nvarchar(30))+N' and t.id<='+cast(@PageUpperBound as nvarchar(30))
exec sp_executesql @table
select @allnum
set rowcount 0
end set nocount offGO
SqlConnection sconn=new SqlConnection(conn); //建立连接
SqlCommand scomm=new SqlCommand();
scomm.Connection=sconn;
scomm.CommandText="page_res"; //存储过程名称
scomm.CommandType=CommandType.StoredProcedure;//command为存储
//过程----下面为添加各种参数---
SqlParameter sp=new SqlParameter("@targettable",SqlDbType.NVarChar,50);
sp.Direction=ParameterDirection.Input;
sp.Value="qyml";
scomm.Parameters.Add(sp);
sp=new SqlParameter("@key",SqlDbType.NVarChar,50);
sp.Direction=ParameterDirection.Input;
sp.Value="id";
scomm.Parameters.Add(sp);
sp=new SqlParameter("@swhere",SqlDbType.NVarChar,4000);
sp.Direction=ParameterDirection.Input;
sp.Value=" where 1=1 ";
scomm.Parameters.Add(sp);
sp=new SqlParameter("@pwhere",SqlDbType.NVarChar,4000);
sp.Direction=ParameterDirection.Input;
sp.Value=" where 1=1 ";
scomm.Parameters.Add(sp);
sp=new SqlParameter("@pageSize",SqlDbType.Int);
sp.Direction=ParameterDirection.Input;
sp.Value=5;
scomm.Parameters.Add(sp);
sp=new SqlParameter("@pageIndex",SqlDbType.Int);
sp.Direction=ParameterDirection.Input;
sp.Value=pageindex;
scomm.Parameters.Add(sp);
sp=new SqlParameter("@allnum",SqlDbType.Int);
sp.Direction=ParameterDirection.Output;
sp.Value=0;
scomm.Parameters.Add(sp);
SqlDataAdapter sda=new SqlDataAdapter(scomm);//声明数据适配器
DataSet ds=new DataSet();
sda.Fill(ds,"coms");// 用存储过程返回的行填充dataset
coms.DataSource=ds.Tables[0].DefaultView;
coms.DataBind();//将数据绑定到datagrid上面
用获取上一页最后一条数据id,然后select 这一页的数据 id+1 到id+1+pagesize
-- 分页
----------------------------------------------------
CREATE PROCEDURE GetRecords
@tblName VARCHAR(255), -- 表名
@fldName VARCHAR(255), -- 字段名
@strFields VARCHAR(5000) = '*', -- 获取的字段字符串
@PageSize INT = 10, -- 页尺寸
@PageIndex INT = 0, -- 页码 (从0开始)
@OrderType BIT = 0, -- 设置排序类型, 非 0 值则降序
@strWhere VARCHAR(1000) = '' -- 查询条件 (注意: 不要加 where)
AS DECLARE @strSQL VARCHAR(8000) -- 主语句
DECLARE @strCountSQL VARCHAR(2000) -- 统计总记录数语句
DECLARE @strTmp VARCHAR(1000) -- 临时变量
DECLARE @strOrder VARCHAR(1000) -- 排序类型
DECLARE @fldInName VARCHAR(255) -- 内层字段名
DECLARE @prefix INT -- 字段名前缀位置
DECLARE @fldNameLen INT -- 字段名长度 SET NOCOUNT ON SET @prefix = CHARINDEX(".", @fldName) -- 判断字段名是否含有前缀
IF @prefix > 0
BEGIN
SET @fldNameLen = LEN(@fldName)
SET @fldNameLen = @fldNameLen - @prefix
SET @prefix = @prefix + 1
SET @fldInName = SUBSTRING(@fldName, @prefix, @fldNameLen)
END
ELSE
SET @fldInName = @fldName -- 是否降序排序
IF @OrderType <> 0
BEGIN
SET @strTmp = " <(SELECT MIN"
SET @strOrder = " ORDER BY " + @fldName +" DESC"
END
ELSE
BEGIN
SET @strTmp = " >(SELECT MAX"
SET @strOrder = " ORDER BY " + @fldName +" ASC"
END
-- 是否为第一页
IF @PageIndex = 0
BEGIN
SET @strTmp = "" IF @strWhere <> ''
SET @strTmp = " WHERE (" + @strWhere + ")" SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strFields + " FROM " + @tblName + @strTmp + " " + @strOrder
END
ELSE
BEGIN
IF @strWhere <> ''
SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strFields + " FROM "
+ @tblName + " WHERE " + @fldName + @strTmp + "([" + @fldInName + "]) FROM (SELECT TOP "
+ STR(@PageIndex * @PageSize) + " " + @fldName + " FROM " + @tblName + " WHERE (" + @strWhere + ") "
+ @strOrder + ") AS tblTmp) AND (" + @strWhere + ") " + @strOrder
ELSE
SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strFields + " FROM "
+ @tblName + " WHERE " + @fldName + @strTmp + "([" + @fldInName + "]) FROM (SELECT TOP "
+ STR(@PageIndex * @PageSize) + " " + @fldName + " FROM " + @tblName + @strOrder + ") AS tblTmp)"
+ @strOrder
END -- 统计总记录数
SET @strTmp = "" IF @strWhere <> ''
SET @strTmp = " WHERE (" + @strWhere + ")" SET @strCountSQL = "SELECT COUNT(" + @fldName + ") AS Total FROM " + @tblName + @strTmp EXEC (@strSQL)
EXEC (@strCountSQL) RETURN 0