Development

Using MySql with VB.NET

July 17, 2016

787

Every now and then when I have a sudden burst of programming inspiration I reach inside my programming toolbox and pull out an old trusty friend – Visual Basic. Over the years I’ve appreciated the benefits of using VB. One of them is not having to spend countless days creating a Windows GUI application the old fashioned way by using C/C++. WndProc, GetMessage, and RegisterClass are things I used to do a decade ago as a Windows developer and it was a real pain.

Microsoft has graciously offered a free version of VB called Visual Basic Expression Edition. This product has some limitations but as far as I am concerned, its a nice little utility to prototype Windows applications quickly.

So here you sit with Visual Basic Express and Mysql installed on your development computer. The question of the day is, “How am I going to connect to Mysql from VB?”

Good question.

Database Drivers Everywhere!

In order to get connected, you need a database driver. Most database vendors offer C/C++ APIs to get to their databases at the lowest level. Some also offer JDBC drivers for the Java folks. For the web development community there maybe PHP, Ruby, and Perl drivers.

In the Windows world, there are ODBC and ADO.NET drivers. For Visual Basic developers, we need to use the ADO.NET driver. Mysql has made available the Mysql Connector. So go to the website and choose the appropriate version for your Mysql database and download it and install on your development machine.

The MySql Connector

Now its time to reference the driver from your project. We need to do this so that VB Express will be able to use it.

Go create a new VB project and click on the Project | Properties Menu. Along the left side is a series of tabs and one of them is labeled “References”. Click on that and then the little Add… button. In the .NET tab, look for the “MySQL.Data”.

While we are at it, lets import the Mysql namespace into our application as well. In the Imported Namespaces list box, select the Mysql, Mysql.Data, Mysql.Data.MysqlClient, and Mysql.Data.Types items.

The MySql driver is now available.

Connecting To Mysql from VB.NET

If you were the clever type and tried to fiddle with the VB Express database settings to get Mysql working you were likely stumped. In the Express editions, 3rd party database drivers are prohibited as Microsoft wants you to use their Access and SQL server products.

You’ll see this if you clicked on the View | Database Explorer or Data | Show Data Sources menus. MySql won’t be listed. This basically means that we won’t be able to modify the database, its tables, the data within it either. Bummer. But we can always use Mysql utilities anyway.

Still, this should not let us down. We can get around this limitation in a non-visual way by doing this by manual programming.

Here’s a snippet of code from my stock quotes program that has a button and two DatePicker controls on a form that connects to a Mysql database.

Public db As Object ' make an object to delay definition

```
Public dbServer As String
```
```
Public dbDatabase As String
```
```
Public dbPort As String
```
```
Public dbUsername As String
```
```
Public dbPassword As String
```
```
 
```

Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click

```
    Dim strConnect As String
```
```
    Dim sql As String
```
```
    Dim cmd As MySqlCommand
```
```
    Dim rdr As MySqlDataReader
```
```
    Dim dt As String
```
```
    Dim tm As String
```
```
    Dim aStrTime() As String
```
```
    Dim aStrDate() As String
```
```
 
```
```
    dbServer = comboServer.Text
```
```
    dbDatabase = comboDatabase.Text
```
```
    dbPort = comboPort.Text
```
```
    dbUsername = txtUsername.Text
```
```
    dbPassword = txtPassword.Text
```
```
 
```
```
    Try
```

        ' create database reference on the fly using user specified data

        strConnect = "Server=" + dbServer + ";Database=" + dbDatabase + ";" + "Port=" + dbPort + ";User=" + dbUsername + ";Password=" + dbPassword

        db = New MySqlConnection(strConnect)

```
 
```

        ' open must be called immediately after to catch exception in case of improper connection data from user

        ' if we place this in the loadalldata method the exception isn't caught here. Open checks for username/password validation.

```
        db.Open()
```
```
 
```

        ' get all meta data from our table

```
        sql = "select * from meta"
```
```
        cmd = New MySqlCommand(sql, db)
```
```
        rdr = cmd.ExecuteReader()
```
```
        rdr.Read()
```

        dbDescription = rdr.GetString(0)

```
        dbSymbol = rdr.GetString(1)
```
```
        dbTimeFrame = rdr.GetString(2)
```

        dbLastUpdateDate = rdr.GetMySqlDateTime(3)

        dbLastUpdateTime = rdr.GetString(4)

```
        rdr.Close()
```
```
 
```

        ' We want to let the user know the entire time slice available and represent that in the datetimepicker controls

```
        ' Get the first record
```

        sql = "select barDate, barTime from bars order by id limit 1"

```
        cmd = New MySqlCommand(sql, db)
```
```
        rdr = cmd.ExecuteReader()
```
```
        rdr.Read()
```

        dt = rdr.GetMySqlDateTime(0)  ' puts this in mm/dd/yyyy format

        tm = rdr.GetString(1)         ' puts this as hh:mm

```
        aStrDate = dt.Split("/")
```
```
        aStrTime = tm.Split(":")
```

        timeSliceStart = New DateTime(aStrDate(2), aStrDate(0), aStrDate(1), aStrTime(0), aStrTime(1), 0)

```
        rdr.Close()
```
```
 
```
```
        ' Get the last record
```

        sql = "select barDate, barTime from bars order by id desc limit 1"

