The difference between Dispose () and close ()

Dispose, you have to create it again.

and close (), you can also open (),

And after Dispose, the object does not exist, it can not open ()

Dispose is for the object itself, and close is for connection to the database

Others are misleading ...

Here are the relevant points of knowledge:

1.SqlConnection conn = new SqlConnection (strconnection), if the original connection is closed, when a new connection needs to be opened, as long as the strconnection is the same as the previous one, The original object is retrieved from the connection pool. Close, the instance of SqlConnection is not released, it is simply closed and placed in another place, and is taken out again the next time it is needed. This avoids creating an example of an instance. Ha, but I don't think that's a big thing, just. NET Framework provides a management optimization mechanism (because the most time-consuming is open and close)

Connection pooling allows applications to obtain a connection from the connection pool and use this connection without having to reestablish a connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse the connection without having to implement the entire database connection creation process.

When an application requests a connection, the connection pool assigns a connection to the application instead of re-establishing a connection, and when the application finishes using the connection, the connection is returned to the connection pool instead of being released directly.

There may be multiple connections in the database connection pool that are not being used to connect to the database (which means a waste of resources).

The main advantage of using connection pooling is performance. The time it takes to create a new database connection depends largely on the speed of the network and the distance between the application and the database server (network), and this process is often a time-consuming process. With a database connection pool, the database connection request can be met directly through the connection pool without having to reconnect to the request and authenticate to the database server, saving time.

Each. NET data provider that is included in ADO can implement a connection pool.

Each connection pool is associated with a separate connection string and its transaction context. Each time a new connection is opened, the data provider attempts to match the specified connection string to the string of the connection pool. If the match fails, the data provider creates a new connection and joins it to the connection pool. After the connection pool is created, it will not be removed unless the process is finished. Some people think that this kind of processing can affect performance, but in fact, it does not require much overhead to maintain an inactive or empty connection pool.

After the connection pool is created, some connection objects are created and joined to the connection pool until the minimum number of connected objects is reached. Later, the system will create and join the connection object as needed until the maximum number of connection objects is reached. If the program requests a connection object when no idle connection objects are available, and the number of objects in the connection pool has reached the upper limit, the request is placed in the queue and is immediately removed once the connection is released back to the buffer pool.

Note: In the case of concurrent access, when a connection is in use and another process or thread is started, a new connection is created and placed in the connection pool. Only when the first call is closed and the second call is repeated, the connection in the connection pool is invoked directly if it is the same connection string.

When a connection is closed, the connection object is returned to the connection pool for reuse, but the actual database connection is not removed. If connection pooling is disabled, the actual database connection is also closed. It must be emphasized here that when the connection object is finished, it should be explicitly closed and returned to the connection pool, not relying on the garbage collector to release the connection.

Call the close or Dispose method to release the connection back to the connection pool. The connection object is removed from the connection pool only if the lifetime ends or a critical error occurs.

