Connection Pool and Timeout expired exception Solution

Connection Pool and timeout expired exception Solution

You need to ensure that you close the connection through close () or dispose () after each call. the simplest way is to use using to change your connection leakage Method to the following code style:

Public void doesnotleakconnections ()
{
Using (sqlconnection sqlconnection1 = new sqlconnection ("server =. sqlexpress; integrated security = sspi; connection timeout = 5 ")){
Sqlconnection1.open ();
Sqlcommand sqlcommand1 = sqlconnection1.createcommand ();
Sqlcommand1.commandtext = "raiserror ('this is a fake exception', 17,1 )";
Sqlcommand1.executenonquery (); // this throws a sqlexception every time it is called.
Sqlconnection1.close (); // still never gets called.
} // Here sqlconnection1.dispose is _ guaranteed _
}

 

Faq:

Q: Why?

A: The using structure is equivalent to try /... /Finally {<using object>. dispose () Even if executenonquery throws an execution error, we can ensure that the finally module will execute

 

Q: Can I use close () or dispose () If no exception is thrown in the above Code?

A: We use either one or both of them with no scruples. Using close () or dispose () in a closed or dipose () connection will not be affected.

Q: What is the difference between close () and dispose ()? Which one should I use?
A: They do the same thing. You can call either of them or use them at the same time.

Q: What do you mean by "practically the same thing?
A: dispose () will clean up related connections through sqlconnection, and then execute close (). There is no essential difference between them. You can prove this through reflector.

Q: Will connection. dispose () remove connections compared to close?
A: No

---------------------------------------------------------------

My shares:

For the exception "timeout expired", I also read a lot of information. In China, many projects adopt the sqlhelper encapsulation class provided by ms. Because this class has its own defects, the probability of a "timeout expiered" exception is high. The solution I saw in an article abroad is:

Add cmd. commandtimeout = "the number of seconds you want to set" in sqlhelper to recompile.

If (trans! = Null)
Cmd. transaction = trans;

Cmd. commandtype = primitive type;
Cmd. commandtimeout = 240;