Over the past year, I have spent a lot of time in learning and doing programming with Visual Basic using Microsoft’s Visual Studio 2008. As a “junior programmer”, I have found myself spending a lot of time in searching for solutions to problems that I have encountered during the development of the program. Therefore, I would like to start documenting my solutions to the problems so that you do not need to spend as much time as I did.
Requirements
The programming language I chose to work with is Visual Basic, simply because someone told me it is easy to learn. Most programs today work with a certain database which stores all the data collected by the program. I have decided to use MySQL as my database because it is free and it provides flexibility in case I want to build a web interface on it in the future with PHP.
To allow your Visual Basic codes to talk to MySQL database, you need to install couple things.
- MySQL Community Server
- MySQL .Net Connector
You can find both items on MySQL’s website. The first one is the server providing the actual database and the second item is the .Net Connector that provides the connection machanism between a .Net program and the MySQL Server. Once you have installed both items, you need to create a database and a user on your MySQL Server, and the user should be given privileges to to the database.
Referencing MySQL Connector Library
To connect to your MySQL database from Visual Studio 2008, you need to reference the MySQL.Data library in your project.
- In Visual Studio 2008, click on “Project” from the menu. Then click on “Properties” – the last item in the drop down menu.
- On the properties screen, click on “References”.
- Click on “Add…”
- In the Add Reference dialog box, under the .Net tab, select the “MySQL.Data” component. Click “OK”
- In the list under “Imported namespaces”, make sure “MySQL.Data” is checked.
- Save your project.
Creating Connection String
The following Visual Basic codes show you how to establish the connection.
Dim DBConnect As MySqlClient.MySqlConnection
Dim connectionString As String = "Data Source=localhost; Database=DB_Name; " & _
"User ID=DB_User_Name; Password=DB_User_Pw"
DBConnect = New MySqlClient.MySqlConnection(connectionString)
DBConnect.Open()
The most important part is the construction of the connection string. It contains the information about where your database is, the name of the database, and the login credentials. These information should match with the configurations you have made when you were installing MySQL Server.
After you have successfully established the connection with the line DBConnect.Open(), you can begin creating queries as if you are connecting to a MS SQL Server, using the MySqlClient namespace instead of SqlClient namespace. When you are done querying the database, you need to close the connection with the following code.
DBConnect.close()
UTF-8 Encoding
If you plan to allow insert data that contains fonts from lanuages other than English, then you should add “CharSet=utf8″ to the end of your connection string to make sure everything is saved as UTF8 encoding.
Dim connectionString As String = "Data Source=localhost; Database=DB_Name; " & _
"User ID=DB_User_Name; Password=DB_User_Pw; CharSet=utf8"
This is extremely important if you are using different operating systems between the server (e.g. Linux) and the client (e.g. Windows).
