Create Connection

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.

You can see index of this series at Accessing MySQL on VB.NET using MySQL Connector/Net, Part I: Introduction

Create Connection

  1. On Development PC, open Microsoft Visual Studio 2005.
    Open Microsoft Visual Studio 2005
  2. Create a New Windows Application Project “SampleMySQL”.
    New Windows Application
  3. First, I need to add a MySQL library. Right-click on the project name (SampleMySQL) -> Add Reference.
    Add Reference
  4. On Add Reference, select MySQL.Data on .NET tab.
    MySQL.Data
  5. 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.
    Show All Files
  6. Expand References -> Select MySQL.Data -> Change Copy Local property to True.
    Set Copy Local to True
  7. 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. 
    Imports MySql.Data.MySqlClient

    Imports MySql.Data

  8. 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

    Code Explanation:

    • 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:
      1. Database. The database to be used after a connection is opened.
      2. Data Source. The name of the MySQL server to which to connect.
      3. Port. The port MySQL is using to listen for connections. The default value is 3306
      4. User ID. The user that use to connect to the database on MySQL Server
      5. Password. Password of the user.
      6. 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.

  9. Next, test the code by run the application. If the connection is successfully connected and disconnected. You’ll see the message “Connection is okay”.
    Successfully Connected
  10. 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.
    Unsuccessful Connection
Share and Enjoy:
  • Digg
  • del.icio.us
  • Reddit
  • Slashdot
  • Technorati
  • Google Bookmarks
  • Live
  • MSN Reporter
  • RSS
  • Twitter
  • email
  • Facebook
  • Netvibes
  • PDF
  • Yahoo! Buzz

Related post