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

This article is about the pros and cons of ADO.NET connection pooling. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Connection pooling is used in ADO.NET to improve performance, so that each request does not have to create a connection, then authenticate, and then execute SQL, but directly removes the connection from the connection pool and executes SQL. After execution, the connection is not actually closed, but put back into the connection pool. If there are 100 clients, and each client saves 10 connections in the connection pool after using it for a period of time, then in this case, even if nothing is done on the client, there are 1000 connections on the SQL Server, so it is not surprising that there are no performance problems.

Since it is a problem of ADO.NET connection pooling, there are two solutions to this problem:

1. Turn off connection pooling for ADO.NET, create a new connection each time SQL is executed, and then close. Doing so will slow down the data query (establish a connection every time, authenticate every time, of course), but this slow is millisecond and generally imperceptible, but it may be felt in detail if hundreds of SQL statements are involved in an operation. The method of modification is very simple, you don't have to modify the code, just add Pooling=False; to the database link string.

two。 Modify the architecture, this CS architecture in addition to performance problems, such as security problems. You can change the method of directly connecting to the database to connecting to the service, in which you can use Remoting, Web services, and so on, of course, you can now use WCF. In this way, only the server connects to the database, while the client only connects to the server, so that there is no bottleneck caused by connection pooling. However, there are a lot of code changes in this way, and it is still very painful to change it.

Introduce ADO.NET connection pooling

Connection pooling allows an application to obtain a connection from a connection pool and use that connection without having to establish a new connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse the connection without having to implement the entire database connection creation process.

When an application requests a connection, the connection pool allocates a connection to the application instead of re-establishing a connection; when the application finishes using the connection, the connection is returned to the connection pool rather than released directly.

How to realize ADO.NET connection Pool

Make sure you use the same connection string each time (the same as the connection pool); the connection pool works only if the connection string is the same. If the connection string is different, the application does not use the connection pool but creates a new connection.

Advantages

The main advantage of using connection pooling is performance. The time it takes to create a new database connection depends largely on the speed of the network and the distance between the application and the database server, and this process is usually a time-consuming process. After using the database connection pool, the database connection request can be satisfied directly through the connection pool without the need to reconnect and authenticate to the database server for the request, which saves time.

Shortcoming

There may be multiple unused connections to the database in the database connection pool (which means a waste of resources).

Tips and tips

1. Create a connection pool when you need a database connection, not in advance. Close the connection as soon as you finish using it, and don't wait for the garbage collector to deal with it.

two。 Ensure that all user-defined transactions are closed before closing the database connection.

3. Do not close all connections in the database and ensure that at least one connection is available in the connection pool. If memory and other resources are your first consideration, you can close all connections and create a connection pool when the next request arrives.

Console.WriteLine ("ReturnValue: {0}", rowCount.ToString ()) Console.WriteLine ("All Rows:") Dim row As DataRow For Each row In categories.Rows Console.WriteLine ("{0}: {1}", row (0), row (1)) Next End Using End Sub thank you for reading! This is the end of this article on "what are the advantages and disadvantages of ADO.NET connection pool?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.