ASP.NET move to next record or move to previous record

Basically, ASP.NET does not have move next or move previous function, therefore you need to manually use a for loop to determine the position of the target index and then use position + 1 or position -1 to get the next or previous record.

If you are developing a windows form then you are lucky you can use BindingContext[dataTable].Position + 1 or BindingContext[dataTable].Position - 1.

Well, we are talking about ASP.NET here, if we are populating a few hundreds items and use for loop to get the position and I think it is fine. Or u might be using datatable something like

DataTable dt = new DataTable();
dt.Rows.IndexOf(datarow);

to get the index and from there u manipulate it.

However what if we have 1 million of records in the table? That would be crazy!

The more efficient way is to use over keyword which only available in SQL Server 2005 or later. The idea is to get the previous record and the next record given the record identity. I just have to get 2 records and I just want the first record as previous and second record as next. So here is the trick.

subcategoryid, name

31	Game Downloads
32	Computers & Accessories
33	Software
34	PC Games
35	Computer Components
36	Office Products & Supplies

select top(1) row_number() over (order by subcategoryid desc) row, subcategoryid, name from subcategory where subcategoryid<33
union
select top(1) row_number() over (order by subcategoryid) row, subcategoryid, name from subcategory where subcategoryid>33

results:

1 32 Computers & Accessories
1 34 PC Games

p/s please note that this solution only applicable for int column having auto numbering enabled only. the subcategoryid is the primary key, auto number and numaric