Getting next row from SQL Server

Question

I have a table in SQL Server with columns id, name, address in which id is the primary key with auto-increment integer.

I have a form in a winforms application in which I have two buttons:

1 previous
2 next

I want the functionality i.e when I click on previous button, I can fetch the previous row of the selected row from table. Same way when I click on next button, I can get the next row of the selected row....

My form looks like this

Answer 1

you can select the top 1 row where row ID is greater/lower than your current Id, so if your Id is 123 you can select for the top 1 (the first) row where ID is greater than 123 without knowing that the following should be 124. same of the previous row.

because it's PK the rows should be sorted so you don't need to add sorting command. of course you should take care when asking previous row from the first one, or next after the last.

declare @CurrentID int = 123;
select top 1 * from [MyTable] where [MyTable].[ID] > @CurrentID;

Of course you better implement it with cache mechanism to avoid making a database query for every button hit so you can query bunch of rows into your application memory and than go by index to the previous or next item.