vb net max_user_connections to mysql

Question

I have a pc program used by dozens of people, and with the increase in people connecting to a database, the program began to throw the error with a large number of database connections. I checked the database after each query creates a process that is in the database as "sleep" if you exceeded the number 50 is the above error crashes. How can I remedy this if the problem lies with the program or hosting?

Database screen ;

http://obrazki.elektroda.pl/5375287900_1423553806.png

Code:

    Public Sub loginUser(ByVal sql As String)
    Try
        Dim maxrow As Integer
        con.Open()
        dt = New DataTable
        With cmd
            .Connection = con
            .CommandText = sql
        End With
        da.SelectCommand = cmd
        da.Fill(dt)
        maxrow = dt.Rows.Count
        If maxrow > 0 Then
            Form1.Show()
        Else
            Label3.Text = ("Invalid Username or Password!")
            Label3.Visible = True
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    con.Close()
    da.Dispose()
End Sub


Private Sub InsertData()

    sql = "SELECT * from users WHERE login = '" & (username.Text) & "' and pass = '" & StringtoMD5(password.Text) & "'and banned = '" & 0 & "'"
    loginUser(sql)

End Sub

Answer 1

When using database connections a special care should be used to correctly close and dispose these connections. If you don't do that correctly you end up with stale connections kept by your program and never reused by the pooling infrastructure of ADO.NET (See ADO.NET Connection Pooling)

The code in your example above has all the checks in place and should not be the cause of your problems but, are you sure that every where in your program you follow the same pattern without forgetting to dispose the involved objects?

The using statement is a life saver here because, EVEN in case of exceptions, you could be sure that the objects enclosed by the using statement are closed and disposed returning any unmanaged resources back to the system.

Another problem is your way to build SQL Commands concatenating strings. This leads directly to SQL Injection attacks and a very poor security standard for your application.

Said that, I think you should change your loginUser method to something like this

Public Sub loginUser(ByVal sql As String, ByVal parameterList as List(Of MySqlParameter))
    Try
        Dim maxrow As Integer

        ' local variables for connection, command and adapter... '
        Using con = new MySqlConnection( ..connstring here.. )
        Using cmd = con.CreateCommand()
           con.Open()
           With cmd
               .Connection = con
               .CommandText = sql
               .Parameters.AddRange(parameterList.ToArray())
           End With
           Using da = new MySqlDataAdapter(cmd)
               Dim dt = New DataTable
               da.Fill(dt)
               maxrow = dt.Rows.Count
               If maxrow > 0 Then
                 Form1.Show()
               Else
                 Label3.Text = ("Invalid Username or Password!")
                 Label3.Visible = True
               End If
           End Using
       End Using
       End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

And call it with

Private Sub InsertData()

    sql = "SELECT * from users " & _ 
          "WHERE login = @uname " & _
          "AND pass = @pwd " & _
          "AND banned = '0'"
    Dim parameterList = new List(Of MySqlParameter)()
    parameterList.Add(new MySqlParameter("@uname", MySqlDbType.VarChar))
    parameterList.Add(new MySqlParameter("@pwd", MySqlDbType.VarChar))
    parameterList(0).Value = username.Text 
    parameterList(1).Value = StringtoMD5(password.Text)
    loginUser(sql, parameterList)

End Sub

As I have said, just this change alone probably don't fix your problem. You should try to find in your program where you have a situation in which the connection is not properly closed and disposed. (and, at least, replace that code with the using statement)