Solved

What is the best way to populate comboBox from database

Posted on 2010-08-27
12
1,225 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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 500 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.

733 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