How to connect SQL server once

Question

Since connecting to SQL server takes time, I want to open once an the connection handle. The connection handle is to be used in any position of a solution. Could anyone tell me how to do it?

TIA

Answer 1

In practice there's not much point trying to optimise SQL connections (unless you have measured the performance issues of your application and a new strategy becomes necessary), as connections are already heavily optimised by ADO.NET using connection pooling, meaning that if you instantiate a SqlConnection and call .Open() on it, this isn't to say that you're opening a new connection. It may be an already opened connection that's no longer in use that can be repurposed for your new connection.

Before considering this approach it's worth having a read of this article:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

Particularly the first paragraph which summarises what pooling is, as well as this word of caution:

   We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool.