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

This article will explain in detail how to use the FAQ connection pool in ADO.NET. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

ADO.NET has a lot to learn. Here we mainly introduce ADO.NET FAQ connection pooling, including the solution to connection pooling. Problem, a CS architecture program, directly regards SQL Server as the server, and each client connects to the database directly. If the number of SQL Server connections opened by the client is too large, the number of connections to the database will be particularly high, and the database side will form a performance bottleneck. What to do in this case? After thinking about it, the reason for this is the internal mechanism of ADO.NET. 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 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.

ADO.NET FAQ connection pool

1. When is the ADO.NET FAQ connection pool created?

Create a connection pool when * connection requests arrive; the establishment of the connection pool is determined by the connection character creation of the database connection. Each connection pool is associated with a different connection string. When a new connection request arrives, if the connection string is the same as the string used by the connection pool, a connection is removed from the connection pool; if it is different, a new connection pool is created.

two。 When do I turn off connection pooling?

Close connection pooling when all connections in the connection pool are closed.

3. What happens when all the connections in the connection pool are used up and a new connection request arrives?

When the connection pool has reached its number of connections, when new connection requests arrive, new connection requests will be placed in the connection queue. When a connection is released to the connection pool, the connection pool allocates the newly released connection to the connection request queued. You can call close and dispose to return the connection to the connection pool.

4. How should I allow connection pooling?

For .NET applications, connection pooling is allowed by default. (this means you don't have to do anything about it.) of course, if you can add Pooling=true; to the connection string of the SQLConnection object, make sure your application allows the use of connection pooling.

5. How should I prohibit connection pooling?

ADO.NET allows database connection pooling by default. If you want to disable connection pooling, you can use the following methods:

1) when using the SQLConnection object, add the following to the connection string: Pooling=False

2) when using the OLEDBConnection object, add the following to the connection string: OLEDB Services=-4

This is the end of the article on "how to use FAQ connection Pool in ADO.NET". 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, please share it out 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.