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