For. NET applications, the default is to allow connection pooling. (which means you don't have to do anything for this matter) of course, if you can add Pooling=true to the SqlConnection object's connection string, make sure your application allows connection pooling. Ado. NET defaults to allow database connection pooling, if you want to disallow 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: OLE DB services=-4;

Note: Before the minimum number of connection objects is reached, new connection objects are created continuously, and the maximum number of connections is actually the number of concurrent connections that are indicated.

======================

Static does not guarantee that you are still thread safe in multithreaded mode, but overall, the consumption of one more connection does not have any effect here, but it is possible to cause runtime exceptions. Set the SqlConnection to static, in general, with no effect.

In fact, in the connection pool, is already actually similar to the singleton maintenance mode, so, even if you are constantly new SqlConnection, will not cause any problems. It is important to note that maintaining a long-open sqlconnnection in WinForm is generally not a problem,

===================================

Ado. NET connection pooling and connection strings detailed description

What is Connection Pool?

Whenever the program needs to read and write to the database. Connection.Open () uses ConnectionString to connect to the database, the database establishes a connection to the program, and maintains the open state, after which the program can use T-SQL statements to query/update the database. When executed to Connection.close (), the database closes the current connection. Well, everything seems to be so methodical.

Of course, if my program needs to open and close the connection occasionally, such as ASP. NET or Web Service, for example, when HTTP request is sent to the server, we need to open connection and then use select* from Table Returns a datatable/dataset to the client/browser and then closes the current connection. That every time open/close Connection so frequently operation is a waste to determine the whole system.

The ADO team gives a better solution. Save the previous connection, and the next time you need to open the connection, the previous connection will be handed over to the second connection. This is connection Pool.

-Connection Pool How to work?

First, when a program executes Connection.Open (), ADO needs to determine whether this connection supports connection Pool (Pooling default is True), and if it is specified as false, ADO creates a connection to the database (in order to avoid confusion, all connections in the database are made by using the "connection" description) and then returned to the program. If specified as true,ado.net, a connection pool is created based on ConnectString and then populated with connection in the connection Pool (all. NET programs are connected, using " Connection "description). How many connection are populated is determined by the Min Pool Size (default = 0) property. For example, if you specify 5, ADO opens 5 connections to the SQL database at a time, then 4 Connection are saved in Connection pool, and 1 Connection are returned to the program.

When the program executes to Connection.close (). If pooling is true,ado.net, place the current connection to connection pool and maintain the connection to the database. The connection Lifetime (default 0) attribute is also judged, and 0 represents infinity if the connection exists more than connection Lifetime, ADO closes the connection connection to the database as appropriate, rather than re-saving it to the connection pool. (This setting focuses on the clustered SQL database, which is load-balanced to the ground). If pooling is specified as false, the connection to the database is dropped directly.

And then the next time Connection.Open () executes the place, ADO. NET will determine whether the new connectionstring is consistent with the connectionstring originally stored in the connection pool connection. (ADO will turn ConnectionString into a binary stream, so that is, the new connectionstring is stored in connection Pool connection connectionstring must be exactly the same, even if you add a space, or modify the connection Some property order in string will let ADO think this is a new connection, and create a new connection. So if you use the ground Userid,password authentication method, modified password will also lead to a connection, if the application is SQL to integrate authentication, you need to save two connection application is the same one). Then ADO needs to determine whether the current connection Pool can be applied to connection (not occupied by other programs), if there is no word, ADO need to judge ConnectionString set to Max Pool Size (default is 100), if all connection in connection pool do not reach Max Pool size,ado.net will connect to the database again, create a connection, and return connection to the program. If you have reached maxpoolsize,ado.net, you will not create any new connections again, but instead wait for the connection pool to be connection released by other programs. This wait time is limited by sqlconnection.connectiontimeout (the default is 15 seconds), which means that if the time exceeds 15 seconds, SqlConnection throws a timeout error (so sometimes if the Sqlconnection.open () method throws a time-out error, one possible reason is that the original connnection was not closed in time, and the corresponding case connection The pool number reached maxpoolsize. If a connection is available, the connection from the connection Pool is not returned directly to the program, and ADO needs to check the ConnectionString Connectionreset attribute ( Default is True) Whether it is necessary to reset the connection. This is because the original return from the program connection may have been modified, such as the use of Sqlconnection.changedatabase method to modify the current connection, At this point, the returned connection may not already be connected to the currently connection string initial the catalog database. Therefore it is necessary to reset the current connection. Of course, due to all the additional checks, the ADO Connection Pool will increase the overhead of the system.

-Connection Pool How to set it?

The only way to modify the connection Pool is to do so by setting the connection string.

Pooling (True)

When True, the connection are drawn from the appropriate pool, or if necessary, created and added to the appropriate pool.

This property represents whether a connection pool needs to be applied, the default is true, and the connection pool is not applied if False is specified.

Connection Lifetime (0)

When a connection was returned to the pool, its creation time was compared with the current time, and the connection was dest royed If, time span (in seconds) exceeds the value specified by Connection Lifetime. This was useful in clustered configurations to force load balancing between a running server and a server just brought Onli Ne.

A value of zero (0) would cause pooled connections to the maximum time-out.

This property represents a connection time, and if a connection is returned to ConnectionPool, the connection will not be put back to lifetime when it exceeds the connection connection time. When the next request is sent, ADO. NET creates a new connection.

This attribute focuses on the clustered SQL database and is used for load balancing.

Enlist (True)

When True, the pooler automatically enlists the connection in the current transaction context of the creation thread if a Transaction context exists.

Max Pool Size (100)

The maximum number of connections allowed in the pool.

Min Pool Size (0)

The minimum number of connections maintained in the pool.

Connectionreset (True)

Gets or sets a Boolean value that indicates whether the connection was reset when drawn from the connection pool.

The value of the Connectionreset property or True if No. value has been supplied.

The corresponds to the "Connection Reset" key within the Connection string.

When connection from connection Pool, in order to ensure that the new connection not because the previous

Incidentally, in the world of ADO 2.0, modify sqlConnectionString We can use the SqlConnectionStringBuilder class to complete

-Connection.dispose () vs Connection.close ()

It may be common for everyone to see that there are many documents on the network and the MSDN site recommends that everyone use the using (SqlConnection Cn=new SqlConnection ()) {} method to create connection, because when the. Net Framwork automatically executes the Connection.dispose () method, so you can ensure that the connetion is closed in a timely manner.

1) so timely call. Dispose () is really that heavy either, if an object goes beyond the living space, is it automatically cleaned up in. NET by the GC (garbage collector)?

This problem is actually due to GC, which is used in. NET GC, he is not as diligent as we are at work. The GC only cleans (cleans up the objects that don't apply) when the environment is very bad (not enough content to allocate). So for connection, even if the variable life cycle is exceeded, it may not have been killed by GC. The connection is still not returned to connection Pool.

So this leads to the next connection that might have connection pool without available to connection and new to the newly opened connection, unnecessarily wasting a lot of room for performance. So the ADO team repeatedly stressed the need to close the current connection in time. One of the best ways to do this is to use the Using{}block system to automatically call the Connection.dispose method at the exit of the {}, and Dispose will automatically execute the Close method to release the current connection.

2) What exactly did Dispose do? protected override void Dispose (bool disposing)

... {

if (disposing)

... {

This._userconnectionoptions = null;

This._poolgroup = null;

This. Close ();

}

This. Disposeme (disposing);

Base. Dispose (disposing);

}

In fact, the Connection.dispose method is to call a close method, so the two are equivalent. In other words, if you execute the Connection.close () method in a timely manner, there is no need to wrap the connection in a using () {}.

3) If using is required, then if the program structure causes me to not use the using () {} to wrap my connection, for example my connection is the same Help class return, then what should I do?

