DBA > Articles

Bound and Unbound DataGridView Control for MySQL 5.0/VB.NET 2008 Windows Applications

By: Ernest Bonat
To read more DBA articles, visit http://dba.fyicenter.com/article/

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()
'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
     Cursor = Cursors.WaitCursor
        Using BoundObject As New UnboundClass(mMySQLConnectionString)
           Call BoundObject.BoundDataLoading(UnboundDataGridView, _
                                             RecordCountTextBox, _
           If Not IsNothing(mErrorMsgString) Then
              Cursor = Cursors.Default
              MessageBox.Show(mErrorMsgString, _
                              Me.Text, _
                              MessageBoxButtons.OK, _
           End If
        End Using
     Catch exError As Exception
        MessageBox.Show(exError.Message, _
                        Me.Text, _
                        MessageBoxButtons.OK, _
     End Try
     Cursor = Cursors.Default
     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
      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
       Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
          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
                   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`()
   SELECT `id`, `column2`, `column3`,`column4`, `column5`, 
          `column6`, `column7`,  `column8`, `column9`, `column10`
   FROM `test`
   ORDER BY `id`;

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/