Shulou(Shulou.com)06/02 Report--

This article mainly explains "ADO. NET connection pool is what", the explanation content in the article is simple and clear, easy to learn and understand, please follow the small series of ideas slowly in-depth, together to study and learn "ADO. NET connection pool is what" bar!

Do you know what's most valuable in programming these days? Database connection, which is actually very resource-intensive. Let's talk about the connection pool here. Database connection pooling is the most important measure for more efficient use of database connections. It is critical to the performance of a large application system, especially Web applications. ADO. NET Data Provider helps us manage connection pools, so some people say that using connection pools is as easy as swimming in children's pools. But this is not to say that having a Data Provider programmer is all right; improper use of connection pools can cause your application to drown in the pool.

What is ADO. NET Connection Pool?

ADO. NET connection pooling is a mechanism provided by Data Provider that allows connections used by applications to be saved in connection pools without having to complete the entire process of making/closing connections each time. To understand connection pooling, first understand the relationship between SqlConnection.Open(), SqlConnection.Close(), and opening/closing a "physical connection" in the program.

The complete process for Data Provider to establish a connection when receiving a connection request is to establish a new connection (i.e., a "logical connection") in the connection pool, and then establish a "physical connection" corresponding to the "logical connection". The establishment of a "logical connection" must be accompanied by the establishment of a "physical connection." Data Provider The complete process of closing a connection is to first close the "physical connection" corresponding to the "logical connection" and then destroy the "logical connection." Destroying logical connections must be accompanied by closing physical connections. SqlConnection.Open() is to request a connection from the Data Provider. The Data Provider does not necessarily need to complete the complete process of establishing the connection, but may only need to take an available connection from the connection pool;SqlConnection.Close() is to request to close a connection. The Data Provider does not necessarily need to complete the complete process of closing the connection, but may only need to release the connection back to the connection pool.

Here is an example. The examples in this article use Console Application. We used the operating system's performance monitor to compare the number of "physical connections" to the database with connection pooling or not. Because the performance monitor collects data at least once every second, for the convenience of observing the effect, Open and Close in the code are connected to Sleep for one second.

SqlConnection con = new SqlConnection("server = .; database = northwind;pooling = false;trusted_connection = true"); for(int i = 0;i

< 10;i++) { try { con.Open(); System.Threading.Thread.Sleep(1000); } catch(Exception e){Console.WriteLine(e.Message);} finally { con.Close(); System.Threading.Thread.Sleep(1000); } } 首先，不使用ADO.NET连接池做测试。以上程序中pooing = false表示不使用连接池，程序使用同一个连接串Open & Close了10次连接，使用性能计数器观察SQL Server的"物理连接"数量。从下面的锯齿图可以看出每执行一次con.Open()，SQLServer的"物理连接"数量都增加一，而每执行一次con.Close()，SQLServer的"物理连接"数量都减少一。由于不使用连接池，每次Close连接的时候Data Provider需要把"逻辑连接"和"物理连接"都销毁了，每次Open连接的时候Data Provider需要 建立"逻辑连接"和"物理连接"，锯齿图因此而成。

Let's test it again with connection pooling enabled. Change the pooling parameter of the connection string to true and add Console.Read() to the for loop.

As can be seen from the figure below, the number of SQL Server "physical connections" remains 1 from the *** Open to the end of Console.Read(), and the number of SQL Server "physical connections" does not become 0 until the console application process is closed. Because connection pools are used, the Data Provider simply releases the logical connection back into the connection pool each time it closes the connection, leaving the physical connection open. Each time you Open a connection, the Data Provider simply takes a logical connection from the connection pool and uses its corresponding physical connection without creating a new physical connection.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.