Hey, Kevin

Can you provide a few specifics about your general architecture and requirements (internal application only, or do you have some/all users who will connect remotely, is there just one database involved or could this app have to access multiple databases, is this with winforms or browser-based, etc.)

Whether this is an internal-only app, or one that's distributed (or potentially may be distributed) can factor into your decisions and approaches.

Rod and Bonnie gave good advice - in a distributed environment you want to open/close on demand. Open the jar, grab the cookie, neatly close the jar...or at some point you may wind up with little crumbs all over the place. ;)

I agree with Bonnie, even if you're using Winforms, you'll still want to use Web Services if some users are accessing remotely. In that instance, the web service will likely work with some business object in your application layer to handle the data access to your database and return results back to your client piece.

Her reference to remoting is partly because some people run into a client/company that flat out will not permit IIS to provide access across physical boundaries. (Pretty unusual, but it can happen). But aside from that, Web Services is a great way to go for a distributed app. They're fairly easy to implement, and it seems MS is investing more and more into that approach.

Just one more thought...sometimes a user may need to access two different databases. Maybe one is production and one is test - or maybe the same app is used for different customer databases. The user could select which database they want to log into, and the client piece sends a flag/variable for the selection through the web service - and then the application layer determines which connection string (which might be read out of XML, might be encrypted, depending on your authentication) to associate with that flag/variable. Just some random thoughts.

Great discussion! ...and Rod/Bonnie are always right! ;)

Kevin