Better way for dropdownlists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Madison - 06 Nov 2007 14:09 GMT

Hi there,

I'm using framework 2 with web application project, one of my page have 5
dropdownlist and one gridview. The dropdownlists data are stored in SQL
Server (data may grow more in the future)

1. sector (about 4 items)
2. business type (39 items) depend on sector selected
3. building type ( 57 items) depend on sector selected
4. electric (1704 items) depend on other textbox entered
5. gas (1026 items) depend on other textbox entered

I'm using SqlDataAdapter to populate all dropdownlists when the page load
and using viewstate for postback. Every time postback happened I must
re-populate dropdownlist whatever values changes. I just wonder there is
better way to handle this solution. I'm thinking about SqlDataSourec for each
dropdownlist but what is the pro vs con for using SqlDataSource. When
postback happen, does each SqlDatasourec calls database? My gridview is
using SqlDataSource now.

Any suggestions. Thanks.

Reply to this Message

William (Bill) Vaughn - 06 Nov 2007 16:33 GMT

Consider that a single CommandText can return all of your dropdown list
rowsets in one round trip. Simply concatenate the SELECT statements
together. When Fill is executed, it generates (or repopulates) a DataTable
for each resultset/rowset.

Signature

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

> Hi there,
>
[quoted text clipped - 18 lines]
>
> Any suggestions. Thanks.

Reply to this Message

WenYuan Wang [MSFT] - 07 Nov 2007 10:00 GMT

Hello Madison,
Thanks for William's suggestion.

> When postback happen, does each SqlDatasourec calls database?

Data Source control doesn't retrieve records from underlying database when
postback happen.

It will be a simple task when we bind a control to a data source control in
ASP.NET 2.0. We do not require any code in a codebehind and not require
caring about Postback.

But your ASPX page may call database 5 times if there are 5 datasource
controls on it. Concatenating the select statements in one command text
should be good idea in your case, if it is possible for you.

Hope this helps. If you have any more concern, please feel free to update
here again. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Madison - 07 Nov 2007 19:06 GMT

Hello,

Thank you for both of you (Bill and Wen Yuan).

If I understand correctly Wen Yuan, you said I could retrieve multiple
records with one SqlDataSourec called. I never done with SqlDataSourece with
multiples recordset return before. Could you give me any samples? I did with
using SqlDataAdapter to return as many as 8 records at one time. In my case,
the one dropdown will retrieve data depend on the value from other dropdown
how it works with one retrieve. The way I have done now that I used
SqlDataAdapter to reteieve all records and saved them to ViewState and then
use DataTable to filter rows which I need for other dropdown.

Do you have any website or article (with samples) about SqlDataSource?

Thanks for your help.

Reply to this Message

WenYuan Wang [MSFT] - 08 Nov 2007 10:10 GMT

Hello Madison,
Thanks for your reply.

I'm sorry, maybe I mislead you. DataSource control only supports one record
set. We cannot retrieve multiple record sets with it.
What I mean is that I prefer Coding to DataSource Control. As Bill said,
using one SQLCommand call to retrieve all record sets from underlying
database is a good way. (Just as what you did) We can store the dataset
returned by SQLDataAdapter into Page.ViewState. When postback happens, we
get all records from ViewState, filter it by DataTableView, and bind them
to drop list box. That's fine.

However, if we choose SQLDataSource, it will save us much time on coding.
We can just drag-drop five SqlDataSource controls into page, set select
command/ parameter for each of them, and bind them to dropdownlist
controls. But there will be a performance issue. SqlDataSource will call
underlying DB, each time its parameter changed. For example: if end user
selects another option in (sector) dropdownlist, this will force two
sqldatasource controls (business type and building type) to call its
underlying DB. Thus, customer need wait a long time for this operation.

If you'd like to see some sample with SqlDataSource, you may refer to
http://www.asp.net/learn/data-access/tutorial-47-vb.aspx

Hope this helps, please feel free to update here again, if there is
anything unclear. It's my pleasure to assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

WenYuan Wang [MSFT] - 12 Nov 2007 11:41 GMT

Hello Madison,

This is Wen Yuan, again. I just want to check if there is anything we can
help with.
Please feel free to update here, if you have any more concern. We are glad
to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Better way for dropdownlists

Free Magazines