Hi,

If I have several different drop down lists on a single web page, what is the best design for retrieving the different result sets?

Should I be opening a connection to the database just once?

Cheers,

Ian.

Sure openning the connection just once for multiple fetches increases the performance a lot.

OK, I'm thinking that I should create all of the predicates, sort clauses etc. in the page itself, open the connection, get the data, close the connection and then dish out the datasets/lists to the appropriate web controls.

There's one thing that bugs me about this though. What if I have a user control that I want to be able to just drop into different pages? Wouldn't it be nice to have the data access code inside the user control so that I don't have to cut and paste the code into the page which will contain the user control? If all of the data for a web page is retrieved in one place, then isn't the page a little monolithic?

Using adapter, you can create an adapter in the page, pass it to the controls for further processing inside the controls.

Though if you're using an n-tier approach, making it more generic also has the disadvantage that the controls have to call the BL routines they need, which thus should be working without knowledge of the page the control is on.

Does anyone have any book recommendations for this sort of stuff? I've been recommended 'Building Object Applications that work' at asp.net. Anyone care to comment on this book? Its kind of old.

Thanks for the replies.

Though if you're using an n-tier approach, making it more generic also has the disadvantage that the controls have to call the BL routines they need, which thus should be working without knowledge of the page the control is on.

Sorry to be picky but I'm just interested in this stuff.

What do you mean by make it more generic?

How does a control calling a BL routine imply that a control has knowledge of the page its on?

Ian wrote:

Though if you're using an n-tier approach, making it more generic also has the disadvantage that the controls have to call the BL routines they need, which thus should be working without knowledge of the page the control is on.

Sorry to be picky but I'm just interested in this stuff.

What do you mean by make it more generic?

With generic I mean: it can function without relying on the logic in the page. So you just drop it on the page, and data it needs will be read by the control itself. Somewhat what you had in mind.

How does a control calling a BL routine imply that a control has knowledge of the page its on?

I meant the opposite: if the component reads data by itself, it calls a BL routine to do that: it calls a BL method and that method returns with the data. If the page has more of these controls, this means that the page can't share a connection object for example, as the components communicate with the BL individually, without the page.

If you don't want that, you have to fetch the data in the page and pass it on to the control.

Can UnitOfWork be used to retrieve multiple result sets? Or is only for doing updates and inserts?

UnitOfWork is for updates, inserts and deletes. You can add calls to procs or other delegates, so you COULD add fetches (using a delegate) but not directly.

Sure openning the connection just once for multiple fetches increases the performance a lot.

Hey Walaa, do you have any data to back that up?

Ian wrote:

Sure openning the connection just once for multiple fetches increases the performance a lot.

Hey Walaa, do you have any data to back that up?

If I say he's right, will you believe me?

The thing is: if you use 1 connection (using adapter, first call OpenConnection, set KeepConnectionOpen to true) for multiple fetches doesn't give the overhead of creating a new connection, initializing it, etc. etc. Normally a pooled connection is ready in 20ms, but if the pool is dry, you get a new one, which is substantially slower to create.

Hey, Ian. Do a search for Inversion of Control and Dependency Injection. Or, just look at Fowler's article: http://www.martinfowler.com/articles/injection.html. IoC/DI is an obvious, but powerful, technique for creating reusable and self-sufficient components without violating the Law of Demeter.

Jeff...