>If I create it at first and use the same handle all the time, I guess this will be somewhat faster. Is that right?
In most cases. Since the connection is already open, you won't absorb the time that it takes to open a new conneciton. Now, ODBC, OLEDB, and the .NET managed provider all support connection pooling. In which case, when you went to connect, if there was already a connection in the pool, it would be returned to you. If there wasn't one available, a new connection would be created.
>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.
That is correct. If you're working with multiple data sources, you would need one for each source/credentials that you wanted to connect to.
>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.
Very true. You'll have to test the connection before trying to use it. You could wrap the connection handle with a function that does this for you and reconnects if the handle is deemed invalid.
-Mike