Reason: Many users may be querying the same database to get the same data. In these cases, the performance of the application can be improved by having the application share the connection to the data source. Otherwise, the overhead of having each user open and close a separate connection adversely affects application performance. This way there is a connection pool.
accomplish:
C# Setting up connection pool in connection string
If you are using the OleDbConnection, OdbcConnection, or OracleConnection classes, the connection pool will be handled automatically by the provider, so you don't have to manage it yourself.
If you are using the SqlConnection class, the connection pool is implicitly managed, but there are options that allow you to manage the pool yourself.
Connection Use the Open() method to open the connection, and the connection pool will initialize and establish the set minimum number of connections. Be sure to close the connection when using the connection so that the connection can return to the pool. To close the connection use Close()
When the number of connections is full and the time to apply for connection exceeds the time to wait for the connection, use the "asynchronous process" to perform asynchronous operations on the database to ensure that the connection can call the Close method in time to close the connection, which can greatly reduce the number of connections being used.
When database operations and access are frequent, reduce the time it takes to create and open connections, and improve the performance of the database server. Here we will analyze the C# database connection pool in detail.
Connection pool using C# database
Connecting to a database server usually consists of several steps that require a long time of softness. A physical channel (such as a socket or named pipe) must be established, a first connection must be made to the server, the connection string information must be analyzed, the connection must be authenticated by the server, and so on.
In fact, most applications use one or several different connection configurations. When the application has a large amount of data and access, this means that many of the same connections will be repeatedly opened and closed during the process of running the application, which will cause inefficiency of the database server and even cause program crashes. To ensure the stability of the application and reduce performance costs, we can use an optimization method called connection pooling to manage and maintain connections.
C# database connection pool can reduce the number of connections created. Define the minimum number of connections (fixed number of connections). When the user calls Open on the connection, the connection pool checks whether there are available connections in the pool. If a connection is found to be available, the connection is returned to the caller instead of creating a new connection. When the application calls Close on the connection, the connection pool will determine whether the connection is within the minimum number of connections. If "Yes", the connection will be reclaimed into the active connection pool instead of actually closing the connection, otherwise the connection will be burned. Once the connection is returned to the pool, it can be reused in the next Open call.
Create a C# database connection pool
The following example uses C# to connect to a SQL database:
class DbConn
{
//using ;
//using ;
private const int MaxPool = 10; //Maximum number of connections private const int MinPool = 5; //Minimum number of connections private const bool Asyn_Process = true; //Set asynchronous access to the database private const bool Mars = true; //Get and manage multiple, forward reference only and read-only result sets on a single connection (ADO.NET2.0) private const int Conn_Timeout = 15; //Set connection waiting time private const int Conn_Lifetime = 15; //Set the life cycle of the connection private string ConnString = ""; //Connection string private SqlConnection SqlDrConn = null; //Connect the object
public DbConn()//Constructor {
ConnString = GetConnString();
SqlDrConn = new SqlConnection(ConnString);
}
private string GetConnString()
{
return "server=localhost;"
+ "integrated security=sspi;"
+ "database=pubs;"
+ "Max Pool Size=" + MaxPool + ";"
+ "Min Pool Size=" + MinPool + ";"
+ "Connect Timeout=" + Conn_Timeout + ";"
+ "Connection Lifetime=" + Conn_Lifetime + ";"
+"Asynchronous Processing=" + Asyn_Process + ";";
//+ "MultipleActiveResultSets=" + Mars + ";";
}
public DataTable GetDataReader(string StrSql)//Data query {
//Close when the connection is open and then open again to avoid the data being unable to be updated in time sometimes if ( == )
{
();
}
try
{
();
SqlCommand SqlCmd = new SqlCommand(StrSql, SqlDrConn);
SqlDataReader SqlDr = ();
if ()
{
DataTable dt = new DataTable();
//Read the content in SqlDataReader (SqlDr);
//Close the object and the connection ();
();
return dt;
}
return null;
}
catch (Exception ex)
{
();
return null;
}
finally
{
();
}
}
}
Open the connection by calling the () method, and the connection pool will initialize and establish the set minimum number of connections. If you want to understand the status of the connection pool more clearly, you can execute the stored procedure sp_Who through SQL's query analyzer. It will list the current database process and view loginname and dbname to distinguish the user's connection information. However, it should be noted that the login query analyzer itself uses two connections, so it is best to log in to the query analyzer with another user name. Another troublesome thing to use this method is to press "Execute Query" frequently to update process information. There is another method I personally think is better. Through the control panel → Management Tools → Performance, right-click to add a calculator, select SQlServer: GeneralStatistics (General Statistics) and then select UserConnections (User Connections) and finally press "Add" to view the current number of connections in real time.
At this point, the connection pool has been implemented, but the problem often occurs during operation. What should I do if the number of connections in the connection pool is full? Here we should reasonably set the ConnectTimeout property and ConnectionLifetime property in the connection string (explained above) to extend the waiting time, and call the Close method after each connection is used as much as possible to close the connection. But there are also things that cannot be avoided. When the number of connections is full and the time to apply for connection exceeds the time to wait for the connection, the program will raise an InvalidOperationExceptio exception. We can ease this situation by catching this exception and prompting the user interface with information such as "The system is busy, please connect later...". In addition, there is another way to solve this situation, which is to use the new ADO.NET 2.0 feature "asynchronous process" to perform asynchronous operations on the database to ensure that the connection can call the Close method in time to close the connection, which can greatly reduce the number of connections being used.
How to use: Add AsynchronousProcessing=true to the connection string to indicate the use of asynchronous processing operations.
When the application no longer needs to use the connection pool, you can use ClearPool or ClearAllPools method to clear the connection pool or reset the connection pool. The method is as follows:
(SqlConnectionconnection) Clear the associated connection pool
()Clear all connection pools
Call the above method. If the connection is in use, the connection pool will be marked accordingly and will automatically burn when the connection is closed.
Summary of C# database connection pool
Advantages: When database operations and access are frequent, it reduces the time it takes to create and open connections, and improves the performance of the database server.
Disadvantages: There may be multiple unused connections in the database connection pool that are connected to the database all the time, which means a waste of resources.
The above is the detailed content of the settings and use of C# connection pool. For more information about C# connection pool, please pay attention to my other related articles!