Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What is the best way to populate comboBox from database

Posted on 2010-08-27
12
Medium Priority
?
1,250 Views
Last Modified: 2013-12-17
Hi,

what is the best way to populate comboBox from database.. the reason I
am asking is that i have more than 8 comboBoxes in one form and each
should retrieve  a lot of data (one combobox contains 50000 items) from a table and I don't want that to slow the form down.

I found this as answer on the net :
You can show the form, but disable the combos. After that, launch an async
delegate populating the combos, and enable them once the data are ready.


But I have no idea on how to do this, can you please submit a sample code...
0
Comment
Question by:dekempeneer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33540798
I don't see any reason to load such a huge combobox: 50.000 items ? I don't want to be the user that open the combobox and search for my item he/she needs ...

 I would suggest that you implement a master/detail combo box wherein the user will first select a category (master) then once a selection has been made, populate another combo box (detail) for the items for the selected category...
0
 

Author Comment

by:dekempeneer
ID: 33540819
It is a list of all the contacts of everybody and they are sorted by alphabet
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33540820
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 53

Expert Comment

by:Dhaest
ID: 33540833
>> It is a list of all the contacts of everybody and they are sorted by alphabet

You can for example also use some kind of search, where the user first enters 3 or 4 characters of a name, that triggers a search a database and with this results the first 50 (or 100) will be filled into a dropdown
0
 

Author Comment

by:dekempeneer
ID: 33540844
and how can I do this if I would choose this ?
0
 

Author Comment

by:dekempeneer
ID: 33540903
by the way that asynchronous is for .net 2.0, is there not a better easier way in 4.0 already ? :)
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33540923
Very easily: add a textbox and a button on the screen. Behind this button, you perform a query and load the data into the combobox.

Example below for an access-database
string connString = "Microsoft.Jet.OLEDB.4.0;Data Source=C:\\bd1.mdb"; 
string SqlString = "Select top 50 LastName From Contacts Where LastName like '?*'";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
  {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
    conn.Open();
    using (OleDbDataReader reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        comboBox1.Items.Add(reader["LastName"].ToString());
      }
    }

  }

}

Open in new window

0
 
LVL 16

Accepted Solution

by:
SriVaddadi earned 2000 total points
ID: 33541376

You might want to implement producer-consumer pattern where the producer would produce the data and consumer would consume it and populate your combo.

Here is a link which might help
http://msdn.microsoft.com/en-us/library/dd997371.aspx

NOTE: It may not be a good idea to search on text fields using "Like" operator.
0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 33545317
I'd still be concerned about performance. I agree with dhaest on this. Most UI controls get REALLY SLOW when you add such large numbers of items. Also, the thumb would be really small with that many items. A simple filter would make it so much better. A textbox to allow the user to enter a partial name would go a long way in making it more usable. When something is entered, re-issue the query, wrap the results in a new collection and bind the combo to it.
0
 
LVL 14

Expert Comment

by:systan
ID: 33550154
>>I found this as answer on the net :
>>You can show the form, but disable the combos.
Use this when your form loads
comboBox1.BeginUpdate();

>>and enable them once the data are ready.
Use this after your data is complete
comboBox1.EndUpdate();  


If you want it asynchronously;
Use
comboBox1.BeginInvoke
0
 
LVL 14

Expert Comment

by:systan
ID: 33550405
OR with background worker
http://www.codeguru.com/forum/showthread.php?t=433482

OR with this for simple psudo code;
//Global Declaration
SqlDataAdapter da1 = new MySqlDataAdapter();
SqlDataAdapter da2 = new MySqlDataAdapter();
SqlDataAdapter da3 = new MySqlDataAdapter();
SqlDataAdapter da4 = new MySqlDataAdapter();
SqlDataAdapter da5 = new MySqlDataAdapter();
SqlDataAdapter da6 = new MySqlDataAdapter();
SqlDataAdapter da7 = new MySqlDataAdapter();
SqlDataAdapter da8 = new MySqlDataAdapter();


//During Form LOAD
...
string s1 = "select  f1 from t1";
da1.SelectCommand = new SqlCommand(s1, YOURsqlconnection);
string s2 = "select  f1 from t2";
da2.SelectCommand = new SqlCommand(s2, YOURsqlconnection);
string s3 = "select  f1 from t3";
da3.SelectCommand = new SqlCommand(s3, YOURsqlconnection);
string s4 = "select  f1 from t4";
da4.SelectCommand = new SqlCommand(s4, YOURsqlconnection);
string s5 = "select  f1 from t5";
da5.SelectCommand = new SqlCommand(s5, YOURsqlconnection);
string s6 = "select  f1 from t6";
da6.SelectCommand = new SqlCommand(s6, YOURsqlconnection);
string s7 = "select  f1 from t7";
da7.SelectCommand = new SqlCommand(s7, YOURsqlconnection);
string s8 = "select  f1 from t8";
da8.SelectCommand = new SqlCommand(s8, YOURsqlconnection);
...
DataTable dt1 = new DataTable();
da1.Fill (dt1);
DataTable dt2 = new DataTable();
da2.Fill (dt2);
DataTable dt3 = new DataTable();
da3.Fill (dt3);
DataTable dt4 = new DataTable();
da4.Fill (dt4);
DataTable dt5 = new DataTable();
da5.Fill (dt5);
DataTable dt6 = new DataTable();
da6.Fill (dt6);
DataTable dt7 = new DataTable();
da7.Fill (dt7);
DataTable dt8 = new DataTable();
da8.Fill (dt8);






//During Form SHOWN / or button click
comboBox1.BeginUpdate();
comboBox2.BeginUpdate();
comboBox3.BeginUpdate();
-do next down-

comboBox1.DisplayMember = "f1";
comboBox2.DisplayMember = "f1";
comboBox3.DisplayMember = "f1";
-do next down-


comboBox1.DataSource = dt1;
comboBox2.DataSource = dt2;
comboBox3.DataSource = dt3;
-do next down-


comboBox1.EndUpdate();
comboBox2.EndUpdate();
comboBox3.EndUpdate();
-do next down-


Note:
//use Datatable for SMALL memory consumption

Open in new window

0
 
LVL 14

Expert Comment

by:systan
ID: 33550419
OPs,
correction; for my second comment.

for MS sql
SqlDataAdapter da1 = new SqlDataAdapter();

for mYsql
MySqlDataAdapter da1 = new MySqlDataAdapter();

for ado
OLEDBlDataAdapter da1 = new OLEDBDataAdapter();

for odbc
ODbCDataAdapter da1 = new ODbcDataAdapter();
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question