Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,


Per the question, I'm new to programming.

I would like to know how using 'C#" to program a 'dropdownlist' so that it will "load and show" a 100 items at a time.

<b>The Problem:</b>
My (MS SQL) data-set has 40,000 records - the 'dropdownlist' pulls all the rows at the same-time

What I have tried:

SQL stored procedures; searched online daily; pulling-out my hair.
Posted
Updated 29-Aug-16 19:25pm
v2
Comments
Kevin Marois 25-Aug-16 14:10pm    
First, tell us some more about your environment. Windows 7/8/10? What version of .Net? What version of SQL? Windows/WPF/Web?

Second, how would the user load the next 100 records? Each time it drops down you get the next 100?

Third, look into Paging - getting x number of records per request. Basically you would need to store the starting record number, then per each call increment that by 100. There are complications when consider ordering. Take a look at this:
http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

Finally, you might want to reconsider your approach here. A dropdown isn't really designed for 40K records.
Member 12703654 2-Nov-16 13:30pm    
Thanks for the tips - big help in learning how/what to ask on the site.

Quote:
My (MS SQL) data-set has 40,000 records - the 'dropdownlist' pulls all the rows at the same-time
The principle of a droplist that pull 40,000 records is a bad idea by itself. Loading records by chunk of 100 is not a good solution because if you need something at the end of list, you still need to load 40;000 records and it will add delay.

You need to build a dynamically populated droplist just like what Google does when you type something.
You have to imagine something that will work for you.
With Google, it show only the most popular answers that match what you already typed and the list is rebuild every time you type a letter.
 
Share this answer
 
Use the TOP statement in your SQL query, e.g.
SQL
SELECT TOP 100 * FROM MyTable;

You can also use SET ROWCOUNT to limit the number of records, see
[^]

However, using LINQ might be simpler as this has .skip and .take to fetch records.
 
Share this answer
 
v2
Im am assuming you are using an ORM of some sort so what you could do for your data that is bound/loads into the drop down is use linq.

So you'd have something like

C#
var dataFromDb = new List<yourentity>();
var data = dataFromDb.Take(100);


In order to load them 100 at a time, to get the next 100 you'd need to keep track of what the current count it is on and then do something along the following lines

C#
var currentCount = 2;
var dataSetSize = 100;

var dataFromDb = new List<yourentity>();
var data = dataFromDb.Skip(dataSetSize * currentCount).Take(dataSetSize);


How you go about keeping track of currentCount is up to you but this is the general idea to achieve what you are asking.
 
Share this answer
 
v3
if you have used store procedure then use offset to load 100 record when scroll

SQL
      SELECT * FROM Students
WHERE Name LIKE N'%abc%' OR Description LIKE N'%%' OR NoofCourses LIKE N'%%' 
ORDER BY ModifiedDate desc OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY


this query get 0 to 100 record only when you need to retrieve another records pass based on that parameter in store procedure and get records
Like below

SQL
      SELECT * FROM Students
WHERE Name LIKE N'%abc%' OR Description LIKE N'%%' OR NoofCourses LIKE N'%%' 
ORDER BY ModifiedDate desc OFFSET 101 ROWS FETCH NEXT 200 ROWS ONLY


above query returns you another 100 records from 101 to 200
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900