>This is a very common pattern for two-tier application (client/server). On startup the application creates and holds a connection to the server.
>
>On the other hand, most n-tier apps acquire the connection right before it's going to be used and then release it right afterwards. This works especially well if you're using COM+ since it will manage the connection pool.
If I create it at first and use the same handle all the time, I guess this will be somewhat faster. Is that right?
Also, if that is the case, that means, we will have to issue a handle per connection string that we will use in the application. By that I mean that we can have several connection strings to access various databases with various passwords. So, I'd have to implement an approach that will allow me to setup various connection strings for the ones I need.
But, this also brings a new consideration. If something happens on the backend, such as a restart of the SQL Server, this would mean that I'd lost those handles. I'll then have to verify or handle that to reset if the situation even happens. Issuing a connection string when require will avoid that as it won't be a factor.