Connecting to MySQL databases using VB.NET

Introduction

Connecting to a MySQL database from Visual Studio 2012 using a wizard might be a bit tricky, because MySQL is not supported by default (like SQL Server). With this tip, I will show you how to connect to a MySQL database and run commands (select, update, delete) using VB.NET and I will also show you how to import MySQL connectors to Visual Studio 2012.

Using the Code

1. You need to download the mysqlconnect (mysql-connector-net) from the MySQL website and add the DLL files to Visual Studio, the website is http://dev.mysql.com/downloads/connector/. Extract the download file, for example, in C:\ and go to --> Visual Studio -->
2. Create a new project in VS2012 and name it whatever you like:

   

3. Go to project --> Add reference.

   

   

4. Select the DLL files from your folder and add them to your project.
5. Create a new VB class, name it mysqldb as follows:

   

6. Now you are ready to use the code of the class that I have created to connect to the MySQL database.  The code for this class is attached with this tip here, or from mySqlDB.zip.
7. Now you can copy the methods from my class or add this class to your project directly, and then use the code in the project, it's up to you. In both cases, you will have a class that connects to a MySQL database and does select, update, delete operations.
8. I will add a simple GridView to my project and try to get data from the database using very few lines of code.
   VB.NET

   Dim mydb As New mySqlDB
   
   Protected Sub Page_Load(ByVal sender As Object, _
             ByVal e As System.EventArgs) Handles Me.Load
       Try
           Dim dataset As New DataSet
           Dim queryresult As String = ""
           dataset = mydb.executeSQL_dset("SELECT COMMAND", queryresult)
           GridView1.DataSource = dataset
           GridView1.DataBind()
       Catch ex As Exception
   
       End Try
   End Sub 

   When you run the above code, you populate the data from the database in a dataset using the executeSQL_dset function. This function will return a dataset that you can then use anywhere in your project.

   To run an update or delete command, there is another method called:

   VB.NET

   Dim dataset As New DataSet
   Dim queryresult As String = ""
   mydb.executeDMLSQL("update or delete SQL command", queryresult)
   If queryresult = "SUCCESS" Then
       'your command is ok
   Else
       'your command is not ok
   End If

Hope this was helpful.

MySQL is very small and very easy to download, and it's free (open source). Hopefully with the above class, you can connect and run (select, update, delete) commands.

I shall try in another post to explain the class in detail in case you want to modify it and make changes.