[RESOLVED] Load Event Aborting
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:
Code:
Private Sub AllDone()
Timer1.Enabled = False
frmMain.Show()
Me.Close()
End Sub
Here's the relevant code in the main form:
Code:
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:
Code:
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?
Re: [RESOLVED] Load Event Aborting
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.
Re: [RESOLVED] Load Event Aborting
Quote:
Originally Posted by
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.
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:
Code:
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:
Code:
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, ..."
Re: [RESOLVED] Load Event Aborting
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.
Re: [RESOLVED] Load Event Aborting
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.
Re: [RESOLVED] Load Event Aborting
Quote:
Originally Posted by
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.
Thanks again, Shaggy Hiker.