How to sum the fields in a Windows Forms DataGrid control and then display the calculated totals in a footer by using Visual Basic .NET (836672)

SUMMARY

INTRODUCTION

Requirements

• The Windows Forms DataGrid control
• Data binding by using Windows Forms and Microsoft ADO.NET
• Handling and raising events

• Microsoft Windows 2000, Microsoft Windows XP, or Microsoft Windows Server 2003
• Microsoft Visual Studio .NET

Create a Windows Control Library project

1. Start Visual Studio .NET.
2. On the File menu, point to New, and then click Project. The New Project dialog box appears.
3. Under Project Types, click Visual Basic Projects.
4. Under Templates, click Windows Control Library.
5. In the Name box, type DataGridControl, and then click OK. By default, a user control that is named UserControl1 is created.
6. On the View menu, click Solution Explorer.
7. In Solution Explorer, right-click UserControl1.vb, and then click Rename.
8. Rename the UserControl1.vb file as DataGridControlVB.vb.

Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods

1. In Solution Explorer, right-click DataGridControlVB.vb, and then click View Code.
2. Import the required namespaces, and then declare a delegate for the event that disables the cells of the DataGrid control. To do this, add the following code at the top of the code window:

   Imports System
   Imports System.Collections
   Imports System.ComponentModel
   Imports System.Drawing
   Imports System.Data
   Imports System.Data.SqlClient
   Imports System.Windows.Forms
   Imports System.Text
   
   ' Declare a delegate for the event that disables the cells of the DataGrid control.
   Public Delegate Sub DataGridDisableCellEventHandler _
      (ByVal sender As System.Object, ByVal e As DataGridDisableCellEventArgs)

3. Locate the following code:

   Public Class UserControl1
      Inherits System.Windows.Forms.UserControl

4. Make your Windows Forms DataGrid control inherit from the System.Windows.Forms.DataGrid class. To do this, replace the code that you located in step 3 with the following code:

   Public Class DataGridControlVB
      Inherits System.Windows.Forms.DataGrid

5. Add the following variable declarations after the code that you added in step 4:

      ' Declare private variables for your DataGrid control.
      Private RowCount As Integer
      Private ColCount As Integer
      Private SortedColNum As Integer
   
      Private Ascending As Boolean
      Private CellValueChanged As Boolean
   
      Private SourceTable As String
   
      Private MyDataView As DataView
      Private MyDataSet As DataSet
      Private MyDataRow As DataRow
   
      Private SummaryCols As ArrayList
      Private CurrentDataGridCellLocation As DataGridCell
   
      Private WithEvents MyDataTable As DataTable
   
      Private Shared FooterBackColor As Brush
      Private Shared FooterForeColor As Brush

6. Locate the following code comment in the "Windows Form Designer generated code" area:

   'Add any initialization after the InitializeComponent() call

7. Perform custom initialization in the constructor of your Windows Forms DataGrid control. To do this, replace the code comment that you located in step 6 with the following code:

         RowCount = 0
         ColCount = 0
   
         CellValueChanged = False
         Ascending = False
   
         MyDataRow = Nothing
         MyDataTable = New DataTable("NewTable")
         CurrentDataGridCellLocation = New DataGridCell
   
         SummaryCols = New ArrayList

8. Locate the following code:

   #End Region

9. Add the following property definitions after the code that you located in step 8:

      Public WriteOnly Property GridDataSet() As DataSet
         Set(ByVal Value As DataSet)
            MyDataSet = Value
         End Set
      End Property
   
      Public Property SummaryColumns() As ArrayList
         Get
            Return SummaryCols
         End Get
         Set(ByVal Value As ArrayList)
            SummaryCols = Value
         End Set
      End Property
   
      Public Property DataSourceTable() As String
         Get
            Return SourceTable
         End Get
         Set(ByVal Value As String)
            SourceTable = Value
         End Set
      End Property
   
      Public Shared Property FooterColor() As Brush
         Get
            Return FooterBackColor
         End Get
         Set(ByVal Value As Brush)
            FooterBackColor = Value
         End Set
      End Property
   
      Public Shared Property FooterFontColor() As Brush
         Get
            Return FooterForeColor
         End Get
         Set(ByVal Value As Brush)
            FooterForeColor = Value
         End Set
      End Property

