[2005] Connection pool Maximum reached?

[Dave Sell]

All,

I created a VB.NET web application project. Recently I got the dreaded above error message when a certain amount of web pages have loaded. On a form1_Load event, I do this:

Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        '
        MyConnection = New SqlConnection
        MyConnection.ConnectionString = CONN_STRING
        MyConnection.Open()
        '
        Me.GridView1.Sort("Priority", SortDirection.Ascending)
        '
    End Sub

Question: Am I required to explicitly call the .Close? Inside which event would be the appropriate place?

Will this do the trick - or break anything?

Protected Sub form1_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Unload
        MyConnection.Close()
        MyConnection = Nothing
    End Sub

Thanks!

Dave

[jmcilhinney]

Don't leave the connection open in the Load event handler. Open it, use it and close it. That's the way ADO.NET is designed to be used. Create a new connection each time you need one. Connection pooling keeps things efficient.

[Dave Sell]

Well see now there's the rub. After the page is loaded, I have buttons that do things that require an open connection.

Protected Sub btnIncrease_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncrease.Click
        '
        Try
            '
            MyCommand = New SqlCommand("increasePriority", MyConnection)
            MyCommand.CommandType = CommandType.StoredProcedure
            MyCommand.Parameters.Add("@workOrderNumber", SqlDbType.VarChar).Value = DropDownList_WorkOrders.SelectedValue
            MyDataReader = MyCommand.ExecuteReader()
            '
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            MyDataReader.Close()
        End Try
        '
    End Sub

Do you suggest I open/close in the beg/end of each of these button's codeblock?

Thanks!

[jmcilhinney]

Do you suggest I open/close in the beg/end of each of these button's codeblock?

It would appear so.

Open it, use it and close it. That's the way ADO.NET is designed to be used. Create a new connection each time you need one.

[Dave Sell]

OK then thanks, I will do that if that is the standard .NET "good programming practice" in situations like this. I will keep this thread open a tad longer to ensure no new problems arise from it - or if it solves the prob.

Dave

[Shaggy Hiker]

You might want to look into what JM was saying about the efficiencies of connection pooling keeping things efficient. I may be reading too much into it, but you seem a bit reluctant to open and close connections. I think that was a valid position back in VB6, because there was a pretty good cost to some of that stuff, so leaving one connection open was often a good idea.....as long as your could afford to do so. Times have changed, though, and if you have concerns about this, checking into it a bit might ease your mind on it some.

[jmcilhinney]

As Shaggy says. With ADO.NET the connections you use are at a high level. There are also connection objects at a much lower level. When you create a connection, open it, use it and discard it, then open a second connection, you will likely be using the same underlying low-level connection. Creating the high-level object is very cheap, so doing so repeatedly is no problem. It's the low-level objects, which you never actually see, that are expensive to create, so they are created and discarded far less often.

[Dave Sell]

Again, VBF failed to notify me that you guys responded to my posts (this is a long-time issue for me), sorry I just saw your replies.

Anyway, what I'm hearing is ADO.NET has under-the-hood connection pooling? That would be sweet. I have followed JM's advice from above. I still do not know if there is a problem on the server since I implemented this few weeks back. I will ask my customer how things are going this week.

[dee-u]

Don't leave the connection open in the Load event handler. Open it, use it and close it. That's the way ADO.NET is designed to be used. Create a new connection each time you need one. Connection pooling keeps things efficient.

To sum it up: Open late, close early.