- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 1: Introduction
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 2: Setup MySQL Server
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 3: Install Sample Database
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 4: Create & Grant MySQL User Account
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 5: Install MySQL Connector Net
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 6: Create Connection
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 7: Perform SQL Operations
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 8: Display Result on GUI
After I have prepared many things for showing how to access MySQL Server using VB.NET. Let’s see what I have done so far. Right now, I have a remote MySQL Server at 192.168.125.21 with port 3306 (Default port). Also, a sample database “world” and a user account “worldUser” with password “worldpassword”. Now it’s time to start Microsoft Visual Studio 2005 on a development PC.
- On Development PC, open Microsoft Visual Studio 2005.
- Create a New Windows Application Project “SampleMySQL”.
- First, I need to add a MySQL library. Right-click on the project name (SampleMySQL) -> Add Reference.
- On Add Reference, select MySQL.Data on .NET tab.
- By default, the reference library (MySQL.Data) won’t be copied to the output directory. That means when you deploy the application on other PC which doesn’t have the library installed, it’ll throw error. So I have to set the Copy Local property of the library file to True. Click Show All Files icon.
- Expand References -> Select MySQL.Data -> Change Copy Local property to True.
- Now it’s time to coding the application. First, I have to import a namespace. Open the Code View and add this line on the top.
- Add these code to the Class.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load TestConnection() End Sub Public Sub TestConnection() Try Dim connStr As String = "Database=world;" & _ "Data Source=192.168.125.21;" & _ "User Id=worldUser;Password=worldpassword" Dim connection As New MySqlConnection(connStr) connection.Open() connection.Close() MsgBox("Connection is okay.") Catch ex As Exception MsgBox(ex.Message) End Try End Sub
- Line 1-4: Simple Form_Load event that call TestConnection() method. The method is invoked when the form is loaded.
- Line: 7-17: Try-Catch scope. If there is any error in try scope, throws exception and goes to catch scope.
- Line: 8-10: A connection string represents configuration for connect to MySQL Server. Common attributes are:
- Database. The database to be used after a connection is opened.
- Data Source. The name of the MySQL server to which to connect.
- Port. The port MySQL is using to listen for connections. The default value is 3306
- User ID. The user that use to connect to the database on MySQL Server
- Password. Password of the user.
- Connection Timeout. Time to wait while trying to establish a connection before terminating the attempt and generating an error.
- Line 11: Create MySqlConnection object and assign connectionString property.
- Line 12-13: Test open and close the connection to the database on MySQL Server.
- Line 14: If there is no error, show a success message.
- Line 16: Show the error message.
- Next, test the code by run the application. If the connection is successfully connected and disconnected. You’ll see the message “Connection is okay”.
- If something wrongs, you’ll see message other than the previous step. The figure below is the example that mistyped the database name in the connection string.