Connect to MySQL Server Using VB.Net

To connect to the MySQL Server using VB.Net, you need to install MySQL .Net Connector or a MySQL ODBC driver. Add reference to the MySQL .Net Connector, then use the following code to connect.

Using ODBC Driver

Imports Microsoft.VisualBasic
Imports System.Data.Odbc
Imports System.Data

Public Class Server
    Private MySQLDataReader As OdbcDataReader
    Private ConnectionString As String = "Driver={MySQL ODBC 5.1 Driver}; Server=localhost;     
                    User=user; Password=password; Database=database; Option=3;"
    
    Protected Function ExecuteQuery(Byval Query As String) As DataTable
        'Reference: http://msdn.microsoft.com/en-us/library/ms998569.aspx
        'When Using DataReaders, Specify CommandBehavior.CloseConnection
        Dim dReturnValue As New DataTable

        Try
            'Open and Close the Connection in the Method. See Reference.
            Dim MySQLAdapter As New OdbcDataAdapter(Query, ConnectionString)

            'Explicitly Close Connections. See Reference.
            MySQLAdapter.SelectCommand.Connection.Close()

            'Do Not Explicitly Open a Connection if You Use Fill or Update for a Single Operation
            MySQLAdapter.Fill(dReturnValue)
        Catch ex As Exception
        End Try

        Return dReturnValue
    End Function

    Protected Function ExecuteNonQuery(Byval Query As String) As Boolean
        Dim MySQLConnection As New OdbcConnection(ConnectionString)
        Try
            'Open and Close the Connection in the Method. See Reference.
            MySQLConnection.Open()
            Dim MySQLCommand As New OdbcCommand(Query, MySQLConnection)
            MySQLCommand.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        Finally
            'Open and Close the Connection in the Method. See Reference.
            MySQLConnection.Close()
        End Try
    End Function
End Class

In the ConnectionString, replace the Server, Username, Password and Database with the actual value. In my example, my Server is localhost, Username is username, Password is password and Database is database.

To get data from the database, use the ExecuteQuery function. This will return a DataTable. To insert, update, delete or any query that requires data manipulation, use the ExecuteNonQuery function.


Using .Net Connector

Imports MySql.Data.MySqlClient

Public Class Server
    Private Shared Connection As New MySqlConnection
    Private Shared Connected As Boolean

    Public Shared Function Login(ByVal Server As String, ByVal User As String, ByVal Password As String, ByVal Database As String, Optional ByVal Port As Integer = 3306) As Boolean
        Connection.ConnectionString = String.Format("server={0}; user id={1}; password={2}; pooling=false; database={3}; port={4}", Server, User, Password, Database, Port)
        Try
            Connection.Open()
            Connected = True
            Return True
        Catch ex As MySqlException
            Connected = False
            Return False
        End Try
    End Function

    Public Shared Sub Logout()
        Try
            Connected = False
            Connection.Close()
        Catch ex As Exception
        End Try
    End Sub

    Public Shared ReadOnly Property IsConnected() As Boolean
        Get
            Return Connected
        End Get
    End Property

    ''' 

    ''' Executes a query (INSERT, UPDATE, DELETE).
    ''' 
    ''' SQL Command (INSERT, UPDATE, DELETE)    ''' Returns true if command is executed successfully
    ''' 
    Public Shared Function ExecuteNonQuery(ByVal Command As String) As Boolean
        Try
            Dim SQLCommand As New MySqlCommand(Command, Connection)
            SQLCommand.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function

    ''' 

    ''' Returns the results of a query in Datatable.
    ''' 
    ''' SQL query    ''' Returns a datatable containing the data (SELECT)
    Public Shared Function ExecuteQuery(ByVal Query As String) As DataTable
        Dim Data As New DataTable
        Try
            Dim dAdapter As New MySqlDataAdapter(Query, Connection)
            Dim CommandBuilder As New MySqlCommandBuilder(dAdapter)
            dAdapter.Fill(Data)
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        End Try
        Return Data
    End Function
End Class

The code for the .Net Connector is similar to the ODBC Driver, except that you have to call the Login function before calling the ExecuteQuery and ExecuteNonQuery. The Login function will return true if it was able to connect to the server. You can also check the IsConnected property to check if the program was able to connect to the database.