MySQL DataConnections not closing/pooling

Question

I am working on completely redeveloped website and sales system and have come up against this Max_connections issue surprisingly quickly.

I posted this question: Closing/Pooling MySQL ODBC connections Recently, but have since tried a few other things, still drawing a blank, but have more detail to offer...

I have a built a pretty complex sales process, and in creating an invoice I seem to be leaving 7 "processes" running each time. I have counted the number of times the data connection is used during the process of creating an invoice, and it is 7-9 depending on a few conditional values, so effectively the data connections are not closing at all.

To try to speed up coding, I have made a couple of functions which handle my database connectivity, so I will post these below.

Firstly, my connection string is:

"DRIVER={MySQL ODBC 3.51 Driver}; SERVER=mysql.dc-servers.com; DATABASE=jamieha_admin; UID=USERID; PASSWORD=pWD; OPTION=3;pooled=true;Max Pool Size=100" 

The functions which I am using to open and close and do stuff with the database are as follows:

Function connectionString(sql As String, closeConnection As String) As OdbcConnection
    Dim DBConnection As String = ConfigurationManager.ConnectionStrings("dbConnNew").ConnectionString
     'this is getting the connection string from web.config file.
    Dim oConnection As OdbcConnection = New OdbcConnection(DBConnection) 'call data connection
    connectionString = New OdbcConnection(DBConnection)
   If closeConnection <> "close" Then _
       connectionString.Open() ' open data connection
End Function

This function gives me a OdbcConnection Connection String Object, which I can then use with:

Function openDatabase(sql As String) As OdbcCommand
    openDatabase = New OdbcCommand(sql, connectionString(sql, ""))
End Function

This function creates a useable data object when called doing something like:

Dim stockLevel As OdbcCommand = openDatabase("SQL STATEMENT HERE")
Dim objDataReader As OdbcDataReader =      stockLevel.ExecuteReader(CommandBehavior.CloseConnection)
    '=== DO STUFF WITH objDataReader ==='
objDataReader.Close()

Having read up trying to ensure data connections were closing properly and so on I read that adding (CommandBehavior.CloseConnection) should ensure that the connection is closed when no longer used, but this doesn't seem to be happening, so I have created a separate "closeCOnnection" function, which looks like:

Function closeConn()
    If connectionString("", "", "close") IsNot Nothing AndAlso connectionString("", "close").State = ConnectionState.Open Then
        connectionString("", "close").Close()
        connectionString("", "close").Dispose()
    End If
End Function

This is called after every use of the openDatabase function and also within the functions I have created for insert/update and delete, which look like this:

Function insertData(InsertSql As String)
    Dim dataInsert = openDatabase(InsertSql, "new")
    dataInsert.ExecuteNonQuery()
    closeConn()
End Function

I am not sure whether making all these functions is making my life easier or harder, but I was trying to reduce the code in each file where data acceess is required, but I'm not convinced it has.

However, it has made it clear where and when I am opening and closing the database (or at least trying to)

The processes are not being closed though. If I run my sale process through 3 or 4 times in quick succession, with these 7 processes still being live and added to, I get the max_connections issue.

Not completely understanding how database connections work, I am afraid I am at a loss with this and hence having to ask you... again!!

Can anyone tell me:

a) Is my connection string correct, is there a better connection available for MySQL?

b) Using this method, creating a ODBCConnection Object, is it possible to close it within a function like this?

c) Why is (CommandBehavior.CloseConnection) not closing the connection (this problem arose before I tried closing the connection manually)

Answer 1

Unfortunately the issues you are having come from your design and a mishandling of references to connections.

But don't worry. It's not difficult to fix. :-)

In VB.Net you always need to access data in the following pattern:

1. Create a connection.
2. Create a command which uses the connection (including adding any parameter values).
3. Open the connection.
4. Execute the command.
5. Close the connection.

There are variations of this, such as looping over rows before closing the connection, but generally this is how it works. In order to ensure that the connection is closed, VB.Net provides Try/Finally blocks and Using statements. You need to use one of these to make sure the connections are closed.

I'll show you what I mean by rewriting your methods in the proper manner.

Firstly, wrap your connection-creation code into a function.

Function GetConnection() As OdbcConnection

    Dim DBConnection As String = ConfigurationManager.ConnectionStrings("dbConnNew").ConnectionString

    GetConnection = New OdbcConnection(DBConnection)

End Function

Secondly, write a function to create your command. (openDatabase is the wrong name, so I have changed it to CreateCommand).

Function CreateCommand(sql As String, connection As OdbcConnection) As OdbcCommand

    CreateCommand = New OdbcCommand(sql, connection)

End Function

Now when you wish to execute a query or a statement in the database, you can follow this pattern:

Dim connection As OdbcConnection = GetConnection()
Dim stockLevel As OdbcCommand = CreateCommand("SQL STATEMENT HERE", connection)
Try
    connection.Open()
    Dim objDataReader As OdbcDataReader = stockLevel.ExecuteReader(CommandBehavior.CloseConnection)
Finally
    connection.Dispose()
End Try

Using the Try/Finally block means that the connection will always be closed correctly, even when an Exception causes the code to return before you expect it to.

An alternative shorthand is the Using statement (which effectively does exactly the same thing as the Dispose in a Finally block):

Dim connection As OdbcConnection = GetConnection()
Dim stockLevel As OdbcCommand = CreateCommand("SQL STATEMENT HERE", connection)

Using connection
    connection.Open()
    Dim objDataReader As OdbcDataReader = stockLevel.ExecuteReader(CommandBehavior.CloseConnection)
End Using

And if you want to wrap your InsertData function in a command, you can do it like this:

Dim connection As OdbcConnection = GetConnection()
Dim stockLevel As OdbcCommand = CreateCommand(InsertSql, connection)

Using connection
    connection.Open()
    Dim result As Integer = stockLevel.ExecuteNonQuery()
End Using

I suspect that the first time you tried this, you were leaving your connections open without ever closing them. I also assume (from what you wrote) that you added the closeConn method to sort that out. Unfortunately, every time you call connectionString you are actually creating and opening a new connection, which you then call Close or Dispose on. The initial connection is never closed.

Hope that helps.