Ado.net 2.0 Technical Insider _ Connection Pool __.net

3.6 Connection Pool

Similar to Microsoft's previous data access technology, ADO. NET includes built-in support for connection pooling. 3.6.1 Connection handle and physical connection

If you are using Visual Studio, you can use the Visual Studio debugging tools to examine some of the object's internal private properties. For example, write some code to open a SqlConnection and set a breakpoint where the Open method is called. Right-click the object in your code and select Add Watch to add the object to the Watch window. In the Watch window, expand the area marked with the NON-PUBIC members. Scrolling down, you will see a private property called Innerconnection.

Structurally, the content of the Innerconnection property is a very thin layer that sits on top of the physical connection of the database. For discussion here, the Innerconnection property and the physical connection to the database are interchangeable. As you step through the code, you will see that the value of the Innerconnection property changes when you open and close the connection. SQL Client When the open method is called. NET data provider to associate a SqlConnection object to the physical connection of the database, you can execute the query and return the results.

The cost of opening and closing a database connection is very high. To help conserve resources and improve performance,. NET data providers in the. NET Framework use connection pooling by default. What is the 3.6.2 connection pool?

Connection pooling is a mechanism for improving application performance when you open a connection to a data store. SQL Client when calling the Close method of the SqlConnection object. NET data provider does not actually close an internal connection. Instead, the data provider stores the internal connection in a pool so that it can be used again later. Even after the SqlConnection object is processed, the inner join remains in the pool. If you later invoke the SqlConnection object's open method with the same connection string and credentials, you will again communicate with the database using the same internal connection.

If you want to confirm that you are really using the same internal connection again, you can use the features in. NET Reflection to programmatically access the contents of the private Innerconnection property. The following code, which requires a reference to the System.Reflection namespace, opens a SqlConnection in the using code block and stores the value of the SqlConnection innerconnection property. SqlConnection is implicitly handled at the end of the code block by using a using code block. This code opens another SqlConnection in the using code block and stores the value of the SqlConnection innerconnection property. Finally, this code compares the contents of the Innerconnection property to confirm that they are actually the same object.

Visual Basic

Dim strconn as String = "Data source=./sqlexpress;integrated security=true;"

Dim Propinnerconn as PropertyInfo

Propinnerconn = GetType (SqlConnection). GetProperty ("Innerconnection", _

BindingFlags.NonPublic Or bindingflags.instance)

Dim objInnerConn1, objInnerConn2 as Object

Using cn as New SqlConnection (strconn)

cn. Open ()

OBJINNERCONN1 = Propinnerconn.getvalue (cn, nothing)

cn. Close ()

End Using

Using cn as New SqlConnection (strconn)

cn. Open ()

OBJINNERCONN2 = Propinnerconn.getvalue (cn, nothing)

cn. Close ()

End Using

Console.WriteLine (ObjInnerConn1 is OBJINNERCONN2)

Visual C #

String strconn = @ "Data source=./sqlexpress;integrated security=true;";

PropertyInfo Propinnerconn;

Propinnerconn = typeof (SqlConnection). GetProperty ("Innerconnection",

BindingFlags.NonPublic | BindingFlags.Instance);

Object ObjInnerConn1, ObjInnerConn2;

using (SqlConnection cn = new SqlConnection (strconn))

{

cn. Open ();

OBJINNERCONN1 = Propinnerconn.getvalue (cn, NULL);

cn. Close ();

}

using (SqlConnection cn = new SqlConnection (strconn))

{

cn. Open ();

OBJINNERCONN2 = Propinnerconn.getvalue (cn, NULL);

cn. Close ();

}

Console.WriteLine (objInnerConn1 = = objInnerConn2);

Two SqlConnection objects are created in different using code blocks, so their resources are purged at the end of each of the using code blocks. The contents of the Innerconnection property and its encapsulated physical connection are not processed, but are stored in the pool for reuse later.

Note If you disable the connection pool in the connection string (which will explain how to disable it later), you will see that the internal connection cannot be reused. How 3.6.3 connection pooling improves code

Consider a typical asp.net or webservices application that accesses a SQL Server database. Each time a client application needs to query the database, it makes a round trip in the server-side code to open the SqlConnection to execute the query. In many of these applications, this code connects to the same database again and again with the same credentials. Theoretically, this means that every time a client application needs to execute a query, the server-side code needs to perform three actions-log in to the database (you need to check the credentials provided), execute the query, and then log off.

Connection pooling can really improve the performance of such applications. By storing internal connections in a pool and reusing them later, you no longer degrade performance by logging on to the database and logging off from it. Calls to the open and close methods of the SqlConnection object can be returned in a short time, which can improve the performance and responsiveness of your code (see Figure 3.4).

Figure 3.4 Connection pooling in a typical asp.net or webservices application 3.6.4 Enable connection pooling

