Solved

populate combobox asynchronously

Posted on 2010-08-27
46
1,336 Views
Last Modified: 2013-12-17
I now have a  private void GetInfoFromSQL() (see code snippet) with which a fill a combobox with data which is run from  private void Window_Shown(..) but it takes too long to load them up.
Can anyone help me out and transform my code into a working "asynchronous data access" code example ?
Eg the combobox is disabled until the asynchronous method has finished loading the data into the combobox.
(I'm using visual studio 2010 with .net 4.0 )

     
private void GetInfoFromSQL()

        {

           



            string ConnectionString = "Data Source=MOM\\STRU;Initial Catalog=Contacts;user id=sa;password=secret";

            SqlConnection conn = new SqlConnection(ConnectionString);



         conn.Open();

                SqlDataReader rdr = null;

                SqlCommand cmd = new SqlCommand("SELECT DISTINCT First_Name + ' ' + Last_Name + ' (' + Org_Name + ')' AS Expr1 FROM dbo.NAME ORDER BY Expr1", conn);

                rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    for (int i = 0; i < rdr.FieldCount; i++)

                    {

                        cboToExternal.Items.Add(rdr.GetValue(i).ToString());

                    }

                }

            }

Open in new window

0
Comment
Question by:dekempeneer
  • 19
  • 16
  • 10
  • +1
46 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33541255
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33541284
Are you looking to make asynchronous call to ADO.Net execute reader if so you will have to use BeginExecuteReader

Please check the example at this link

http://msdn.microsoft.com/en-us/library/7szdt0kc.aspx
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33541297
FYI, MARS is only supported in SQL Server 2005 and up.
0
 

Author Comment

by:dekempeneer
ID: 33541484
Can anyone please transform my code with use of such a executereader ?
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33541639
public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

       
        private delegate void FillComboDelegate(SqlDataReader reader);

        // This flag ensures that the user does not attempt
        // to restart the command or close the form while the
        // asynchronous command is executing.
        private bool isExecuting = false;

        // Because the overloaded version of BeginExecuteReader
        // demonstrated here does not allow you to have the connection
        // closed automatically, this example maintains the
        // connection object externally, so that it is available for closing.
        private SqlConnection connection = null;

      private void FillCombo(SqlDataReader reader)
        {
            try
            {
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        cboToExternal.Items.Add(reader.GetValue(i).ToString());
                    }
                }
               
            }
            catch (Exception ex)
            {
               //TODO: Exception handling
            }
            finally
            {
                // Do not forget to close the connection, as well.
                if (reader != null)
                {
                    reader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        private void HandleCallback(IAsyncResult result)
        {
            try
            {
                // Retrieve the original command object, passed
                // to this procedure in the AsyncState property
                // of the IAsyncResult parameter.
                SqlCommand command = (SqlCommand)result.AsyncState;
                SqlDataReader reader = command.EndExecuteReader(result);
                // You may not interact with the form and its contents
                // from a different thread, and this callback procedure
                // is all but guaranteed to be running from a different thread
                // than the form. Therefore you cannot simply call code that
                // fills the grid, like this:
                // FillGrid(reader);
                // Instead, you must call the procedure from the form's thread.
                // One simple way to accomplish this is to call the Invoke
                // method of the form, which calls the delegate you supply
                // from the form's thread.
                FillComboDelegate del = new FillComboDelegate(FillCombo);
                this.Invoke(del, reader);
                // Do not close the reader here, because it is being used in
                // a separate thread. Instead, have the procedure you have
                // called close the reader once it is done with it.
            }
            catch (Exception ex)
            {
                //TODO: Your exception handling here
            }
            finally
            {
                isExecuting = false;
            }
        }

        private string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file.

            // If you do not include the Asynchronous Processing=true name/value pair,
            // you wo not be able to execute the command asynchronously.
            return "Data Source=MOM\\STRU;Initial Catalog=Contacts;user id=sa;password=secret; Asynchronous Processing=true";
        }

        private void GetInfoFromSQL()
        {
            if (isExecuting)
            {
                MessageBox.Show(this,
                    "Already executing. Please wait until the current query " +
                    "has completed.");
            }
            else
            {
                SqlCommand command = null;
                try
                {
                   
                    connection = new SqlConnection(GetConnectionString());
                    // To emulate a long-running query, wait for
                    // a few seconds before retrieving the real data.
                    command = new SqlCommand("SELECT DISTINCT First_Name + ' ' + Last_Name + ' (' + Org_Name + ')' AS Expr1 FROM dbo.NAME ORDER BY Expr1",
                        connection);
                    connection.Open();

                   
                    isExecuting = true;
                    // Although it is not required that you pass the
                    // SqlCommand object as the second parameter in the
                    // BeginExecuteReader call, doing so makes it easier
                    // to call EndExecuteReader in the callback procedure.
                    AsyncCallback callback = new AsyncCallback(HandleCallback);
                    command.BeginExecuteReader(callback, command);
                }
                catch (Exception ex)
                {
                   
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
            }
        }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            this.FormClosing += new FormClosingEventHandler(Form1_FormClosing);
        }

        void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (isExecuting)
            {
                MessageBox.Show(this, "Cannot close the form until " +
                    "the pending asynchronous command has completed. Please wait...");
                e.Cancel = true;
            }
        }
    }
