Database development-Database Data Base connection simple pool function inspiration

Source: Internet
Author: User
In development Community A problem that must be taken into account when a website is a concurrent request by a network user. In addition to the common means of adding cache and static html, it is also very important to optimize the tables relationship of the database and SQL statements, however, pages of users' concurrent requests often lead to SQL connection time out errors in queries of some complex services. The connection pool of the database is used up for users' concurrent requests. What should I do, it is not easy to solve the problem from the root. There are many aspects and related technologies involved, and there are also problems with the database structure. Code But if the above problems occur, there will certainly be a "red screen", which is a big ban for the system, this kind of user experience is too bad for users. Professional Developers certainly think that this website technology is not doing well, to solve this problem, the direct starting point is to manage the database connection pool and control the size of the database connection pool in the Code. From time to time, the user directly requests the database connection pool, adds a layer to the intermediate business logic to manage database connections.
The basic idea is to use generics as a data dictionary to maintain the sqlconnection set.
Basic Process:
When a user requests sqlconnection, the available sqlconnection connection pool is first retrieved and directly returned to the user, and the currently in use sqlconnection is set to locked unavailable. If no sqlconnection is available in the pool, a new sqlconnection is directly generated.
After the user has used up the sqlconnection, put it back to the pool for the next medium request.
Similarly, a thread is used to track the status of available connection strings in the pool, if its waiting request time exceeds a time period (from its creation time to its unused interval of one minute without receiving the user's request), execute the close method, let him recycle it to the database connection pool.
Note that the close method of sqlconnection in ado.net does not close the connection physically. You can use sp_who to check whether your request still exists, but its format is like sleeping, only when there is no user request, the resources will automatically disappear after the expiration time is exceeded. Of course, you can also release an unmanaged sqlconnection resource in C # To notify GC to recycle the resource.
Dataaccess. objectpool. objectpool is a dictionary implemented using generics.

The basic code is as follows:
Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data;
Using system. Data. sqlclient;
Namespace dataaccess. dbconn
{
/// <Summary>
/// Database connection object pool
/// </Summary>
Public class dbconn: dataaccess. objectpool. objectpool <sqlconnection>
{
/// <Summary>
/// Private constructor
/// </Summary>
Private dbconn ()
{

}
/// <Summary>
/// Return the singleton object)
/// </Summary>
Public static readonly dbconn instance = new dbconn ();

/// <Summary>
/// Default connection statement
/// </Summary>
// Public static string dbpoolconnstr = dataaccess. basedao. conn_string_non_dtc;
Private string dbpoolconnstr
{
Get
{
Return dataaccess. basedao. conn_string_non_dtc;
}
}
Private Static int _ poolmaxcount = 10;
/// <Summary>
/// Gets or sets the pool Max count.
/// </Summary>
/// <Value> the pool Max count. </value>
Public new int poolmaxcount
{
Get
{
Return _ poolmaxcount;
}
Set
{
Base. poolmaxcount = _ poolmaxcount;
_ Poolmaxcount = value;
}
}
/// <Summary>
/// Create a database connection object
/// </Summary>
/// <Returns> opened database connection object </returns>
Protected override sqlconnection create ()
{
Sqlconnection conn = new sqlconnection (dbpoolconnstr );
Conn. open ();
Return conn;
}

/// <Summary>
/// Verify the connection status
/// </Summary>
/// <Param name = "T"> database connection object </param>
/// <Returns> connection status: open: true close: false </returns>
Protected override bool validate (sqlconnection T)
{
Try
{
Return! (T. state. Equals (connectionstate. Closed ));
}
Catch (sqlexception)
{
Return false;
}
}

/// <Summary>
/// Close the database connection
/// </Summary>
/// <Param name = "T"> database connection object </param>
Protected override void expire (sqlconnection T)
{
Try
{
If (T. State = connectionstate. open)
{
T. Close ();
}
}
Catch (sqlexception)
{

}
}

/// <Summary>
/// Obtain the database connection from the pool
/// </Summary>
/// <Returns> database connection object </returns>
Public sqlconnection borrowdbconn ()
{
Try
{
Return base. getobjectfrompool ();
}
Catch (exception ex)
{
Throw ex;
}
}

/// <Summary>
/// Put the connection object in the pool
/// </Summary>
/// <Param name = "conn"> database connection object </param>
Public void returndbconn (sqlconnection conn)
{
Base. returnobjecttopool (conn );
}
}
}

Compassionate Leung because of understanding

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.