It doesn't matter whether this is a Web or desktop app really. Re-using connections from the SQL Server connection pool is nearly as fast as just reusing an existing connection that is cached (the overhead to retrieve the open connection is so minmal as to not really matter).

The only time it might make sense to hang on to a connection handle is if you are running multiple requests in a single sequential operation. But it's a bad idea to cache connections on an application level when that's the purpose of the connection pool in the SQL Server client connection components.

+++ Rick ---


>Yeah, if this was a Web app I wouldn't have the questions as it 'forces' you to close inbetween - almost like being on the mainframe again (grin). Unfortunately, we're not using any kind of framework here - and it would take forever and a day to bring one in.
>
>This is a computer adaptive test that will have anywhere from 30 - 160 people taking it on any given week. It's also a timed test, hence my concern for performance.
>
>>If you're using a SQL connection to a SQL backend tha supports a Connection Pool of connections (which is most server based SQL providers), it's best to always open, use, and then immediately close connections. There's no noticable performance benefit to hanging on to an open connection to run multiple requests to just closing the connection each time. Even if you're running consecutive batches it can be better for scalability and performance to release the connection between batches so that other threads can share the connection instance.
>>
>>For consistency's sake - and for the business layer to have the freedom to not have to worry about connections being open or not it simply better to open and close connections as needed. Most ORMs do just that - if you're using Entity Framework, nHibernate or other ORM you don't even get a choice it'll just happen automatically.
>>
>>There are scenarios where you need to leave connections open - like for spanning transactions, but otherwise it's best just to rely on connection pooling.
>>
>>For Web applications in particular it would be really problematic to hold connections open for more than single operations as you would likely end up with lots and lots of connections on busy sites. Using a connection pool a limited number of connections are shared briefly only for the time the queries are executing and can shuffle between active threads, allowing much more efficient use of actual Sql connections used.
>>
>>+++ Rick ---
>>
>>>Mayhaps I'm overthinking it again,and I'll grant that perhaps part of my problem is that I'm STILL waiting for .Net to become all that much better and I keep getting lost in the stupid dots but here we go.
>>>
>>>Is it better to
>>>a) Create/Open/Close as DB connection everytime I need one,
>>>b) can I just create one in the signon screen that will persist through to the DispQuestions screen, or
>>>c) Create/Open/Close DB connection on Signon screen and then one for the DispQuestions screen.
>>>
>>>Thanks
>>>
>>>Dorris