```
        cmd = New MySqlCommand(sql, db)
```
```
        rdr = cmd.ExecuteReader()
```
```
        rdr.Read()
```

        dt = rdr.GetMySqlDateTime(0)  ' puts this in mm/dd/yyyy format

        tm = rdr.GetString(1)         ' puts this as hh:mm

```
        aStrDate = dt.Split("/")
```
```
        aStrTime = tm.Split(":")
```

        timeSliceEnd = New DateTime(aStrDate(2), aStrDate(0), aStrDate(1), aStrTime(0), aStrTime(1), 0)

```
        rdr.Close()
```
```
 
```

    Catch ex As MySql.Data.MySqlClient.MySqlException

        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Database Connection Error")

```
        Exit Sub
```
```
    End Try
```
```
 
```
```
End Sub
```

There’s a lot of things going on above so lets just focus on the code that deals with the database connection. I have a login group on the form that allows the user to type in the server, database name, username, password, and port. When the user clicks on the Connect button, I create the connection string on the fly using this information and then call the connector to create a database connection and open it immediately:

strConnect = "Server=" + dbServer + ";Database=" + dbDatabase + ";" + "Port=" + dbPort + ";User=" + dbUsername + ";Password=" + dbPassword

```
db = New MySqlConnection(strConnect)
```
```
 
```

' open must be called immediately after to catch exception in case of improper connection data from user

```
db.Open()
```

Getting Number Of Records

If you want to get the number of records returned on a SELECT statement do the following:

sql = "select id, barDate, barTime, open, high, low, close, adjustedClose, COUNT(*) from bars where barDate >= '" + strStartDate + "' and barDate <= '" + strEndDate + "' order by id"

```
 
```
```
cmd = New MySqlCommand(sql, db)
```
```
rdr = cmd.ExecuteReader()
```
```
rdr.Read()
```
```
barCount = rdr.GetUInt32(8)
```
```
rdr.Close()
```

Notice COUNT(*) is used as a column to count the number of records. To get the sum of the count, we read the 8th position (positions start from 0) in the the SELECT statement and let the Mysql connector read the data as a 32-bit unsigned int. We immediately close the command execution.

Dynamically Creating Your Data Sets

In my stock program, I have arrays to store the date, time, open, high, low, and close data. For speed reasons, I choose to load the entire quote database into memory. But I don’t know the size of the data set to create my arrays.

The solution to this problem is to create array references:

```
Public barId() As Int32
```
```
Public barDate() As String
```
```
Public barTime() As String
```
```
Public barOpen() As Double
```
```
Public barHigh() As Double
```
```
Public barLow() As Double
```
```
Public barClose() As Double
```

and then REDIM them using the barCount I obtained above:

```
ReDim barId(barCount - 1)
```
```
ReDim barDate(barCount - 1)
```
```
ReDim barTime(barCount - 1)
```
```
ReDim barOpen(barCount - 1)
```
```
ReDim barHigh(barCount - 1)
```
```
ReDim barLow(barCount - 1)
```
```
ReDim barClose(barCount - 1)
```

Filling The Data Arrays

Now that I allocated space for these objects in memory, I can now read from the Mysql database.

' execution is much faster if you do not use the rdr.GetString("fieldname") format

' always use the integer format (i.e. 0, 1, 2... N)

```
startTime = Now()
```
```
While rdr.Read()
```
```
    barId(i) = rdr.GetInt32(0)
```

    barDate(i) = rdr.GetMySqlDateTime(1)  'mm/dd/yyyy and not necessarly fixed 6/21/1999 (missing 06)

```
    barTime(i) = rdr.GetString(2)
```
```
    barOpen(i) = rdr.GetFloat(3)
```
```
    barHigh(i) = rdr.GetFloat(4)
```
```
    barLow(i) = rdr.GetFloat(5)
```
```
    barClose(i) = rdr.GetFloat(6)
```

    barAdjustedClose(i) = rdr.GetFloat(7)

```
    i = i + 1
```
```
    If (i Mod notifyCount = 0L) Then
```
```
        Application.DoEvents()
```

        lblBars.Text = "Loading bar " + CStr(i) + " out of " + CStr(barCount)

```
    End If
```
```
End While
```
```
endTime = Now()
```

elapsedTime = endTime.Subtract(startTime)

lblBars.Text = CStr(barCount) + " bars in " + elapsedTime.TotalSeconds.ToString("0.000") + "secs"

```
rdr.Close()
```

First notice there is code to time the execution. I use the DateTime type to store the start and end time. Also note that there are two ways to get column data. The first is using the integer column and the other is referencing by string name. I have found that by using the integer column notation, I get a much faster time for reading database records.

Closing The Database

Lastly, always close your database connection. Here I have a disconnect button on the form that when clicked closes the database.

Private Sub btnDisconnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisconnect.Click

```
   Try
```
```
       db.Close()
```

   Catch ex As MySql.Data.MySqlClient.MySqlException

       MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Database Close Error")

```
       Exit Sub
```
```
   End Try
```
```
End Sub
```

Of Interest

MySQL Cookbook: Solutions for Database Developers and Administrators	MySQL (5th Edition) (Developer’s Library)
Learning Visual Basic .Net	Programming VB .NET: A Guide for Experienced Programmers