This is a frequently encountered problem. In this environment, we cannot wrap the entire connection in a connection.

There are two ways to fix this, and one is to modify your code structure. Pass in a connectionstring to return connection. Another way is to repeatedly check your code, whether to close the connection in time. Because the close effect is identical to dispose. Of course, if the use () {} is not used, the task of closing the connection in a timely manner is tantamount to being delivered to our own hands, instead of being stopped by the. NET Framework.

-When you say so much, when do we need to use connection pool?

Generally, this should be determined by your project needs.

If your project is Asp.net/webservice we would recommend that you use connection pool because this feature can help you reduce the huge overhead associated with frequent creation of connections.

If your system is a C/S model structure, we will not recommend that you use connection Pool, this is because in general, in the C/S model, each user is to use their own username and password to connect to the background database, the use of the ground are different connection String, there is no frequent open/Close database connection problem, in fact, in the C/S model, you can always make a connection maintenance open to close, not close, so as to improve the performance of your system, not due to connection Pool additional checks to bring system resources to consume, in the case also do not have to worry about the open connection long time to occupy the connection, resulting in other connections can not be obtained from connection pool in a timely manner. (because you don't need to use connection pool at all).

Hope this helps.

In addition, a little bit:

Connection Lifetime

0

When the connection returns to the pool, its time is compared to the creation time, and if it exists longer than connection Lifetime, it is freed. This is useful for new servers that are added to the cluster server balance. A value of 0 ensures that the connection has a maximum time limit.

Connection Reset

' True '

Determines whether the database connection is reset when it is removed from the pool.

Enlist

' True '

When True, Pooler automatically lists the current creation thread to the operation context if the action contexts exist in the session.

Max Pool Size

100

The maximum number of connections allowed in the pool.

Min Pool Size

0

The minimum number of connections allowed in the pool.

Pooling

' True '

When True, the connection is fetched from the corresponding pool, if needed, to be created or added to the appropriate pools

If the SqlConnection is out of range, it will not be closed. Therefore, unless you place the code inside a using statement, you must call Close or Dispose to explicitly close the connection. They are functionally equivalent.

Difference:

The Close () method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if the connection pool is disabled, and the application can call close multiple times. No exceptions will be generated. If you set the connection pool value Pooling to true or Yes, the physical connection is also released.

The Dispose () method is actually the same thing as close (), the only difference being that the Dispose method empties the ConnectionString, which is set to null.

Using means that the Dispose method is called automatically after the {} interval to ensure that the object is destroyed. Using can only be used on classes that integrate the IDispose interface

Example:

SqlConnection con = new SqlConnection ("Data source=localhost;initial catalog=northwind; User Id=sa; Password=steveg ");

Con. Open ();

Con. Close ();

Con. Open ();

Con. Dispose ();

Con. Open ();

Close off the connection can be open,dispose again, because ConnectionString empty, will throw InvalidOperationException hint the ConnectionString Property had not been initialized, but at this point the SqlConnection object is still there.

If the ConnectionString is re-assigned after Dispose, no error is given.

The conclusion is that neither dispose nor close will destroy the object, that is, the memory is not freed, and they throw the SqlConnection object into the connection pool, when will the object be destroyed? I think it's supposed to be connection timeout. If the program does not make a request to the connection pool to connection the object, the SqlConnection object is destroyed, which is the meaning of the connection pool existence.

The difference between Dispose () and close ()