.net connection pooling

Question

I don't get what is the syntax difference between regular connection and connection pool.

When I'm using the using key such as:

using (SqlConnection connection = new SqlConnection(connectionString)) {     connection.Open();     command.ExecuteNonQuery(); } 

Is this the way to perform a connection pool?

Answer 1

You can read about connection pooling here.

Basically, as long as the connection string is the same (including case), connections will be taken from the same connection pool.

Answer 2

You do not control the connection pool with connections but with the connection string. Most ADO providers use pooling per default.

The using statement is used to call the Dispose method of the object (in this case the connection class). By doing so, the connection is either returned to the pool or being disconnected depending of the connection string configuration.

You should also be aware of that connections are not returned to the pool directly if distributed transactions are being used (TransactionScope in .Net 4). The connections are returned when the transaction have been completed/rolled back.

If you are not using using, you should make sure that you call Connection.Close() as soon as possible. Especially if your application is under some form of load.