Databound dropdownlist with a million item

Question

Pls Whats the best method of binding a items from(select COLUMN from TABLE) with a million rows on ASP.NET.

What I have now is freezing the ASP.NET page, as the dropdownList tries to populate from SQL SERVER, with over a million rows of data.

Thanks

Jaffa

Answer 1

The best way is not to do it! No user wants to select from a million rows your best bet is to have some kind of progressive search that when the user starts typing they are given a list of options

Answer 2

I don't think you should bind 1 million records to a DropDownList. This is very bad for usability, how is the user supposed to find the option they want?

If possible these should be grouped into cascading DropDownList's to avoid timeout issues and improve usability.

Here is an example of the ASP.NET AJAX Control Toolkit Cascading DropDown:

http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/CascadingDropDown/CascadingDropDown.aspx

Answer 3

Consider that freezing time as a big warning signal. Why should you let the user choose among 1000K of items. That would be a big UI fail. Use a autocomplete control with a JSON webservice call.

Answer 4

The best way is for Grids to page and for DropDowns to filter.

in a grid page you only show the first X records and users can move to the next/prev/first/last page and so on.

in a filtered control you ask the user to type the first x chars and then only show the matching records.

Answer 5

For such big list you need to ask yourself something first

• Do you really want to put 1 million rows in a dropdown?
• Wouldn't be easier to show several dropdowns and fillup the next one based on the previous selection?
• You could have a div emulating a dropdown and when the scroll bar reached the end you could load more rows...

Best approach is to sort in in categories and show the user a wizard alike step by step process with several dropdowns as I doubt that anyone will read 1 million of records.

if you still want to go for that weird step of showing everything in just one dropdown, you can easily cache the result for a period of time, and when request to fillup the data, it will use what's in memory and never reached the database.

Or, if it's something that does not change that much, you could load 1 million rows from a text file witch is faster that connect to the database... and then refresh that file on request or automatically.

Answer 6

If you really want to do that, first you can bind your object after searching with your user, else if user search with nothing binding all. In .net 4 you can use Linq, best way to get your data is using this technology. But its too much to show to your user.

Answer 7

As stated by every answer here biding one million records to a drop down list is not a practical option. The folks from Smashing Magazine wrote a fantastic (similar) article on a better approach - specifically focusing on the user experience for large drop downs and is well worth a read.