VB/MySQL.com

Introduction

This article is fifth in a series of articles describing how to create a simple Windows application that queries and updates a MySQL database. So far we have designed our application and our database, installed MySQL and VB.NET, created our database, and created a basic application. This application is capable of querying event data from the database and updating it.

At the end of the fourth article of this series, I listed some improvements that could be made to our application. These included automatic refreshing of the data and storage of the MySQL server IP address in a configuration file.

Automating Data Refresh

One of the drawbacks of our current application is that it requires our user to manually click the Refresh button every time they want to see the latest information. This means that a change in status can go unnoticed if the user does not click the refresh button. In addition, we cannot allow for one user to set a watch for another user to login if the first user has to regularly click the Refresh button.

Well will be automating the refreshing of our status information through the use of a VB.NET Timer control. A timer fires at a configurable interval and executes a block of code. Timers are very useful in application programming and can be used for a variety of tasks.

Abstracting the Refresh Code

Before we create a Timer, we should make our refresh code more generic. Currently the code for refreshing the status data is located directly within the event handler for a click of the Refresh button. We can move this code to a function, whttp://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-5.zip
vb-mysql-tutorial-part-5.ziphere it can then be called from the click event and the timer.

The new function will contain all the code that was previously in the Refresh button click event, with one small change:

Private Sub refreshStatus(ByRef statusView As DataGridView)
        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim SQL As String

        SQL = "SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, " _
         & "DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status " _
         & "FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u " _
         & "WHERE(e.user_id = u.user_id) " _
         & "AND event_id IN( " _
         & "SELECT MAX(e.event_id) " _
         & "FROM event e " _
         & "GROUP BY e.user_id) " _
         & "ORDER BY name"

        conn.ConnectionString = myConnString

        Try
                conn.Open()

                        Try
                                myCommand.Connection = conn
                                myCommand.CommandText = SQL

                                myAdapter.SelectCommand = myCommand
                                myAdapter.Fill(myData)

                                statusView.DataSource = myData
                                dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                        Catch myerror As MySqlException
                                MsgBox("There was an error reading from the database: " & myerror.Message)
                        End Try
        Catch myerror As MySqlException
                MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
                If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
End Sub

In the declaration of the function we pass an argument named statusView that will point to the DataGridView on our form. In the code, we change the code that binds the DataGridView to bind against the statusView. This way we can change the name of the DataGridView without changing the code within the function.

Our click event is also changed to call this function:

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
     refreshStatus(dgvStatus)
End Sub

Clicking the Refresh button now calls the refreshStatus function. In addition, we can change the following line of our update code:

cmdRefresh.PerformClick()

to

refreshStatus(dgvStatus)

This is a much better approach when the same code is executed in multiple places. In addition, add the call to refreshStatus to your frmMain_load event so that the user is presented with a view of the status from the beginning.

Adding the Timer

Now that our refresh code is moved, we can call it from a timer. The Timer control is located in the toolbox:

Click and drag the timer control onto your form. Unlike other controls, the Timer control does not stay on your form, but is automatically moved to a special area below the form:

This area is reserved for controls that are not visible, such as the timer and common dialogs. Change the name of the timer to tmrRefresh using the properties window and set the Enabled property to True.

Choosing an Interval

The Interval property of the timer determines how often the timer will trigger an event. The Interval is measured in milliseconds, with 1000 milliseconds to a second. We do not want to fire this event too often, or we produce excessive load on our server. We also do not want to wait too long before firing our event, or our users may not see updated status information fast enough. Ideally we want to have an interval of about one minute, or 60,000 milliseconds. Set the Interval property of our timer to 60000.

Catching the Tick Event

When the Interval set previously is reached, the timer triggers the Tick event. We can catch the Tick event and add code to it, allowing that code to be run every time the interval is reached, or once every minute.

Double-click on the timer and the following code template is generated:

Private Sub tmrRefresh_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmrRefresh.Tick

End Sub

We can add a call to our refreshStatus function within this function to cause the status information to refresh automatically:

Private Sub tmrRefresh_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmrRefresh.Tick
        refreshStatus(dgvStatus)
End Sub

This will cause our status information to be refreshed once per minute, regardless of whether the user clicks the Refresh button or not. You can test this by launching the application, then adding a row to the event table using the MySQL Query Browser. Within one minute the status display will change.

Storing Application Settings

Now that we have added a timer to frmMain, let’s make some improvements to frmLogin. Currently, our users need to manually enter the MySQL server IP address every time they use our application. In addition, there is no option to remember a username for future use. To solve both of these problems we need a way to persist information for future use. There are a variety of options for persisting data, including INI files, the registry, and XML files.

Most of the options mentioned above require either the use of API calls or file handling code to set and retrieve data. With VB.NET 2005 a new My.Settings syntax has been added that greatly simplifies the storage and retrieval of application settings.

The new My.Settings Syntax

With My.Settings, we can application and user-level settings to our application and easily access them without using any API or file handling code. Settings are stored in XML files located either with the application executable or in the Documents and Settings/Username directory, depending on the scope of the setting. Setting can have either a User or Application scope, depending on whether an setting applies to the application in general or to a specific user.

To use My.Settings, we first create the setting within the VB.NET IDE, then access the setting through code.

Storing the Host IP with an Application Setting

The first step in using an application-level setting is to create is using the setting designer. Choose Properties from the Project menu and choose the Settings tab. A grid with available settings is displayed:

Within this grid we create a new HostIP setting as a String, with an Application scope. Set the value to the IP address of your MySQL server machine. Once your setting is in place you can close the Project Properties window. After closing the window you will be prompted to save the application settings file, click Yes and you will be returned to the form designer.

The following is added to the App.Config file:

<applicationSettings>
    <In_Out.MySettings>
      <setting name="HostIP" serializeAs="String">
        <value>192.168.1.10</value>
      </setting>
    </In_Out.MySettings>
  </applicationSettings>

This file will be compiled into a file named in-out.exe.config file when you build your project, and will be located in the bin folder of your project.

Accessing the Host IP Information in frmLogin

After the HostIP setting is stored we can access it from within our code. First change the form design by deleting the controls related to the server IP address:

Once the form is redesigned, we can modify the login code, specifically the connection string::

SQL = "server="  &  My.Settings.HostIP  &  ";" _
                  &  "user id="  &  txtUsername.Text  &  ";" _
                  &  "password="  &  txtPassword.Text  &  ";" _
                  &  "database=in_out"

Our login code will now use the stored IP address when connecting to the server.

Storing the User Name with a User Setting

Now that our host IP is taken care of, let’s look at optionally storing the username to save time for our users. We can store the username in a User scope setting, which will allow it to be read and written to a file in the Documents and Settings directory (Application scope settings are read-only though the My.Settings interface). One advantage of this approach is that even when our application is used with multiple Windows user accounts, each user can have their username stored without overwriting the previous user’s information.

We create a User scope setting with the same interface as we used to create a Application scope setting. When creating the UserName setting, create it as a string, set a User scope, and leave the Value blank.

The Settings section of the App.Config file will look like this:

<applicationSettings>
    <In_Out.MySettings>
      <setting name="HostIP" serializeAs="String">
        <value>192.168.1.10</value>
      </setting>
    </In_Out.MySettings>
  </applicationSettings>
  <userSettings>
    <In_Out.MySettings>
      <setting name="Username" serializeAs="String">
        <value />
      </setting>
    </In_Out.MySettings>
  </userSettings>

Accessing the User Name in frmLogin

As an added convenience to our user, let’s allow the username to be optionally stored by means of a checkbox. This will save time when the user logs in. First lets redesign our login form and add a CheckBox control:

Name the CheckBox chkRemember and leave it unchecked by default (set the text property ‘Remember Me’).

First let’s add code to the form_load event to check if we have a saved username:

If My.Settings.Username <> "" Then
        txtUsername.Text = My.Settings.Username
        chkRemember.Checked = True
