question

BenTam avatar image
0 Votes"
BenTam asked Paul-5034 edited

How to connect SQL server once

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

dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Paul-5034 avatar image
0 Votes"
Paul-5034 answered Paul-5034 edited

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://docs.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.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.