populate combobox asynchronously

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

FYI, MARS is only supported in SQL Server 2005 and up.

ASKER

Can anyone please transform my code with use of such a executereader ?

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;
}
}
}

ASKER

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 ?

ASKER

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 ?

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.

ASKER

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.

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

ASKER

naman_goel, does that also work asynchronous so it does not slow down the formrendering ?

Yes, This method is asynchronous only, BeginInvoke call will always be asynch...

Thanks

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.

sorry I am not able access that link.

ASKER

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

dekempeneer, I wonder how many records are you trying to fetch?

ASKER

for the time being there are 50000 records in one list(combobox)
300 in another (AD accounts)

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

ASKER

Sorry naman_goel, it works, but the other comboboxes stay unworking as well

what do you want?? I am not getting ...
Other combo box should work until they are not dependent on this combobox (cboToExternal)...

ASKER

I have for now 3 comboboxes. But they do not open until the large one has completed filling.

so are they dependent on the first one... ?

ASKER

can you send me the code @ goel.naman@gmail.com
as this should work in all cases...

https://filedb.experts-exchange.com/incoming/ee-stuff/7979-MemoWindow.zip

ASKER

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.

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.

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

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.

ASKER

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.

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

ASKER

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.

ok. So now once you select an item in the from combo box, is the form freezing? is that ur prob

ASKER

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.

It seems wierd though.. The whole idea and purpose of BackgroundWorker is to avoid UI freezing .. let me tsee..

ASKER

Don't I also neeed to put a BackgroundWorker Component from the toolbox in the windowsform ?

Not required. It is either of them. Either component or class not both.

Please add the below lines of code to the code in MemoWindow_Load event handler adInfoFetcher.RunWorkerAsync(); sqlInfoFetcher.RunWorkerAsync(); matterInfoFetcher.RunWorkerAsync();

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

Start Free Trial

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();

ASKER

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

ASKER

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

ASKER

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 ?

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));
}
}

}