Research on ADO. NET database connection pool (1)-view the number of connection pools)

We all know that ADO. Net comes with a link pool and is enabled by default. If not set, the maximum number of connections allowed is 100.

So how can we see the number of active connections in the connection pool?

After talking about the research, we found that a stored procedure in the data can help us, that is, sp_who

To verify this, we wrote an Asp.netProgramIt is released to the IIS server of Windows2003 to create a website and specify an independent application pool for the website.

CodeAs follows:

CODe
Sqlconnection Conn = New Sqlconnection ( " Server = 192.168.0.213; uid = sa; Pwd = cpkf! @ # $ % ^; Database = test; " );
Conn. open ();

Sqlcommand cmd= NewSqlcommand ("Select * From tablese", Conn );

Sqldataadapter SDA= NewSqldataadapter (CMD );

Dataset DS= NewDataset ();
SDA. Fill (DS );

As you can see from the code above, the database link is not closed, and the table tablese does not exist here. In this way, an error occurs on the page.

I continuously press F5 on two clients and run exec sp_who to view them. The result is as follows:

54

0

Sleeping

SA

Cloneserver2

I can wonder why the status is sleeping and the CMD command is awaiting. I found it online:

Sleeping

Spid does not exist currently. Generally, it indicates that the spid is waiting for the attacker to send a command from the application.

Awaiting command

Currently, no commands need to be processed.

This indicates that all sleeping is because we have not closed the database link, and this link will no longer be awakened in the connection pool. As we accumulate, after 100, other programs will not be able to get the connection, and an empty reference exception will occur (if you are using the connection object and do not make a null judgment ).

Then, restart the application pool of the website. At this time, you can use sp_who to check that all sleeping is gone.

Summary: The above is a study on the situation where connections are not closed in the connection pool, which is very bad when the connection pool is not closed. It also shows how to view connections in the connection pool, it is important to understand sleeping and never sleep as a thread. This is a big mistake. Once this happens, it indicates that the link has a problem and may not be released.

From: http://hi.baidu.com/westfruit/blog/item/1bbebb097567bcc53bc76382.html