-1

The question is: Is there any problem if i open the db connection once every 100 ms in a timer? I think the best way is to open once the connection and then do the query all the times needed. Right?

My code is:

 Friend conexion As MySqlConnection
    Private cmd As New MySqlCommand
    Private dr As MySqlDataReader
    Private ConnectionString = "server=localhost; uid=root; pwd=; database=myblahdb;"

  conexion = New MySqlConnection()
  conexion.ConnectionString = ConnectionString
conexion.Open()

 Dim cmd As New MySqlCommand
       With cmd
            .CommandText = "select blah blah"
            .CommandType = CommandType.Text
            .Connection = conexion
        End With
        dr = cmd.ExecuteReader

        If dr.HasRows Then
            dr.Read()
            //do stuff
        End If

        cmd.Dispose()
        dr.Close()
        dr.Dispose()
        conexion.Close()
        conexion.Dispose()
PeterPam
  • 337
  • 6
  • 20
  • How long will this `Timer` be running? Performing a query 10 times a second for an extended period of time seems excessive. – jmcilhinney May 29 '19 at 08:37
  • 1
    Iirc in ADO.NET, connections are cached behind the scenes anyway - the system will detect that you're opening and closing, and simply keep the connection open between calls. I would also add that polling every 100ms generally suggests a poor system design that is unlikely to scale in any business environment. – Steve May 29 '19 at 08:48
  • i do the check every 250ms. after some time running i got an error about connections pool if full... how to open only one connection? – PeterPam May 29 '19 at 08:58
  • 1
    It *should* work correctly the way you have shown if the rest of the code involves opening and closing the connection for every query. You might want to consider using the [Using Statement](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement) as it is a bit tidier for making sure that Dispose is called. (Also, you don't need `.CommandType = CommandType.Text` because that is the default, so you can save lines with no loss of readability by removing the `With`.) – Andrew Morton May 29 '19 at 10:40
  • 1
    @PeterPam If the code is running out of connections when it is only making ten calls per second then somewhere there is code which is not closing the connections. That could be in the form of connections not being closed when an error occurs. The Using statement will make sure that the connections are disposed of behind the scenes even if there is an exception. Also, does your code always complete before the timer ticks again? – Andrew Morton May 29 '19 at 12:10
  • @AndrewMorton. there is only one connection. on start. and there is one disconnection, on end program. It is no possible to have "connections being closed", as there is only ONE conn. And the errors are catched also. And of course, there are only one query at same time. Timer is stopped while query is working – PeterPam May 29 '19 at 13:25
  • @PeterPam If you are using Try...Catch around the code which creates the connection, then you have to use a Finally clause to Dispose of the connection, as shown in [Structured Exception Handling Instead of a Using Block](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement#structured-exception-handling-within-a-using-block). – Andrew Morton May 29 '19 at 13:28
  • @PeterPam I found "Keeping connections open for long periods of time, however, can cause issues." in [C# Data Connections Best Practice?](https://stackoverflow.com/a/17553377/1115360) although unfortunately it does not specify how long a time or what those issues might be. – Andrew Morton May 29 '19 at 14:03
  • @AndrewMorton. nice comment to keep in mind. Possibly a max time connection can do a bad card with this... – PeterPam May 29 '19 at 15:09
  • @AndrewMorton as far as i saw, only timeout related parameters found. connect_timeout=28800 wait_timeout=28800 interactive_timeout=28800 – PeterPam May 29 '19 at 15:11

1 Answers1

0

moving this to form_load in order to do on start

conexion = New MySqlConnection()
conexion.ConnectionString = ConnectionString
conexion.Open()

and doing this on exit

 conexion.Close()
 conexion.Dispose()

is the solution

PeterPam
  • 337
  • 6
  • 20
  • 1
    That is exactly the wrong way to do it. It should be open connection, immediately perform query, immediately close connection. Documentation: [Connection Pooling](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-pooling). – Andrew Morton May 29 '19 at 11:08
  • @AndrewMorton, please, read slowly your link. connecting is a slow process and the connection polling minimize this. But the way to avoid this is only open the needed connections. Opening and closing connections all the time in this situation is a very bad idea. With the opened connection you can do all the queries you need without close, open, close and open... – PeterPam May 29 '19 at 11:43