VBMySQL.com

Introduction

This article is part three of a series of articles made to document the creation of Windows® applications using Visual Basic.NET and MySQL. In the previous articles, I have described how to design an application and database, install MySQL and the MySQL Query Browser, and how to populate our database using the Script Editor feature of the MySQL Query Browser.

In this article, I will be describing the how to install Microsoft Visual Basic 2005 Express Edition, the MySQL Administrator, and MySQL Connector/NET. We will use these tools to create a login form for our tracking application.

Designing a Login System

There are multiple options available when working on a login system, but typically it comes down to one of two options:

1. Build your own login system. Store usernames and passwords in a MySQL table, and use a single user account for all instances of the application that connect to MySQL. Each instance of the application will connect to one server account, then validate the user against the tables you created.
2. Use MySQL’s built-in security tables. Create MySQL users for each account, and let MySQL handle authentication and privilege management.

These approaches both have their respective benefits and drawbacks. When you build your own login system, you potentially have greater control over users, groups, and privileges. You have simpler permission management because only one native MySQL user account is needed. On the other hand, there is more potential for security problems because something you create yourself does not have the review process seen in the MySQL server. In addition, you have a security weakness in the fact that every client installation has a copy of the central user account for connecting to MySQL, and that account has to have the maximum permissions needed for an administrative user. These concerns are not so great when you are developing a web application, because it is easier to manage the one web instance than a collection of desktop applications.

When you use the security built-into MySQL, you inherit a robust privilege management system that has been thoroughly reviewed and tested. You can limit privileges on a fine-grained level, and even assign different permissions based on which host the user connects from. The tradeoff is one of complexity: making the most of the built-in MySQL authentication is more difficult than building one of your own, and you must add a user to the MySQL server every time you want to add a new user to your application.

Because this is not a web-based application, we will be building our application using the built-in MySQL privilege system. In order to create a native MySQL user we will first install MySQL Administrator; a GUI application for server management.

Using MySQL Administrator

The MySQL Administrator is a GUI tool provided by MySQL AB for managing your MySQL server. The MySQL Administrator can be used to manage users, change server configuration, manage server databases, and monitor server status.

Downloading and Installing MySQL Administrator

The MySQL Administrator can be downloaded from http://dev.mysql.com/downloads/administrator/1.0.html. In our case we want the Windows version that includes an installer, avoid the ‘Without Installer’ version. Save the installer file to your hard-drive and double click the installer icon to begin the installation. The installer is a standard Windows installer and should not require any special steps.

Connecting to the MySQL Server

After the install is complete, there should be a MySQL Administrator icon on your desktop. Double-click the MySQL Administrator icon to display the login form.You can also start the MySQL Administrator by clicking Start > Programs > MySQL > MySQL Administrator.

The login form for the MySQL Administrator is almost identical to the login form for the MySQL Query Browser, with the exception being that MySQL Administrator does not require you to specify a default schema. Enter your root login information and click the OK button to start the MySQL Administrator.

Adding A User

Click the User Administration option from the left menu panel to bring up the user administration screen.

Click the New User button to create your new user account. For the MySQL User setting use the username value you set in your user row in the last tutorial. Set a password and set any Additional Information you want to specify (all information in the Additional Information section is optional).

Once you have set a username and password, click the Schema Privileges tab. Click the in_out database and then the << button to assign all available privileges for the database to your user (we will refine the privilege list in the future). Click the Apply Changes button to create the new user. You can now exit the MySQL Administrator.

Connector/NET

In early 2004 MySQL AB hired Reggie Burnett of ByteFX and acquired his ByteFX .NET data provider for MySQL. The provider was renamed Connector/NET and not only is it provided free under the terms of the GNU Public License, but it is one of the most feature-rich and best performing .NET providers for MySQL that is currently available. Connector/NET is written in C# and is completely managed code, allowing it to be ported to any platform that supports .NET, including Mono. One advantage Connector/NET provides over other solutions is its use of the native MySQL protocol: many other solutions wrap the MySQL C client library and suffer a performance loss as a result.

Downloading and Installing Connector/NET

