How can I solve a max pool size - ASP.NET

Question

Public Function QueryDataSet(ByVal strSQL As String) As DataSet
    Dim ds As New DataSet
    Dim dtAdapter As New SqlDataAdapter
    objConn = New SqlConnection
    With objConn
        .ConnectionString = strConn
        .Open()
    End With
    objCmd = New SqlCommand
    With objCmd
        .Connection = objConn
        .CommandText = strSQL
        .CommandType = CommandType.Text
    End With
    dtAdapter.SelectCommand = objCmd
    dtAdapter.Fill(ds)

    objConn.Close()
    objConn.Dispose()
    SqlConnection.ClearPool(objConn)
    objConn = Nothing
    Return ds 
End function

The above is in the class

How can I fix my function that solve the Max Pool Size is Full on my server?

Please Help me.

possible duplicate http://stackoverflow.com/questions/15848239/how-to-solve-max-connection-pool-error — Genish Parvadia, Dec 30 '16 at 04:42
Possible duplicate of [How can I solve a connection pool problem between ASP.NET and SQL Server?](http://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server) — Chetan Sanghani, Dec 30 '16 at 07:37
You have a connection leak. You aren't closing connections properly. Use the `Using` statement and fix your code instead of trying to cover up the bug. If you *increase* the number of open connections you *increase* the resources used on the server for no reason, increase the chance of contention and eventually reduce the server's througput — Panagiotis Kanavos, Dec 30 '16 at 14:01
If anything goes wrong your method exits *without* closing the connection. Fix it by using `Using objConn = New SqlConnection ...`. Connection pooling isn't broken — Panagiotis Kanavos, Dec 30 '16 at 14:03

Arjun Singh · Answer 1 · 2023-08-12T15:21:45.800

0

Use following code

Public Function QueryDataSet(ByVal strSQL As String) As DataSet
    Dim ds As New DataSet
    Dim dtAdapter As New SqlDataAdapter
Try
    objConn = New SqlConnection
    With objConn
        .ConnectionString = strConn
        .Open()
    End With
    objCmd = New SqlCommand
    With objCmd
        .Connection = objConn
        .CommandText = strSQL
        .CommandType = CommandType.Text
    End With
    dtAdapter.SelectCommand = objCmd
    dtAdapter.Fill(ds)

    objConn.Close()
    objConn.Dispose()
    SqlConnection.ClearPool(objConn)
    objConn = Nothing

Catch ex As Exception
MessageBox.Show(ex.Message)

Finally

    objConn.Close()
    objConn.Dispose()
    SqlConnection.ClearPool(objConn)
    objConn = Nothing

End Try

    Return ds 
End function

or Add MaxPoolSize={123} in the Connection String.

edited Aug 12 '23 at 15:21

answered Dec 30 '16 at 13:57

Arjun Singh

125
9

A *far* better solution would be to make sure that connections are closed using the `Using` statement. Increasing the pool size just covers up the connection leak and it will *still* reduce throughput at the server – Panagiotis Kanavos Dec 30 '16 at 14:00
Thank arjun I will try it. Before I post a question in this I already fix MaxPoolSize=1000 in connection string. – Nakarin Karnsubwedchkij Jan 04 '17 at 02:51
It does not even cover it up - how many thousands will you allow when connections are leaked? – TomTom Aug 12 '23 at 15:24

How can I solve a max pool size - ASP.NET

1 Answers1