在select查询中使用order by时，按页面顺序对gridview列进行排序 [英] sorting gridview columns pagewise when we use order by inthe select query

问题描述

嗨 我正在将数据从sqlserver提取到gridview.
我正在使用按国家/地区排序"对gridview列(国家/地区代码)进行排序
和按代码排序"
在查询中.我的意思是，我不使用datview.sort属性进行排序.
排序和分页一切正常...

我的问题是:

当我转到nextpage(page2)并单击标题进行排序时，它正在对所有数据进行排序.我只需要对page2中的数据进行排序.我知道原因是排序是从数据库而不是网格中进行的.

我该如何实现这一目标....请尽快提供帮助

感谢

解决方案

这里是存储proc调用的示例，您可以在其中传递页码和计数或返回的行以及"IN"参数.如有必要，您可以将其更改为您的国家代码，例如.

键是< b> SELECT ROW_NUMBER()OVER(按名称排序)AS行</b>

在GridView上的网格排序事件中，添加对DB的调用，以仅返回您需要的数据.

如果您有大数据集，这将更快地工作，因此您不需要将所有数据仅连接一小块...

< pre>
创建过程[dbo].[sp_test]
@list nvarchar(256)，
@start int，
@count int
AS
开始
宣告@sql nvarchar(256)
宣告@params nvarchar(256)
宣告@end int
SET @end = @start + @count-1
SET @params = N''@开始整数，@count整数，@end整数''
SET @sql = N''
SELECT ID，类型，名称
从测试
在哪里输入ID(
从
选择ID (SELECT ROW_NUMBER()OVER(按名称排序)AS行，ID
从测试
在哪里输入(''+ @list + N'')
)PAGED
行> = @开始AND行< = @end
)
''
打印@sql
EXEC SP_EXECUTESQL @ sql，@ params，@ start，@ count，@ end
</pre>

hi i am fetching the data from the sqlserver to the gridview.
i am sorting the gridview columns(country,code)using "order by country"
and "order by code"
in the query. I mean, iam not sorting using the datview.sort property.
sorting and paging all working fine...

my problem is:

when i go to nextpage(page2) and clicked on header to sort, it is sorting all the data. i need to sort only the data in page2. i know the reason is the sorting is from database not in the grid.

how can I achieve this in my case....please help ASAP

thanks

解决方案

Here is an example of stored proc call where you can pass page number and count or rows returned as well as "IN" parameters. If needed, you can change them to your country code for example.

The key is <b>SELECT ROW_NUMBER() OVER (ORDER BY Name) AS Row</b>

in your GridView on grid sorting event add a call to DB to return only the data you''ll need.

This is works faster if you have large data sets, so you don''t need to wire all the data just a small chunk...

<pre>
CREATE PROCEDURE [dbo].[sp_test]
@list nvarchar(256),
@start int,
@count int
AS
BEGIN
DECLARE @sql nvarchar(256)
DECLARE @params nvarchar(256)
DECLARE @end int
SET @end = @start + @count - 1
SET @params = N''@start int, @count int, @end int''
SET @sql = N''
SELECT id, type, name
FROM TEST
WHERE ID IN (
SELECT ID FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Name) AS Row, ID
FROM TEST
WHERE TYPE IN ('' + @list + N'')
) PAGED
WHERE Row >= @start AND Row <= @end
)
''
PRINT @sql
EXEC SP_EXECUTESQL @sql, @params, @start, @count, @end
</pre>

这篇关于在select查询中使用order by时，按页面顺序对gridview列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！