0

My Project running on VS 2013 & MySQL, I have declared public variable conn as mysqlconnection and I use the same connection all over the project to perform any database operation.

    Public conn As New MySqlConnection

     Public Sub createConnection()
            If conn.State = ConnectionState.Closed Then
                conn.ConnectionString = parseConfigXML()
                conn.Open()
            End If
        End Sub

Public Function parseConfigXML()
        Try
            Dim m_xmld As XmlDocument
            Dim m_nodelist As XmlNodeList
            Dim m_node As XmlNode
            m_xmld = New XmlDocument()
            m_xmld.Load("../../../appconfig.xml")
            m_nodelist = m_xmld.SelectNodes("/app/config")
            Dim hostname, dbname, dbuser, dbpassword As String
            hostname = ""
            dbname = ""
            dbuser = ""
            dbpassword = ""
            For Each m_node In m_nodelist
                hostname = m_node.ChildNodes.Item(0).InnerText.ToString
                dbname = m_node.ChildNodes.Item(1).InnerText.ToString
                dbuser = m_node.ChildNodes.Item(2).InnerText.ToString
                dbpassword = m_node.ChildNodes.Item(3).InnerText.ToString
            Next
            Dim connectionString = "Database=" & dbname & "; Data Source=" & hostname & "; User Id=" & dbuser & ";Password=" & dbpassword & "; Character Set=utf8"
            Return connectionString

        Catch errorVariable As Exception
            MsgBox("Oops, An error occured: " & errorVariable.ToString)
        End Try
    End Function

Whenever connection is required, I use it as follows:

 Try
            Dim query As String = "select * from tbl_lang where lang_status=1"
            Dim cmd As New MySqlCommand(query, conn)
            Dim dr As MySqlDataReader
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                Do While dr.Read
                    cmb.Items.Add(dr.GetString("lang_name"))
                Loop
            End If
            cmb.SelectedItem = "English"
            If Not dr.IsClosed Then dr.Close()
        Catch ex As Exception
            lbl_error.text="Oops, An error occured: " & ex.ToString
        End Try

I always have this issue when the code is reused: There is already open datareader for this connection.

What is the best way to deal with it? someone had mentioned to use open new connection for every query and then close it. Somehow, I am not convinced with it as it may create performance issues.

Please suggest the best way to overcome the issue.

Iqlas Uddin
  • 177
  • 2
  • 15
  • 2
    This is an old belief (keeping a connection open improves performances) that is no more true with [connection pooling](http://dev.mysql.com/doc/connector-net/en/connector-net-programming-connection-pooling.html) and it is well know to be a source of very bad bugs like the one you see in your code. The pattern is Create, Open, Use, Close, Dispose (where the latest twos are often a single call) – Steve Apr 08 '16 at 07:13
  • 2
    1. I would not use global connections which remain open. Create one for each transactional purpose and close it properly. Use `using` for auto dispose. Also `conn.Open()` internally uses connection pooling which most of the time is smarter than custom code. 2. You do not close your DataReader in error case. Again use `using` on the DataReader too. – Alex B. Apr 08 '16 at 07:15
  • To add on to what AlexB said, I would recommend that you move the `If Not dr.IsClosed Then dr.Close()` into a `Finally` block of your `Try\Catch`. – Dave Michener Apr 08 '16 at 12:53

0 Answers0