10. Disable the default sorting feature of the DataGrid control to prevent the footer row from being sorted. To do this, and to bind the custom DataGrid control to the related data, add the following code after the code that you added in step 9:

       Public Sub BindDataGrid()
          MyDataTable = MyDataSet.Tables(0)
          MyDataView = MyDataTable.DefaultView
          Me.DataSource = MyDataView
    
          Dim TableStyle As DataGridTableStyle = New DataGridTableStyle
          TableStyle.MappingName = SourceTable
    
          ' Add a Boolean data type column to the DataTable object.
          ' You can use this column during your custom sorting.
          MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"))
          MyDataTable.Columns("ID").DefaultValue = False
          MyDataTable.Columns("ID").ColumnMapping = MappingType.Hidden
          ColCount = MyDataTable.Columns.Count
    
          ' Create a footer row for the DataTable object.
          MyDataRow = MyDataTable.NewRow()
    
          ' Set the footer value as an empty string for all columns that contains string values.
          Dim MyIterator As Integer
          For MyIterator = 0 To ColCount - 1
             If (MyDataTable.Columns(MyIterator).DataType.ToString() = "System.String") Then
                MyDataRow(MyIterator) = ""
             End If
          Next
    
          ' Add the footer row to the DataTable object.
          MyDataTable.Rows.Add(MyDataRow)
          RowCount = MyDataTable.Rows.Count
    
          ' Add a MyDataGridTextBox control to each cell of the DataGrid control.
          Dim TempDataGridTextBox As MyDataGridTextBox
          For MyIterator = 0 To ColCount - 2
             TempDataGridTextBox = New MyDataGridTextBox(MyIterator)
             TempDataGridTextBox.HeaderText = MyDataTable.Columns(MyIterator).ColumnName
             TempDataGridTextBox.MappingName = MyDataTable.Columns(MyIterator).ColumnName
             AddHandler TempDataGridTextBox.DataGridDisableCell, _
                New DataGridDisableCellEventHandler(AddressOf SetEnableValues)
    
             ' Disable the default sorting feature of the DataGrid control.
             TableStyle.AllowSorting = False
             TableStyle.GridColumnStyles.Add(TempDataGridTextBox)
          Next
    
          Me.TableStyles.Add(TableStyle)
          Me.DataSource = MyDataView
          MyDataView.ApplyDefaultSort = False
          MyDataView.AllowNew = False
    
          ' Set the value of the footer cell.
          Dim MyCell As DataGridCell = New DataGridCell
          MyCell.RowNumber = MyDataTable.Rows.Count - 1
    
          ' Calculate the value for each of the cells in the footer.
          Dim MyArray(2) As String
          Dim MyString As String
          For Each MyString In SummaryCols
             MyArray = MyString.Split(","c)
             MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
             Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").ToString()
          Next
    
       End Sub

Handle the events that are raised when a cell in the DataGrid control is changed

1. Handle the ColumnChanged event of the DataTable object. To do this, add the following code after the code that you added in step 10 of the "Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods" section:

      ' Handle the DataTable object's ColumnChanged event
      ' to track whether the value in a cell has changed.
      Private Sub MyDataTable_ColumnChanged(ByVal sender As Object, _
         ByVal e As System.Data.DataColumnChangeEventArgs) Handles MyDataTable.ColumnChanged
   
         Dim Row As Integer, Col As Integer
         Row = 0
         Col = 0
   
         ' Determine the row that contains the changed cell.
         Dim TempDataRow As DataRow
         For Each TempDataRow In MyDataTable.Rows
            If (TempDataRow.Equals(e.Row)) Then
               CurrentDataGridCellLocation.RowNumber = Row
               CellValueChanged = True
               Exit For
               Row = Row + 1
            End If
         Next
   
         ' Determine the column that contains the changed cell.
         Dim TempDataColumn As DataColumn
         For Each TempDataColumn In MyDataTable.Columns
            If (TempDataColumn.Equals(e.Column)) Then
               CurrentDataGridCellLocation.ColumnNumber = Col
               CellValueChanged = True
               Exit For
               Col = Col + 1
            End If
         Next
      End Sub

2. Handle the CurrentCellChanged event of the DataGrid control. To do this, add the following code after the code that you added in step 1:

      ' Handle the CurrentCellChanged event of the DataGrid control.
      Private Sub DataGridControlVB_CurrentCellChanged(ByVal sender As Object, _
         ByVal e As System.EventArgs) Handles MyBase.CurrentCellChanged
   
         If (CellValueChanged = True) Then
            Dim MyCell As DataGridCell = New DataGridCell
            MyCell.RowNumber = MyDataTable.Rows.Count - 1
   
            ' Calculate the value for each cell in the footer.
            Dim MyArray(2) As String
            Dim MyString As String
            For Each MyString In SummaryCols
               MyArray = MyString.Split(",")
               MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
               Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").ToString()
            Next
         End If
   
         CellValueChanged = False
      End Sub

