SQL Connection Pool Exceding limit despite Finally blocks closing the connection

Question

I've been over this site up and down but I cannot find whats going on with my WebService Connection Pool. Using an application, I call a WebMethod on my service which in turn fires the code below.

Using cn As New SqlConnection(ConnectionString)
   Try
       Using cmd As New SqlCommand()
           cmd.Connection = cn
           cmd.CommandType = CommandType.Text
           cmd.CommandText = "select * " &
                        " from Customer " &
                        " where CustomerID=@CustomerID and active=1"
           cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4)
           cmd.Parameters("@CustomerID").Value = CustomerID
       End Using
       da = New SqlDataAdapter(cmd)
       da.Fill(ds, "Customer")
   Catch ex As Exception
       Throw ex
   Finally
       If cn.State = ConnectionState.Open Then
            cn.Close()
       End If
   End Try
End Using

Then within my SSMS I fire the below and I can see on my DB I still have an Active connection with a 'sleeping' status

SELECT 
   DB_NAME(dbid) as DBName, 
   COUNT(dbid) as NumberOfConnections,
   loginame as LoginName,
   status
FROM
   sys.sysprocesses
WHERE 
   dbid = 16  'my dbid
GROUP BY 
   dbid, loginame, status

I pass over similar methods and my NumberOfConnections just keeps building. help

DBNAME | NumberOfConnections | LoginName | Status
MyDB   | 1                   | jgritten  | sleeping

Answer 1

You may have simply lost it in translating the code for your post -- but there is no cn.Open(). I would also cn.Close() in the Try statement and then just as a catch-all leave the close in the finally statement. You may also include the check and cn.close() in the catch statement so that any exception will also close the connection.

Secondly, your using statement for sqlcommand ends before you fill your dataadapter with it.. Fix those things and terminate existing connections. Verify your closing connections in your transactions. Maybe step through and make sure it's not secretly throwing exceptions or write the exception string out to the Console just in case it's skipping on by.