asp.net 如何让处理绑定10万条以上的数据
只能用存储过程分页,每次只取一页的数据,下面这个存储过程承受30万的数据量完全没问题。
--分页存储过程
CREATE PROC Common_Page
@RecordCount INT,
@QueryStr NVARCHAR(100)='table1',--表名、视图名、查询语句
@PageSize INT = 20, --每页的大小(行数)
@PageCurrent INT = 0, --要显示的页 从0开始
@FdShow NVARCHAR (1000)='*', --要显示的字段列表
@IdentityStr NVARCHAR (100)='id', --主键
@WhereStr NVARCHAR (1000)='1=1',
@FdOrder NVARCHAR(100)='desc' --排序 只能取desc或者asc
AS
DECLARE
@sql NVARCHAR(4000)
IF @WhereStr = ''
BEGIN
SET @WhereStr = '1=1'
END
SET @WhereStr = ' ( ' + @WhereStr + ' ) '
IF @PageCurrent = 0
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @FdShow + ' from ' +@QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
END
ELSE
BEGIN
IF UPPER(@FdOrder) = 'DESC'
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (SELECT TOP ' + CAST(@PageSize*@PageCurrent AS NVARCHAR(10)) + ' ' + @IdentityStr+ ' FROM ' + @QueryStr + ' WHERE ' + @WhereStr + ' ORDER BY ' + @IdentityStr + ' DESC) AS t) ORDER BY ' + @IdentityStr + ' DESC'
END
ELSE
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + CAST(@PageSize*@PageCurrent AS NVARCHAR(10)) + ' ' + @IdentityStr + ' FROM ' + @QueryStr + ' WHERE ' + @WhereStr + ' ORDER BY ' + @IdentityStr + ' ASC) AS t) ORDER BY ' + @IdentityStr + ' ASC'
END
END
--print @sql
EXECUTE sp_executesql @sql
IF(@RecordCount is null or @RecordCount<0)
BEGIN
DECLARE @tsql NVARCHAR(1000)
SET @tsql=N'SELECT @RecordCount = count(*) FROM ' + @QueryStr + ' WHERE ' + @WhereStr
EXEC sp_executesql @tsql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SELECT @RecordCount
END
GO
--分页存储过程
CREATE PROC Common_Page
@RecordCount INT,
@QueryStr NVARCHAR(100)='table1',--表名、视图名、查询语句
@PageSize INT = 20, --每页的大小(行数)
@PageCurrent INT = 0, --要显示的页 从0开始
@FdShow NVARCHAR (1000)='*', --要显示的字段列表
@IdentityStr NVARCHAR (100)='id', --主键
@WhereStr NVARCHAR (1000)='1=1',
@FdOrder NVARCHAR(100)='desc' --排序 只能取desc或者asc
AS
DECLARE
@sql NVARCHAR(4000)
IF @WhereStr = ''
BEGIN
SET @WhereStr = '1=1'
END
SET @WhereStr = ' ( ' + @WhereStr + ' ) '
IF @PageCurrent = 0
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @FdShow + ' from ' +@QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
END
ELSE
BEGIN
IF UPPER(@FdOrder) = 'DESC'
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (SELECT TOP ' + CAST(@PageSize*@PageCurrent AS NVARCHAR(10)) + ' ' + @IdentityStr+ ' FROM ' + @QueryStr + ' WHERE ' + @WhereStr + ' ORDER BY ' + @IdentityStr + ' DESC) AS t) ORDER BY ' + @IdentityStr + ' DESC'
END
ELSE
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + CAST(@PageSize*@PageCurrent AS NVARCHAR(10)) + ' ' + @IdentityStr + ' FROM ' + @QueryStr + ' WHERE ' + @WhereStr + ' ORDER BY ' + @IdentityStr + ' ASC) AS t) ORDER BY ' + @IdentityStr + ' ASC'
END
END
--print @sql
EXECUTE sp_executesql @sql
IF(@RecordCount is null or @RecordCount<0)
BEGIN
DECLARE @tsql NVARCHAR(1000)
SET @tsql=N'SELECT @RecordCount = count(*) FROM ' + @QueryStr + ' WHERE ' + @WhereStr
EXEC sp_executesql @tsql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SELECT @RecordCount
END
GO
