C # and Oracle database connection pooling

C # and Oracle database connection pooling

In the middle of a project, use WebService and Oracle database. I did the user authentication on the server, that is, using the session to pass the user's login information. In the test, when the user is less, no problem, but when a large number of users simultaneously access, the error, Initially thought it was their own Oracle connection part of the problem, several confirmed, finally found that the connection pool problem.

The following is from other people's blog excerpt, Dare not act UP, included as follows:

"Connections establish pooled connections based on connection strings and user identities. Therefore, if you use Basic authentication or Windows authentication on the Web site and an integrated secure login, each user will get a pool. Although this can improve the performance of subsequent database requests for a single user, the user cannot take advantage of connections established by other users. This also enables each user to produce at least one connection to the database server. This has a side effect on the structure of a particular WEB application because developers need to measure security and audit requirements.
"

This may be the problem.

Oracle connection Pooling

The original: Many users may be querying the same database to get the same data. In these cases, you can improve the performance of your application by sharing your application's connections to the data source. Otherwise, the overhead of having each user open and close a separate connection can adversely affect application performance. This will have a connection pool.

Realize:

C # Sets the connection pool in the connection string.

If you are using the OleDbConnection, OdbcConnection, or OracleConnection classes, the connection pool will be handled automatically by the provider, so you do not have to manage it yourself.

If you are using the SqlConnection class, the connection pool is implicitly managed, but also provides the option to allow you to manage the pool yourself.

The connection opens the connection using the open () method, at which point the connection pool initializes and establishes the minimum number of connections set. Be sure to close the connection when you are finished using it so that the connection can return to the pool. To close the connection using close ()

When the number of connections is full and the time to request a connection exceeds the time to set the connection wait, asynchronous processes are used to asynchronously manipulate the database to ensure that the connection is able to call the Close method to close the connection in a timely manner, which can significantly reduce the number of connections being used.

Improves database server performance when database operations and access are frequent, reducing the time it takes to create connections and open connections. The C # database Connection pool is analyzed in detail here.

Using a C # database Connection Pool

Connecting to a database server typically consists of several steps that require a long, soft time. A physical channel, such as a socket or named pipe, must be established and must be first connected to the server, the connection string information must be parsed, the connection must be authenticated by the server, and so on.

In fact, most applications use one or several different connection configurations. When the amount of data and access to the application is large, this means that many of the same connections will be opened and closed repeatedly while the application is running, causing the database server to become inefficient or even cause the program to crash. To ensure application stability and reduce performance costs, we can use an optimization method called connection Pooling in ADO to manage maintenance connections.

A C # database Connection pool can reduce the number of times a connection is created. Defines the minimum number of connections (number of fixed connections), and when the user calls open on the connection, the connection pool checks to see if the connection is available 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 an application calls close on the connection, the connection pool Determines whether the connection is within the minimum number of connections, and if "yes" will recycle the connection into the active connection pool instead of actually shutting down the connection, the connection will burn down. 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 System.Data;
Using System.Data.SqlClient;
Private Const int maxpool = 10; Maximum number of connections
Private Const int minpool = 5; Minimum number of connections
Private Const BOOL Asyn_process = TRUE; Setting up an asynchronous Access database
Private Const BOOL Mars = TRUE; Get and manage multiple, forward-only, and read-only result sets on a single connection (ado.net2.0)
Private Const int conn_timeout = 15; Setting the connection Wait time
Private Const int conn_lifetime = 15; Set the life cycle of a connection
private string connstring = ""; Connection string
Private SqlConnection sqldrconn = null; Connection 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 it to avoid data not being updated in time
if (sqldrconn.state = = ConnectionState.Open)
{
Sqldrconn.close ();
}
Try
{
Sqldrconn.open ();
SqlCommand SQLCMD = new SqlCommand (strSQL, sqldrconn);
SqlDataReader Sqldr = Sqlcmd.executereader ();
if (sqldr.hasrows)
{
DataTable dt = new DataTable ();
Read the content in the SqlDataReader
Dt. Load (SQLDR);
Close objects and connections
Sqldr.close ();
Sqldrconn.close ();
return DT;
}
return null;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show (ex. Message);
return null;
}
Finally
{
Sqldrconn.close ();
}
}
}

By calling the Sqldrconn.open () method to open the connection, the connection pool is initialized and the minimum number of connections set is established. To better understand the status of the connection pool , you can execute the stored procedure sp_who through SQL's Query Analyzer, which lists the current database processes, LoginName, dbname can differentiate the user's connection information, but Note that the Login Query Analyzer itself uses two connections, so it is a good idea to log in to Query Analyzer with another user name. Another trouble with this approach is that you often press execute query to update process information. There is another way to personally think better, through the control Panel → management tools → performance, right-click Add Calculator, performance object selection Sqlserver:generalstatistics (general statistics) then calculator select userconnections (user connection) and finally press " Add to see the current number of connections in real time.

Here, the connection pool has been implemented, but the problem tends to occur during the run. How do I handle a connection pool with a full number of connections? Here we should reasonably set the ConnectTimeout property in the connection string and the Connectionlifetime property (explained above) to extend the wait time and, whenever possible, call the Close method to close the connection after each use of the connection. But there are also unavoidable, when the connection is full and the time to request a connection exceeds the time to set the connection wait, the program will throw a Invalidoperationexceptio exception, we can catch this exception to the user interface prompt "System is busy, please connect later ..." Information to mitigate this situation. In addition, there is another way to solve this situation, is to use the ado.net2.0 new feature "Asynchronous process", the database asynchronous operation, to ensure that the connection can call the Close method to close the connection, which can greatly reduce the number of connections in use.

How to use: adding Asynchronousprocessing=true to the connection string means using asynchronous processing operations.

When the application no longer needs to use the connection pool , you can use the Clearpool or Clearallpools method to clear the connection pool can also be used to reset the connection pool use, as follows:

Sqlconnection.clearpool (sqlconnectionconnection) empties the associated connection pool

Sqlconnection.clearallpools () empty all connection pools

Call the above method, if the connection is in use, the connection pool will be marked, and so on when the connection is closed automatically burned.

Summary C # database Connection Pool

Pros: Reduce the time it takes to create connections and open connections, and improve database server performance when database operations and access are frequent.

Disadvantage: There may be multiple connections in the database connection pool that are not connected to the database, which means a waste of resources.

C # and Oracle database connection pooling