Try Catch not catching Database connection error

Question

I'm using the following code to open a database connection. If the connection fails for any reason, I get the typical VB error message with all of the details. and my program stops. It does not give me my graceful message and ending.

My try/catch does not work regardless of the connection error (whether it is password related, network related, or sql server related).

I need my program to continue on even if I can't connect to the database.

Public Class SQL_Connection

    Public conn As New SqlConnection
    Public cmd As New SqlCommand

    Public Sub New()

            conn.ConnectionString = my_connection_string
            Try
                conn.Open()
            Catch ex As Exception
                MessageBox.Show("Unable to open database. " + ex.ToString)
            End Try

            cmd = conn.CreateCommand()
            
        End If

    End Sub
    
End Class

You are doing this wrong anyway. Don't cache connection and command objects, create them when you need them **and dispose with `Using`**. See https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice. Also a messagebox inside a constructor is pretty surprising, you would expect the code *creating* the object to catch an exception. You probably also want to look at https://learn.microsoft.com/en-us/visualstudio/debugger/managing-exceptions-with-the-debugger?view=vs-2019 — Charlieface, Aug 10 '21 at 14:31
@Charlieface, note that this is a wrapper class for connections and commands. So, it is valid to store them here. However, the class should implement `IDisposable` — Olivier Jacot-Descombes, Aug 10 '21 at 14:36
@OlivierJacot-Descombes Have you ever seen a wrapper class written by a poster here that did this properly? It's almost certainly unnecessary in this case and just promotes bad practices. — Charlieface, Aug 10 '21 at 14:38
@Charlieface - the connection is used thousands of times throughout the program. Opening it and closing it then disposing it and creating a new one each time would slow the program to a halt. Keeping the connection open is the correct way to do this. The connection is only opened once throughout the life of the program. This allows for the fastest possible database interaction. — John, Aug 11 '21 at 20:27
That is absolutely and patently not true, because of connection pooling, see the link above and also https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling and https://stackoverflow.com/questions/32645554/is-the-performance-of-non-singleton-sql-connections-better. When a connection is fetched from the pool, the next packet sent has `reset_connection` bit set, so you don;t see any difference — Charlieface, Aug 11 '21 at 20:46
See also https://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 — Charlieface, Aug 11 '21 at 21:23
@John keeping one persistent connection open through the life of the application execution is absolutely NOT the correct way. — HardCode, Aug 17 '21 at 15:38

score 1 · Answer 1 · answered Aug 10 '21 at 14:29

1

Have a look at this article.

If the program is not run in debug mode, the try catch would do it's thing right away with out stopping the execution.

answered Aug 10 '21 at 14:29

AwiringCameron

620
3
18

1

I tried the information in the link and it did not change anything. I'm still not able to bypass the SQL server connection error. My error message still never shows. – John Aug 11 '21 at 20:06

Try Catch not catching Database connection error

1 Answers1