Symptoms
To sum the fields in a Microsoft Windows Forms DataGrid control and then to display the calculated totals in a footer, you must first create a user control that inherits from the System.Windows.Forms.DataGrid class. Then you must handle the events that are raised when a cell in this user control is changed.
You must disable the default sorting feature of the DataGrid control to prevent the footer row from being sorted. To implement custom sorting for your DataGrid control, you must handle the MouseDown event.
You must also disable the footer row of the DataGrid control to prevent users from editing the cells of the footer row. To provide data for the event that prevents users from editing the cells of the DataGrid control, you must define an event arguments class, and then you must define a class that contains methods to paint and to disable the footer row.
To sum the fields and to display the calculated totals in a footer, build the DataGrid control, add an instance of the DataGrid control to a Windows Application project, bind the DataGrid control to the related data, and then build and run the application.
Resolution
This step-by-step article describes how to sum the fields in a Windows Forms DataGrid control by using Microsoft Visual Basic .NET. This article also describes how to customize the Windows Forms DataGrid control to display the calculated totals in a footer.
RequirementsThis article assumes that you are familiar with the following topics: The Windows Forms DataGrid controlData binding by using Windows Forms and Microsoft ADO.NETHandling and raising events
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:Microsoft Windows 2000, Microsoft Windows XP, or Microsoft Windows Server 2003Microsoft Visual Studio .NET
Create a Windows Control Library projectStart Visual Studio .NET.On the File menu, point to New, and then click Project. The New Project dialog box appears.Under Project Types, click Visual Basic Projects.Under Templates, click Windows Control Library.In the Name box, type DataGridControl, and then click OK. By default, a user control that is named UserControl1 is created.On the View menu, click Solution Explorer.In Solution Explorer, right-click UserControl1.vb, and then click Rename.Rename the UserControl1.vb file as DataGridControlVB.vb.
Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methodsIn Solution Explorer, right-click DataGridControlVB.vb, and then click View Code.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 SystemImports System.CollectionsImports System.ComponentModelImports System.DrawingImports System.DataImports System.Data.SqlClientImports System.Windows.FormsImports 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)Locate the following code:
Public Class UserControl1Inherits System.Windows.Forms.UserControlMake 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 DataGridControlVBInherits System.Windows.Forms.DataGridAdd the following variable declarations after the code that you added in step 4:
‘ Declare private variables for your DataGrid control.Private RowCount As IntegerPrivate ColCount As IntegerPrivate SortedColNum As IntegerPrivate Ascending As BooleanPrivate CellValueChanged As BooleanPrivate SourceTable As StringPrivate MyDataView As DataViewPrivate MyDataSet As DataSetPrivate MyDataRow As DataRowPrivate SummaryCols As ArrayListPrivate CurrentDataGridCellLocation As DataGridCellPrivate WithEvents MyDataTable As DataTablePrivate Shared FooterBackColor As BrushPrivate Shared FooterForeColor As BrushLocate the following code comment in the “Windows Form Designer generated code” area:
‘Add any initialization after the InitializeComponent() callPerform 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 = 0ColCount = 0CellValueChanged = FalseAscending = FalseMyDataRow = NothingMyDataTable = New DataTable(“NewTable”)CurrentDataGridCellLocation = New DataGridCellSummaryCols = New ArrayListLocate the following code:
#End RegionAdd the following property definitions after the code that you located in step 8:
Public WriteOnly Property GridDataSet() As DataSetSet(ByVal Value As DataSet)MyDataSet = ValueEnd SetEnd PropertyPublic Property SummaryColumns() As ArrayListGetReturn SummaryColsEnd GetSet(ByVal Value As ArrayList)SummaryCols = ValueEnd SetEnd PropertyPublic Property DataSourceTable() As StringGetReturn SourceTableEnd GetSet(ByVal Value As String)SourceTable = ValueEnd SetEnd PropertyPublic Shared Property FooterColor() As BrushGetReturn FooterBackColorEnd GetSet(ByVal Value As Brush)FooterBackColor = ValueEnd SetEnd PropertyPublic Shared Property FooterFontColor() As BrushGetReturn FooterForeColorEnd GetSet(ByVal Value As Brush)FooterForeColor = ValueEnd SetEnd PropertyDisable 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.DefaultViewMe.DataSource = MyDataViewDim TableStyle As DataGridTableStyle = New DataGridTableStyleTableStyle.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 = FalseMyDataTable.Columns(“ID”).ColumnMapping = MappingType.HiddenColCount = 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 IntegerFor MyIterator = 0 To ColCount – 1If (MyDataTable.Columns(MyIterator).DataType.ToString() = “System.String”) ThenMyDataRow(MyIterator) = “”End IfNext’ 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 MyDataGridTextBoxFor MyIterator = 0 To ColCount – 2TempDataGridTextBox = New MyDataGridTextBox(MyIterator)TempDataGridTextBox.HeaderText = MyDataTable.Columns(MyIterator).ColumnNameTempDataGridTextBox.MappingName = MyDataTable.Columns(MyIterator).ColumnNameAddHandler TempDataGridTextBox.DataGridDisableCell, _New DataGridDisableCellEventHandler(AddressOf SetEnableValues)’ Disable the default sorting feature of the DataGrid control.TableStyle.AllowSorting = FalseTableStyle.GridColumnStyles.Add(TempDataGridTextBox)NextMe.TableStyles.Add(TableStyle)Me.DataSource = MyDataViewMyDataView.ApplyDefaultSort = FalseMyDataView.AllowNew = False’ Set the value of the footer cell.Dim MyCell As DataGridCell = New DataGridCellMyCell.RowNumber = MyDataTable.Rows.Count – 1′ Calculate the value for each of the cells in the footer.Dim MyArray(2) As StringDim MyString As StringFor Each MyString In SummaryColsMyArray = MyString.Split(“,”c)MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))Me(MyCell) = MyDataTable.Compute(MyArray(1), “ID is null”).ToString()NextEnd Sub
Handle the events that are raised when a cell in the DataGrid control is changedYou must handle the ColumnChanged event of the DataTable object, and then you must handle the CurrentCellChanged event of the DataGrid control to track when a cell value in the DataGrid control is changed. To do this, follow these steps: 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.ColumnChangedDim Row As Integer, Col As IntegerRow = 0Col = 0′ Determine the row that contains the changed cell.Dim TempDataRow As DataRowFor Each TempDataRow In MyDataTable.RowsIf (TempDataRow.Equals(e.Row)) ThenCurrentDataGridCellLocation.RowNumber = RowCellValueChanged = TrueExit ForRow = Row + 1End IfNext’ Determine the column that contains the changed cell.Dim TempDataColumn As DataColumnFor Each TempDataColumn In MyDataTable.ColumnsIf (TempDataColumn.Equals(e.Column)) ThenCurrentDataGridCellLocation.ColumnNumber = ColCellValueChanged = TrueExit ForCol = Col + 1End IfNextEnd SubHandle 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.CurrentCellChangedIf (CellValueChanged = True) ThenDim MyCell As DataGridCell = New DataGridCellMyCell.RowNumber = MyDataTable.Rows.Count – 1′ Calculate the value for each cell in the footer.Dim MyArray(2) As StringDim MyString As StringFor Each MyString In SummaryColsMyArray = MyString.Split(“,”)MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))Me(MyCell) = MyDataTable.Compute(MyArray(1), “ID is null”).ToString()NextEnd IfCellValueChanged = FalseEnd Sub
Handle the MouseDown event of the DataGrid control to implement custom sortingBecause you have disabled the default sorting feature of the DataGrid control, you must perform custom sorting when a user clicks a column header. You must handle the MouseDown event of the DataGrid control to implement custom sorting.
To do this, add the following event handler after the code that you added in step 2 of the “Handle the events that are raised when a cell in the DataGrid control is changed” section:
‘ 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.MouseDownDim MyHitTestInfo As DataGrid.HitTestInfoMyHitTestInfo = Me.HitTest(e.X, e.Y)Dim ColName As StringIf (MyHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader) ThenDim ColNum As Integer = MyHitTestInfo.ColumnIf (ColNum <> -1) ThenColName = 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 = NewStringIf (Ascending = True) ThenMyDataView.Sort = “ID Asc,” + ColName + ” desc”Ascending = FalseMe.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + ” ↑”SortedColNum = ColNumElseMyDataView.Sort = “ID Asc,” + ColName + ” asc”Ascending = TrueMe.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + ” ↓”SortedColNum = ColNumEnd IfEnd IfEnd IfEnd Sub
Disable the footer row of the DataGrid controlTo disable the footer row of the DataGrid control, add the following code after the code that you added in the “Handle the MouseDown event of the DataGrid control to implement custom sorting” section:
‘ Disable the footer row of the DataGrid control.Public Sub SetEnableValues(ByVal sender As Object, ByVal e As DataGridDisableCellEventArgs)If (e.Row = RowCount – 1) Thene.EnableValue = FalseElsee.EnableValue = TrueEnd IfEnd Sub
Define an event arguments class that provides data for the DataGridDisableCell eventTo define an event arguments class that provides data for the DataGridDisableCell event, follow these steps: In the Form1.vb file, locate the following code:
End ClassAdd 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 DataGridDisableCellEventArgsInherits EventArgsPrivate MyCol As IntegerPrivate MyRow As IntegerPrivate MyEnableValue As BooleanPublic Sub New(ByVal Row As Integer, ByVal Col As Integer)MyRow = RowMyCol = ColMyEnableValue = TrueEnd SubPublic Property Column() As IntegerGetReturn MyColEnd GetSet(ByVal Value As Integer)MyCol = ValueEnd SetEnd PropertyPublic Property Row() As IntegerGetReturn MyRowEnd GetSet(ByVal Value As Integer)MyRow = ValueEnd SetEnd PropertyPublic Property EnableValue() As BooleanGetReturn MyEnableValueEnd GetSet(ByVal Value As Boolean)MyEnableValue = ValueEnd SetEnd PropertyEnd Class
Define a class that contains methods to paint and to disable the footer rowTo define a class that contains methods to paint and to disable the footer row, add the following code after the code that you added in step 2 of the “Define an event arguments class that provides data for the DataGridDisableCell event” section:
Public Class MyDataGridTextBoxInherits DataGridTextBoxColumn’ Declare an event for the DataGridDisableCellEventHandler delegate that you have defined.Public Event DataGridDisableCell As DataGridDisableCellEventHandlerPrivate MyCol As Integer’ Save the column number of the column to add the MyDataGridTextBox control to.Public Sub New(ByVal Column As Integer)MyCol = ColumnEnd 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 ThenIf DataGridControlVB.FooterColor Is Nothing _Or DataGridControlVB.FooterFontColor Is Nothing ThenbackBrush = Brushes.WhiteforeBrush = Brushes.BlackElsebackBrush = DataGridControlVB.FooterColorforeBrush = DataGridControlVB.FooterFontColorEnd IfEnd 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 ThenMyBase.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible)End IfEnd SubEnd Class
Save the DataGridControlVB.vb file, and then build the DataGrid controlOn the File menu, click Save DataGridControlVB.vb As. The Save File As dialog box appears.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.Click Yes. The Advanced Save Options dialog box appears.In the Encoding box, select Unicode (UTF-8 with signature) – Codepage 65001, and then click OK.On the Build menu, click Build DataGridControl to build the DataGridControl.dll assembly.
Create a Windows Application project that uses the DataGrid controlIn Solution Explorer, right-click the DataGridControl solution, point to Add, and then click New Project. The Add New Project dialog box appears.Under Project Types, click Visual Basic Projects.Under Templates, click Windows Application.In the Name box, type TestApplication, and then click OK. By default, a Windows Form that is named Form1 is created.In Solution Explorer, right-click TestApplication, and then click Set as StartUp Project.On the View menu, click Toolbox.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.On the .NET Framework Components tab, click Browse. The Open dialog box appears.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.Click Open, and then click OK. The DataGridControlVB control is added to the Toolbox.In the Toolbox, double-click the DataGridControlVB control to add the DataGridControlVB1 control to the Form1 form.Click Form1.On the View menu, click Properties Window to view the Properties window for the Form1 form.Set the Size property to 450, 200.In the Design view of the Form1 form, click the DataGridControlVB1 control.On the View menu, click Properties Window to view the Properties window for the DataGridControlVB1 control.Set the Size property to 420, 115.
Bind the custom DataGrid control to the related dataIn Solution Explorer, right-click Form1.vb, and then click View Code.Import the required namespaces. To do this, add the following code at the top of the code window:
Imports System.DataImports System.Data.SqlClientLocate the following code:
Public Class Form1Inherits System.Windows.Forms.FormAdd the following variable declarations after the code that you located in step 3:
Dim MyConnString As StringDim MyDataSet As DataSet = NothingDim MyDataAdapter As SqlDataAdapter = NothingDim MyConn As SqlConnectionBind 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.LoadDim 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 DataSetTryMyDataAdapter.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 ArrayListSummary.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.BlueVioletDataGridControlVB1.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 SqlExceptionMessageBox.Show(“Database exception: ” & DatabaseException.Message)Catch OtherException As ExceptionMessageBox.Show(OtherException.Message)FinallyMyDataAdapter.Dispose()MyConn.Dispose()End TryEnd Sub
Complete code listingDataGridControlVB.vb
Imports SystemImports System.CollectionsImports System.ComponentModelImports System.DrawingImports System.DataImports System.Data.SqlClientImports System.Windows.FormsImports 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 DataGridControlVBInherits System.Windows.Forms.DataGrid’ Declare private variables for your DataGrid control.Private RowCount As IntegerPrivate ColCount As IntegerPrivate SortedColNum As IntegerPrivate Ascending As BooleanPrivate CellValueChanged As BooleanPrivate SourceTable As StringPrivate MyDataView As DataViewPrivate MyDataSet As DataSetPrivate MyDataRow As DataRowPrivate SummaryCols As ArrayListPrivate CurrentDataGridCellLocation As DataGridCellPrivate WithEvents MyDataTable As DataTablePrivate Shared FooterBackColor As BrushPrivate 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 = 0ColCount = 0CellValueChanged = FalseAscending = FalseMyDataRow = NothingMyDataTable = New DataTable(“NewTable”)CurrentDataGridCellLocation = New DataGridCellSummaryCols = New ArrayListEnd Sub’UserControl1 overrides dispose to clean up the component list.Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)If disposing ThenIf Not (components Is Nothing) Thencomponents.Dispose()End IfEnd IfMyBase.Dispose(disposing)End Sub’Required by the Windows Form DesignerPrivate 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.ContainerEnd Sub#End RegionPublic WriteOnly Property GridDataSet() As DataSetSet(ByVal Value As DataSet)MyDataSet = ValueEnd SetEnd PropertyPublic Property SummaryColumns() As ArrayListGetReturn SummaryColsEnd GetSet(ByVal Value As ArrayList)SummaryCols = ValueEnd SetEnd PropertyPublic Property DataSourceTable() As StringGetReturn SourceTableEnd GetSet(ByVal Value As String)SourceTable = ValueEnd SetEnd PropertyPublic Shared Property FooterColor() As BrushGetReturn FooterBackColorEnd GetSet(ByVal Value As Brush)FooterBackColor = ValueEnd SetEnd PropertyPublic Shared Property FooterFontColor() As BrushGetReturn FooterForeColorEnd GetSet(ByVal Value As Brush)FooterForeColor = ValueEnd SetEnd PropertyPublic Sub BindDataGrid()MyDataTable = MyDataSet.Tables(0)MyDataView = MyDataTable.DefaultViewMe.DataSource = MyDataViewDim TableStyle As DataGridTableStyle = New DataGridTableStyleTableStyle.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 = FalseMyDataTable.Columns(“ID”).ColumnMapping = MappingType.HiddenColCount = 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 IntegerFor MyIterator = 0 To ColCount – 1If (MyDataTable.Columns(MyIterator).DataType.ToString() = “System.String”) ThenMyDataRow(MyIterator) = “”End IfNext’ 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 MyDataGridTextBoxFor MyIterator = 0 To ColCount – 2TempDataGridTextBox = New MyDataGridTextBox(MyIterator)TempDataGridTextBox.HeaderText = MyDataTable.Columns(MyIterator).ColumnNameTempDataGridTextBox.MappingName = MyDataTable.Columns(MyIterator).ColumnNameAddHandler TempDataGridTextBox.DataGridDisableCell, _New DataGridDisableCellEventHandler(AddressOf SetEnableValues)’ Disable the default sorting feature of the DataGrid control.TableStyle.AllowSorting = FalseTableStyle.GridColumnStyles.Add(TempDataGridTextBox)NextMe.TableStyles.Add(TableStyle)Me.DataSource = MyDataViewMyDataView.ApplyDefaultSort = FalseMyDataView.AllowNew = False’ Set the value of the footer cell.Dim MyCell As DataGridCell = New DataGridCellMyCell.RowNumber = MyDataTable.Rows.Count – 1′ Calculate the value for each of the cells in the footer.Dim MyArray(2) As StringDim MyString As StringFor Each MyString In SummaryColsMyArray = MyString.Split(“,”c)MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))Me(MyCell) = MyDataTable.Compute(MyArray(1), “ID is null”).ToString()NextEnd 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.ColumnChangedDim Row As Integer, Col As IntegerRow = 0Col = 0′ Determine the row that contains the changed cell.Dim TempDataRow As DataRowFor Each TempDataRow In MyDataTable.RowsIf (TempDataRow.Equals(e.Row)) ThenCurrentDataGridCellLocation.RowNumber = RowCellValueChanged = TrueExit ForRow = Row + 1End IfNext’ Determine the column that contains the changed cell.Dim TempDataColumn As DataColumnFor Each TempDataColumn In MyDataTable.ColumnsIf (TempDataColumn.Equals(e.Column)) ThenCurrentDataGridCellLocation.ColumnNumber = ColCellValueChanged = TrueExit ForCol = Col + 1End IfNextEnd Sub’ Handle the CurrentCellChanged event of the DataGrid control.Private Sub DataGridControlVB_CurrentCellChanged(ByVal sender As Object, _ByVal e As System.EventArgs) Handles MyBase.CurrentCellChangedIf (CellValueChanged = True) ThenDim MyCell As DataGridCell = New DataGridCellMyCell.RowNumber = MyDataTable.Rows.Count – 1′ Calculate the value for each of the cells in the footer.Dim MyArray(2) As StringDim MyString As StringFor Each MyString In SummaryColsMyArray = MyString.Split(“,”)MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))Me(MyCell) = MyDataTable.Compute(MyArray(1), “ID is null”).ToString()NextEnd IfCellValueChanged = FalseEnd 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.MouseDownDim MyHitTestInfo As DataGrid.HitTestInfoMyHitTestInfo = Me.HitTest(e.X, e.Y)Dim ColName As StringIf (MyHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader) ThenDim ColNum As Integer = MyHitTestInfo.ColumnIf (ColNum <> -1) ThenColName = 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 = NewStringIf (Ascending = True) ThenMyDataView.Sort = “ID Asc,” + ColName + ” desc”Ascending = FalseMe.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + ” ↑”SortedColNum = ColNumElseMyDataView.Sort = “ID Asc,” + ColName + ” asc”Ascending = TrueMe.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + ” ↓”SortedColNum = ColNumEnd IfEnd IfEnd IfEnd 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) Thene.EnableValue = FalseElsee.EnableValue = TrueEnd IfEnd SubEnd Class’ Define a custom event arguments class that inherits from the EventArgs class.Public Class DataGridDisableCellEventArgsInherits EventArgsPrivate MyCol As IntegerPrivate MyRow As IntegerPrivate MyEnableValue As BooleanPublic Sub New(ByVal Row As Integer, ByVal Col As Integer)MyRow = RowMyCol = ColMyEnableValue = TrueEnd SubPublic Property Column() As IntegerGetReturn MyColEnd GetSet(ByVal Value As Integer)MyCol = ValueEnd SetEnd PropertyPublic Property Row() As IntegerGetReturn MyRowEnd GetSet(ByVal Value As Integer)MyRow = ValueEnd SetEnd PropertyPublic Property EnableValue() As BooleanGetReturn MyEnableValueEnd GetSet(ByVal Value As Boolean)MyEnableValue = ValueEnd SetEnd PropertyEnd ClassPublic Class MyDataGridTextBoxInherits DataGridTextBoxColumn’ Declare an event for the DataGridDisableCellEventHandler delegate that you have defined.Public Event DataGridDisableCell As DataGridDisableCellEventHandlerPrivate MyCol As Integer’ Save the column number of the column to add the MyDataGridTextBox control to.Public Sub New(ByVal Column As Integer)MyCol = ColumnEnd 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 ThenIf DataGridControlVB.FooterColor Is Nothing _Or DataGridControlVB.FooterFontColor Is Nothing ThenbackBrush = Brushes.WhiteforeBrush = Brushes.BlackElsebackBrush = DataGridControlVB.FooterColorforeBrush = DataGridControlVB.FooterFontColorEnd IfEnd 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 ThenMyBase.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible)End IfEnd SubEnd ClassForm1.vbNote In the following code, replace <ServerName> with the appropriate value for an instance of Microsoft SQL Server.
Imports System.DataImports System.Data.SqlClientPublic Class Form1Inherits System.Windows.Forms.FormDim MyConnString As StringDim MyDataSet As DataSet = NothingDim MyDataAdapter As SqlDataAdapter = NothingDim 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() callEnd Sub’Form overrides dispose to clean up the component list.Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)If disposing ThenIf Not (components Is Nothing) Thencomponents.Dispose()End IfEnd IfMyBase.Dispose(disposing)End Sub’Required by the Windows Form DesignerPrivate 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.DataGridControlVBCType(Me.DataGridControlVB1, System.ComponentModel.ISupportInitialize).BeginInit()Me.SuspendLayout()”DataGridControlVB1′Me.DataGridControlVB1.DataMember = “”Me.DataGridControlVB1.DataSourceTable = NothingMe.DataGridControlVB1.HeaderForeColor = System.Drawing.SystemColors.ControlTextMe.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 RegionPrivate Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadDim 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 DataSetTryMyDataAdapter.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 ArrayListSummary.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.BlueVioletDataGridControlVB1.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 SqlExceptionMessageBox.Show(“Database exception: ” & DatabaseException.Message)Catch OtherException As ExceptionMessageBox.Show(OtherException.Message)FinallyMyDataAdapter.Dispose()MyConn.Dispose()End TryEnd SubEnd Class
Build and then run your applicationOn the Build menu, click Build Solution.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.
