asked on

backgroundworker problem

On a windows form I have 3 comboboxes being filled asynchronously.
When a form is loaded one combobox is filled with items from a sql-table which uses a query like : select * from tbl where name like 'A%'.
If I now click on eg button B I want this combobox being filled with a query like ... where name like 'B%'.
But for some reason this does not work it errors with : cross-thread operation not vaild.

(It errors on FillComboFromStringCollection2 on cobo.items.Add(item))

Can anyone please have a look, I don't understand a lot of these backgroundworkers.

private void MemoWindow_Load(object sender, EventArgs e)
        {
            sqlInfoFetcher = new BackgroundWorker();
            sqlInfoFetcher.DoWork += new DoWorkEventHandler(sqlInfoFetcher_DoWork);
            sqlInfoFetcher.RunWorkerCompleted += new RunWorkerCompletedEventHandler(sqlInfoFetcher_RunWorkerCompleted);
           
            sqlInfoFetcher.RunWorkerAsync();
        }

 private void btnA_Click(object sender, EventArgs e)
        {
            sqlInfoFetcher2 = new BackgroundWorker();
            sqlInfoFetcher2.DoWork += new DoWorkEventHandler(sqlInfoFetcher2_DoWork);
            sqlInfoFetcher2.RunWorkerCompleted += new RunWorkerCompletedEventHandler(sqlInfoFetcher2_RunWorkerCompleted);
            sqlInfoFetcher2.RunWorkerAsync();
        }

 void sqlInfoFetcher2_DoWork(object sender, DoWorkEventArgs e)
        {
            string connectionString = "Data Source=CLUSTER;Initial Catalog=Contacts;user id=sa;password=sa; Asynchronous Processing=true";
            string query = "SELECT DISTINCT First_Name + ' ' + Last_Name AS Expr1 FROM         dbo.NAME WHERE     (Last_Name LIKE 'B%')";
            e.Result = FetchDataFromDB(connectionString, query);
        }
        void sqlInfoFetcher2_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            FillComboFromStringCollection2(cboToExternal, e.Result as StringCollection);
        }
        void sqlInfoFetcher_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            FillComboFromStringCollection(cboToExternal, e.Result as StringCollection);
        }

        void sqlInfoFetcher_DoWork(object sender, DoWorkEventArgs e)
        {
            string connectionString = "Data Source=CLUSTER;Initial Catalog=Contacts;user id=sa;password=sa; Asynchronous Processing=true";
            string query = "SELECT DISTINCT First_Name + ' ' + Last_Name AS Expr1 FROM         dbo.NAME WHERE     (Last_Name LIKE 'A%')";
            e.Result = FetchDataFromDB(connectionString, query);
        }


  private void FillComboFromStringCollection(ComboBox combo, StringCollection items)
        {
            foreach (string item in items)
            {
                combo.Items.Add(item);
            }
            combo.Enabled = true;
        }
        private StringCollection FetchDataFromDB(string connectionString, string query)
        {
            SqlCommand command = null;
            SqlConnection connection = null;
            SqlDataReader reader = null;
            StringCollection results = new StringCollection();
            try
            {

                connection = new SqlConnection(connectionString);
                command = new SqlCommand(query, connection);
                command.CommandTimeout = 0;
                connection.Open();
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    results.Add(reader.GetString(0));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                command = null;
                if (reader != null) reader.Close();
                if ((connection != null) && (connection.State != ConnectionState.Closed))
                    connection.Close();
                reader = null;
                connection = null;
            }
            return results;
        }


        private void FillComboFromStringCollection2(ComboBox combo, StringCollection items)
        {
            foreach (string item in items)
            {
                combo.Items.Add(item);
            }
            combo.Enabled = true;
        }

Open in new window

james-ct16

Howdy

This problem is by design to stop non ui thread from making changes to the controls. The good news is though there is a nice pattern to follow to solve this problem.

Below is a link to an MSDN walk though example using the background worker object and setting values in a win forms control.
http://msdn.microsoft.com/en-us/library/ms171728(VS.80).aspx

Hope this helps

James

dekempeneer

ASKER

Ok, but what I can do with my code then ??
For all I understand it looks good.

joriszwaenepoel

I don't see what is wrong with the code at first glance.

However, does it really take such a long time to fill the combobox, that it needs to be done on a background thread. It seems that in this case it just makes your code much more complicated.

Are you sure you need " Asynchronous Processing=true " in the connection string? I have never used that, and you are not doing async processing (which would be using BeginExecuteReader instead of ExecuteReader).

ASKER CERTIFIED SOLUTION

james-ct16

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

james-ct16

joriszwaenepoel does make a very good point which I missed just answering your question, why go to the trouble of using a background worker thread (ok so its not that much work) when its built in for you in the async execute readers as documented at

http://msdn.microsoft.com/en-us/library/1a674khd.aspx

They provide a couple of nice worked examples as well.

James

dekempeneer

ASKER

James,
thx, that code works.
I'll look into that async sql reader.
And yes it takes a while to load up > 50000 items from sql :)

joriszwaenepoel

Actually,

1. The backgroundworker was created to avoid the need to use techniques like the one in the solution (delegate, InvokeRequired, etc..). The RunWorkerCompleted event should be raised on the correct thread automatically. I don't see why it doesn't in your example, but it should.

2. If you want to speed up the filling of the combobox, you can look at using "combo.Items.AddRange" instead of "combo.Items.Add", andalso call "BeginUpdate" before you start filling it, and "EndUpdate" when you are done.

3. Do you have an index on the LAST_NAME column in your database? This would improve the speed of reading from the DB.

4. A combobox with that much items is probably very difficult to use. Maybe you should consider to create another filter to limit the amount of data in the comboboxes.

dekempeneer

ASKER

1. Ok, but for now it works. I will have a look at it comparing it with examples for msdn allthough I alwyas find it hard to do that as MSDN usually uses the most simple examples.
2. It is ok now as I do it by using alphabet-buttons so not all 50000 are being loaded into the combobox, only the ones that start with eg A, B, C, ...
3. This is an old database and will be adapted in November to a CRM-system so who knows what the future brings
4. See 2.