MySQL Connector/NET is available for download at http://dev.mysql.com/downloads/connector/net/. Download the version that includes an installer to your local hard-drive and extract the Zip file.

Double-click the installer file to begin the installation process. Perform a complete install to the default directory.

Visual Basic.NET

Visual Basic.NET is the new version of Visual Basic. While it shares the Visual Basic name, there are significant differences between Visual Basic 6 and Visual Basic.NET. VB.NET is now entering into its third version. The first version was Visual Basic.NET, the second was Visual Basic.NET 2003, and the new version is Visual Basic.NET 2005. Visual Basic.NET 2005 introduces a new Express version that we will use in this tutorial.

The Express version of Visual Basic.NET is essentially a stripped-down version that still retains all the functionality needed to produce basic applications (no pun intended).

Downloading and Installing Visual Basic.NET 2005 Express Edition

VB.NET 2005 Express Edition is available for download at http://msdn.microsoft.com/vstudio/express/vb/. Look for a link to a Download Now link.

Once you have downloaded the installer, double-click the installer to being the installation process. The default installation options should be sufficient.

Starting Visual Basic.NET

Once you have installed VB.NET 2005, look for a link in the Programs section of your Start menu named Visual Basic 2005 Express Edition Beta. Start VB.NET and click Ctrl + N to start a new project.

Enter a name for the project and choose the Windows Application template. Click the OK button to create the project.

The Main VB.NET Window

Once your project is created you should see a window similar to this one:

On the left is the toolbox. You can drag items from the toolbox to add them to your application. In the center is the workspace, showing your first form in design mode. On the right is the Solution Explorer, which shows all the files involved in our project.

Naming The Default Form

First right-click on the Form1.vb object in the Solution Explorer and choose the Properties option. This will display a properties dialog where we can rename the file.

The properties tool is used to set various properties for the objects we will deal with as we work on our project. In this case we want to change the File Name property to more accurately reflect the purpose of the form. I will be prefixing the names of my forms with a frm prefix. Do not change the extension of the form file.

Once you change the file name, click the form in the workspace. The properties tool will change to reflect the properties of the form itself. Scroll down until you find the Text property and set it to something appropriate. In my case I set this to In-Out - Login. The Text property determined what appears as the title of the form, and you can see this reflected in the workspace.

Adding a Reference

Before we can use Connector/NET with Visual Basic, we need to add a reference to our project. By adding a reference, we are telling VB.NET where to find MySQL Connector/NET in order to access the objects and methods of Connector/NET.

To add a reference, choose the Add Reference… option of the Project menu. Choose the Browse tab and browse to the Connector/NET installation, typically located at C:\Program Files\MySQL\MySQL Connector Net n.n.n\bin\.NET N.N (The path may vary depending on the version number of Connector/NET and the .NET Framework). Choose the MySql.Data.dll file and Connector/NET will be added to your project. You will also need to add a reference to System.Data.dll.

Saving the Project

Now that we have configured a few settings in our project, we should save the project before continuing. Choose the Save All option from the File menu. Because this is the first time we are saving the project, we are presented with the following dialog:

These settings should generally be acceptable, with the project being saved to a new folder within the My Documents/Visual Studio/Projects folder (In my case I use a custom path to My Documents). Click the Save button to save your project.

Designing The Login Form

To design the login form, drag objects from the toolbox onto the form. In our first version we will need to prompt users for a server address, username, and password. Click on the TextBox object from the toolbox and drag it into the form. Once placed on the form you can widen it, move it, and generally fine-tune its size and positioning. Once you are happy with the placement of the textbox, go to the properties tool and find the Name property. Change the name to better reflect the use of the textbox (in my case I will be naming it txtServer). Place two additinal textboxes and name them txtUsername and txtPassword.

While it may be obvious to you and I what the purpose of these textboxes is, the end user will probably need some help. We will use Label objects as cues to the user to indicate what each TextBox is used for. Drag the label object onto the form and place it in-line with the first textbox. Once the label is placed, find the Text property in the properties tool and set the text to something like Server:. Repeat this process two additional times for the remaining Textbox objects. Because we will not be using the labels in actual application code there is not a real need to change the default names of the labels.

