[RESOLVED] Load Event Aborting

[silverblatt]

In a VB.Net 2017 Windows form application, so far I have only a splash form and a main form. When the user clicks a button on the splash form or a ten second timer fires (whichever occurs first) the splash form runs the AllDone() sub, which opens the main form and then closes itself. Here's the relevant code in the splash form:

Private Sub AllDone()
    Timer1.Enabled = False
    frmMain.Show()
    Me.Close()
End Sub

Here's the relevant code in the main form:

Imports System.Data.SqlClient
...
Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'display version information
    Me.Text = "Referral Tracking - Version " & My.Application.Info.Version.ToString
    'open database connection
    Conn.ConnectionString = ConnString
    Conn.Open()
    MsgBox("Connection State: " & Conn.State)
End Sub

Conn and ConnString are defined in a separate module as follows:

Imports System.Data.SqlClient
...
Public Conn As SqlConnection = New SqlConnection
Public ConnString As String = "[actual connection string]"

I've used this approach successfully in other projects. But in this particular project, it doesn't work. When frmMain_Load() runs, only its first two statements execute, then execution returns to AllDone() and the remaining two statements in frmMain_Load() never execute. No error is raised and the connection is not actually opened.

What am I doing wrong?

[TysonLPrice]

Have you stepped through the code? It seems like something like that would jump right out at you.

[silverblatt]

Have you stepped through the code? It seems like something like that would jump right out at you.

Yes, I have. That's how I determined which statements were executing and which were not.

[HardCode.NET]

This seems like an X/Y problem. You shouldn't be opening a public connection at application startup and leaving it open for the life of the application's runtime. You should instantiate and open a connection just before you're ready to use it, and then close it after the query executes. Preferably, it would be declared with a Using statement:

    Using conn As New SqlConnection(myConnectionString),
            cmd As New SqlCommand("MyStoredProcedureName", conn)

            With cmd

                .CommandType = CommandType.StoredProcedure

                ' Parameters

                conn.Open()
                .ExecuteNonQuery()

            End With

        End Using

[si_the_geek]

In some circumstances errors that occur in the Load event get swallowed up, try moving the code to the Shown event instead, as that doesn't have the same issue.

[dday9]

In some circumstances errors that occur in the Load event get swallowed up, try moving the code to the Shown event instead, as that doesn't have the same issue.

Yeah, it is documented here: https://docs.microsoft.com/en-us/pre...vs.85)#remarks. Notifications generated by the 64-bit window manager (e.g. Load) silently ignore exceptions, but I want to say that this was resolved in Windows 8 and I want to say that it only happens when a debugger is attached.

Could you compile the application and run the executable from the bin file to see if any exception is thrown?

P.S. - I would say that the code that is currently in the Load event should probably be moved to the constructor.

[silverblatt]

Thanks si_the_geek and dday9. Moving the code to the Shown event revealed a run-time error (an issue with the actual connection string) that was in fact being swallowed when it happened in the Load event. This is definitely a new one on me.

[silverblatt]

This seems like an X/Y problem. You shouldn't be opening a public connection at application startup and leaving it open for the life of the application's runtime. You should instantiate and open a connection just before you're ready to use it, and then close it after the query executes. Preferably, it would be declared with a Using statement:

    Using conn As New SqlConnection(myConnectionString),
            cmd As New SqlCommand("MyStoredProcedureName", conn)

            With cmd

                .CommandType = CommandType.StoredProcedure

                ' Parameters

                conn.Open()
                .ExecuteNonQuery()

            End With

        End Using

Not sure what you mean by an "X/Y problem." The connection needs to be used by different forms at different times, which is why I leave it open for the life of the application - constantly opening and closing what is essentially the same connection at multiple places in multiple forms and modules produces cumbersome and verbose code. In any event, it turned out that an error in the connection string was raising an exception, and certain exceptions get swallowed silently in the Load event (this was a new wrinkle for me). Moving the code to the Shown event revealed the exception and made the solution obvious.

[Shaggy Hiker]

