Sqlconnection. Open why is there no exception when the database is disconnected

Source: Internet
Author: User
Tags knowledge base

The problem encountered over the past few days is that sqlconnection. when the database is disconnected (such as stopping SQL Service), open returns normally without errors. After research, it is found that ado.net adopts the connection pool technology by default. When the connection is closed, sqlconnection. open can still return conn from the pool, but it is no longer valid, but only when you use it will you know it is invalid, sqlconnection. open will check whether there is a corresponding connection in the connection pool. If yes, it will be taken out and not created. When the database is disconnected, it will call sqlconnection in the program. open will be successful because there is still a corresponding connection in the connection pool, but it is already invalid, so there will be exceptions during use, such as calling dataadapter. the fill method is described as follows:

 

SQL Server best practices article

 

SQL Server Connection Pooling (ADO. Net)
Http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

 

Using Connection pooling with SQL Server Visual Studio 2005

Other Versions

  • . NET framework 4

  • Visual Studio 2008

  • . NET Framework 1.1

 

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different deployments for connections. this means that during application execution, incluidentical connections will be repeatedly opened and closed. to minimize the cost of opening connections, ADO. net uses an optimization technique calledConnection Pooling

.

Connection Pooling CES the number of times that new connections need to be opened.Pooler

Maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user CILSOpen

On a connection, the pooler looks to see if there is an available connection in the pool. if a pooled connection is available, it returns it to the caller instead of opening a new connection. when the application CILSClose

On the connection, the pooler returns it to the pooled set of active connections instead of actually closing it. Once the connection is returned to the pool, it is ready to be reused on the nextOpen

Call.

Only connections with the same configuration can be pooled. ADO. net keeps several pools concurrently, one for each configuration. connections are separated into pools by connection string, as well as by Windows identity when integrated security is used.

Pooling connections can significantly enhance the performance and scalability of your application. connection pooling is enabled by default in ADO. net. unless you explicitly disable it, The pooler will optimize the connections as they are opened and closed in your application. you can also supply several connection string modifiers to control Connection Pooling behavior. for more information, see "controlling Connection pooling with connection string keywords" later in this topic.

Pool creation and assignment

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. each connection pool is associated with a distinct connection string. when a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. connections are pooled per process, per application domain, per connection string and when using integrated security, per windows identity.

In the following C # example, three new sqlconnection

Objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assignedInitial catalog

.

Copy

using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// Pool A is created.
}

using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=pubs"))
{
connection.Open();
// Pool B is created because the connection strings differ.
}

using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// The connection string matches pool A.
}

IfMinpoolsize

Is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specifiedMinpoolsize

Is greater than zero, the connection pool is not destroyed untilAppdomain

Is unloaded and the process ends. Maintenance of inactive or empty pools Involves minimal system overhead.

Note

The pool is automatically cleared when a fatal error occurs, such as a Failover or an alias change in the registry.

Adding connections

A connection pool is created for each unique connection string. when a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. connections are added to the pool as needed, up to the maximum pool size specified (100 is the default ). connections are released back into the pool when they are closed or disposed.

WhenSqlconnection

Object is requested, it is obtained from the pool if a usable connection is available. to be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. if the maximum pool size has been reached and no usable connection is available, the request is queued. the pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds ). if the pooler cannot satisfy the request before the connection times out, an exception is thrown.

Caution

We recommend that you always close the connection when you are finished using it in order for the connection to be returned to the pool. You can do this using eitherClose

OrDispose

Methods ofConnection

Object, or by opening all connections inside ofUsing

Statement in C #, orUsing

Statement in Visual Basic. connections that are not explicitly closed might not be added or returned to the pool. for example, a connection that has gone out of scope but that has not been explicitly closed is only returned to the connection pool if the maximum pool size has been reached and the connection is still valid. for more information, see using Statement (C # reference)

Or how to: dispose of a system resource

For Visual Basic.

Note

Do not callClose

OrDispose

OnConnection

,Datareader

, Or any other managed object inFinalize

Method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not includeFinalize

Method in your class definition. For more information, see garbage collection

.

Removing connections

The connection pooler periodically scans connection pools looking for unused connections that were not closedClose

OrDispose

, And reclaims those it finds. if your application does not explicitly close or dispose of its connections, it can take quite a while for the connection pooler to reclaim them, so it is best to ensure that you explicitly callClose

AndDispose

In your connections.

The connection pooler removes a connection from the pool after it has been idle for an extended period of time, or if the pooler detects that the connection with the server has been severed. note that a severed connection can be detected only after attempting to communicate with the server. if a connection is found that is no longer connected to the server, it is marked as invalid. invalid connections are removed from the connection pool only when they are closed or reclaimed.

If a connection exists to a server that has disappeared, it is possible for this connection to be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. this is the case because the overhead of checking that the connection is still valid wocould eliminate the benefits of having a pooler by causing another round trip to the server to occur. when this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.

Clearing the pool

Ado. NET 2.0 introduces two new methods to clear the pool: clearallpools

And clearpool

.Clearallpools

Clears the connection pools for a given provider, andClearpool

Clears the connection pool that is associated with a specific connection. if there are connections in use at the time of the call, they are marked appropriately. when they are closed, they are discarded instead of being returned to the pool.

Transaction support

Connections are drawn from the pool and assigned based on transaction context. UnlessEnlist = false

Is specified in the connection string, the connection pool ensures that the connection is enlisted in the current

Context. When a connection is closed and returned to the pool with an enlistedSystem. Transactions

Transaction, it is set aside in such a way that the next request for that connection pool with the sameSystem. Transactions

Transaction will return the same connection. If there is no connection available for that transaction, the connection is automatically enlisted when it is opened.

When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. this allows you to commit or abort the distributed transaction at a later time.

Controlling Connection pooling with connection string keywords

TheConnectionstring

Property ofSqlconnection

Object supports connection string key/value pairs that can be used to adjust the behavior of the Connection Pooling logic. For more information, see connectionstring

.

Pool fragmentation

Pool fragmentation is a common problem in your web applications where the application can create a large number of pools that are not freed until the process exits. this leaves a large number of connections open while consuming memory, resulting in poor performance.

Pool Fragmentation due to integrated security

Connections are pooled according to the connection string plus the user identity. therefore, if you use basic authentication or Windows authentication on the web site and an integrated security login, you get one pool per user. although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. it also results in at least one connection per user to the database server. this is a side effect of a particle web application architecture that developers need to weigh against security and auditing requirements.

Pool Fragmentation due to replicate Databases

Using Internet Service Providers host several web sites on a single server. they may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. the connection to the Authentication database gets pooled and used by everyone. however, there is a separate pool of connections to each database, thus increasing the number of connections to the server.

This is also a side effect of the application design. there is a relatively simple way to avoid this side effect, however, without compromising security when connecting to SQL Server. instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the transact-SQL use statement to change to the desired database. the following code fragment demonstrates creating an initial connection toMaster

Database and then switching to the desired database specified inDatabasename

String variable.

// Assumes that command is a sqlcommand object.

Using

(Sqlconnection connection = new

Sqlconnection (

"Server = mssql1; uid = xxx; Pwd = xxx; database = Master"

))

{

Connection. open ();

Command. executenonquery ("use"

+ Databasename );

} Application roles and Connection Pooling

After a SQL Server application role has been activated by callingSp_setapprole

System stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused.

If you are using SQL Server application roles, we recommend that you disable Connection pooling for your application in the connection string. for more information, see the Knowledge Base Article, "SQL application role errors with OLE DB resource pooling

."

 

 

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.