优化的选择查询可以获得10到10条记录，形成数百万条记录 [英] Optimized select query to get 10 by 10 records form millions of records

问题描述

在我的数据库中，我有10000条记录。在GridView首先，我显示前十条记录。为了查看下一个10条记录用户需要单击下一步按钮。但是当下一个按钮点击它显示最后10条记录，所以我需要检查10到10条记录，同时单击下一步按钮。



有没有办法在ASP.NET中显示GridView？

In My Database I Have 10000 Records. In GridView First I Am Showing First 10 Records. In Order To See the Next 10 Records User Need to Click Next Button. But when next button clicks it shows last 10 records so i need to check 10 by 10 records while click next button.

Is There Any Way To Show In ASP.NET GridView ?

推荐答案

这是服务器端cutom分页的概念：

Here is an idea of cutom pagination on server side:

--create temporary table
DECLARE @tmp TABLE(ID INT,SomeText NVARCHAR(50))
--add sample data
;WITH InsertionCTE AS
(
	SELECT 1 AS ID, NEWID() AS SomeText
	UNION ALL
	SELECT ID + 2 AS ID, NEWID() AS SomeText
	FROM InsertionCTE 
	WHERE ID<1000
)
INSERT INTO @tmp (ID, SomeText )
SELECT ID, SomeText
FROM InsertionCTE
OPTION (MAXRECURSION 0)

--------------------------------------
--------------------------------------

--an idea of SP begins here:
--------------------------------------
--SP input parameters
DECLARE @startpos INT = 31
DECLARE @countofrecs INT = 10

--body of SP
SELECT RowNo, ID, SomeText
FROM (
	SELECT ID, SomeText, ROW_NUMBER() OVER(ORDER BY ID) AS RowNo
	FROM @tmp
) AS T
WHERE RowNo BETWEEN @startpos AND @startpos + @countofrecs -1

结果：

Result:

31	61	B0D1ED50-48BD-4FDF-8EAD-6575FA2957DF
32	63	3CD0B40D-5DDD-48F7-BEAC-8BCFCDB7F037
33	65	9443CDBC-0004-4C7D-A5CB-1B1D30750405
34	67	FEEFC83D-756A-4A99-8DA5-EB95344CDEA8
35	69	7F38B406-98A0-4D4A-8B61-716BF4B0AA6B
36	71	8584DD61-A2C5-4F1A-B56B-7D2AF0F9AF70
37	73	6BBBF847-516A-42E1-9C8A-B7A67A844145
38	75	C45FF16F-6C3C-46DD-849E-5F5DA667D045
39	77	4D6A745E-2ECB-493C-9F81-AF4CCBCFAC3E
40	79	87BD7EA5-12EA-4310-AC3C-D9E3B6C32551

如需更多样品，请使用SearchBox [ ^ ]。

有几种方法可以做到这一点



你应该搜索grid view pagination



这里有几篇关于codeproject的文章和问题



请注意有2种分页方式，

客户端（查询返回10.000条记录和gui10时间）

服务器端（查询返回10条记录，gui相同）



服务器端是减少服务器负载的最佳选择

there are several way to doing that

you should search for "grid view pagination"

there are several article and question here on codeproject

note that there are 2 way of pagination ,
client side (the query returns 10.000 records and the "gui" 10 at time)
server side (the query returns 10 records and the gui the same)

server side is the best choice to reduce the server load

这篇关于优化的选择查询可以获得10到10条记录，形成数百万条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！