C# record navigation
I have to do a record navigation web form in C#. It needs to be able to go to the next/previous record and go to a specific record number typed in by the user and fill the form with the data for that record. I'm using sql server as the database. I was wondering what is the best way to find the specific record. I could pull it from a DataTable but that loads the entire table into memory first. Looping though a SqlDataReader just to find the one record just seems wrong. The table could have 10000 records in it. Most of the time( 80%), it will probably have about 300 or less. Any thoughts on the best way to do this? Thanks
If you have an 'id' column, you could just use SELECT * FROM table WHERE id = (whatever), then display whatever comes back.
There is no general solution that is "by far much better than anything else".
If you have an autoincrement column, and no records get ever deleted, it's easy - Albatross already said that.
Otherwise: Let's assume you have an "CustomberNumber" column which is unique (and of course indexed).
Navigation to next:
select min(CustomerNumber) from customers where CustomerNumber>:currentCustomerNumber;(or
select top 1 * from customers where CustomerNumber>:currentCustomerNumberto get the whole row)
order by CustomerNumber desc;
select max(CustomerNumber) from customers where CustomerNumber<:currentCustomerNumber;(or
select top 1 * from customers where CustomerNumber<:currentCustomerNumber
order by CustomerNumber;
to get the whole row)
Select top :n CustomerNumber from customers order by CustomerNumber;Take the CustomerNumber of the last record of the resultset.
Do it in a stored procedure so the useless n-1 numbers do not have to go through the network.
Yes, this is slow if you do that on a table with 3500000 records and ask for the 3499997th record.
That's why you should rather avoid this n-th record thingy and let the user search for a key instead. That's what relational database systems were made for.
Stupid me... the "order by ... desc" should be in the previous record section
Thanks for the help. That's what I was looking for