Online all

Let me preface this by saying I have no hate for VB.Net. When used properly, it is just as good as C#. However, VB as a whole has gotten a bad rep because generally the people that flock to it are the ones with no formal programming experience. Thus, they don’t really always know what they are doing. I’ve seen it a lot. I will also admit that I have done a lot of these horrendous things that I should have never done. In fact, this post is about something I did that turned out way bad when we fast forward a few years later (yes, it took that long for me to find this problem.)

Here is the problem and the solution I came up with over two years ago. We have a very large MDI application that has a lot of different forms. We didn’t want users opening up multiple copies of those forms, plus we wanted a neat way to bring certain forms to the foreground if they were already open.

My simple and what I thought was ingenious solution was to create a few functions that just check to see if the form is open… Something like this:

Public Function IsFormOpen(ByVal oFormName As Form) As Boolean
Dim bAlreadyShown As Boolean = False
Dim obj As Form
For Each obj In Me.MdiChildren
If obj.Name = oFormName.Name Then
bAlreadyShown = True
End If
Next
Return bAlreadyShown
End Function

When I wrote that and it worked, it made sense to me that it would just pass in the type and I could get the name of the type and it would match and the world would be happy…

Now fast forward 2 years and we are getting some complaints of the application/whole computer being slow after running for a while. This smelled of memory leaks (which we have had lots of in the past). So I started doing my memory leak testing and now I am seeing all of our forms still being held open when they should be closed!

So turning off just my code debugging and putting a breakpoint in InitializeComponent() told me a big huge story. A story of how VB “helps” a developer by just doing things for them without them knowing. It seems that before going into the IsFormOpen function call, VB creates an object of that form type and passes in that object. That object never gets destroyed, either. Dispose on that object only gets called when the application exits. Nice, huh? So every time we open a form, we actually are creating two and only opening one. One simple change fixed it:

Public Function IsFormOpen(ByVal oFormName As System.Type) As Boolean
Dim bAlreadyShown As Boolean = False
Dim obj As Form
For Each obj In Me.MdiChildren
If obj.Name = oFormName.Name Then
bAlreadyShown = True
End If
Next
Return bAlreadyShown
End Function

Incidentally, C# wouldn’t allow the first function to happen. It won’t even compile in any way, shape, or form (at least in the way I was trying to use it). It tells me the obvious, I was trying to use a Type as a variable. VB “helps” you with that…

One of the biggest causes of memory leaks that I find from time to time is from forgetting to dispose of some SQLConnection object. Even if the connection is closed, the SQLConnection object seems to stay alive forever, which keeps any form objects alive as well.

This happens in VB a lot if you forget to call the Dispose method of the SQLConnection object or if you don’t use the Using keyword. While cleaning up code, the best way to get in the habit of doing it, seems to be changing everything into a Using statement so I get in the habit of using it…
Instead of:
view plaincopy to clipboardprint?

1. Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. myConnection.Close()
7. myConnection.Dispose()
8. myCommand.Dispose()

Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myConnection.Close()
myConnection.Dispose()
myCommand.Dispose()

Do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Using myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. End Using
7. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Using myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
End Using
End Using

Since the IDisposable interface on the SQLConnection object will automatically close open connections, there is no need to close it… However, if you decide to do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
2. , New SqlClient.SqlConnection(ConnectionString))
3. myCommand.Connection.Open()
4. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
5. myCommand.Connection.Close()
6. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
, New SqlClient.SqlConnection(ConnectionString))
myCommand.Connection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myCommand.Connection.Close()
End Using

Make sure you close the connection first… Although, I don’t recommend doing it that way, because the IDisposable interface of the SQLCommand object does not call the IDisposable interface of the SqlConnection object that it holds. I’ve read that all the SqlConnection object’s IDisposable interface does is close an open connection, I wouldn’t trust that completely and dispose of it properly.

I don’t know how a lot of people handle it, but one thing that always bugged me about table adapters and datasets is the way it handles it’s connection strings. It starts out innocent enough. A new dataset is created with it’s associated table adapter. The connection string is saved to the application settings file and that property is saved in the dataset. Running it on the development machine (or on the same network) is no big deal and just works. But what if you send that application to someone else that has their own SQL Server?

Did anyone at Microsoft actually use this scenario in a production environment? What were they thinking?

In order to run that in a production environment you have to set the connection string in the app.config file. While this may be fine for some people, what about the people like me that do not want my users to access that database? I don’t want them to have that username and password… While the chances of a normal user loading SQL Server Management Studio and logging in are slim, it’s still possible and it’s definitely possible if a user purposely wants to get out of having to do work that day.

What are the options? Well, one option is to use the encryption to encrypt the settings in app.config. For me, this option is not ideal. Reports of those settings getting corrupted are quite high, plus you have to deal with the loading and saving of those settings, which isn’t all that easy to do.

The other option is to take the route I was taking for a while… I had a function that would build me a connection string. Then I could: TableAdapter.Connection.ConnectionString = myLibrary.ConnectionStringFunction()

This was great, until that day came where I was in a hurry and added a few more tables to a form but forgot to set the ConnectionStrings. Whoops.

So I needed a solution that would stop me from having to set those ConnectionString properties, keep my connection string out of the app.config, and be easy to use (i.e. Just Works).

I started out by just giving all my datasets the same connection string. Then on application startup, I tried to change that one application setting. Hmm.. It seems those ConnectionString properties are set to friend and are read only.

Upon further investigation, it seems that there are some events that fire, such as SettingsLoaded. This event fires when the app.config is read and all the settings are loaded. When this event fires, it fires inside the MySettings class. This should allow that property to be changed.
view plaincopy to clipboardprint?

1. Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
2. Me.Item(“MyAppConnectionString”) = MyLibrary.BuildConnectionString()
3. End Sub

Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
Me.Item(“MyAppConnectionString”) = MyLibrary.BuildConnectionString()
End Sub

This will set the MyAppConnectionString setting to the proper connection string. Now, all table adapters will have an up to date connection string.

So what happens if you want to change the connection string later while the application is still running? Well, there is no way to do that. So it’s time to come up with a way to trick it into updating that property.

In looking at the MySettings class, there is another event called PropertyChanged. We can use this event if we create another setting that can be updated anywhere in the application. First, we create a new string setting that has a User scope (I called mine ConnectionString). This will allow the application to update the setting at any time.

Next, we need to create a function that will update that property with our connection string.
view plaincopy to clipboardprint?

1. Public Shared Sub ChangeConnectionString()
2. My.Settings.ConnectionString = BuildConnectionString()
3. End Sub

Public Shared Sub ChangeConnectionString()
My.Settings.ConnectionString = BuildConnectionString()
End Sub

Now we can change the events in the MySettings class to look like this.
view plaincopy to clipboardprint?

1. Private Sub MySettings_PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Handles Me.PropertyChanged
2. If e.PropertyName = “ConnectionString” Then
3. Me.Item(“MyAppConnectionString”) = My.Settings.ConnectionString
4. End If
5. End Sub
6.
7. Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
8. MyLibrary.ChangeConnectionString()
9. End Sub
10. End Class

Private Sub MySettings_PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Handles Me.PropertyChanged
If e.PropertyName = “ConnectionString” Then
Me.Item(“MyAppConnectionString”) = My.Settings.ConnectionString
End If
End Sub

Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
MyLibrary.ChangeConnectionString()
End Sub
End Class

Now, every time that ChangeConnectionString() is called, the MyAppConnectionString will be updated. The ChangeConnectionString procedure can be changed so that it can accept a string parameter that is the actual connection string. Then you can build a Connection String anywhere and just pass it to that procedure.