In ado.net, connection pooling is enabled by default. The following code snippet opens and closes the same SqlConnection object 5 times. Because the connection pool is enabled by default, when the Close method is called, the actual connection to the database is not actually closed, but the database connection is sent to the pool for reuse later.

Visual Basic

Dim strconn as String

strconn = "Data source=./sqlexpress;integrated security=true;"

Dim cn as New SqlConnection (strconn)

For intcounter as Integer = 1 to 5

cn. Open ()

cn. Close ()

Next intcounter

Visual C #

String strconn;

strconn = @ "Data source=./sqlexpress;integrated security=true;";

SqlConnection cn = new SqlConnection (strconn);

for (int intcounter = 1; intcounter <= 5; intcounter++)

{

cn. Open ();

cn. Close ();

when the connection in the pool is closed 3.6.5

When the Close method is called, SqlClient returns the connection to the pool. Assuming that the connection is not used again, it will be removed from the pool in about 5 minutes. But the exact number of seconds after the deletion, and there is no precise value. The behavior depends on the number of random numbers generated and the relative humidity (relative humidity) when the pool is created. Of course, if there are open connection pools when exiting the application, these connections will be closed and processed as part of the normal cleanup process for the application. 3.6.6 Disable connection pooling

You may not want to use connection pooling. For example, if you are using a simple Windows application that communicates directly with the database, you may want to disable connection pooling. When this architecture is adopted, individual client applications need their own connections. When connection pooling is enabled, each application's connection is put into the pool, and if the connection is reopened before the connection pool is cleared, the connection into the pool is reused. Therefore, if the application frequently uses the connection repeatedly, the call to Sqlconnection.open will return more quickly when connection pooling is enabled. However, this approach will result in many active database connections at any given time. Disabling connection pooling will reduce the number of active database connections at any one time, but this will force all calls to Sqlconnection.open to establish a new database connection.

If you want to disable connection pooling, you can disable connection pooling individually by adding pooling=false to the connection string.

Fortunately, there is no longer a need for memory and such attributes in Ado.net 2.0. If there is a doubt, you can check the options for the SqlConnectionStringBuilder class. In this class, you can find a pooling property whose value is a Boolean type. By default, this value is set to true. Setting this value to False will prevent the connection from being put into the pool. Therefore, when you call the Close method of the SqlConnection object, the actual connection to the database is closed.

Note In Windows applications that occasionally connect, it may be helpful to use a connection pool, depending on your application. If your application wants to reconnect to the database on a regular basis, you can play the role of a connection pool and keep the physical connection to the database open, at least temporarily. If the application attempts to reconnect to the database before removing the physical connection from the pool, the connection pool logic (pooling logic) will reuse the physical connection to the database. 3.6.7 Frequently asked questions about connection pooling

The more developers who learn connection pooling, the more problems they will have. For example, one of the most common problems I've heard about connection pooling is "How do I know if the physical connection to the database is actually turned off, or is it just being put into the pool?" , another common question is "how can I tell if the connection I just opened is a new physical connection or is it a reuse of a connection that is placed in a pool?" ”。

There are a number of tools to help answer questions about connection pooling. Some of these tools are more outstanding. I regularly use SQL Server Profiler to monitor connections and queries to SQL Server databases. In version 2.0 of Ado.net, you can also use Performance Monitor in Windows.

SQL Client in Ado.net 2.0. NET Data provider includes performance counters for connection pooling. Tools such as Performance Monitor can now be used to view the following numbers: Joins in the pool, active connections, free connections, active and inactive connection pools, and active and inactive connection pool groups. You can also gather information about the number of connections and disconnects per second.

In some cases, maintaining performance counters can have some performance impact. To do this, SQL Client. NET data provider does not maintain the following performance counters: The number of active or free connections, or the number of connections placed in the pool per second or disconnected. You can enable these performance counters in your application by adding an entry to the application's configuration file. For more information about using these performance counters, see the article "Using Ado.net performance Counters" on the MSDN Web site (using the Ado.net performance counters).

To make it easier for you to raise questions about connection pooling, and to help me answer these questions, I have developed a sample application that can be downloaded as part of the sample code in this book, as shown in Figure 3.5. This application allows the connection string to be generated using the Sqlconnectionstringbuilder/propertygrid dialog box shown in Figure 3.3. You can easily generate new SqlConnection, open and close existing connections, and invoke Clearpool and Clearallpools methods. This example also accesses the SQL Client performance counters through Performance Monitor without the need to manually add performance counters. The configuration file for this application contains an entry that enables performance counters that are closed by default. The performance counters in this example are updated each time the SqlConnection is created, turned on or off, or when one or all of the connection pools are closed.

Figure 3.5 Research Connection Pool 3.6.8 ADO. NET how to determine whether to use a connection into a pool

Simply put, the SQL Client assumes that the connection pool is not disabled. NET Data Provider checks connectionstring when you call the SqlConnection object's Open method, and determines whether an available connection exists in the pool. If there are available connections, SQL client uses the connection. Otherwise, open a new connection to the database.

There are actually a few things to explain. Imagine a asp.net application in which multiple users log on to the same database with impersonation (impersonation), and each user accesses the SQL Server database using his or her credentials. Each user's connection string is the same, but their credentials vary greatly. Because SQL client considers user rights, the logic used to determine which connections are available in the pool is slightly more complex. 3.6.9 force Ado.net to use a new pool

At some point, you may no longer want to extract the connection from the old connection pool, but you want to create a new pool. In this case, the goal is to modify the connection string in a way that has an impact on the pool cache (pooling) without affecting the rest of the application. The easiest way to reach this goal is to add a space at the end of the connection string. 3.6.10 manually free cached connections in the pool

The previous tip is handy for the Ado.net 1.x version because the API's features do not help to free the cached connections in the pool. In Ado.net 2.0, there are two new static methods in the SqlConnection class that can provide help--clearpool and Clearallpools.

The Clearpool method obtains a SqlConnection object and frees all connections that are cached in a connection pool, which is associated with the SqlConnection object. Assume that there are 10 SqlConnection objects that use the same connection string and credentials, and that connection pooling is enabled. You can call the Open method of all 10 objects, and then call the Close method of three of them.

There are 10 open connections to the SQL Server database. The 7 connections in these connections are associated with 7 open SqlConnection objects. To use the terminology used by performance counters, we say that these 7 connections are "active." The remaining three connections exist in the connection pool. Once again, using the terms of performance counters, they are "free" connections. Calling the Clearpool method frees the three free connections that exist in the connection pool, but does not affect the active connections that are used by 7 open SqlConnection objects.

Clearallpools method has no parameters, it clears all free SqlConnection. 3.6.11 Other connection pooling options

Now take a quick look at the other common connection pooling options. Each option is available through sqlconnectionstringbuilder and connection strings. 1. Connection Reset

If you look closely at the trajectory of a SQL Profiler, you may notice that a stored procedure named Sp_reset_connection is called, and you might want to know when the stored procedure was invoked and why it was invoked.

Simply reusing SqlConnection that are cached in the pool can produce unexpected results. Because of the lack of a more appropriate term, it can be said that there is a "remnant" associated with a cached (pooled) connection. Developers do not always clean up the clutter when closing the connection to return the connection to its original state. When you call the Close method, you may already have an open cursor or transaction associated with the connection, or even worse. If a query such as "Use AdventureWorks" is issued, the connection may be associated to a different database when the database is opened. If you specify an application role for a connection by calling Sq_setapprol, those permissions are still applied to the connection until the Sp_unsetapprole is invoked, or until the sp_reset_connection is invoked, or until the connection is actually closed ( Rather than just being put into the pool).

SQL Client. NET data provider tracks which SqlConnection use connections extracted from the connection pool. When the open method is called, SqlClient does not invoke the sp_reset_connection stored procedure, but executes the query just before the first operation of the connection.

You can add connection Reset=false to the connection string if you are absolutely certain that no "remnants" are left on the server for the cached connections in the pool;. This setting tells SqlClient that you do not need to invoke the sp_reset_connection stored procedure when you reuse a connection that is cached in the pool. However, I recommend that you do not set the connection reset to False. 2. Min Pool Size

As you can see from its name, the min pool size controls the minimum number of connections in the pool. By default, this property is set to 0.

The Min Pool Size property can help you prepare a connection pool. Suppose you set the property to 5. After opening the first connection, SqlClient opens another 4 connections in a background thread. There are always at least 5 connections in the pool. Suppose the code creates 10 SqlConnection objects and opens them all. As before, when you open the first SqlConnection, SqlClient will open the other 4 connections in the background thread. The next 4 SqlConnection objects will use the remaining connections from the pool. The remaining 5 SqlConnection objects will establish a new connection.

Now, suppose you close the 8 objects in these 10 SqlConnection objects. All 8 connections will remain alive in the pool. When SqlClient clears this connection pool (assuming approximately 5 minutes after the SqlConnection is closed and is not reused), it ensures that at least 5 connections (Min Pool size setting) still exist in the connection pool. This number includes connections currently in use and both connections are still in use. So there will be three other connections stored in the connection pool. The remaining 5 connections will be discarded.

The main disadvantage of the Min pool size is that there will be at least a set number of connection retention activities in the pool. In asp.net applications, the presence of these connections may be too long. Therefore, it is a good idea to keep the min Pool size zero. 3. Max Pool Size

The Max Pool size setting is slightly simpler to understand. This setting is used as a limiting feature to prevent the number of connections opened in a single pool from exceeding the specified value. The default value for Max Pool size is 100. If the maximum number of connections in the connection pool is reached, a invalidoperationexception is raised after the time specified by the Connect Timerout setting after the next attempt to open a connection, stating that "The timeout period has expired." The timeout has expired before getting a connection from the pool. This may occur because all pool connections have been used and the maximum pool size has been reached.