How do I control number of record fetch from database

I currently developed a window form application using data binding concept.
Due to there are too many records (> 50K) in the database table, it take very long to load the form.
I am looking for a solution to partially fetch the record from database.
My idea is to fetch first 100 records when form is loaded. When user navigates (using binding navigator) the record let's say until record number 90, it will automatically fetch another 100 records. Same concept applies to datagridview. If user scroll down the scrollbar of a datagrid let's say scrolling until 3/4 of the scroll area, it will automatically fetch more record and shown in the grid.

Can anyone of you guys know about how to do it?
jinhong23Asked:
Who is Participating?
 
jinhong23Author Commented:
I found some reference from code project website
http://www.codeproject.com/KB/database/WinFormGridviewCaching.aspx

It give me some idea to proceed.

0
 
carlnorrbomCommented:
Hi,

You can limit the number of returned rows directly in the sql query such as:

SELECT TOP 100 * FROM tblTable

which would return the first 100 resulting rows from the table tblTable.

/Carl.
0
 
jinhong23Author Commented:
hi Carl,
Thanks for your reply. Sorry for the ambiguous of my question.
I have no concern on how to retrieve the first 100 records. My concern is the logic to retrieve the record based on the user event.
E.g. When Next record button is clicked; need to check the record position to determine whether to fetch another set of record.
When Last record button is clicked; need to sort the record in descending order and get first 100 records using and sort again using dataview sort method.

I believe the logic/flow that I thought might be not optimized, so I would like to check if anyone have an idea or there is any the sample code.

Also, I am using data-binding concept for CRUD. Changing or the data source query might affect the CRD functionalities. Is it advisable to create another dataset for this record navigator purpose?

Do appreciate for any reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.