c# - Modify custom query to paginate with a gridview -

i have following code executes custom query database. queries definition stored on table, users wont enter pagination.

because can return 10,000 rows or more need paginate modifying query.

querysql= "select * requestbases currentstatus == 'approved 1' , ammountwithvat>100000"

it returns 10,000 rows.

my code:

 public datatable getgenericresults(string strsql)             {                 using(var connection = (sqlconnection)_context.database.connection)                 {                     var adapter = new sqldataadapter(strsql, connection);                     var results = new dataset();                     adapter.fill(results, "results");                     return results.tables["results"];                 }             }     var datatable = requestbasebl.getgenericresults(query.querysql);                     if (datatable.rows.count > 0)                     {                         lblcount.text = datatable.rows.count + " records";                         panelresults.visible = true;                         grvcustomresults.visible = true;                         grvcustomresults.datasource = datatable;                         grvcustomresults.databind(); 

i use query in cte, this

with mypageddata ( select *, row_number() over(order identitycol desc) rownum, row_number() over(order identitycol asc) inverserownum requestbases currentstatus == 'approved 1' , ammountwithvat > 100000 )  select * mypageddata rownum between @startindex , @startindex + 20   

so assumes able write code inserts rownumber bit in right place of select statement , using mssql 2005. may need adjust identitycol bit if haven't got identity columns.

so startindex last shown item of data , 20 amount of items want on page. total amount of items sum of rownum , inverserownum -1.