Handle the MouseDown event of the DataGrid control to implement custom sorting

   ' Handle the MouseDown event to perform custom sorting.
   Private Sub DataGridControlVB_MouseDown(ByVal sender As Object, _
      ByVal e As System.Windows.Forms.MouseEventArgs) Handles MyBase.MouseDown

      Dim MyHitTestInfo As DataGrid.HitTestInfo
      MyHitTestInfo = Me.HitTest(e.X, e.Y)

      Dim ColName As String

      If (MyHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader) Then
         Dim ColNum As Integer = MyHitTestInfo.Column

         If (ColNum <> -1) Then
            ColName = MyDataTable.Columns(ColNum).ColumnName

            ' Perform custom sorting. To do this, always sort the Boolean data type column in
            ' ascending order so that the footer row stays at the end.
            Dim MyChar() As Char = {"?"c, "?"c}

            Dim NewString As String = _
               Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText.TrimEnd(MyChar).Trim()
            Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText = NewString

            If (Ascending = True) Then
               MyDataView.Sort = "ID Asc," + ColName + " desc"
               Ascending = False
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ?"
               SortedColNum = ColNum
            Else
               MyDataView.Sort = "ID Asc," + ColName + " asc"
               Ascending = True
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ?"
               SortedColNum = ColNum
            End If
         End If
      End If
   End Sub

Disable the footer row of the DataGrid control

   ' Disable the footer row of the DataGrid control.
   Public Sub SetEnableValues(ByVal sender As Object, ByVal e As DataGridDisableCellEventArgs)
      If (e.Row = RowCount - 1) Then
         e.EnableValue = False
      Else
         e.EnableValue = True
      End If
   End Sub

Define an event arguments class that provides data for the DataGridDisableCell event

1. In the Form1.vb file, locate the following code:

   End Class

2. Add the following code after the code that you located in step 1:

   ' Define a custom event arguments class that inherits from the EventArgs class.
   Public Class DataGridDisableCellEventArgs
      Inherits EventArgs
   
      Private MyCol As Integer
      Private MyRow As Integer
      Private MyEnableValue As Boolean
   
      Public Sub New(ByVal Row As Integer, ByVal Col As Integer)
         MyRow = Row
         MyCol = Col
         MyEnableValue = True
      End Sub
   
      Public Property Column() As Integer
         Get
            Return MyCol
         End Get
         Set(ByVal Value As Integer)
            MyCol = Value
         End Set
      End Property
   
      Public Property Row() As Integer
         Get
            Return MyRow
         End Get
         Set(ByVal Value As Integer)
            MyRow = Value
         End Set
      End Property
   
      Public Property EnableValue() As Boolean
         Get
            Return MyEnableValue
         End Get
         Set(ByVal Value As Boolean)
            MyEnableValue = Value
         End Set
      End Property
   
   End Class

Define a class that contains methods to paint and to disable the footer row

Public Class MyDataGridTextBox
   Inherits DataGridTextBoxColumn

   ' Declare an event for the DataGridDisableCellEventHandler delegate that you have defined.
   Public Event DataGridDisableCell As DataGridDisableCellEventHandler

   Private MyCol As Integer

   ' Save the column number of the column to add the MyDataGridTextBox control to.
   Public Sub New(ByVal Column As Integer)
      MyCol = Column
   End Sub

   ' Override the Paint method to set colors for the footer row.
   Protected Overloads Overrides Sub Paint(ByVal g As System.Drawing.Graphics, _
      ByVal bounds As System.Drawing.Rectangle, ByVal source As System.Windows.Forms.CurrencyManager, _
         ByVal rowNum As Integer, ByVal backBrush As System.Drawing.Brush, _
            ByVal foreBrush As System.Drawing.Brush, ByVal alignToRight As Boolean)

      ' Initialize the event arguments by using the number
      ' of the current row and the current column.
      Dim e As New DataGridDisableCellEventArgs(rowNum, MyCol)

      ' Raise the DataGridDisableCell event.
      RaiseEvent DataGridDisableCell(Me, e)

      ' Set the foreground color and the background color for the footer row.
      If Not e.EnableValue Then
         If DataGridControlVB.FooterColor Is Nothing _
            Or DataGridControlVB.FooterFontColor Is Nothing Then
            backBrush = Brushes.White
            foreBrush = Brushes.Black
         Else
            backBrush = DataGridControlVB.FooterColor
            foreBrush = DataGridControlVB.FooterFontColor
         End If
      End If

      ' Call the Paint event of the DataGridTextBoxColumn class.
      MyBase.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight)
   End Sub

   ' Override the Edit method to disable the footer row.
   Protected Overloads Overrides Sub Edit(ByVal source As System.Windows.Forms.CurrencyManager, _
      ByVal rowNum As Integer, ByVal bounds As System.Drawing.Rectangle, ByVal readOnlyFlag As Boolean, _
         ByVal instantText As String, ByVal cellIsVisible As Boolean)

      Dim e As DataGridDisableCellEventArgs = Nothing

      ' Initialize the event arguments by using the number
      ' of the current row and the current column.
      e = New DataGridDisableCellEventArgs(rowNum, MyCol)

      ' Raise the DataGridDisableCell event.
      RaiseEvent DataGridDisableCell(Me, e)

      ' Call the Edit event of the DataGridTextBoxColumn
      ' class for all rows other than the footer row.
      If e.EnableValue Then
         MyBase.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible)
      End If
   End Sub