0
 

Author Comment

by:dekempeneer
ID: 33548559
SriVaddadi, thank you so much.
So in my Shown I do this :
private void MemoWindow_Shown(object sender, EventArgs e)
        {            GetInfoFromSQL();        }
to start the process, right ?

Allthough if I do this, the other comboboxes are not working either until the cboToExternal has finished loading its data ? is this normal ?

0
 

Author Comment

by:dekempeneer
ID: 33548639
Somebody told me he'd simplify it a lot, just do the entire DB operation synchronously on a background thread, then either invoke back for each combo item, or wait till done and invoke back with all the items

you want me to open another call so you would get more points ?
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33548901

process starting is correct. But the second point is not correct and we will have see what you have done with posted snippet.

I would ask you to upload the code at http://www.ee-stuff.com/ and send me link. I will take a look.

Well, that is the best way of doing it. You will have to spawn multiple threads (one for each combo) and retrieve data in parallel. I guess that is what you are looking for?

And also it depends on what kind of machine would run your code as it would not be very efficient to spawn too many threads time slicing the CPU.

I will see if i can provide you wth some example and you could proceed along those lines.
0
 

Author Comment

by:dekempeneer
ID: 33556645
Hi, this is my code I have now, I know the AD will slow it down , but it appears before the sql lookup has finished.
I can choose one name of the AD list (from) , but then the AD combobox does not work anymore until the sql combobox has finished loading its data.
the code :
https://filedb.experts-exchange.com/incoming/ee-stuff/7979-MemoWindow.zip

The new machines will have i5 cpu's with 4GB of ram.
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565244
Use Delegate BeginInvoke method for getting this.
public partial class Form1 : Form

    {

        delegate void ReadValuesDelegate();

        delegate void PopulateComboDelegate(List<string> s);



        ReadValuesDelegate rv;

        PopulateComboDelegate pc = null;





        public Form1()

        {

            rv = new ReadValuesDelegate(ReadValues);

            InitializeComponent();

        }





        private void button1_Click(object sender, EventArgs e)

        {

            cboToExternal.Enabled = false;

            rv.BeginInvoke(null, null);

        }





        private void PopulateCombo(List<String> comboValues)

        {

            cboToExternal.Items.AddRange(comboValues.ToArray());

            cboToExternal.Enabled = true;

        }



      



        private void GetInfoFromSQL()

        {

            List<String> lstValues = new List<string>();

            string ConnectionString = "Data Source=MOM\\STRU;Initial Catalog=Contacts;user id=sa;password=secret";

            using (SqlConnection conn = new SqlConnection(ConnectionString))

            {



                conn.Open();

                SqlDataReader rdr = null;

                using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT First_Name + ' ' + Last_Name + ' (' + Org_Name + ')' AS Expr1 FROM dbo.NAME ORDER BY Expr1", conn))

                {

                    rdr = cmd.ExecuteReader();

                    while (rdr.Read())

                    {

                        for (int i = 0; i < rdr.FieldCount; i++)

                        {

                            lstValues.Add(rdr.GetValue(i).ToString());

                        }

                    }

                }

            }

            cboToExternal.BeginInvoke(new PopulateComboDelegate(PopulateCombo), lstValues);

        }

}

}

Open in new window

0
 

Author Comment

by:dekempeneer
ID: 33565333
naman_goel, does that also work asynchronous so it does not slow down the formrendering ?
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565339
Yes, This method is asynchronous only, BeginInvoke call will always be asynch...
 
Thanks
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33565461
Hi dekempeneer,

