populate combobox asynchronously

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

dekempeneerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SriVaddadiConnect With a Mentor Commented:
Please download the code from the below link and runhttps://filedb.experts-exchange.com/incoming/ee-stuff/7983-Memo2010a.zip
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
SriVaddadiCommented:
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
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.

 
SriVaddadiCommented:
FYI, MARS is only supported in SQL Server 2005 and up.
0
 
dekempeneerAuthor Commented:
Can anyone please transform my code with use of such a executereader ?
0
 
SriVaddadiCommented:
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
 
dekempeneerAuthor Commented:
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
 
dekempeneerAuthor Commented:
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
 
SriVaddadiCommented:

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
 
dekempeneerAuthor Commented:
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
 
Naman GoelSoftware engineer 1Commented:
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
 
dekempeneerAuthor Commented:
naman_goel, does that also work asynchronous so it does not slow down the formrendering ?
0
 
Naman GoelSoftware engineer 1Commented:
Yes, This method is asynchronous only, BeginInvoke call will always be asynch...
 
Thanks
0
 
SriVaddadiCommented:
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
 
Naman GoelSoftware engineer 1Commented:
sorry I am not able access that link.
0
 
dekempeneerAuthor Commented:
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
 
SriVaddadiCommented:
dekempeneer, I wonder how many records are you trying to fetch?
0
 
dekempeneerAuthor Commented:
for the time being there are 50000 records in one list(combobox)
300 in another (AD accounts)
 
0
 
Naman GoelSoftware engineer 1Commented:
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
 
dekempeneerAuthor Commented:
Sorry naman_goel, it works, but the other comboboxes stay unworking as well
0
 
Naman GoelSoftware engineer 1Commented:
what do you want?? I am not getting ...
Other combo box should work until they are not dependent on this combobox (cboToExternal)...
 
0
 
dekempeneerAuthor Commented:
I have for now 3 comboboxes. But they do not open until the large one has completed filling.
0
 
Naman GoelSoftware engineer 1Commented:
so are they dependent on the first one... ?
0
 
dekempeneerAuthor Commented:
No
0
 
Naman GoelSoftware engineer 1Commented:
can you send me the code @ goel.naman@gmail.com
as this should work in all cases...
0
 
Naman GoelSoftware engineer 1Commented:
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
 
Naman GoelSoftware engineer 1Commented:
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
 
SriVaddadiCommented:
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
 
SriVaddadiCommented:
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
 
dekempeneerAuthor Commented:
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
 
SriVaddadiCommented:
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
 
dekempeneerAuthor Commented:
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
 
SriVaddadiCommented:
ok. So now once you select an item in the from combo box, is the form freezing? is that ur prob
0
 
dekempeneerAuthor Commented:
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
 
SriVaddadiCommented:
It seems wierd though.. The whole idea and purpose of BackgroundWorker is to avoid UI freezing .. let me tsee..
0
 
dekempeneerAuthor Commented:
Don't I also neeed to put a BackgroundWorker Component from the toolbox in the windowsform ?
0
 
SriVaddadiCommented:
Not required. It is either of them. Either component or class not both.
0
 
SriVaddadiCommented:
Please add the below lines of code to the code in MemoWindow_Load event handler adInfoFetcher.RunWorkerAsync();            sqlInfoFetcher.RunWorkerAsync();            matterInfoFetcher.RunWorkerAsync();
0
 
SriVaddadiCommented:
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
 
SriVaddadiCommented:
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
 
dekempeneerAuthor Commented:
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
 
dekempeneerAuthor Commented:
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
 
dekempeneerAuthor Commented:
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
 
Naman GoelSoftware engineer 1Commented:
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
 
dekempeneerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.