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>:currentCustomerNumber
     order by CustomerNumber desc;
    to get the whole row)

    Previous:
    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)


    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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.