I m sorry. I got busy and could not look into your code. Let me look at it and get back to you.

Mean while i guess my friend Naman_goes is helping you.

Hi Naman_goes,

Did you look at the code the dekempeneer uploaded to EE stuff as I requested.
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565550
sorry I am not able access that link.
0
 

Author Comment

by:dekempeneer
ID: 33565573
I copied the   cboToExternal.Enabled = false;
            rv.BeginInvoke(null, null);
into the Form1_Shown to start it ?

But I get an error on the (ReadValues) :
The name 'ReadValues' does not exist in the current context
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33565574
dekempeneer, I wonder how many records are you trying to fetch?
0
 

Author Comment

by:dekempeneer
ID: 33565584
for the time being there are 50000 records in one list(combobox)
300 in another (AD accounts)
 
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565605
Sorry my mistake..
You can use the following code this should work.

public partial class Form1 : Form

    {

        delegate void ReadValuesDelegate();

        delegate void PopulateComboDelegate(List<string> s);



        ReadValuesDelegate rv;

        PopulateComboDelegate pc = null;





        public Form1()

        {

            rv = new ReadValuesDelegate(GetInfoFromSQL);

            InitializeComponent();

        }





        private void button1_Click(object sender, EventArgs e)

        {

            cboToExternal.Enabled = false;

            rv.BeginInvoke(null, null);

        }





        private void PopulateCombo(List<String> comboValues)

        {

            cboToExternal.Items.AddRange(comboValues.ToArray());

            cboToExternal.Enabled = true;

        }



      



        private void GetInfoFromSQL()

        {

            List<String> lstValues = new List<string>();

            string ConnectionString = "Data Source=MOM\\STRU;Initial Catalog=Contacts;user id=sa;password=secret";

            using (SqlConnection conn = new SqlConnection(ConnectionString))

            {



                conn.Open();

                SqlDataReader rdr = null;

                using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT First_Name + ' ' + Last_Name + ' (' + Org_Name + ')' AS Expr1 FROM dbo.NAME ORDER BY Expr1", conn))

                {

                    rdr = cmd.ExecuteReader();

                    while (rdr.Read())

                    {

                        for (int i = 0; i < rdr.FieldCount; i++)

                        {

                            lstValues.Add(rdr.GetValue(i).ToString());

                        }

                    }

                }

            }

            cboToExternal.BeginInvoke(new PopulateComboDelegate(PopulateCombo), lstValues);

        }

}

}

Open in new window

0
 

Author Comment

by:dekempeneer
ID: 33565626
Sorry naman_goel, it works, but the other comboboxes stay unworking as well
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565676
what do you want?? I am not getting ...
Other combo box should work until they are not dependent on this combobox (cboToExternal)...
 
0
 

Author Comment

by:dekempeneer
ID: 33565735
I have for now 3 comboboxes. But they do not open until the large one has completed filling.
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565766
so are they dependent on the first one... ?
0
 

Author Comment

by:dekempeneer
ID: 33565777
No
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565790
can you send me the code @ goel.naman@gmail.com
as this should work in all cases...
0
 

Author Comment

by:dekempeneer
ID: 33565954
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33565968
sorry dekempeneer I am not able to access this page
https://filedb.experts-exchange.com/incoming/ee-stuff/7979-MemoWindow.zip

I don't have access for this.
 
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33566061
Got your code and there should not be any problem
 
this all is happening because you are using SelectedValueChanged eventhandler.
remove it or add one flag which will be checked when we are filling combobox.
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566112
As I understand from your code, you are trying to populate the combo boxes by fetching the data from Sql server and AD. AD calls take lot of time to complete.

All the while you do not want your form to hang.

In this scenario, you are supposed to use the BackgroundWorker class. I refactored your code using the same.

*** PLEASE BACK UP YOU EXISTING CODE (JUST IN CASE OF NO SOURCE CONTROL) AND MERGE MY CHANGES CAREFULL.

Try and understand the code and merge the changes. Please do not do it in a hurry. I m sure the code i m attaching is what you are looking for.

Feel free to come with questions if any.
MemoWindow.zip
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566235
Just in case, if you want to run as-is without merging. here is the entire solution.

https://filedb.experts-exchange.com/incoming/ee-stuff/7982-Memo2010a.zip

It would miss the delete icon though.
0
 

Author Comment

by:dekempeneer
ID: 33566565
SriVaddadi, isn't that the same code as the one you pasted before?