End Class

Save the DataGridControlVB.vb file, and then build the DataGrid control

1. On the File menu, click Save DataGridControlVB.vb As. The Save File As dialog box appears.
2. Click the arrow next to the Save button, and then click Save with Encoding. You receive a message to replace the existing DataGridControlVB.vb file.
3. Click Yes. The Advanced Save Options dialog box appears.
4. In the Encoding box, select Unicode (UTF-8 with signature) - Codepage 65001, and then click OK.
5. On the Build menu, click Build DataGridControl to build the DataGridControl.dll assembly.

Create a Windows Application project that uses the DataGrid control

1. In Solution Explorer, right-click the DataGridControl solution, point to Add, and then click New Project. The Add New Project dialog box appears.
2. Under Project Types, click Visual Basic Projects.
3. Under Templates, click Windows Application.
4. In the Name box, type TestApplication, and then click OK. By default, a Windows Form that is named Form1 is created.
5. In Solution Explorer, right-click TestApplication, and then click Set as StartUp Project.
6. On the View menu, click Toolbox.
7. Do one of the following, depending on the version of Visual Studio .NET that you have:
   • If you are using Visual Studio .NET 2003, click Add/Remove Toolbox Items on the Tools menu.
   • If you are using Visual Studio .NET 2002, click Customize Toolbox on the Tools menu.
   The Customize Toolbox dialog box appears.
8. On the .NET Framework Components tab, click Browse. The Open dialog box appears.
9. Locate and then click the DataGridControl.dll assembly that you created in step 5 of the "Save the DataGridControlVB.vb file, and then build the DataGrid control" section.
10. Click Open, and then click OK. The DataGridControlVB control is added to the Toolbox.
11. In the Toolbox, double-click the DataGridControlVB control to add the DataGridControlVB1 control to the Form1 form.
12. Click Form1.
13. On the View menu, click Properties Window to view the Properties window for the Form1 form.
14. Set the Size property to 450, 200.
15. In the Design view of the Form1 form, click the DataGridControlVB1 control.
16. On the View menu, click Properties Window to view the Properties window for the DataGridControlVB1 control.
17. Set the Size property to 420, 115.

Bind the custom DataGrid control to the related data

1. In Solution Explorer, right-click Form1.vb, and then click View Code.
2. Import the required namespaces. To do this, add the following code at the top of the code window:

   Imports System.Data
   Imports System.Data.SqlClient

3. Locate the following code:

   Public Class Form1
      Inherits System.Windows.Forms.Form

4. Add the following variable declarations after the code that you located in step 3:

      Dim MyConnString As String
      Dim MyDataSet As DataSet = Nothing
   
      Dim MyDataAdapter As SqlDataAdapter = Nothing
      Dim MyConn As SqlConnection

