• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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?
0
jinhong23
Asked:
jinhong23
  • 2
1 Solution
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now