Introduction
Understanding and programming MySQL data load into the DataGridView control is a must for Windows Application Developers today. Many times, in real production environment the end-users need to see a result set of data to make daily business decisions. The main question is about how many records they need to see when the control gets loaded. The answer of the question is defined by the client's business rules. At this point application performance is a critical issue for Application Developers. In this paper I'll show you how load MySQL data into the DataGridView control using VB.NET bound and unbound programming modes. A timer class named Stopwatch for VB.NET will be used to measure the execution time between theses two programming modes.
Required Software
MySQL Database Server 5.0.51b
MySQL Connector/NET 5.2.2
Toad for MySQL Freeware 3.1.1
Microsoft Visual Basic 2008 Express Edition
Bound and Unbound Programming Modes
Bound and unbound programming modes are defined based on how the controls are linked to the database table. A control is called bound if some of his database properties are linked directly to a column(s) in a table. This control displays the contents of the column and when the user edits the data in the control, it overwrites the data in the table. An unbound control is not linked to a column in a table. Data load and update for this control is done by the code behind written by the Application Developers. Before Microsoft .NET framework appear, Application Developers tried to develop Windows Client/Server applications using bound controls in Visual Basic 6.0. The data load and update using this programming mode was a very slow and unstable process. Because of that we, Application Developers, decided to develop Client/Server applications in unbound mode. Of course, the development time of these applications increased, but they did run fastest and stable. It was, for us, the only choice at this time. With Visual Studio .NET (2002, 2003, 2005 and 2008) Microsoft continues improving bound Windows controls. One of these controls is the DataGridView control.This control represents a data table view with standard structure of columns and rows. The DataGridView control can be used to load and edit the data at the same time. In this paper, it will be used to show data load techniques only. For some reasons I have not seen many Windows applications developed with editable DataGridView control. Before start looking at the VB.NET programming code, let's look at the code timer class named Stopwatch.
Stopwatch - A Code Timer Class
Stopwatch represents a high-resolution code timer class for VB.NET using Kernel32 API functions. The main objective of this class is for precisely measuring how long specific operations take to complete. Because one of my topics in this paper is to compare application performance between bound and unbound modes I have decided to use this class. The Kernel32 API function QueryPerformanceCounter() contains an excellent finer-grained tick counter. This timer boasts near microsecond (0.000001) resolution and is easily callable from any VB.NET Windows applications. Listing 1 shows how to use the Stopwatch object class swatch to measure how long a VB.NET code will run. As you can see a new single instant is started and stopped, and the elapsed time gives us the duration to run the VB.NET code.
Private mTimeDouble As Double
Private swatch As New Stopwatch()
swatch.Reset()
swatch.Start()
'VB.NET code swatch.Stop()
mTimeDouble = swatch.ElapsedMilliseconds * 0.001
Listing 1: Stopwatch object class swatch implementation in VB.NET code
Let's look at the loading process of MySQL data into the DataGirdView control using VB.NET bound and unbound programming modes.
Bound DataGridView Data Load
To illustrate how to load MySQL data into the DataGridView control a simple VB.NET 2008 project was developed (Figure 1). As you can see, the form contains a DataGridView control with ten columns and two groups of bound and unbound data load buttons (Load 1 and Load 2). Both groups show the execution time in seconds (s) for 20,000 records of MySQL data load.
Let's look at the code of the buttons Load 1 (bound mode) and Load 2 (unbound mode). Listing 2 shows the click event code of the bound Load 1 button. The Using statement creates and destroys the instance of a class UnboundClass (Listing 3). The ADO.NET Connection String mMySQLConnectionString has been passed as a constructor to the class. Just a reminder that this Connection String value is defined and stored in the app.config file as I explained in the article "Define and Store MySQL ADO Connection String in VB.NET 2005". The procedure BoundDataLoading() (Listing 4) is called to load the data. The DataGridView UnboundDataGridView and the TexBox RecordCountTextBox are passed by reference. These procedures is showing below.
Private Sub BoundLoadButton_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs)
Handles BoundLoadButton.Click
swatch.Reset()
swatch.Start()
Cursor = Cursors.WaitCursor
Try
Using BoundObject As New UnboundClass(mMySQLConnectionString)
Call BoundObject.BoundDataLoading(UnboundDataGridView, _
RecordCountTextBox, _
mErrorMsgString)
If Not IsNothing(mErrorMsgString) Then
Cursor = Cursors.Default
MessageBox.Show(mErrorMsgString, _
Me.Text, _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
End If
End Using
Catch exError As Exception
MessageBox.Show(exError.Message, _
Me.Text, _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
End Try
Cursor = Cursors.Default
swatch.Stop()
mTimeDouble = swatch.ElapsedMilliseconds * 0.001
BoundTimeTextBox.Text = mTimeDouble.ToString
End Sub
Listing 2: Click event of the bound Load 1 button
Imports MySql.Data.MySqlClient
Namespace UnboundLibrary
Public Class UnboundClass
Inherits ObjectDisposeClass
Private mDataView As DataView
Private mObjectjValue As Object
Private mMySQLConnectionString As String
Public Sub New(ByVal pMySQLConnectionString As String)
mMySQLConnectionString = pMySQLConnectionString
End Sub
End Class
#Region "IDisposable Object ..."
Public Class ObjectDisposeClass
Implements IDisposable
Private disposedValue As Boolean = False
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
' TODO: free unmanaged resources when explicitly called
End If
' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
End Class
#End Region
End Namespace
Listing 3: Class UnboundClass with IDisposable implementation
The procedure BoundDataLoading() shown in Listing 4 load the data into the DataGridView UnboundDataGridView with the DataView object mDataView. This object contains the data defined in the user stored procedure `usp_test_select_all` (Listing 5). This stored procedure selects all columns and data from table `test` (Listing 6), and was developed and tested using Toad for MySQL 3.1.1 freeware version from Quest Software, Inc. The table `test` gets loaded with data from the Excel spreadsheet by using the Import Wizard.
The Using statement was applied four times for the following ADO.NET objects: MySqlConnection, MySqlCommand, MySqlDataAdapter and DataSet. This statement allows automatically creating and destroying these unmanaged resources without declaring and initializing these objects. Let's find out how the DataGridView UnboundDataGridView gets formatted and loaded with data.
Public Sub BoundDataLoading(ByRef pDataGridViewControl As DataGridView, _
ByRef pTextBoxRecordCount As TextBox, _
ByRef pErrorMessageString As String)
Dim RecordCountInt32 As Int32
Try
Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
mMySqlConnection.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection = mMySqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_test_select_all"
End With
Using mMySqlDataAdapter As New MySqlDataAdapter(mMySqlCommand)
Using mDataSet As New DataSet
mDataSet.Clear()
mMySqlDataAdapter.Fill(mDataSet, "test")
mDataView = New DataView
mDataView.Table = mDataSet.Tables("test")
End Using
End Using
End Using
End Using
RecordCountInt32 = mDataView.Count
pTextBoxRecordCount.Text = FormatNumber(RecordCountInt32.ToString, 0)
Call DataGridViewBound(pDataGridViewControl)
pDataGridViewControl.DataSource = mDataView
Catch exError As Exception
pErrorMessageString = exError.Message
End Try
End Sub
Listing 4: Procedure BoundDataLoading() for data loading into the DataGridView UnboundDataGridView
DROP PROCEDURE IF EXISTS `vwww`.`usp_test_select_all`;
CREATE PROCEDURE `usp_test_select_all`()
BEGIN
SELECT `id`, `column2`, `column3`,`column4`, `column5`,
`column6`, `column7`, `column8`, `column9`, `column10`
FROM `test`
ORDER BY `id`;
END;
Full article...