5. Bind the DataGrid control to the related data. To do this, add the following code after the "Windows Form Designer generated code" area.
   
   Note In the following code, replace <ServerName> with the appropriate value for an instance of Microsoft SQL Server:

      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         Dim SqlString As String = "SELECT * FROM discounts"
   
         MyConnString = "server=<ServerName>;Integrated Security=SSPI;database=pubs"
         MyConn = New SqlConnection(MyConnString)
         MyDataAdapter = New SqlDataAdapter(SqlString, MyConn)
         MyDataSet = New DataSet
   
         Try
            MyDataAdapter.Fill(MyDataSet, "discounts")
            ' Specify the dataset that you want your DataGrid control to use.
            DataGridControlVB1.GridDataSet = MyDataSet
            ' Specify the source table that you want your DataGrid control to use.
            DataGridControlVB1.DataSourceTable = "discounts"
   
            ' Add the columns that you want to sum to an array list. Use the following format:
            ' "<ColumnNumber>,summing expression"
            Dim Summary As New ArrayList
            Summary.Add("2,sum(lowqty)")
            Summary.Add("3,sum(highqty)")
            ' Map the array list to the SummaryColumns property of your DataGrid control.
            DataGridControlVB1.SummaryColumns = Summary
   
            ' Set the foreground color and the background color for the footer row.
            DataGridControlVB1.FooterColor = Brushes.BlueViolet
            DataGridControlVB1.FooterFontColor = Brushes.White
   
            ' Bind the DataGrid control to the related data.
            DataGridControlVB1.BindDataGrid()
   
            ' Dispose the data adapter, and then close the connection.
   
         Catch DatabaseException As SqlException
            MessageBox.Show("Database exception: " & DatabaseException.Message)
         Catch OtherException As Exception
            MessageBox.Show(OtherException.Message)
         Finally
            MyDataAdapter.Dispose()
            MyConn.Dispose()
         End Try
      End Sub

Complete code listing

DataGridControlVB.vb

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Text

' Declare a delegate for the event that disables the cells of the DataGrid control.
Public Delegate Sub DataGridDisableCellEventHandler _
   (ByVal sender As System.Object, ByVal e As DataGridDisableCellEventArgs)

Public Class DataGridControlVB
   Inherits System.Windows.Forms.DataGrid

   ' Declare private variables for your DataGrid control.
   Private RowCount As Integer
   Private ColCount As Integer
   Private SortedColNum As Integer

   Private Ascending As Boolean
   Private CellValueChanged As Boolean

   Private SourceTable As String

   Private MyDataView As DataView
   Private MyDataSet As DataSet
   Private MyDataRow As DataRow

   Private SummaryCols As ArrayList
   Private CurrentDataGridCellLocation As DataGridCell

   Private WithEvents MyDataTable As DataTable

   Private Shared FooterBackColor As Brush
   Private Shared FooterForeColor As Brush

#Region " Windows Form Designer generated code "

   Public Sub New()
      MyBase.New()

      'This call is required by the Windows Form Designer.
      InitializeComponent()

      RowCount = 0
      ColCount = 0

      CellValueChanged = False
      Ascending = False

      MyDataRow = Nothing
      MyDataTable = New DataTable("NewTable")
      CurrentDataGridCellLocation = New DataGridCell

      SummaryCols = New ArrayList

   End Sub

   'UserControl1 overrides dispose to clean up the component list.
   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
      If disposing Then
         If Not (components Is Nothing) Then
            components.Dispose()
         End If
      End If
      MyBase.Dispose(disposing)
   End Sub

   'Required by the Windows Form Designer
   Private components As System.ComponentModel.IContainer

   'NOTE: The following procedure is required by the Windows Form Designer
   'It can be modified using the Windows Form Designer.  
   'Do not modify it using the code editor.
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
      components = New System.ComponentModel.Container
   End Sub

