Using MySql with VB.NET

0
640
Visual Basic and MySql

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.

  1. Public db As Object ' make an object to delay definition
  2. Public dbServer As String
  3. Public dbDatabase As String
  4. Public dbPort As String
  5. Public dbUsername As String
  6. Public dbPassword As String
  7.  
  8. Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
  9.     Dim strConnect As String
  10.     Dim sql As String
  11.     Dim cmd As MySqlCommand
  12.     Dim rdr As MySqlDataReader
  13.     Dim dt As String
  14.     Dim tm As String
  15.     Dim aStrTime() As String
  16.     Dim aStrDate() As String
  17.  
  18.     dbServer = comboServer.Text
  19.     dbDatabase = comboDatabase.Text
  20.     dbPort = comboPort.Text
  21.     dbUsername = txtUsername.Text
  22.     dbPassword = txtPassword.Text
  23.  
  24.     Try
  25.         ' create database reference on the fly using user specified data
  26.         strConnect = "Server=" + dbServer + ";Database=" + dbDatabase + ";" + "Port=" + dbPort + ";User=" + dbUsername + ";Password=" + dbPassword
  27.         db = New MySqlConnection(strConnect)
  28.  
  29.         ' open must be called immediately after to catch exception in case of improper connection data from user
  30.         ' if we place this in the loadalldata method the exception isn't caught here. Open checks for username/password validation.
  31.         db.Open()
  32.  
  33.         ' get all meta data from our table
  34.         sql = "select * from meta"
  35.         cmd = New MySqlCommand(sql, db)
  36.         rdr = cmd.ExecuteReader()
  37.         rdr.Read()
  38.         dbDescription = rdr.GetString(0)
  39.         dbSymbol = rdr.GetString(1)
  40.         dbTimeFrame = rdr.GetString(2)
  41.         dbLastUpdateDate = rdr.GetMySqlDateTime(3)
  42.         dbLastUpdateTime = rdr.GetString(4)
  43.         rdr.Close()
  44.  
  45.         ' We want to let the user know the entire time slice available and represent that in the datetimepicker controls
  46.         ' Get the first record
  47.         sql = "select barDate, barTime from bars order by id limit 1"
  48.         cmd = New MySqlCommand(sql, db)
  49.         rdr = cmd.ExecuteReader()
  50.         rdr.Read()
  51.         dt = rdr.GetMySqlDateTime(0)  ' puts this in mm/dd/yyyy format
  52.         tm = rdr.GetString(1)         ' puts this as hh:mm
  53.         aStrDate = dt.Split("/")
  54.         aStrTime = tm.Split(":")
  55.         timeSliceStart = New DateTime(aStrDate(2), aStrDate(0), aStrDate(1), aStrTime(0), aStrTime(1), 0)
  56.         rdr.Close()
  57.  
  58.         ' Get the last record
  59.         sql = "select barDate, barTime from bars order by id desc limit 1"
  60.         cmd = New MySqlCommand(sql, db)
  61.         rdr = cmd.ExecuteReader()
  62.         rdr.Read()
  63.         dt = rdr.GetMySqlDateTime(0)  ' puts this in mm/dd/yyyy format
  64.         tm = rdr.GetString(1)         ' puts this as hh:mm
  65.         aStrDate = dt.Split("/")
  66.         aStrTime = tm.Split(":")
  67.         timeSliceEnd = New DateTime(aStrDate(2), aStrDate(0), aStrDate(1), aStrTime(0), aStrTime(1), 0)
  68.         rdr.Close()
  69.  
  70.     Catch ex As MySql.Data.MySqlClient.MySqlException
  71.         MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Database Connection Error")
  72.         Exit Sub
  73.     End Try
  74.  
  75. 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:

  1. strConnect = "Server=" + dbServer + ";Database=" + dbDatabase + ";" + "Port=" + dbPort + ";User=" + dbUsername + ";Password=" + dbPassword
  2. db = New MySqlConnection(strConnect)
  3.  
  4. ' open must be called immediately after to catch exception in case of improper connection data from user
  5. db.Open()

Getting Number Of Records

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

  1. sql = "select id, barDate, barTime, open, high, low, close, adjustedClose, COUNT(*) from bars where barDate >= '" + strStartDate + "' and barDate <= '" + strEndDate + "' order by id"
  2.  
  3. cmd = New MySqlCommand(sql, db)
  4. rdr = cmd.ExecuteReader()
  5. rdr.Read()
  6. barCount = rdr.GetUInt32(8)
  7. 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:

  1. Public barId() As Int32
  2. Public barDate() As String
  3. Public barTime() As String
  4. Public barOpen() As Double
  5. Public barHigh() As Double
  6. Public barLow() As Double
  7. Public barClose() As Double

and then REDIM them using the barCount I obtained above:

  1. ReDim barId(barCount - 1)
  2. ReDim barDate(barCount - 1)
  3. ReDim barTime(barCount - 1)
  4. ReDim barOpen(barCount - 1)
  5. ReDim barHigh(barCount - 1)
  6. ReDim barLow(barCount - 1)
  7. 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.

  1. ' execution is much faster if you do not use the rdr.GetString("fieldname") format
  2. ' always use the integer format (i.e. 0, 1, 2... N)
  3. startTime = Now()
  4. While rdr.Read()
  5.     barId(i) = rdr.GetInt32(0)
  6.     barDate(i) = rdr.GetMySqlDateTime(1)  'mm/dd/yyyy and not necessarly fixed 6/21/1999 (missing 06)
  7.     barTime(i) = rdr.GetString(2)
  8.     barOpen(i) = rdr.GetFloat(3)
  9.     barHigh(i) = rdr.GetFloat(4)
  10.     barLow(i) = rdr.GetFloat(5)
  11.     barClose(i) = rdr.GetFloat(6)
  12.     barAdjustedClose(i) = rdr.GetFloat(7)
  13.     i = i + 1
  14.     If (i Mod notifyCount = 0L) Then
  15.         Application.DoEvents()
  16.         lblBars.Text = "Loading bar " + CStr(i) + " out of " + CStr(barCount)
  17.     End If
  18. End While
  19. endTime = Now()
  20. elapsedTime = endTime.Subtract(startTime)
  21. lblBars.Text = CStr(barCount) + " bars in " + elapsedTime.TotalSeconds.ToString("0.000") + "secs"
  22. 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.

  1. Private Sub btnDisconnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisconnect.Click
  2.    Try
  3.        db.Close()
  4.    Catch ex As MySql.Data.MySqlClient.MySqlException
  5.        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Database Close Error")
  6.        Exit Sub
  7.    End Try
  8. End Sub

Of Interest