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

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 Removed)

"R Reyes" <(E-Mail Removed)> wrote in message
news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
>

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 [.NET/C# MVP]" wrote:

> 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 Removed)
>
> "R Reyes" <(E-Mail Removed)> wrote in message
> news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
> >
>

Not sure it applies to ComboBox, but more similar controls (such as
grids), VirtualMode is an option.

For ComboBox, perhaps an AutoComplete source might be more
user-friendly than a mammoth list?

Marc

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 Removed)

"R Reyes" <(E-Mail Removed)> wrote in message
news:CFFA851A-994F-458A-8B51-(E-Mail Removed)...
> 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 [.NET/C# MVP]" wrote:
>
>> 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 Removed)
>>
>> "R Reyes" <(E-Mail Removed)> wrote in message
>> news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
>> >
>>

Have you tried using BeginUpdate() and EndUpdate()? I'm not sure these work
with data-bound combo boxes.

Have you tried adding the items to the combo box manually? The following
code executed in about 5 seconds on my laptop:

comboBox1.BeginUpdate();
try
{
comboBox1.Items.Clear();
for (int i = 1; i < 115000; i++)
comboBox1.Items.Add(i.ToString());
}
finally
{
comboBox1.EndUpdate();
}




"R Reyes" <(E-Mail Removed)> wrote in message
news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
>

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 [.NET/C# MVP]" wrote:

> 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 Removed)
>
> "R Reyes" <(E-Mail Removed)> wrote in message
> news:CFFA851A-994F-458A-8B51-(E-Mail Removed)...
> > 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 [.NET/C# MVP]" wrote:
> >
> >> 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 Removed)
> >>
> >> "R Reyes" <(E-Mail Removed)> wrote in message
> >> news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
> >> >
> >>
>

Interesting - I wouldn't have thought a for loop would work so quickly. I
will give this a shot and let you know how it went.

Thank you, Scott.

"Scott Roberts" wrote:

> Have you tried using BeginUpdate() and EndUpdate()? I'm not sure these work
> with data-bound combo boxes.
>
> Have you tried adding the items to the combo box manually? The following
> code executed in about 5 seconds on my laptop:
>
> comboBox1.BeginUpdate();
> try
> {
> comboBox1.Items.Clear();
> for (int i = 1; i < 115000; i++)
> comboBox1.Items.Add(i.ToString());
> }
> finally
> {
> comboBox1.EndUpdate();
> }
>
>
>
>
> "R Reyes" <(E-Mail Removed)> wrote in message
> news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
> >
>
>

Not as slow, but not so fast either. I unbinded the ComboBox and added the
items manually using the .Add() function. My test ran for about 25-30
seconds down from 50-55 with the asynchronous attempt solution.

Now, as soon as I commented out the line
"cbxAccountID.Items.Add(strAccountData);" (manual BIND CODE) it ran in a few
seconds. However when I reenable that line of code, the time goes up to
25-30 seconds as expected.

What I discovered:
I'm not looping through 115,000 items but an actual DataTable/DataSet filled
with 115,000 rows. Maybe that is why it takes longer? To further
investigate, I did what you did, and used a for loop with a max count number
of 115,000. This actually increased speed up to around 20-25 seconds instead
of 25-30.

What I am thinking of doing now is maybe finding a way to convert the
DataTable/DataSet into an array or list (I don't know which is faster? Or
maybe there is something even faster than these?) and then loading THAT type
of data into the ComboBox.

Other than that, I'm not sure how you got 115,000 to load in a couple of
seconds. My computer is not slow either 3MB RAM and 3GHz...very curious.
Maybe I will also try removing all related code with that ComboBox, it's
possible it could be linked to some other processes. I'll do that now.

Thanks

"R Reyes" wrote:

> Interesting - I wouldn't have thought a for loop would work so quickly. I
> will give this a shot and let you know how it went.
>
> Thank you, Scott.
>
> "Scott Roberts" wrote:
>
> > Have you tried using BeginUpdate() and EndUpdate()? I'm not sure these work
> > with data-bound combo boxes.
> >
> > Have you tried adding the items to the combo box manually? The following
> > code executed in about 5 seconds on my laptop:
> >
> > comboBox1.BeginUpdate();
> > try
> > {
> > comboBox1.Items.Clear();
> > for (int i = 1; i < 115000; i++)
> > comboBox1.Items.Add(i.ToString());
> > }
> > finally
> > {
> > comboBox1.EndUpdate();
> > }
> >
> >
> >
> >
> > "R Reyes" <(E-Mail Removed)> wrote in message
> > news:2D302E7A-A596-49E5-8B7E-(E-Mail 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
> > >
> >
> >

> What I am thinking of doing now is maybe finding a way to convert the
> DataTable/DataSet into an array or list (I don't know which is faster? Or
> maybe there is something even faster than these?) and then loading THAT
> type
> of data into the ComboBox.

Well, you can start by not putting the data into a DataTable/DataSet in the
first place. Use a DataReader instead. Although I think you've already
established that data access isn't the problem.

> Other than that, I'm not sure how you got 115,000 to load in a couple of
> seconds. My computer is not slow either 3MB RAM and 3GHz...very curious.
> Maybe I will also try removing all related code with that ComboBox, it's
> possible it could be linked to some other processes. I'll do that now.

It wasn't a couple of seconds. I said 5 but I was guess-timating. I put a
timer on it and it's really 8 or 9 (my guess-timating is evidently not so
good). It was, however, significantly faster than 50-55 and that was my
point. I have a table with just over 285K rows and I can get it to load in
about 24 seconds. Unless I throw more hardware at it, I think that's about
as good as I'm going to get.

FWIW, here's my code:

DateTime startTime = DateTime.Now;

using (SqlConnection cn = new
SqlConnection(Sbs.Common.Framework.Data.ConnectionManager.DefaultAdaptor.ConnectionString))
{
SqlCommand cmd = new SqlCommand("select * from vh", cn);
cn.Open();
SqlDataReader reader = cmd.ExecuteReader();
Month.BeginUpdate();
try
{
Month.Items.Clear();
while (reader.Read())
Month.Items.Add(reader.GetString(1));
}
finally
{
reader.Close();
Month.EndUpdate();
}
}

DateTime endTime = DateTime.Now;
TimeSpan ts = new TimeSpan(endTime.Ticks - startTime.Ticks);


MessageBox.Show("Elapsed Time: " + ts.Seconds.ToString() + "
seconds");