Thanks si_the_geek and dday9. Moving the code to the Shown event revealed a run-time error (an issue with the actual connection string) that was in fact being swallowed when it happened in the Load event. This is definitely a new one on me.

It's sort of a new one on me, too. We used to have that answer a few times a month, but that was years ago. I thought this one had gone away, so I'm a bit surprised to see it pop up, again. Still, if I remember it right, this was ultimately due to a disagreement between two groups within MS as to what the best approach was to a certain problem, and those types of discussions have a tendency to linger without resolution.

Also, HardCode has a good point. Don't leave a connection open for a long time. You might expect that what was shown in that snippet is inefficient, since it is opening and closing the same connection multiple times. That's not the case, though. I did a bit of profiling on this one to convince myself that there is no cost, and there is not. There IS a cost of leaving a connection open if there could be others using the same SQL Server database, as you'll end up with more total connections. If that isn't a consideration, then still do it the way HardCode showed, as there's just no real cost to that...except that some exception handling should be used whenever you work with a database connection. Queries can be...exceptional.

[HardCode.NET]

Not sure what you mean by an "X/Y problem." The connection needs to be used by different forms at different times, which is why I leave it open for the life of the application - constantly opening and closing what is essentially the same connection at multiple places in multiple forms and modules produces cumbersome and verbose code. In any event, it turned out that an error in the connection string was raising an exception, and certain exceptions get swallowed silently in the Load event (this was a new wrinkle for me). Moving the code to the Shown event revealed the exception and made the solution obvious.

Maybe not exactly an X/Y problem, but, the code that shouldn't have been in Form_Load... but was and was swallowing an exception because of the .NET bug... would have thrown if it was in the widely-accepted place (declared and opened around the query, then disposed). With Connection Pooling, you're not saving anything by keeping one connection open for the life of the application runtime, but that is mostly off-topic from this post.

[silverblatt]

Thanks for the additional information, Shaggy Hiker. I get that there is a cost to leaving a connection open (versus closing it immediately after each operation that uses it) and that minimizing that cost CAN be an important consideration. However, it's not the only consideration. There's also a cost associated with code that's unnecessarily repetitive and cumbersome - that cost may include increased coding effort and decreased efficiency, especially when reading and maintaining code you haven't looked at in a while, if ever. IMHO, design choices need to be guided by an evaluation of the relative costs and benefits of different approaches, in light of the particular requirements, operating environment, usage patterns and likely maintenance profile of the application. With a relatively few exceptions, I'm very skeptical of rules or design practices which assert that a particular task should ALWAYS be done in a particular way (the exception that does come to mind is that one should always maintain sufficient backups, whatever sufficient means in the particular context).

In this case, I'm re-writing an existing VB6 application. Its database sees very light usage (typically just single CRUD operations) by a handful of users, with even report queries executing quickly, all running on a fast new server. Given that, and the fact that some OTHER programmer will have to maintain the new application after my impending retirement, I'm doing my best to make the code as lean and clean as possible, even if that results in a tiny performance hit that neither the users nor the DBA will ever even notice, let alone care about. Part of my perspective on this question is a huge legacy application I support, with literally hundreds of users pounding on it all day, and various batch processes and report queries running at unpredictable times. That application runs fine, even though each user's main database connection stays open from the time the user launches the application until the time they exit from it.

Of course, if the usage were heavier and/or the queries more demanding, either at present or in the foreseeable future, I'd take a different approach and do what I could to wring every last bit of performance out of the application (including closing connections immediately after every database access), even if that resulted in more bloated code. In the end, I think one needs to choose what's most appropriate to the particular application, rather than blindly following a list of design patterns to the letter in every case.

Thanks for your response.

[silverblatt]

Indeed, having the code in the Load event was the cause of the problem. I'd never seen that issue before, but hey ... live and learn.

[dday9]