#End Region

   Public WriteOnly Property GridDataSet() As DataSet
      Set(ByVal Value As DataSet)
         MyDataSet = Value
      End Set
   End Property

   Public Property SummaryColumns() As ArrayList
      Get
         Return SummaryCols
      End Get
      Set(ByVal Value As ArrayList)
         SummaryCols = Value
      End Set
   End Property

   Public Property DataSourceTable() As String
      Get
         Return SourceTable
      End Get
      Set(ByVal Value As String)
         SourceTable = Value
      End Set
   End Property

   Public Shared Property FooterColor() As Brush
      Get
         Return FooterBackColor
      End Get
      Set(ByVal Value As Brush)
         FooterBackColor = Value
      End Set
   End Property

   Public Shared Property FooterFontColor() As Brush
      Get
         Return FooterForeColor
      End Get
      Set(ByVal Value As Brush)
         FooterForeColor = Value
      End Set
   End Property

   Public Sub BindDataGrid()
      MyDataTable = MyDataSet.Tables(0)
      MyDataView = MyDataTable.DefaultView
      Me.DataSource = MyDataView

      Dim TableStyle As DataGridTableStyle = New DataGridTableStyle
      TableStyle.MappingName = SourceTable

      ' Add a Boolean data type column to the DataTable object.
      ' You can use this column during your custom sorting.
      MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"))
      MyDataTable.Columns("ID").DefaultValue = False
      MyDataTable.Columns("ID").ColumnMapping = MappingType.Hidden
      ColCount = MyDataTable.Columns.Count

      ' Create a footer row for the DataTable object.
      MyDataRow = MyDataTable.NewRow()

      ' Set the footer value as an empty string for all columns that contains string values.
      Dim MyIterator As Integer
      For MyIterator = 0 To ColCount - 1
         If (MyDataTable.Columns(MyIterator).DataType.ToString() = "System.String") Then
            MyDataRow(MyIterator) = ""
         End If
      Next

      ' Add the footer row to the DataTable object.
      MyDataTable.Rows.Add(MyDataRow)
      RowCount = MyDataTable.Rows.Count

      ' Add a MyDataGridTextBox control to each cell of the DataGrid control.
      Dim TempDataGridTextBox As MyDataGridTextBox
      For MyIterator = 0 To ColCount - 2
         TempDataGridTextBox = New MyDataGridTextBox(MyIterator)
         TempDataGridTextBox.HeaderText = MyDataTable.Columns(MyIterator).ColumnName
         TempDataGridTextBox.MappingName = MyDataTable.Columns(MyIterator).ColumnName
         AddHandler TempDataGridTextBox.DataGridDisableCell, _
            New DataGridDisableCellEventHandler(AddressOf SetEnableValues)

         ' Disable the default sorting feature of the DataGrid control.
         TableStyle.AllowSorting = False
         TableStyle.GridColumnStyles.Add(TempDataGridTextBox)
      Next

      Me.TableStyles.Add(TableStyle)
      Me.DataSource = MyDataView
      MyDataView.ApplyDefaultSort = False
      MyDataView.AllowNew = False

      ' Set the value of the footer cell.
      Dim MyCell As DataGridCell = New DataGridCell
      MyCell.RowNumber = MyDataTable.Rows.Count - 1

      ' Calculate the value for each of the cells in the footer.
      Dim MyArray(2) As String
      Dim MyString As String
      For Each MyString In SummaryCols
         MyArray = MyString.Split(","c)
         MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
         Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").ToString()
      Next

   End Sub

   ' Handle the DataTable object's ColumnChanged event
   ' to track if the value in a cell has changed.
   Private Sub MyDataTable_ColumnChanged(ByVal sender As Object, _
      ByVal e As System.Data.DataColumnChangeEventArgs) Handles MyDataTable.ColumnChanged

      Dim Row As Integer, Col As Integer
      Row = 0
      Col = 0

      ' Determine the row that contains the changed cell.
      Dim TempDataRow As DataRow
      For Each TempDataRow In MyDataTable.Rows
         If (TempDataRow.Equals(e.Row)) Then
            CurrentDataGridCellLocation.RowNumber = Row
            CellValueChanged = True
            Exit For
            Row = Row + 1
         End If
      Next

      ' Determine the column that contains the changed cell.
      Dim TempDataColumn As DataColumn
      For Each TempDataColumn In MyDataTable.Columns
         If (TempDataColumn.Equals(e.Column)) Then
            CurrentDataGridCellLocation.ColumnNumber = Col
            CellValueChanged = True
            Exit For
            Col = Col + 1
         End If
      Next
   End Sub

   ' Handle the CurrentCellChanged event of the DataGrid control.
   Private Sub DataGridControlVB_CurrentCellChanged(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles MyBase.CurrentCellChanged

      If (CellValueChanged = True) Then
         Dim MyCell As DataGridCell = New DataGridCell
         MyCell.RowNumber = MyDataTable.Rows.Count - 1

         ' Calculate the value for each of the cells in the footer.
         Dim MyArray(2) As String
         Dim MyString As String
         For Each MyString In SummaryCols
            MyArray = MyString.Split(",")
            MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
            Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").ToString()
         Next
      End If

      CellValueChanged = False
   End Sub

   ' Handle the MouseDown event to perform custom sorting.
   Private Sub DataGridControlVB_MouseDown(ByVal sender As Object, _
      ByVal e As System.Windows.Forms.MouseEventArgs) Handles MyBase.MouseDown

      Dim MyHitTestInfo As DataGrid.HitTestInfo
      MyHitTestInfo = Me.HitTest(e.X, e.Y)

      Dim ColName As String

      If (MyHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader) Then
         Dim ColNum As Integer = MyHitTestInfo.Column

         If (ColNum <> -1) Then
            ColName = MyDataTable.Columns(ColNum).ColumnName

            ' Perform custom sorting. To do this, always sort the Boolean data type column in
            ' ascending order so that the footer row stays at the end.
            Dim MyChar() As Char = {"?"c, "?"c}

            Dim NewString As String = _
               Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText.TrimEnd(MyChar).Trim()
            Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText = NewString

            If (Ascending = True) Then
               MyDataView.Sort = "ID Asc," + ColName + " desc"
               Ascending = False
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ?"
               SortedColNum = ColNum
            Else
               MyDataView.Sort = "ID Asc," + ColName + " asc"
               Ascending = True
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ?"
               SortedColNum = ColNum
            End If
         End If
      End If
   End Sub

   ' Disable the footer row of the DataGrid control.
   Public Sub SetEnableValues(ByVal sender As Object, ByVal e As DataGridDisableCellEventArgs)
      If (e.Row = RowCount - 1) Then
         e.EnableValue = False
      Else
         e.EnableValue = True
      End If
   End Sub

End Class

' Define a custom event arguments class that inherits from the EventArgs class.
Public Class DataGridDisableCellEventArgs
   Inherits EventArgs

   Private MyCol As Integer
   Private MyRow As Integer
   Private MyEnableValue As Boolean

   Public Sub New(ByVal Row As Integer, ByVal Col As Integer)
      MyRow = Row
      MyCol = Col
      MyEnableValue = True
   End Sub

   Public Property Column() As Integer
      Get
         Return MyCol
      End Get
      Set(ByVal Value As Integer)
         MyCol = Value
      End Set
   End Property

   Public Property Row() As Integer
      Get
         Return MyRow
      End Get
      Set(ByVal Value As Integer)
         MyRow = Value
      End Set
   End Property

   Public Property EnableValue() As Boolean
      Get
         Return MyEnableValue
      End Get
      Set(ByVal Value As Boolean)
         MyEnableValue = Value
      End Set
   End Property

End Class

Public Class MyDataGridTextBox
   Inherits DataGridTextBoxColumn

   ' Declare an event for the DataGridDisableCellEventHandler delegate that you have defined.
   Public Event DataGridDisableCell As DataGridDisableCellEventHandler

   Private MyCol As Integer

   ' Save the column number of the column to add the MyDataGridTextBox control to.
   Public Sub New(ByVal Column As Integer)
      MyCol = Column
   End Sub

   ' Override the Paint method to set colors for the footer row.
   Protected Overloads Overrides Sub Paint(ByVal g As System.Drawing.Graphics, _
      ByVal bounds As System.Drawing.Rectangle, ByVal source As System.Windows.Forms.CurrencyManager, _
         ByVal rowNum As Integer, ByVal backBrush As System.Drawing.Brush, _
            ByVal foreBrush As System.Drawing.Brush, ByVal alignToRight As Boolean)

      ' Initialize the event arguments with the number
      ' of the current row and the current column.
      Dim e As New DataGridDisableCellEventArgs(rowNum, MyCol)

      ' Raise the DataGridDisableCell event.
      RaiseEvent DataGridDisableCell(Me, e)

      ' Set the foreground color and the background color for the footer row.
      If Not e.EnableValue Then
         If DataGridControlVB.FooterColor Is Nothing _
            Or DataGridControlVB.FooterFontColor Is Nothing Then
            backBrush = Brushes.White
            foreBrush = Brushes.Black
         Else
            backBrush = DataGridControlVB.FooterColor
            foreBrush = DataGridControlVB.FooterFontColor
         End If
      End If

      ' Call the Paint event of the DataGridTextBoxColumn class.
      MyBase.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight)
   End Sub

   ' Override the Edit method to disable the footer row.
   Protected Overloads Overrides Sub Edit(ByVal source As System.Windows.Forms.CurrencyManager, _
      ByVal rowNum As Integer, ByVal bounds As System.Drawing.Rectangle, ByVal readOnlyFlag As Boolean, _
         ByVal instantText As String, ByVal cellIsVisible As Boolean)

      Dim e As DataGridDisableCellEventArgs = Nothing

      ' Initialize the event arguments with the number
      ' of the current row and the current column.
      e = New DataGridDisableCellEventArgs(rowNum, MyCol)

      ' Raise the DataGridDisableCell event.
      RaiseEvent DataGridDisableCell(Me, e)

      ' Call the Edit event of the DataGridTextBoxColumn
      ' class for all rows other than the footer row.
      If e.EnableValue Then
         MyBase.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible)
      End If
   End Sub