End If

This code checks for a blank username. If the Username setting is not blank, it fills the txtUsername Text property and checks chkRemember.

Next we edit the login code for the form. We only save the username if chkRemember is checked and the username is successfully used to login. If chkRemember is not checked, we blank out the Username setting:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand

        Dim myConnString As String
        Dim UserID As Integer

        myConnString = "server=" & My.Settings.HostIP & ";" _
         & "user id=" & txtUsername.Text & ";" _
         & "password=" & txtPassword.Text & ";" _
         & "database=in_out"

        conn.ConnectionString = myConnString

        Try
                conn.Open()

                myCommand.Connection = conn
                myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username"
                myCommand.Parameters.Add("?Username", txtUsername.Text)

                UserID = myCommand.ExecuteScalar

                conn.Close()

                If chkRemember.Checked Then
                        My.Settings.Username = txtUsername.Text
                        My.Settings.Save()
                Else
                        My.Settings.Username = ""
                End If

                Dim mainForm As New frmMain
                mainForm.UserID = UserID
                mainForm.connectionString = myConnString
                mainForm.Show()

                Me.Hide()
                Me.Close()
        Catch myerror As MySqlException
                MessageBox.Show("Error Connecting to Database: " & myerror.Message)
                conn.Dispose()
        End Try
End Sub

Our frmLogin class should now look like this:

Public Class frmLogin
        Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
                Application.Exit()
        End Sub

        Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand

                Dim myConnString As String
                Dim UserID As Integer

                myConnString = "server=" & My.Settings.HostIP & ";" _
                 & "user id=" & txtUsername.Text & ";" _
                 & "password=" & txtPassword.Text & ";" _
                 & "database=in_out"

                conn.ConnectionString = myConnString

                Try
                        conn.Open()

                        myCommand.Connection = conn
                        myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username"
                        myCommand.Parameters.Add("?Username", txtUsername.Text)

                        UserID = myCommand.ExecuteScalar

                        conn.Close()

                        If chkRemember.Checked Then
                                My.Settings.Username = txtUsername.Text
                                My.Settings.Save()
                        Else
                                My.Settings.Username = ""
                        End If

                        Dim mainForm As New frmMain
                        mainForm.UserID = UserID
                        mainForm.connectionString = myConnString
                        mainForm.Show()

                        Me.Hide()
                        Me.Close()
                Catch myerror As MySqlException
                        MessageBox.Show("Error Connecting to Database: " & myerror.Message)
                        conn.Dispose()
                End Try
        End Sub

        Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
                Me.AcceptButton = cmdLogin
                Me.CancelButton = cmdCancel

                If My.Settings.Username <> "" Then
                        txtUsername.Text = My.Settings.Username
                        chkRemember.Checked = True
                End If
        End Sub
End Class

You can test our setting code by running the application, checking the Remember Me box, and logging in. Close the application and restart it, your username should be present.

Hiding the Password

One last tweak we can make before leaving frmLogin has to do with txtPassword. At present, any password entered is in plain sight, and could be read by an observer of the user.

To hide the text of a textbox, add the following line to the form_load event:

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.AcceptButton = cmdLogin
        Me.CancelButton = cmdCancel

        If My.Settings.Username <> "" Then
                txtUsername.Text = My.Settings.Username
                chkRemember.Checked = True
        End If

                txtPassword.PasswordChar = "*"
End Sub

All text entered is replaced with *, preventing casual observers from seeing sensitive information.

Conclusion

In this article we have tweaked our application to improve its ease of use. Specifically, we have added a Timer control to frmMain to allow for automatic refreshing of event data, and made use of the My.Settings system to store IP and username information. Username information is stored in a file that is unique to each Windows user account, allowing multiple users to store username information without overwriting each other.

In our next article we will improve the event display and generation code to allow for the creation of custom status messages and to support blank status messages. We will also explore the creation of custom classes and data binding to an object.

An archive of the code so far can be found at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-5.zip.