Thanks for the additional information, Shaggy Hiker. I get that there is a cost to leaving a connection open (versus closing it immediately after each operation that uses it) and that minimizing that cost CAN be an important consideration. However, it's not the only consideration. There's also a cost associated with code that's unnecessarily repetitive and cumbersome - that cost may include increased coding effort and decreased efficiency, especially when reading and maintaining code you haven't looked at in a while, if ever. IMHO, design choices need to be guided by an evaluation of the relative costs and benefits of different approaches, in light of the particular requirements, operating environment, usage patterns and likely maintenance profile of the application. With a relatively few exceptions, I'm very skeptical of rules or design practices which assert that a particular task should ALWAYS be done in a particular way (the exception that does come to mind is that one should always maintain sufficient backups, whatever sufficient means in the particular context).

In this case, I'm re-writing an existing VB6 application. Its database sees very light usage (typically just single CRUD operations) by a handful of users, with even report queries executing quickly, all running on a fast new server. Given that, and the fact that some OTHER programmer will have to maintain the new application after my impending retirement, I'm doing my best to make the code as lean and clean as possible, even if that results in a tiny performance hit that neither the users nor the DBA will ever even notice, let alone care about. Part of my perspective on this question is a huge legacy application I support, with literally hundreds of users pounding on it all day, and various batch processes and report queries running at unpredictable times. That application runs fine, even though each user's main database connection stays open from the time the user launches the application until the time they exit from it.

Of course, if the usage were heavier and/or the queries more demanding, either at present or in the foreseeable future, I'd take a different approach and do what I could to wring every last bit of performance out of the application (including closing connections immediately after every database access), even if that resulted in more bloated code. In the end, I think one needs to choose what's most appropriate to the particular application, rather than blindly following a list of design patterns to the letter in every case.

Thanks for your response.

I think that's a perfectly valid case. However, if that's the case then I'd suggest abandoning using ADO.NET objects directly and instead use a ORM like Entity Framework, PetaPoco, or Dapper.

My personal experience is with PetaPoco and instead of running something like this:

Using cmd As New SqlCommand("my insert statement here", MyModule.Conn)
    ' parameterized query and what have you here
    cmd.ExecuteNonQuery()
End Using

You'd run:

Dim insertion As PocoRepresentationOfTable = _database.Insert(record)

It makes it much more obvious as to what you are doing which in turn allows for a developer to come behind you and say "oh it looks like the database is inserting the values in this class to the database" instead of "ok, it looks like a command was created, it is running an insert statement with these specific fields, it is (or isn't) parameterized, ..."

[silverblatt]

Thanks, dday9. The PetaPoco example you gave does seem both clear and concise. I'm not sure if I'll have the opportunity to use any ORMs in my remaining time in this shop, but it's good to know about.

[Shaggy Hiker]

I used to handle some DB connections that way when I was writing in VB6. I was only using an Access DB, and there was expected to be only one user at a time. It made more sense, in that situation.

As you have noted, it often doesn't hurt all that much, unless you exceed the consecutive connections limit, which you sound like you are not likely to.

I've switched to opening, getting in, getting out, and let the End Using clean things up (whether or not an exception is thrown, the connection is properly dispensed with, as needed). I have no idea how many concurrent connections I'll end up with, or how many others are hitting the same SQL Server. My point, though, is that there is no time cost to reopening the connection each time, as one might naturally expect. The connection pooling really is that good.

So, do as you wish, but know that there isn't a time cost to doing that reopening. Also, the cost in writing that turns out to be pretty insignificant, too, because you write it once, then copy and paste.

[silverblatt]

I used to handle some DB connections that way when I was writing in VB6. I was only using an Access DB, and there was expected to be only one user at a time. It made more sense, in that situation.

As you have noted, it often doesn't hurt all that much, unless you exceed the consecutive connections limit, which you sound like you are not likely to.

I've switched to opening, getting in, getting out, and let the End Using clean things up (whether or not an exception is thrown, the connection is properly dispensed with, as needed). I have no idea how many concurrent connections I'll end up with, or how many others are hitting the same SQL Server. My point, though, is that there is no time cost to reopening the connection each time, as one might naturally expect. The connection pooling really is that good.

So, do as you wish, but know that there isn't a time cost to doing that reopening. Also, the cost in writing that turns out to be pretty insignificant, too, because you write it once, then copy and paste.

Thanks again, Shaggy Hiker.