Multiple databound dropdown lists

[Lair]

I am creating a page that has three to four databound dropdown lists.
Each one is has different data sometimes from the same table but with a
different where clause. What is the best way to populate the data
onload? Right now it's opening and closing a connection for each
dropdown list/Query. I would like to find a way that I can do all the
queries at once without having to open and close the connection several
times. I am using mysql/ asp.net (VB).

Thanks.

 

[Steve C. Orr [MVP, MCSD]]

Actually the connections are not actually opened and closed as many times as
you think because ADO.NET has built-in connection pooling. Therefore your
technique may be more efficient than you thought.
If you want to gain more efficiency you may be able to call one stored
procedure that returns 4 result sets.
Or you may be able to cache the results so you don't have to requery the
data.

 

[sloan]

I'd suggest ( like Steve C did)
a stored procedure with multi select statements.

I have a sample at:
http://sholliday.spaces.msn.com/PersonalSpace.aspx 5/24/2006 entry

the datareader allows you to go thru multiple resultSets

...

Or you could create a strongly typed DataSet, and keep all data in it.
and some column that says "DropDownBoxNumber"..

<MyInfoDS>
<EntryItem>
<State>VA</State>
<DDL>1</DDL>
</EntryItem>

<EntryItem>
<State>NC</State>
<DDL>2</DDL>
</EntryItem>

<EntryItem>
<State>VA</State>
<DDL>2</DDL>
</EntryItem>

</MyInfoDS>


Here, VA would show up in DDL(1)
NC and VA would show up in DDL(2)
(use the DataSet.Select method to filter the rows .. )

You have some choices. I think the DataSet idea is fudgy, but it would
work.


If you check my blog: you find a "Web Session Object Holder" .. which you
could incorporate to use as a caching mechism..
if you DDL values seldom change, and you load the page(s) that use them alot
of times.