We should always open the connections late and release them
early; in other words, immediately after we are done using it. Connections
should be opened only at the time when they are actually required. The
connections should not be acquired prior to its usage as it would decrease the
number of available connections in the Connection Pool and, hence, have
detrimental effects to the operation of the Connection Pool and the application's
performance. The connections should be explicitly released immediately when we
are done using it. This would facilitate better Connection Pooling as the
connection would be returned to the pool and be available for reuse. The
following snippet of code shows how we should open and close connections in our
applications efficiently.
Listing 4
SqlConnection sqlConnection = new SqlConnection(connectionString);
try
{
sqlConnection.Open();
//Some Code
}
finally
{
sqlConnection.Close();
}
The code in Listing 4 can be simplified further by using the
"using" keyword as shown in the code snippet below.
Listing 5
using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some Code
}
Note that using the "using" keyword as shown in
Listing V above, generates the try-finally block implicitly.
The following points illustrate the measures that can be
taken for better utilization of the Connection Pool.
·
Always open connections when needed and close it immediately when
you are done using it.
·
Close the user-defined transactions before closing the related
connections.
·
Ensure that there is at least one connection open in the pool to
maintain the Connection Pool.
·
Avoid using connection pooling if integrated security is being
used.
Connection Pooling can be monitored in one of the following
ways:
·
Executing the sp_who or sp_who2 stored procedures
·
Using the SQL Server Profiler
·
Using the Performance Monitor and Performance Counters