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;
(orselect top 1 * from customers where CustomerNumber>:currentCustomerNumber
to get the whole row)
order by CustomerNumber desc;
Previous:select max(CustomerNumber) from customers where CustomerNumber<:currentCustomerNumber;
(orselect top 1 * from customers where CustomerNumber<:currentCustomerNumber
order by CustomerNumber;
to get the whole row)
n-th record: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