VB.NET to MySQL Database Connection

Introduction

I'm currently developing an inventory management system which caters to multiple warehouses. I will share with you some tips and tricks in the development of this program. The system is still under development.

The database I use is MySQL Server 5.0.x together with the MySQL .NET Connector. Firstly, I'll share a few tricks about connecting to MySQL. Discussions on saving, querying, and other useful tricks will follow later on.

Using the code

To be able to create this program, you must have basic knowledge concerning MySQL 5.0 as well as VB.NET.

Prerequisites

• MySQL Server 5.0
• MySQL .NET Connector 5.1
• VB.NET Express edition/ VB.NET Family

Steps

1. Install MySQL Server 5.0 and and MySQL .NET Connector.
2. Import a reference of the MySQL >NET Conenctor in Project Settings.
3. Go to Settings and type the following settings:

Name         Type      Scope   Value
myDB ->      String -> User -> DatabaseName
myServer->   String -> User -> Servername
myUsername-> String -> User -> DbUserName
myPassword-> String -> User -> DbPassword
myPort->     String -> User -> mySQLPort(3306)



4. Change the values in your MySQL database settings. The purpose of changing the setting is that if the MySQL database settings will change, e.g., the server is migrated, you can easily change the server settings and you won't have to rebuild the project.
5. Add a Module in your project and name it mdlDataConn6. Paste the following code in your Module:
VB

Imports MySql.Data.MySqlClient

Module mdlDataConn
    Public conn As New MySqlConnection

    Public Sub ConnectDatabase()

        Try
            If conn.State = ConnectionState.Closed Then
                conn.ConnectionString = "DATABASE=" & My.Settings.myDB & ";" _
                  & "SERVER=" & My.Settings.myServer & ";user id=" & My.Settings.myUsername _
                  & ";password=" & My.Settings.myPassword &";port=" & _
                  My.Settings.myPort & ";charset=utf8"
                conn.Open()
            End If

        Catch myerror As Exception
            MessageBox.Show("Error Connecting to the database", "Error Database Server", _
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End
        End Try
    End Sub

    Public Sub DisconnectDatabase()
        Try
            conn.Close()
        Catch myerror As MySql.Data.MySqlClient.MySqlException

        End Try
    End Sub
End Module
'To connect to mySQL Database just call.
ConnectDatabase

'To close the mySQL Database
DisconnectDatabase

That's all!