c# - .Net Database how do I properly close my database connections? -

So I've read a lot about SqlDataReaders are not being dealt with properly - and I "expire the timeout The timeout period had expired before getting the connection to the pool, it could be because all the pool connections were in use and the maximum pool size "error for a few days" obviously I could jack the maximum pool size up to 30,000 I do not solve the real problem.

As I step through the code, I execute the following SQL query: Select

  from sys.dm_os_performance_counters where counter_name = 'user connection' < / Code> 

after

  cmd.Connection.Open ();  

line, the user connection has increased by 1. However, it does not return until I recycle the app pool on the web server (at which point all the active database connections from the website are killed).

This is my The code is:

  public static datatable SPExecuteDataTable (string [] connection data, parameters object [] args) {SqlConnection conn = null; SqlCommand CMD = Faucet; SqlDataReader dr = faucet; Try {conn = new SqlConnection (connection data [1]); CMD = new SQL Commands (connection data [0], new SQL connection (connection data [1]); Cmd.CommandType = CommandType.StoredProcedure; For {i.ic. Cmd.Parameters.Add (Ultimate); } Cmd.Connection.Open (); Datatable dt = new datatable (); (Dr = cmd.ExecuteReader ()) using {if (dr! = Null) dt.Load (dr); Else dt = null; } Return DT; } Hold (exception e) {exception x = new exception (String.Format ("DataAccess.SPExecuteDataTable () {0}", e.Message)); Throw x; } Finally {conn.Close (); Cmd.Connection.Close (); Dr.Close (); Conn.Dispose (); Cmd.Dispose (); Dr.Dispose (); }  

So far, I have tried to explicitly close the connection (like the block at my end), but it does not work. I have also tried to use such statements:

 using  (SqlDataReader dr = blah blah blah) {// code here}  

< P> But it is not even working what's wrong in my code, here?

Preferred practice is to wrap connections, commands, and readers (SqlCommand cmd = new SqlCommand (ConnectionData [0 ], Using conn) using block:

  (SqlConnection conn = new SqlConnection (connection data [1]) {{// ^ - Reuse Connection - See commands below for CMD.CommentType = CommandType for StardPriver; (Int I = 0; I and LT; AGLFi ++) {SQLParameter Ultimate = New SQLPamerator (Connect Shan Data [i + 2], DBNILEFinal (args [ii]); CMD.Pamateters.ed (ultimate);} CMD.connect.open (); datatable dt = new datatable (); (dr = CMD.exacut reader ()) Using {if (Dr.! = Tap) dt.Load (Dr.); and dt = null;} Return DT;}}  

that

< P> Although I think the heart of your problem is that you are making two connections every time:

  conn = new SqlConnection (connection data [1] ); CMD = new SQL Commands (connection data [0] , New SQL Connection (connection data [1]); ^ ---- Creating another connection  

Finally, you are losing out on the original exception by creating a new exception and potentially important information (stack trace, etc.):

  hold (exception e) {exception x = new exception (String.Format ("DataAccess.SPExecuteDataTable () {0}", e.Message)); Throw x; }  

I leave either the original exception bubble or the original exception in the form of InnerException :

  hold E) { String message = String.Format ("DataAccess.SPExecuteDataTable () {0}", e.Message); Exception = x Exception (message, e); Throw x; }