Database Reference
In-Depth Information
We will discuss a few data access techniques commonly used in applications; however, it is important to talk
about database connections and connection pooling as a first step.
Connection Pooling
It is expensive to establish a connection to SQL Server. It requires an application to parse the connection string
information and establish communication with the server through a socket or name pipe. SQL Server needs
to authenticate a client and perform a few other steps before the connection can be used.
However, it is also beneficial to avoid keeping open connections in applications. Each open connection
becomes a SQL Server session and has a worker thread assigned to it. A large number of open connections can
consume a large number of threads, which negatively affect SQL Server performance and can prevent other clients
from connecting to it.
■
Note
We will discuss the SQL Server execution Model in greater detail in Chapter 27, “System troubleshooting.”
Consider the typical ASP.Net MVC-based application that works with SQL Server. A busy application can handle
dozens or even hundreds of users simultaneously. Internet Information Server (IIS) would create an instance of
the controller class to handle each request. Obviously, you would like to avoid the overhead of establishing a new
database connection every time a controller object is created.
Fortunately, SQL Server client libraries address this issue by utilizing connection pooling and caching active SQL
Server connections. When an application closes or disposes of a connection, the client library returns it to the pool
keeping the connection open rather than closing it. When the application requests the connection the next time, it is
reusing an already active connection from the pool rather than opening a new one.
Connection pooling can significantly decrease the number of connections required for an application. In our
web application example, every instance of the controller can access a database as part of the
Action
method,
which processes requests; however, the database connection could be kept open just for a fraction of the method
execution time. This will allow multiple objects to reuse connections without the overhead of opening them with
every call.
Connection pooling is enabled by default and controlled by a connection string. Each unique connection string
creates its own pool. By default, in ADO.Net, a connection pool is limited to 100 connections; however, you can change it
with the
Max Pool Size
connection string property. You can also specify the minimum number of connections to keep
in the pool with the
Min Pool Size
property and prevent providers from closing connections after a period of inactivity.
Finally, you can disable connection pooling with the
Pooling=false
configuration setting if needed.
It is extremely important to dispose of connections properly in the client applications and return them to the
pool. A client application would be unable to connect to SQL Server when the number of connections in the pool
reaches its maximum size and all connections are currently utilized.
Listing 16-1 shows how connections need to be managed in ADO.Net. A connection is opened when a client
needs it, kept active as little as possible, and disposed of afterwards.
Listing 16-1.
Working with SqlConnections in ADO.Net
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Do work here; Keep connection open as little time as possible.
// SqlConnection.Dispose method will be called at the end of
// using block. It will close connection and return it to pool.
}