I tried this one and I still have the same problem I can open the fromcombobox once and then the frombox freezes up until the externalbox has been filled.
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566636
Could you please elaborate what you are trying to do?1. You launch the application2. Select a value from FromCombobox once it is enabled?
0
 

Author Comment

by:dekempeneer
ID: 33566718
The full app goes as follows,
I click on the launch button,
Word gets started,
The windows form comes up,
the Fromcombobox gets filled
Meanwhile the externalcombo is doing its thing,
I choose an item from the frombox
next I have to wait until the externalbox is done doing its thing before I can open another combobox.
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566743
ok. So now once you select an item in the from combo box, is the form freezing? is that ur prob
0
 

Author Comment

by:dekempeneer
ID: 33566793
I just tested it now by waiting for 5 seconds and then trying to open a combobox, but the windows was frozen. Until the Externalbox is filled, then the window (and the comboboxes) work again.

0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566817
It seems wierd though.. The whole idea and purpose of BackgroundWorker is to avoid UI freezing .. let me tsee..
0
 

Author Comment

by:dekempeneer
ID: 33566871
Don't I also neeed to put a BackgroundWorker Component from the toolbox in the windowsform ?
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566909
Not required. It is either of them. Either component or class not both.
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566958
Please add the below lines of code to the code in MemoWindow_Load event handler adInfoFetcher.RunWorkerAsync();            sqlInfoFetcher.RunWorkerAsync();            matterInfoFetcher.RunWorkerAsync();
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33566974
Looks like you have merged the my code with yours. My code never ran. You code only ran and caused the issue. Please download the solution i have uploaded to ee stuff and run that to be clear.
0
 
LVL 16

Accepted Solution

by:
SriVaddadi earned 500 total points
ID: 33567000
Please download the code from the below link and runhttps://filedb.experts-exchange.com/incoming/ee-stuff/7983-Memo2010a.zip
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33567060
Looks like i forgot to uncomment below lines of code in the recent upload. Please uncomment the below 3 lines in form load event handler      adInfoFetcher.RunWorkerAsync();            sqlInfoFetcher.RunWorkerAsync();            matterInfoFetcher.RunWorkerAsync();
0
 

Author Comment

by:dekempeneer
ID: 33576087
It seems to work better, but it still hangs.
when the Frombox is filled the form hangs again until the other comboboxes have been filled.


I also get an error on the ToExternal-fill (see image) , when I (can) click OK on the messagebox, the ToExternal is filled though.

What do you suggest ?
error2.png
0
 

Author Comment

by:dekempeneer
ID: 33577115
If I change the query to :
string query = "SELECT DISTINCT TOP (500) First_Name + ' ' + Last_Name + ' (' + Org_Name + ')' as Expr1 FROM dbo.NAME "; it works.

So the query contains some NULLs probably.
I was also wondering how I could change the query to something like :
string query = "SELECT DISTINCT TOP (500) First_Name + ' ' + Last_Name + ' (' + Org_Name + ')' as Expr1 , Org_Name FROM dbo.NAME order by Org_Name";
I only want to see the Expr1, but ordered by Org_Name
0
 

Author Comment

by:dekempeneer
ID: 33660172
SriVaddadi:
I get this error :
System.Data.SqlTypes.SqlNullValueException: Data is Null on results.Add(...)
:
 while (reader.Read())
                {
                    results.Add(reader.GetString(0));
                }

How can I solve this best ?
I did this :

while (reader.Read())
                {
                    try
                    {

                        results.Add(reader.GetString(0));
                    }
                    catch (Exception ex)
                    {
                                            }

                   
                }

Is that ok ?
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 33661076
no You should use IsDBNull method to check whether value is null or not.
 
and anothe question is why field have null value in database table?
while (reader.Read())
                {
                    try
                    {
if(results.IsDBNull(0) == false)
{
                        results.Add(reader.GetString(0));
}
                    }
                                     
                }
 
 
0
 

Author Comment

by:dekempeneer
ID: 33661204
why field have null value in database table

I have no idea, it is an old table, I'll just leave it like that as it will be phased out end of year.

I do get this error :
'System.Collections.Specialized.StringCollection' does not contain a definition for 'IsDBNull' and no extension method 'IsDBNull' accepting a first argument of type 'System.Collections.Specialized.StringCollection' could be found (are you missing a using directive or an assembly reference?)      

I have :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.DirectoryServices;

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now