catalogue

• 1, Connection pool knowledge background
• 2, Empty. NET connection pool

1, Connection pool knowledge background

In our program, connecting to the database is a time-consuming behavior. In order to reduce the cost of opening the connection, ado.net uses an optimization technology called connection pool. Using database connection pool can reduce the number of new connections, and leave the connection of physical database to the pool program. The pool program manages database connections by maintaining a set of active connection objects for each specific connection configuration. Whenever an application initiates a request to connect to the database, the pool program will look for available connections in the connection pool, and if so, return them to the caller. When the application closes the connection object, the pool program returns the connection object to the pool, which can be reused the next time it initiates a connection to the database. So how does. NET form a database connection pool? First, only the same connection configuration can be pooled. NET maintains different connection pools for different configurations. The same configuration mentioned here must have the same process, the same connection string and the same key order of the connection string. The number of connections available in the connection pool is determined by Max Pool Size in the connection string. For example, in an application, the codes related to database connection are as follows:

using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test1"))  
{  
	connection.Open();
} 
using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test2"))  
{  
	connection.Open();
}  
  
using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test1"))  
{  
	connection.Open();
}  

In the above code, although three Connection objects are created, only two database Connection pools are formed. When will the connections in the Connection pool be removed? The answer is that the connections in the Connection pool will be removed after being idle for 4-8 minutes, or when the application process closes, the connections in the Connection pool will also be removed.

2, Empty. NET connection pool

Earlier, we briefly talked about the connection pool. Now let's see how to empty the database connection pool. ClearAllPools and ClearPool static methods are provided in. NET to empty the connection pool. ClearAllPools means clearing all database connection pools related to the specified DBProvider, and ClearPool(DBConnection conn) means clearing connection pools related to the specified connection object. In general, we often use the ClearPool(DBConnection conn) method. Let's use the ClearPool method to demonstrate how to empty the database connection pool:

public class DBHelper
{
    public string Get()
    {
        var s = "User ID=root;Password=1qazxsw2;DataBase=test;Server=127.0.0.1;Port=6987;Min Pool Size=5;Max Pool Size=50;CharSet=utf8;";
        using (var conn = new MySqlConnection(s))
        {
            var comm = conn.CreateCommand();
            comm.CommandText = "select count(*) from usertest;";
            conn.Open();
            var ret = comm.ExecuteScalar();
            comm.CommandText = "select count(*) from TestTable;";
            var len = comm.ExecuteScalar();
            return $"The query result is:{ret} ,The number of connection objects in the connection pool is: {len}";
        };
    }
    public string CP()
    {
        var s = "User ID=root;Password=1qazxsw2;DataBase=test;Server=127.0.0.1;Port=6987;Min Pool Size=5;Max Pool Size=50;CharSet=utf8;";
        using (var conn = new MySqlConnection(s))
        {
            conn.Open();
            MySqlConnection.ClearPool(conn);
        };
        using (var conn = new MySqlConnection(s))
        {
            conn.Open();
            var comm = conn.CreateCommand();
            comm.CommandText = "select count(*) from TestTable;";
            var len = comm.ExecuteScalar();
            return $"The connection pool has been emptied. There are currently query connection pool objects {ken}  individual";
        }
    }
}

We call the above code in the main function and query mysql to find that the data in the database connection pool is the same as the result executed by our code.