End Class

Form1.vb

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
   Inherits System.Windows.Forms.Form

   Dim MyConnString As String
   Dim MyDataSet As DataSet = Nothing

   Dim MyDataAdapter As SqlDataAdapter = Nothing
   Dim MyConn As SqlConnection

#Region " Windows Form Designer generated code "

   Public Sub New()
      MyBase.New()

      'This call is required by the Windows Form Designer.
      InitializeComponent()

      'Add any initialization after the InitializeComponent() call

   End Sub

   'Form overrides dispose to clean up the component list.
   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
      If disposing Then
         If Not (components Is Nothing) Then
            components.Dispose()
         End If
      End If
      MyBase.Dispose(disposing)
   End Sub

   'Required by the Windows Form Designer
   Private components As System.ComponentModel.IContainer

   'NOTE: The following procedure is required by the Windows Form Designer
   'It can be modified using the Windows Form Designer.  
   'Do not modify it using the code editor.
   Friend WithEvents DataGridControlVB1 As DataGridControl.DataGridControlVB
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
      Me.DataGridControlVB1 = New DataGridControl.DataGridControlVB
      CType(Me.DataGridControlVB1, System.ComponentModel.ISupportInitialize).BeginInit()
      Me.SuspendLayout()
      '
      'DataGridControlVB1
      '
      Me.DataGridControlVB1.DataMember = ""
      Me.DataGridControlVB1.DataSourceTable = Nothing
      Me.DataGridControlVB1.HeaderForeColor = System.Drawing.SystemColors.ControlText
      Me.DataGridControlVB1.Location = New System.Drawing.Point(0, 0)
      Me.DataGridControlVB1.Name = "DataGridControlVB1"
      Me.DataGridControlVB1.Size = New System.Drawing.Size(420, 115)
      Me.DataGridControlVB1.TabIndex = 0
      '
      'Form1
      '
      Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
      Me.ClientSize = New System.Drawing.Size(442, 173)
      Me.Controls.Add(Me.DataGridControlVB1)
      Me.Name = "Form1"
      Me.Text = "Form1"
      CType(Me.DataGridControlVB1, System.ComponentModel.ISupportInitialize).EndInit()
      Me.ResumeLayout(False)

   End Sub

