MySQL Connection In Windows Application Using VB.NET

Introduction

Nowadays, many people are trying to use MySQL as database because MySQL is an Open-Source Relational Database Management System which is mainly widely used in Web Applications. Here, we will go through Windows based application as an example for MySQL connection.

Using the Code

Before making any connection, we need to download the latest version of MySQL Connector from the official website of MySQL: http://dev.mysql.com/downloads/connector/ after downloading DLL files from the official site.

Step 1

Now, we have to place DLL files in our project as Reference, So to place Reference, right click on project, go through Add reference, then Select Browser button and select DLL files from where DLL files are saved directory. This Connection String has been placed in App.config file. Then, in Connecting string for DataSource as follows:

add name="Demo" connectionString="server=localhost;database=demo;uid=root;password=;" 
providerName="MySql.Data.MySqlClient"

Step 2

Now, we have to import Imports files for MySQL Client as they are as follows:

Imports MySql.Data
Imports MySql.Data.MySqlClient

Here, I will use Login for example to make connection between database and application, go through code as follows:

Public Class Login
    'MySQL
    Public conn As SqlConnection

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
        Dim a, b As String
        a = TextBox1.Text
        b = TextBox2.Text
        Dim flag As Integer = 0
            conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("Demo").ConnectionString)
            conn.Open()
            '' MySQL Commands
            Dim mySelectQuery As String = "select * from demo.login where username='" & TextBox1.Text & "' and u_password='" & TextBox2.Text & "' "
            Dim myCommand As New MySqlCommand(mySelectQuery, conn)

            Dim rd As MySqlDataReader
            rd = myCommand.ExecuteReader()

        '' Data Reading
            While (rd.Read())
            If (a = rd(0).ToString.Trim() And b = rd(1).ToString.Trim()) Then
                flag = 1
                Exit While
            Else
                flag = 0
            End If
        End While
        If (flag = 1) Then
            MsgBox("Login Sucessfully", MsgBoxStyle.OkOnly, "Done")
            Me.Hide()
            MDI.Show()
        Else
            MsgBox("Username or Password is Wrong....", MsgBoxStyle.Critical,
                   "Error")
        End If
            '' Closing Database.
            conn.Close()
        Catch ex As Exception
            MsgBox("Error Loading Database", MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

For Insert and Update as follows:

''UPDATE:
Try
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("Demo").ConnectionString)
            conn.Open()
            Dim query as String
            query = "update demo.login set <set as="" follows="" query="">
            conn.Close()
Catch ex as Exception
MsgBox("Some message as example shown above")
End Try


'' INSERT
Try
Dim rd As SqlDataReader
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("Demo").ConnectionString)
            conn.Open()
            Dim query as String
            query = "insert into demo.login values <insert>
            Dim Command As New MySqlCommand(query, con)
            Dim rd As MySqlDataReader
            rd = Command.ExecuteReader()
            MsgBox("Data saved Successfully", MsgBoxStyle.MsgBoxRight, "Done")
            conn.Close()
            Catch ex as Exception
                MsgBox("Some message goes here")
            End Try
</insert>

This topic helps you to create a connection to MySql Database with Select, Update and Insert queries.