title: InvalidOperationException: Timeout - recursive DB access
body: <p>currently I have a serious problem with one of my web applications which runs into a Timeout Exception around half a dozen times a day. </p>
  
  <p>Error: "The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached".</p>
  
  <p>After a lot of googling I found out that the problem has something to do with unclosed connections. So I checked all functions that access the database in any way until I stumbled upon this one:</p>
  
  <pre><code>    Private Sub getOrgas(ByVal orgID As String)
          Dim Id = orgID
          orgColl.Add(Id)
          While (Not IsNothing(Id))
              Dim conn = Database.DbWrapper.GetConnection(1, Integration.Mandanten.DatabaseType.AddonSQL)
              Dim paras As New HashSet(Of System.Data.Common.DbParameter)
              Dim orgatmp As String
              paras.Add(New SqlClient.SqlParameter("@Id", orgID))
              Dim dr = Database.DbWrapper.GetDataReaderFromStoredProcedure("stp_Orgas_Get", paras, conn)
              While dr.Read
                  If Not valueInColl(CStr(dr(0))) Then
                      orgatmp = dr(0).ToString
                      orgColl.Add(orgatmp)
                      getOrgas(orgatmp)
                  End If
              End While
              dr.Close()
              conn.Close()
              Id = Nothing
          End While
      End Sub
  </code></pre>
  
  <p>As you can see this function executes a stored procedure and runs the results through a while loop where it calls the function again if a specific condition -valueInColl-. Now in that way it is possible that there are 20 or more open connections. It has nothing to do with the timeout-value which is set via the GetDataReaderFromStoredProcedure to 600 which actually should be enough. To be sure I doubled the value and will roll it out this evening. I'll see whether that helped within the next day then.
  I believe the problem is that there are too many open connections at the same time, because of the recursive function, but I have no clue how to solve this.</p>
  
  <p>I couldn't find anything as to how to edit the max connections. I'm not even entirely sure where have to set it. Is it the IIS, the DB itself or is it a programming-parameter (VB.net/ASP.NET).
  Would be nice if you guys could help me out here.</p>
  
  <p>[EDIT]
  Ok, somebody had the idea to reuse the connection variable, but this won't work as the datareader is still running. As long as it is not closed I can't reuse the connection in any way and I can't close the datareader, because I might lose data if I do so. The while-loop for dr.read hasn't ended, yet ..
  On the other hand I deleted the (pretty much useless) outer while and used an If-clause in exchange:</p>
  
  <pre><code>Private Sub getOrgas(ByVal orgID As String, ByVal con As DbConnection)
      Dim Id = orgID
      Dim conn As DbConnection
      Dim tmpOrga As String
      orgColl.Add(Id)
      If Not IsNothing(Id) Then
          If IsNothing(con) Then
              conn = Database.DbWrapper.GetConnection(1, Integration.Mandanten.DatabaseType.AddonSQL)
          Else
              conn = con
          End If
          Dim paras As New HashSet(Of System.Data.Common.DbParameter)
          paras.Add(New SqlClient.SqlParameter("@Id", orgID))
          Dim dr = Database.DbWrapper.GetDataReaderFromStoredProcedure("stp_Orgas_Get", paras, conn)
          While dr.Read
              If Not valueInColl(CStr(dr(0))) Then
                  tmpOrga = dr(0).ToString
                  orgColl.Add(tmpOrga)
                  getOrgas(tmpOrga, conn)
              End If
          End While
          dr.close()
          conn.Close()
          Id = Nothing
      End If
  End Sub
  </code></pre>