Correctly Use the sqlconnection object and talk about the database connection pool

Why should I disable the database connection when I saw lovejenny IN THE toutiao comment tonight ?,ArticleEasy to understand, andCodeThe post is in place, and the discussion below is intense (I hate it when Zhao ran to leave a message twice ). I checked the code in the original article twice. I agree with several comments. The connection problem may be caused by improper use of sqlconnection during multi-threaded parallel database operations. Why? Let's take a look at an important source code posted by the lovejenny brothers:

 
String sqlconnstring = @ "Data Source =. \ sqlexpress; attachdbfilename = "" E: \ dB \ northwnd. MDF ""; Integrated Security = true; Connect timeout = 30; user instance = true "; sqlconnection conn = new sqlconnection (sqlconnstring); Conn. open (); parallel. for (1, int32.maxvalue, (ID) =>{ executecommand (Conn, ID );});
 

The specific implementation of executecommand is as follows:

Private Static object syncobj = new object (); Private Static void executecommand (sqlconnection Conn, int ID) {lock (syncobj) {If (conn. State! = Connectionstate. open) {Conn. open ();} console. writeline ("executing. "+ id); thread. sleep (100); sqlcommand cmd = new sqlcommand (string. format ("insert into Nums values ('{0}')", ID), Conn); cmd. executenonquery ();}}
 

The code is concise, but many people, including myself, may have a few major questions:

1. How to operate n databases in parallel multiple times to share only one connection object?

2. A lock is applied to the parallel processing, and each database operation must be locked. (I don't think this gives full play to the advantages of multi-thread parallel processing, I personally think it is not as fast as a single thread.) Is this really necessary?

3. How can I transmit parameters to the same database connection string using the database connection object sqlconnection? Isn't explicit Open and Close better? Generally, isn't it all about using?

After a simple thought, I improved the implementation code on the local machine for testing, as shown below:

 
Parallel. For (1, int32.maxvalue, (ID) =>{ executecommand (ID );});
 

The executecommand method no longer accepts the sqlconnection object as the parameter. Removing lock makes database operations look like a database operation and opening a database connection to ensure thread safety:

Private Static void executecommand (int id) {using (sqlconnection conn = new sqlconnection (sqlconnstring) {Conn. open (); console. writeline ("executing. "+ id); thread. sleep (100); sqlcommand cmd = new sqlcommand (string. format ("insert into Nums values ('{0}')", ID), Conn); cmd. executenonquery ();}}
 

Of course, executecommand can also be processed through explicit open and close:

Private Static void executecommand (int id) {sqlconnection conn = new sqlconnection (sqlconnstring); Conn. open (); console. writeline ("executing. "+ id); thread. sleep (100); sqlcommand cmd = new sqlcommand (string. format ("insert into Nums values ('{0}')", ID), Conn); cmd. executenonquery (); Conn. close ();}
 

According to my local machine test, the two improved statements above indicate that the database connection object is normal and thread-safe, and the performance is significantly higher than the locking operation database, if you are interested, try again.

However, the purpose of this article is not to mention how the code can improve thread security to improve performance, but to explain a very simple question. How can we correctly and reasonably use the database connection object sqlconnection?

So how can I correctly and reasonably use the database connection object sqlconnection?

The most classic saying is that it is about using. This is actually very correct. The msdn usage is usually about using, which is very common:

Sqlconnection        Private   Static   Void Opensqlconnection (){ String Connectionstring = getconnectionstring (); Using (Sqlconnection connection = New Sqlconnection (connectionstring) {connection. open (); console. writeline (" Serverversion: {0} ", Connection. serverversion); console. writeline (" State: {0} ", Connection. State );}} Static   Private   String Getconnectionstring (){ // To avoid storing the connection string in your code,              // You can retrieve it from a configuration file, using              // System. configuration. configurationsettings. deleetaskproperty              Return " Data Source = (local); initial catalog = adventureworks; "+" Integrated Security = sspi; ";}

In fact, we all know that the sqlconnection object must inherit from the idispose interface before it can be using. The objects used by using are eventually processed by dispose. Is the close method of the database connection object equivalent to dispose?

Here we clearly tell you that dispose is different after explicit close and using. Do you want to know the difference between dispose and close?

Msdn interprets the close method as follows:

Close the connection to the database, which is the first choice to close any open connection. If sqlconnection is out of the range, it will not be closed. Therefore, you must explicitly close the connection by calling close or dispose. Close and dispose are functionally equivalent. If the connection pool value is set to true or yes, the basic connection is returned to the connection pool. On the other hand, if pooling is set to false or no, the basic connection to the server is closed.

We can understand this as follows: The sqlconnection object closed by calling the close method should be put back into the connection pool, that is, this object can still be used, but the current connection status of this object is closed. When you apply for a connection object from the pool next time, this object can be activated and reused without the need to create a new object.

By calling the dispose () method, this object may call GC. suppressfinalize (this) (some special classes may not call this method). In this way, GC preferentially recycles these objects, which may make the object unusable. Here we only need to understand,If you call the dispose method on the sqlconnection object, the connection cannot be used in the connection pool..

Finally, ado.net uses the connection pool technology to reuse connections, reducing the overhead of repeatedly opening and closing connections to improve performance, so as I said above, "Database Operations look like a database operation, opening a database connection to ensure thread security" is actually not correct. The following is an excerpt from the msdn statement:

The connection pool reduces the number of times new connections must be enabled. The pool Process maintains the ownership of the physical connection. Manage connections by retaining a set of active connections for each given connection configuration. When you call open on a connection, the pool process searches for available connections in the pool. If a pool connection is available, the connection is returned to the caller instead of opening a new connection. ApplicationProgramWhen close is called on the connection, the pool process returns the connection to the active connection pool instead of closing the connection. After the connection is returned to the pool, it can be reused in the next open call.

You can establish a pool connection only when the same connection is configured. Ado. Net retains multiple pools at the same time, one for each configuration. When integrated security is used, connections are allocated to multiple pools according to the connection string and Windows identity. The pool connection is also established based on whether the connection has been registered in the transaction.

Pool connections can significantly improve application performance and scalability. By default, the connection pool is enabled in ADO. net. Unless explicitly disabled, the pool process will optimize the connection when the connection is opened or closed in the application. You can also provide several connection string modifiers to control the behavior of the connection pool.

We can perform some operations on the connection pool explicitly, but we do not recommend this operation. Do you have any questions about the connection pool? Welcome to the discussion.

Refer:

Http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlconnection (vs.80). aspx

Http://msdn.microsoft.com/zh-cn/library/8xx3tyca (vs.90). aspx