#End Region

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim SqlString As String = "SELECT * FROM discounts"

      MyConnString = "server=<ServerName>;Integrated Security=SSPI;database=pubs"
      MyConn = New SqlConnection(MyConnString)
      MyDataAdapter = New SqlDataAdapter(SqlString, MyConn)
      MyDataSet = New DataSet

      Try
         MyDataAdapter.Fill(MyDataSet, "discounts")
         ' Specify the dataset that you want your DataGrid control to use.
         DataGridControlVB1.GridDataSet = MyDataSet
         ' Specify the source table that you want your DataGrid control to use.
         DataGridControlVB1.DataSourceTable = "discounts"

         ' Add the columns that you want to sum to an array list in the following format:
         ' "<ColumnNumber>,summing expression"
         Dim Summary As New ArrayList
         Summary.Add("2,sum(lowqty)")
         Summary.Add("3,sum(highqty)")
         ' Map the array list to the SummaryColumns property of your DataGrid control.
         DataGridControlVB1.SummaryColumns = Summary

         ' Set the foreground color and the background color for the footer row.
         DataGridControlVB1.FooterColor = Brushes.BlueViolet
         DataGridControlVB1.FooterFontColor = Brushes.White

         ' Bind the DataGrid control to the related data.
         DataGridControlVB1.BindDataGrid()

         ' Dispose the data adapter, and then close the connection.

      Catch DatabaseException As SqlException
         MessageBox.Show("Database exception: " & DatabaseException.Message)
      Catch OtherException As Exception
         MessageBox.Show(OtherException.Message)
      Finally
         MyDataAdapter.Dispose()
         MyConn.Dispose()
      End Try
   End Sub

End Class

Build and then run your application

1. On the Build menu, click Build Solution.
2. On the Debug menu, click Start.
   
   The Form1 form appears. Your custom DataGrid control is present on the Form1 form. The footer row of the DataGrid control contains the sums of the values of the fields that you specified to sum. You cannot edit the footer row. However, when you change the value in any one of the cells, the corresponding footer cell is updated.