The final element to add to our form will be a pair of buttons; one to start the login process and one to cancel. Drag the buttons onto the form, and set the Name property to be cmdLogin and cmdCancel. Set the Text property to be Login and Cancel, respectively.

Once the process is complete your form should look something like this:

Creating an Event

I’m going to start by creating an event for the Cancel button. An event is something that triggers execution of code within VB.NET. In this case, the event in question will be the clicking of the Cancel button. The simplest way to create a button click event is to double click the button on the form. When you double-click the cancel button, the code view of the form will be displayed and you will see the following code appear:

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

  End Sub
End Class

The Public Class line indicates that this class describes our form, and the Private Sub line shows that this is a subfunction that handles the Click event of the cmdCancel object. The two End lines show where the code for each of these sections ends.

We will add a single line to the subfunction to close the application when this button is clicked:

Application.Exit()

This line instructions the application to close, and will be executed when we click the Cancel button. Your code 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
End Class

Starting the Application

Once you have entered the code, save the project and press the F5 key to test your application. You can also choose the Start option from the Debug menu or click the green icon on the toolbar.

Your form should be displayed and if you click on the Cancel button, the form should disappear as the application is closed. If you click on the Login button nothing would happen as we have not created any code for the event of clicking on the Login button.

Importing the Connector/NET Namespace

Objects in VB.NET are organized into namespaces. Namespaces are logical grouping of objects used to help organize the various objects available in VB.NET.

To use a Connector/NET Connection object, you need to define it as MySql.Data.MySqlClient.MySqlConnection (more on this object later). This of course is a lot to type on a regular basis, and we can use the Imports statement to shorten this.

By adding Imports MySql.Data.MySqlClient to the start of the source file, we can just refer to the Connector/NET Connection object as MySqlConnection.

Adding a MySqlConnection Object

MySQL Connector/NET is essentially a collection of objects used to access a MySQL database. The first object we will use is the MySqlConnection object. The connection object serves as a broker between the other objects contained within Connector/NET and the MySQL server. The connection object handles the login process and is the object we will use to verify that a user’s login information is correct.

There are two steps to adding an object. First we declare the object, then we instanciate it. When declaring an object we assign a name that we will use to refer to it, and also indicate the scope of the object, or in other words, what functions and procedures can access the object. In our case we will want to ensure that any function or procedure within the form will have access to the connection object, so we will declare the connection object first thing within the class:

Imports MySql.Data.MySqlClient

Public Class frmLogin
 Dim conn As MySqlConnection

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

The Dim keyword is used when declaring objects and variables. I’m using conn as the name of my connection object. The As keyword is used to indicate what we are declaring (an object, a variable, etc). Finally, MySqlConnection is the object we are declaring.

Instanciating the MySqlConnection Object

Now that we have declared the connection object, we will instanciate it. Until we instanciate an object it is not actually available for use. We will instanciate the object within the subfunction that handles the click event for the Login button. Double-click the Login button in the design view to create the subfunction.

To instanciate an object, we use the New keyword:

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

Building the Connection String

The MySqlConnection object uses a connection string to know which server to connect to, which database to access, and what username and password to use to authenticate. The various properties are separated by semicolons. Here is a sample connection string:

server=localhost; user id=mike; password=12345; database=in_out

Of course, we need the connection string to reflect the information our user enters into the form. To do this we shall use the & character to combine multiple strings together, and the .Text value of the TextBox objects. Additionally I will use the _ character to split our code into multiple lines:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
  conn.ConnectionString = "server=" & txtServer.Text & ";" _
    & "user id=" & txtUsername.Text & ";" _
    & "password=" & txtPassword.Text & ";" _
    & "database=in_out"

End Sub

Opening the Connection

The last thing we need to do is instruct the connection object to open the connection to the MySQL server with the .Open() method of the connection object:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
  conn.ConnectionString = "server=" & txtServer.Text & ";" _
    & "user id=" & txtUsername.Text & ";" _
    & "password=" & txtPassword.Text & ";" _
    & "database=in_out"

  conn.Open()
