Connection pooling and Timeout expired exception resolution

Connection pooling and timeout expired exception resolution

You need to make sure that every time you call a connection, it is closed with close () or Dispose (). The easiest way to do this is to modify your connection leak 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 do you do this?

A: Using a using structure is equivalent to try/.../finally{<using object>.dispose () Even when ExecuteNonQuery throws an execution error, we can guarantee that the finally module will execute

Q: Can I use close () or Dispose () if there is no exception in the above code?

A: We have no qualms about using either of them, or two of them at the same time. Using close () or Dispose () in a connection that is already close or dipose () does not affect

What's the difference between q:close () and Dispose (), which one should I use?
A: They do the same thing, you can call either of them, or two of them to use at the same time.

Q: What do you mean by "practically the same thing"?
A:dispose () will clean the associated connection through SqlConnection, followed by close (). There's no essential difference, you can prove it by reflector.

Q: Does Connection.dispose () remove the connection as compared to close ()?
A: No

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

My share:

I have also consulted a lot of information on the anomaly of "timeout expired". In China, many of our project will use MS-provided sqlhelper this encapsulation class. Because this class has its own defects, so the occurrence of "timeout expiered" abnormal probability is large. The solution I see in an article abroad is:

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

if (trans!= null)
cmd.transaction = trans;

Cmd.commandtype = Cmdtype;
Cmd.commandtimeout = 240;