ASP.Net Custom Paging (w/ C#)

Cenario:

I have a GridView bound to a DataSource, every column is sortable.

my main query is something like:

select a, b, c, d, e, f from table order by somedate desc

i added a filter form where i can define values to each one of the fields and get the results of a where form. As a result from this, i had to do a custom sorting so that when i sort by a field, i am sorting the filtered query and not the main one. Now i have to do custom paging, for the same reason, but i don't understand the philosophy of it: I want to guarantee that i can:

1. filter the results
2. sort by a column
3. whe开发者_运维技巧n i click on page 2, i get page two of the filtered and sorted results

I don't know what i have to do, so i can bind the GV with this. My sorting Method, that is working just fine looks something like:

string condition = GetConditions(); //gets  a string like " where a>1 and b>2" depending on the filter the user defines
string query = "select a, b, c, d, e, f from table ";
string direction = (e.SortDirection == SortDirection.Ascending)? "asc": "desc";
string order = " order by " + e.SortExpression + " " + direction;
UtilizadoresDataSource.SelectCommand = query + condition + order;

i've never done custom paging, i am trying:

GetConditions() //no problem here

How can i find out how the GridView is sorted (by what field and sortingorder)?

thank you very much

You can use ROW_NUMBER to get the number of rows that a query is returning and then filter only those element that will be visible for the given page. For example you should add ROW_NUMBER function in the select clause and add the filtering in the where cause.

string condition = GetConditions(); //gets  a string like " where a>1 and b>2" depending on the filter the user defines
string query = "select ROW_NUMBER() OVER(ORDER BY " + order + ") a, b, c, d, e, f from table ";
string direction = (e.SortDirection == SortDirection.Ascending)? "asc": "desc";
string order = " order by " + e.SortExpression + " " + direction;
condition = condition + " RowNo  BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) "
UtilizadoresDataSource.SelectCommand = query + condition + order;

You can find a more detailed example here.It also contains a sample project with binding a grid.

P.S. I would suggest you to create a stored procedure and pass the parameters from the code behind. This can increase the speed and also it easier to maintain.