End Sub

Using a MessageBox

Once we have successfully opened the connection, we will want to let the user know that their username and password were correct. We can do this with the MessageBox object. In its simplest form, a MessageBox will display a message to the user, with an OK button. Here’s a simple line of code to display a MessageBox:

MessageBox.Show("Connection Opened Successfully!")

Closing the Connection

When we are finished with our connection object we need to close it. By closing the connection we release the resources needed to keep the connection active. It is a good practice to close connections as soon as you are finished with them. The connection is closed with the .Close() method.

conn.Close()

Disposing of the MySqlConnection Object

Once we are completely finished with an object, it is a good practice to dispose of it, thus minimizing resource usage in our application. When we dispose of an object, the resources it occupied are freed and the object no longer exists. We dispose of an object by calling its .Dispose() method:

conn.Dispose()

Catching Errors

Our code is currently only appropriate for an ideal situation. If we cannot connect to the server or if we provide the wrong username or password the connection object will return an error, also known as an exception. To handle errors, VB.NET has a special TRY … CATCH … FINALLY syntax. We place the code with the potential error after the TRY keyword and before the CATCH keyword. The CATCH keyword is used to indicate what kind of error we anticipate we might encounter (in this case the error returned will be a Connector/NET MySqlException object). Any code present after the FINALLY keyword will be executed whether there is an error or not. If an exception is encountered, the remaining code in the TRY section will not be executed.

Here’s the final code for the procedure that handles the connection:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
  conn.ConnectionString = "server=" & txtServer.Text & ";" _
    & "user id=" & txtUsername.Text & ";" _
    & "password=" & txtPassword.Text & ";" _
    & "database=in_out"

  Try
    conn.Open()
    MessageBox.Show("Connection Opened Successfully")
    conn.Close()
  Catch myerror As MySqlException
    MessageBox.Show("Error Connecting to Database: " & myerror.Message)
  Finally
    conn.Dispose()
  End Try
End Sub

This brings all of our connection code together and allows it to handle errors without crashing. We instanciate the connection object and assign it a connection string. Within an error handling TRY … CATCH block we attempt to open the connection to the server and, if successful, we will show a MessageBox to the user indicating our success and then close the connection.

If an error occurs while connecting, the code in the CATCH block will be executed. In this case we will show the user a MessageBox with the .Message property of the MySqlException object, which contains the human-readable error message associated with the error.

In the FINALLY block we will dispose of the connection object. We do this in the FINALLY block because whether or not the connection succeeds we will want to dispose of the object.

Testing the Project

Once your code is in place, save the project and press the F5 key to begin the application in debug mode. Try using the proper server address, username, and password, then try using the wrong server address, then try the wrong password. You will notice different error messages for using the wrong address and for using the wrong password. The error message for using the wrong username is the same as the error message for using the wrong password, as a seperate error message would provide a security threat in that a potential attacker would know whether they had a correct username or not.

Improvements

There are a few improvements that will need to be made to our login form. First of all, our user should not be expected to enter the server address every time they want to use our application, and in the future we will cover how to store the server address in a configuration file.

The second improvement will be with regards to error handling. Showing the MySQL error message is fine during development, but the production version of our application should not show the database error messages directly. We can use the .Number property of the MySqlException object to determine what kind of error we are dealing with, and then create a custom error message.

Finally, we will of course need to eventually have a successful connection lead to a new form in the application. If our application consisted of a login form and nothing else, it would not be very popular.

Conclusion

In this tutorial we have covered installing MySQL Administrator and created a new MySQL user account. We then installed VB.NET and MySQL Connector/NET. Finally we used VB.NET to create a login form for our application that will connect us to a MySQL server (assuming we provide the correct server address and username/password). The form is configured to handle errors during the connection by using the TRY … CATCH … FINALLY syntax.

The project file created in this tutorial is available at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-3.zip. The project files are for Visual Basic 2005 Express Edition.

In the next tutorial we will create our first event and will create a form to display and update the current status of our users.