Hi again Nicholas/Mark,
After attempting to load the data on another thread it seems that - as we
predicted - the "choke" still occurs ON the databind.
Now that we've tried, I will not ask that we search for another similar
method since we all knew this was a bad idea to begin with and will see what
I can do about laying down the law on my supervisors who want things done
"their way".
If you are interested, I've posted my code to show you how I tried:
private SqlConnection sqlConnGetAccountsOnAnotherThread = null;
// delegate to fill grid/combobox from a thread other than the UI thread
private delegate void DatabindTheControlHandler(SqlDataReader reader);
// delegate to update status
private delegate void DisplayStatusHandler(string Text);
// boolean to indicate whether the asynchronous command is active or closed
private bool boolIsGetAccountsOnAnotherThreadExecuting = false;
private void cbxAccountID_GetAccountsOnAnotherThread()
{
if (boolIsGetAccountsOnAnotherThreadExecuting)
{
// do nothing, the button should be disabled as the process is
already running.
// MessageBox.Show("List is already refreshing.\r\n\r\nPlease wait
until the current refresh has completed.", "Information",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
// disable the refresh button until the process is complete
btnAccountRefresh.Enabled = false;
SqlCommand sqlCommand = null;
string strSQL = "";
try
{
DisplayStatus("Connecting...");
sqlConnGetAccountsOnAnotherThread = new SqlConnection(@"Data
Source=MyDataSource;Server=MyServer;DataBase=MyDatabase;Asynchronous
Processing=true");
sqlCommand = new SqlCommand();
strSQL = "MySQLCode";
sqlCommand.CommandText = strSQL;
sqlCommand.Connection = sqlConnGetAccountsOnAnotherThread;
sqlConnGetAccountsOnAnotherThread.Open();
DisplayStatus("Executing...");
boolIsGetAccountsOnAnotherThreadExecuting = true;
// passing SqlCommand as a parameter makes it easier to call
EndExecuteReader();
AsyncCallback acGetAccountsOnAnotherThread = new
AsyncCallback(callbackGetAccountsOnAnotherThread);
sqlCommand.BeginExecuteReader(acGetAccountsOnAnotherThread,
sqlCommand);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
if (sqlConnGetAccountsOnAnotherThread != null)
sqlConnGetAccountsOnAnotherThread.Close();
}
}
}
private void callbackGetAccountsOnAnotherThread(IAsyncResult iarResult)
{
try
{
// retrieve the original command object
SqlCommand sqlCommand = (SqlCommand)iarResult.AsyncState;
SqlDataReader sqlDataReader = sqlCommand.EndExecuteReader(iarResult);
// execute the code from a different thread instead of main thread.
DatabindTheControlHandler dchGetAccountsOnAnotherThread = new
DatabindTheControlHandler(DatabindTheControl);
// call the UI delegate.
Invoke(dchGetAccountsOnAnotherThread, sqlDataReader);
}
catch (Exception ex)
{
// we are running the code in a seperate thread so we need to catch
the exception.
// Else we are unable to catch the exception anywhere.
Invoke(new DisplayStatusHandler(DisplayStatus), ex.ToString());
}
finally
{
boolIsGetAccountsOnAnotherThreadExecuting = false;
}
}
private void DisplayStatus(string Text)
{
this.Text = Text;
}
private void DatabindTheControl(SqlDataReader sqlDataReader)
{
try
{
DataTable dt = new DataTable();
dt.Load(sqlDataReader);
cbxAccountID.DataSource = dt;
// dgvAccountID.DataSource = dt; // DataGridView instead of ComboBox
DisplayStatus("Ready");
}
catch (Exception ex)
{
DisplayStatus(string.Format("Ready: Last attempt failed.",
ex.Message));
}
finally
{
// Don't forget to close the connection, as well.
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (sqlConnGetAccountsOnAnotherThread != null)
{
sqlConnGetAccountsOnAnotherThread.Close();
}
// Renable our button
btnAccountRefresh.Enabled = true;
}
}
Nicholas Paldino said:
RR,
Yes, you are still going to have the issue of the binding being slow.
As I mentioned, no matter what, you MUST bind on the UI thread, and like
Marc said, on other controls, virtualizing the list might be an option, but
not in this case (the ComboBox does not support virtualization).
This is simply the price you pay for binding so many items to the list
and it must be done in the UI thread. I mentioned loading the data in a
background thread only to help minimize some of the time. This is the best
you can do in this situation, since you MUST use the list with 115K items.
That's why I mentioned what I did in my last paragraph in my previous
post about filtering, how it would be a better user experience. The benefit
though of doing it that way is that you could probably get a much faster
"list" as well.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)
R Reyes said:
Hi Nicholas and thanks for your input - I will give this a shot tomorrow
morning first thing and let you know what happens.
My thoughts:
Currently, loading 115,000 items on the UI thread (which is really just a
simple key/value pair converted into a DataSet/DataTable returned from a
SqlServer query) loads in a few seconds or so, I believe. This is NOT
where
things slow down. I will confirm this tomorrow.
I THINK the problem is that the "slow/choke" occurs DURING the actual BIND
CODE and NOT at the loading of items:
// some code that gets our data (My code: UI thread; Your code: Another
thread)
[...]
// this BIND CODE is WHEN the slow down really occurs not during the load
above
cbxAccounts = dt; // or ds.Tables[0];
// bs.datasource = dt; // ...wondering if bindingsource works in this case
also?
I will test and confirm the exact line of code WHEN the time lapse occurs.
What I'm questioning here is that even if the data loads well on another
thread (or the UI thread as I've been doing it so far) and a call to the
main
thread is marshaled, would the problem still "slow/choke" for the same
minute
or two during the - as you put - "bind to newly loaded data" (BIND CODE)?
Or, is that how the marshaling/threading resolves this problem?
My logic is only how I see things because I'm not the best at
understanding
threading/marshaling yet, but maybe will understand more with your input
after I dive into the code you suggested a bit further =)
Will work on this in the AM and keep you posted. Thanks again, Nicholas
RR
Nicholas Paldino said:
RR,
You aren't going to be able to bind to the ComboBox asynchronously.
It's just not possible, since binding the data to the ComboBox MUST be on
the UI thread.
However, that doesn't mean you can't load the data on another thread.
This is really your best bet. Load the data on another thread, then
marshal
a call to the main thread when done loading the data, and bind to the
newly
loaded data.
Of course, I am sure you know that from a UI design perspective,
having
a list of 100K items just doesn't work that well, and filtering is really
a
better user experience (and probably more productive, since you won't
have
to scroll down 100K items if you have to get to something relatively
close
to the end of the list). You might want to try and impress that on your
superiors.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)
Hi,
Problem: How can I databind (or put) a SqlServer query's row return of
115,000 items into a ComboBox quickly? Not much longer than a matter
of
seconds, that is...
Scenario: I am rebuilding my company's Access 97 VBA database app. It
pulls
115,000 items (of account names) from SqlServer and the data is bound
to a
single Access 97 ComboBox control. My C# version needs to work exactly
like
this one. Our executive employees want to be able to select from the
entire
list of 115,000 items in the Combo as it is in Access 97 app. They
prefer
not to use filters to populate the ComboBox with less items as it
"slows"
down their production by having to take extra steps.
My attempt using C#.NET: Currently, I can databind the 115,000 objects
to
the ComboBox and as you can already guess it takes at least a minute or
two
to finish the process.
What to do now?: Is "asynchronous databinding" possible? I see tons of
articles on asynchronous programming including progressbar reporting
however
none of these articles explain how to use databinding into the picture
to
speed up load times. Or is that not even how it works...
What are my alternatives to getting these 115,000 items to populate the
ComboBox in a matter of seconds as it does in the Access 97 program?
Does
anyone even know why it's so quick to databind in Access 97 but so slow
in
.NET?
Any help is appreciated. Thanks in advance.
RR
