The DataGrid control is one of the most flexible and versatile controls in Windows Forms. In this section, we discuss some of the DataGrid functionality.
The DataGrid class represents the DataGrid control in Windows Forms. Before writing any code, you'll learn about the DataGrid class properties and methods. Figure 7-6 shows a DataGrid's parent items and background, and Figure 7-7 shows some of the DataGrid parts.
Figure 7-6: The DataGrid's parent items and background
Figure 7-7: The DataGrid's parts
Like all other Windows controls, the DataGrid inherits from the Control class, which means that the data-binding functionality defined in the Control class is available in the DataGrid control. Besides the hundreds of members implemented in theControl class, the DataGrid provides many more members. Table 7-2 describes the DataGrid class properties.
PROPERTY | DESCRIPTION |
---|---|
AllowNavigation | Indicates whether navigation is allowed. True or false. Both get and set. |
AllowSorting | Indicates whether sorting is allowed. True or false. Both get and set. |
AlternatingBackColor | Background color of alternative rows. |
BackColor | Background color of the grid. |
BackgroundColor | Color of the nonrow area of the grid. This is the background color if the grid has no rows. |
BorderStyle | Style of the border. |
CaptionBackColor | Background color of caption. |
CaptionFont | Font of caption. |
CaptionForeColor | Foreground color of caption. |
CaptionText | Caption text. |
CaptionVisible | Indicates whether caption is visible. |
ColumnHeadersVisible | Indicates whether column headers are visible. |
CurrentCell | Returns current selected cell. |
CurrentRowIndex | Index of the selected row. |
DataMember | Represents the data sources among multiple data sources. If there's only one data source, such as a DataTable or a DataSet with a single table, there's no need to set this property. Both get and set. |
DataSource | Represents the data source such as a DataSet, a DataTable, or Ilist. |
FirstVisibleColumn | Index of the first visible column. |
FlatMode | FlatMode. Type of FlatMode enumeration. |
ForeColor | Foreground color. |
GridLineColor | Color of grid lines. |
GridLineStyle | Style of grid lines. |
HeaderBackColor | Background color of column headers. |
HeaderFont | Font of column headers. |
HeaderForeColor | Foreground color of column headers. |
Item | Value of the specified cell. |
LinkColor | Color of the text that you can click to navigate to a child table. |
LinkHoverColor | Link color changes to when the mouse moves over it. |
ParentRowBackColor | Background color of parent rows. Parent rows are rows that allow you to move to child tables. |
ParentRowForeColor | Foreground color of parent rows. |
ParentRowLabelStyle | Label style of parent rows. |
ParentRowsVisible | Indicates whether parent rows are visible. |
PreferredColumnWidth | Default width of columns in pixel. |
PreferredRowHeight | Default height of rows in pixels. |
ReadOnly | Indicates whether grid is read only. |
RowHeaderVisible | Indicates whether row header is visible. |
RowHeaderWidth | Width of row headers. |
SelectionBackColor | Background color of selected rows. |
SelectionForeColor | Foreground color of selected rows. |
TableStyles | Table style. DataGridTableStyle type. |
VisibleColumnCount | Total number of visible columns. |
VisibleRowCount | Total number of visible rows. |
HorizScrollBar | Protected. Returns the horizontal scroll bar of the grid. |
VertScrollBar | Protected. Returns the horizontal scroll bar of the grid. |
ListManager | Protected. Returns the CurrencyManager of the grid. |
Table 7-3 describes the DataGrid class methods.
METHOD | DESCRIPTION |
---|---|
BeginEdit | Starts the editing operation |
BeginInit | Begins the initialization of grid that is used on a form or used by other components |
Collapse | Collapses children if a grid has parent and child relationship nodes expanded |
EndEdit | Ends the editing operation |
EndInit | Ends grid initialization |
Expand | Expands children if grid has children in a parent/child relation |
GetCurrentCellBounds | Returns a rectangle that specifies the four corners of the selected cell |
HitTest | Gets information when clicking on the grid |
IsExpanded | True if node of the specified row is expanded; otherwise false |
IsSelected | True if specified row is selected; otherwise false |
NavigateBack | Navigates to the table previously displayed in the grid |
NavigateTo | Navigates to the table specified by the row and relation name |
ResetAlternatingBackColor | Resets the AlternatingBackColor property to the default color |
ResetBackColor | Resets background color to default |
ResetGridLineColor | Resets grid lines color to default |
ResetHeaderBackColor | Resets header background to default |
ResetHeaderFont | Resets header font to default |
ResetHeaderForeColor | Resets header foreground color to default |
ResetLinkColor | Resets link color to default |
ResetSelectionBackColor | Resets selection background color to default |
ResetSelectionForeColor | Resets selection foreground color to default |
Select | Selects a specified row |
SetDataBinding | Sets the DataSource and DataMember properties |
UnSelect | Unselects a specified row |
Besides the methods described in Table 7-3, the DataGrid class provides some protected methods (see Table 7-4).
PROTECTED METHOD | DESCRIPTION |
---|---|
CancelEditing | Cancels the current edit operation and rolls back all changes |
GridHScrolled | Listens for the horizontal scroll bar's scroll event |
GridVScrolled | Listens for the vertical scroll bar's scroll event |
OnBackButtonClicked | Listens for the caption's Back button clicked event |
OnBorderStyleChanged | Raises the BorderStyleChanged event |
OnCaptionVisibleChanged | Raises the CaptionVisibleChanged event |
OnDataSourceChanged | Raises the DataSourceChanged event |
OnFlatModeChanged | Raises the FlatModeChanged event |
OnNavigate | Raises the Navigate event |
OnParentRowsLabelStyleChanged | Raises the ParentRowsLabelStyleChanged event |
OnParentRowsVisibleChanged | Raises the ParentRowsVisibleChanged event |
OnReadOnlyChanged | Raises the ReadOnlyChanged event |
OnRowHeaderClick | Raises the RowHeaderClick event |
OnScroll | Raises the Scroll event |
OnShowParentDetailsButtonClicked | Raises the ShowParentDetailsButtonClick event |
ProcessGridKey | Processes keys for grid navigation |
ProcessTabKey | Gets a value indicating whether the Tab key should be processed |
ResetSelection | Turns off selection for all rows that are selected |
The DataGrid class comes with 13 helper objects (classes, structures, and enumerations). What do we mean by helper classes? Helper classes provide simple methods to access some of the more complicated aspects of the DataGrid class. These helper objects are DataGrid.HitTestInfo, the DataGrid.HitTestType enumeration, DataGridBoolColumn, the DataGridCell structure, DataGridColumnStyle, DataGridColumnStyle.CompModSwitches, the DataGridColumnStype.DataGridColumnHeaderAccessibleObject DataGridLineStyle enumeration, the DataGridParentRowsLabelStyle enumeration, DataGridPreferredColumnWidthTypeConverter, DataGridTableStyle, DataGridTextBox, and DataGridTextBoxColumn. We discuss some of these objects in the following section. You'll see the rest of them later in this chapter.
The DataGrid control hides much more functionality in it. Not only can it display data and data relations, it also provides functionality to customize its styles including color, text, caption, and font. The TableStyles property of DataGrid opens the door for formatting a grid and its columns. The GridStyles property returns an object of GridTableStyleCollection, which is a collection of DataGridTableStyle.
DataGridTableStyle represents the style of a DataTable that can be viewed in the grid area of a DataGrid. The GridTableStyles class of DataGridTableStyle represents a collection of DataGridColumnStyle. Figure 7-8 represents the relationship between the DataGrid-related style objects. We discuss these objects in more detail in the following sections.
Figure 7-8: DataGrid-related style objects
Before you see these objects in action, you'll look at these object classes and their members briefly.
The DataGridTableStyle object customizes the grid style for each DataTable in a DataSet. However, the DataGridTableStyle name is a little misleading. From its name, you would probably think the DataGridTableStyle represents the style of a DataGridTable such as its color, text, and font. Correct? Actually, DataGridTableStyle represents the grid itself. Using DataGridTableStyle, you can set the style and appearance of each DataTable, which is being displayed by the DataGrid. To specify which DataGridTableStyle is used when displaying data from a particular DataTable, set the MappingName to the TableName of a DataTable. For example, if a DataTable's TableName is Customers, you set MappingName to the following:
DataGridTableStyle.Mapping="Customers"
The DataGridTableStyle class provides similar properties and methods to those in the DataGrid class. Some of these properties are AllowSorting, AlternativeBackColor, BackColor, ColumnHeaderVisible, ForeColor, and GridColumnStyle.
The GridColumnStyles property returns a collection of columns available in a DataGridTableStyle as a GridColumnStylesCollection, which is a collection of DataGridColumnStyle objects. By default all columns are available through this property.
The GridTableStyleCollection is a collection of DataGridTableStyle. The TableStyle property of DataGrid represents and returns a collection of DataGridTableStyle objects as a GridTableStylesCollection object.
Tip | DataGridTableStyle is useful when it comes to managing a DataGrid's style programmatically. One of the real-world usages of DataGridTableStyle is when you need to change the column styles of a DataGrid or want to move columns from one position to another programmatically. |
Unlike other collection objects, by default the GridTableStylesCollection doesn't contain any DataGridTableStyle objects. You need to add DataGridTableStyle objects to the collection. By default a DataGrid displays default settings such as color, text, font, width, and formatting. By default all columns of DataTable are displayed.
You'll now learn how to create a DataGridTableStyle object and add it to the DataGrid's DataGridTableStyle collection. Listing 7-17 creates a DataGridTableStyle, set its properties, and adds two columns: a Boolean and a text box column. The DataGridBookColumn class represents a Boolean column with check boxes, and the DataGridTextBoxColumn represents a text box column. (We discuss these classes in the following sections.)
Listing 7-17: Creating and Adding a DataGridTableStyle
Private Sub AddDataGridStyleMethod() ' Create a new DataGrudTableStyle Dim dgTableStyle As New DataGridTableStyle() dgTableStyle.MappingName = "Customers" dgTableStyle.BackColor = Color.Gray dgTableStyle.ForeColor = Color.Wheat dgTableStyle.AlternatingBackColor = Color.AliceBlue dgTableStyle.GridLineStyle = DataGridLineStyle.None ' Add some columns to the style Dim boolCol As New DataGridBoolColumn() boolCol.MappingName = "boolCol" boolCol.HeaderText = "boolCol Text" boolCol.Width = 100 ' Add column to GridColumnStyle dgTableStyle.GridColumnStyles.Add(boolCol) ' Text column Dim TextCol As New DataGridTextBoxColumn() TextCol.MappingName = "Name" TextCol.HeaderText = "Name Text" TextCol.Width = 200 ' Add column to GridColumnStyle dgTableStyle.GridColumnStyles.Add(TextCol) ' Add DataGridTableStyle to the collection DataGrid1.TableStyles.Add(dgTableStyle) End Sub
You can even create a DataGridTableStyle from a CurrencyManager. Listing 7-18 creates a DataGridTableStyle from a CurrencyManager and adds it the collection.
Listing 7-18: Creating a DataGridTableStyle from CurrencyManager
Private Sub CreateNewDGTableStyle() Dim curManager As CurrencyManager Dim newTableStyle As DataGridTableStyle curManager = CType _ (Me.BindingContext(ds, "Customers"), CurrencyManager) newTableStyle = New DataGridTableStyle(curManager) DataGrid1.TableStyles.Add(newTableStyle) End Sub
The DataGridColumnStyle represents the style of a column. You can attach a DataGridColumnStyle to each column of a DataGrid. The DataGrid can contain different types of columns such as a check box or a text box. As you saw earlier, a DataGridTableStyle contains a collection of DataGridColumnStyle objects, which can be accessed through the GridColumnStyles property of DataGridTableStyle. This object is pretty useful and allows many formatting- and style-related members. Table 7-5 describes the DataGridColumnStyle properties.
PROPERTY | DESCRIPTION |
---|---|
Alignment | Alignment of text in a column. Both get and set. |
DataGridTableStyle | Returns the DataGridTableStyle object associated with the column. |
HeaderText | Text of the column header. Both get and set. |
MappingName | Name used to map the column style to a data member. Both get and set. |
NullText | You can set the column text when the column has null values using this property. Both get and set. |
PropertyDescriptor | PropertyDescriptor object that determines the attributes of data displayed by the column. Both get and set. |
ReadOnly | Indicates if column is read only. Both get and set. |
Width | Width of the column. Both get and set. |
Besides the methods described in Table 7-6, the DataGridColumnStyle class provides a method, ResetHeaderText, which resets the header text to its default value of null.
METHOD | DESCRIPTION |
---|---|
Abort | Aborts the edit operation. |
BeginUpdate | Suspends the painting operation of the column until the EndUpdate method is called. |
CheckValidDataSource | If a column is not mapped to a valid property of a data source, this throws an exception. |
ColumnStartEditing | Informs DataGrid that the user has start editing the column. |
Commit | Completes the editing operation. |
ConcedeFocus | Notifies a column that it must relinquish the focus to the control it's hosting. |
Edit | Prepares the cell for editing a value. |
EndUpdate | Resumes the painting of columns suspended by calling the BeginUpdate method. |
EnterNullValue | Enters a DBNullValue into the column. |
GetColumnValueAtRow | Returns the value in the specified row. |
GetMinimumHeight | Returns the minimum height of a row. |
GetPreferedHeight | Returns the height used for automatically resizing columns. |
GetPreferedSize | Automatic size. |
Invalidate | Redraws the column. |
SetColumnValueAtRow | Sets a value in the specified row. |
SetDataGrid | Sets the DataGrid to which this column belongs. |
SetDataGridInColumn | Sets the DataGrid for the column. |
UpdateUI | Updates the value of a row. |
A DataGrid can contain different types of columns such as a check box or a text box. By default all columns are in a simple grid format. The DataGridBoolColumn class represents a Boolean column of a DataGrid. Each cell of a Boolean column contains a check box, which can be checked (true) or unchecked (false). The DataGridBoolColumn class is inherited from the DataGridColumnStyle class. Besides the functionality provided by the DataGridColumnStyle, it provides its own members. Table 7-7 describes the DataGridBoolColumn class properties.
PROPERTY | DESCRIPTION |
---|---|
AllowNull | Represents whether null values are allowed in this column or not (both get and set) |
FalseValue | Represents the actual value of column when the value of column is set to False (both get and set) |
NullValue | The actual value used when setting the value of the column to Value (both get and set) |
TrueValue | Represents the actual value of column when the value of column is set to True (both get and set) |
Listing 7-19 creates a new DataGridBoolColum and sets its properties.
Listing 7-19: Creating a DataGridBoolColumn
Dim dgCol As DataGridBoolColumn dgCol = CType(dtGrid.TableStyles _ ("Customers").GridColumnStyles("Current"), DataGridBoolColumn) dgCol.TrueValue = True dgCol.FalseValue = False dgCol.NullValue = Convert.DBNull
By default, navigation and sorting is on in a DataGrid. If a DataGrid is filled with data and you click the DataGrid header, it sorts data in ascending or descending order, depending on the current state. In other words, if the data is sorted in ascending order, right-clicking the header will sort it in descending order—and vice versa. You can activate or deactivate sorting programmatically using the AllowSorting property, which is a Boolean type. The following code shows how to set the AllowSorting property:
' Allow Sorting If (allowSortingCheckBox.Checked) Then dtGrid.AllowSorting = True Else dtGrid.AllowSorting = False End If
Like the AllowSorting property, the AllowNavigation property enables or disables navigation. Setting the property to True indicates that navigation in a DataGrid is allowed and setting it to False means that navigation is not allowed. When you change the AllowNavigation property, the AllowNavigationChanged event is fired. Perhaps you notice in the previous samples that if a DataSet had more than one database table, there were links to each table? When you click a table link, the DataGrid opens that table. If AllowNavigation is False, then no links to child tables display. Listing 7-20 uses AllowNavigation and also handles the AllowNavigationChanged event.
Listing 7-20: AllowNavigation in Action
' Change navigation using AllowNavigation property Private Sub NavigationMenu_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles NavigationMenu.Click ' Change navigation. If its true, change it to false and ' vice versa If dtGrid.AllowNavigation = True Then dtGrid.AllowNavigation = False Else dtGrid.AllowNavigation = True End If End Sub Private Sub AllowNavigationEvent(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles dtGrid.AllowNavigationChanged Dim nav As Boolean = dtGrid.AllowNavigation Dim str As String = "AllowNavigationChanged event fired. " If (nav) Then str = str + "Navigation is allowed" NavigationMenu.Checked = True Else str = str + "Navigation is not allowed" NavigationMenu.Checked = False End If MessageBox.Show(str, "AllowNavigation") End Sub
As mentioned, the DataGrid provides properties to set the foreground and background color of almost every part of a DataGrid such as headers, grid lines, and so on. The DataGrid also provides font properties to set the font of the DataGrid. Listing 7-21 sets Font and Color properties of a DataGrid.
Listing 7-21: Using Some of the DataGrid's Color and Font Properties
' Setting DataGrid's Color and Font properties dtGrid.BackColor = Color.Beige dtGrid.ForeColor = Color.Black dtGrid.BackgroundColor = Color.Red dtGrid.SelectionBackColor = Color.Blue dtGrid.SelectionForeColor = Color.Yellow dtGrid.GridLineColor = Color.Blue dtGrid.HeaderBackColor = Color.Black dtGrid.HeaderForeColor = Color.Gold 'dtGrid.AlternatingBackColor = Color.AliceBlue dtGrid.LinkColor = Color.Pink dtGrid.HeaderFont = New Font("Verdana", FontStyle.Bold) dtGrid.Font = New Font("Verdana", 8, FontStyle.Regular)
Tip | You can customize a DataGrid and allow the user to select a color and font for each part of the DataGrid at runtime as well as at design-time using the Properties window. |
You just saw the Font property of the DataGrid itself. The DataGrid also provides properties to set the caption's fonts and color. For example, Listing 7-22 sets the font, background color, and foreground color of caption of the DataGrid.
Listing 7-22: The DataGrid's Caption Properties
dtGrid.CaptionText = "Customized DataGrid" dtGrid.CaptionBackColor = System.Drawing.Color.Green dtGrid.CaptionForeColor = System.Drawing.Color.Yellow dtGrid.CaptionFont = New Font("Verdana", 10, FontStyle.Bold)
A common use of DataGridColumnStyle is changing the positions of a DataGrid's columns programmatically. In the following sections, you'll see some common usages of DataGridTableStyle and DataGridColumnStyle.
As mentioned, you'll see some real-world uses of data-bound controls in this chapter. Specifically, you'll learn how to add check box and text box columns to a DataGrid. You also know that the GridColumnStyles property returns a collection of DataGridTableStyle as an object of GridColumnStyleCollection. Using GridColumnStyleCollection you can add and remove column styles to a collection. This is what you'll use to add new columns to a collection and attach them to a DataGridTableStyle.
To start this application, create a Windows application and define a DataSet variable as private:
Private ds As DataSet = Nothing
On the form's Load event, you call the CreateDataSet, DataGrid.SetDataBinding, and FillDataGrid methods (see Listing 7-23).
Listing 7-23: Form's Load Method
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create in memory DataSet. You can even create ' a DataSet from a database CreateDataSet() ' Bind DataSet to DataGrid dtGrid.SetDataBinding(ds, "Employees") ' Fill data in DataGrid FillDataGrid() End Sub
The CreateDataSet method in Listing 7-24 simply creates a DataSet by creating a DataTable and adding three columns: EmployeeID (integer), Name (string), and StillWorking (Boolean). This method also adds four rows to the DataTable and adds the DataTable to a DataSet.
Listing 7-24: The CreateDataSet Method
' Create a DataSet with two tables and populate it. Private Sub CreateDataSet() ' Create a DataSet, add a DataTable ' Add DataTable to DataSet ds = New DataSet("ds") Dim EmployeeTable As DataTable = New DataTable("Employees") ' Create DataColumn objects and add to the DataTAable Dim dtType As System.Type dtType = System.Type.GetType("System.Int32") Dim EmpIDCol As DataColumn = _ New DataColumn("EmployeeID", dtType) Dim EmpNameCol As DataColumn = New DataColumn("Name") dtType = System.Type.GetType("System.Boolean") Dim EmpStatusCol As DataColumn = New DataColumn("StillWorking", dtType) EmployeeTable.Columns.Add(EmpIDCol) EmployeeTable.Columns.Add(EmpNameCol) EmployeeTable.Columns.Add(EmpStatusCol) ' Add first records Dim row As DataRow = EmployeeTable.NewRow() row("EmployeeID") = 1001 row("Name") = "Jay Leno" row("StillWorking") = False EmployeeTable.Rows.Add(row) ' Add second records row = EmployeeTable.NewRow() row("EmployeeID") = 1002 row("Name") = "Peter Kurten" row("StillWorking") = True EmployeeTable.Rows.Add(row) ' Add third records row = EmployeeTable.NewRow() row("EmployeeID") = 1003 row("Name") = "Mockes Pope" row("StillWorking") = False EmployeeTable.Rows.Add(row) ' Add fourth records row = EmployeeTable.NewRow() row("EmployeeID") = 1004 row("Name") = "Rock Kalson" row("StillWorking") = True EmployeeTable.Rows.Add(row) ' Add the tables to the DataSet ds.Tables.Add(EmployeeTable) End Sub
In Listing 7-25, the FillDataSet method creates a DataGridTableStyle and sets its properties. After that, it creates two DataGridTextBoxColumns and one DataGrid-BoolColumn and sets their properties. Also, it makes sure that the MappingName of the columns matches with the name of the columns of the DataTable. After creating each column, you add these methods to the column collection by using the DataGrid.GridColumnStyles.Add method. Finally, you add DataGridTableStyle to the DataGrid by using the DataGrid.TableStyles.Add method. After doing so, the DataGrid should have a new style with a check box and two text box columns.
Listing 7-25: The FillDataGrid Method
Private Sub FillDataGrid() ' Create a DataGridTableStyle and set its properties Dim dgTableStyle As DataGridTableStyle = New DataGridTableStyle() dgTableStyle.MappingName = "Employees" dgTableStyle.AlternatingBackColor = Color.Gray dgTableStyle.BackColor = Color.Black dgTableStyle.AllowSorting = True dgTableStyle.ForeColor = Color.White ' Create a DataGridColumnStyle. Add it to DataGridTableStyle Dim dgTextCol As DataGridColumnStyle = New DataGridTextBoxColumn() dgTextCol.MappingName = "Name" dgTextCol.HeaderText = "Employee Name" dgTextCol.Width = 100 dgTableStyle.GridColumnStyles.Add(dgTextCol) ' Get PropertyDescriptorCollection by calling GetItemProperties Dim pdc As PropertyDescriptorCollection = Me.BindingContext _ (ds, "Employees").GetItemProperties() 'Create a DataGrodTextBoxColu Dim dgIntCol As DataGridTextBoxColumn = _ New DataGridTextBoxColumn(pdc("EmployeeID"), "i", True) dgIntCol.MappingName = "EmployeeID" dgIntCol.HeaderText = "Employee ID" dgIntCol.Width = 100 dgTableStyle.GridColumnStyles.Add(dgIntCol) ' Add CheckBox column using DataGridCoolColumn Dim dgBoolCol As DataGridColumnStyle = New DataGridBoolColumn() dgBoolCol.MappingName = "StillWorking" dgBoolCol.HeaderText = "Boolean Column" dgBoolCol.Width = 100 dgTableStyle.GridColumnStyles.Add(dgBoolCol) ' Add table style to DataGrid dtGrid.TableStyles.Add(dgTableStyle) End Sub
You can use a hit test to get information about a point where a user clicks a control. There are many real-world usages of a hit test. For example, say you want to display two pop-up menus when a user right-clicks a certain area on a DataGrid. One area is on the DataGrid column header; this right-click pop-up menu will have options such as Sort Ascending, Sort Descending, Hide, and Find. As you can pretty guess from these names, the sort menu items will sort a column's data in ascending and descending order, the Hide menu item will hide (or delete) a column, and the Find menu item will search for a keyword in the selected column.
The second pop-up menu will pop up when you right-click any grid's cell. This menu will have options such as Move First, Move Previous, Move Next, and Move Last that will allow you to move to the first, previous, next, and last rows of a DataGrid.
Now, using only these two cases, you can find out what DataGrid part is processing the hit test action (in other words, which one is being clicked by the user). The HitTest method of DataGrid performs a hit test action.
The HitTest method takes a point and returns the DataGrid.HitTestInfo object, which determines the part of a DataGrid clicked by the user. It's useful when you're designing a custom grid and want to do different things when user clicks different parts of the DataGrid.
The DataGrid.HitTestInfo class has three properties: Column, Row, and Type. The Column and Row properties represent the number of the column and row that the user has clicked. The Type property specifies the part of the DataGrid that is clicked. The DataGrid.HitTestType enumeration is used as the Type property, which is defined in Table 7-8.
MEMBER | DESCRIPTION |
---|---|
Caption | Returns True if the caption was clicked. |
Cell | Returns True if a cell was clicked. |
ColumnHeader | Returns True if a column header was clicked. |
ColumnResize | Represents the column border, the line between column headers. |
None | Returns True if the background area was clicked. |
ParentRow | The parent row displays information about the parent table of the currently displayed child table. |
RowHeader | Returns True if the row header was clicked. |
RowResize | Returns True if the line between rows. |
You can also check the Type property against the combination of DataGrid.HitTestType enumeration members. Listing 7-26 is the mouse down event handler of a DataGrid, which tracks almost every portion of a DataGrid and generates a message when you right-click a DataGrid. Simply copy this code, right-click the DataGrid, and see it in action.
Listing 7-26: Seeing HitTest in Action
' DataGrid Mouse down event handler Private Sub dtGrid_MouseDown(ByVal sender As Object, _ ByVal e As System.Windows.Forms.MouseEventArgs) Handles dtGrid.MouseDown Dim grid As DataGrid = CType(sender, DataGrid) Dim hti As DataGrid.HitTestInfo ' When right mouse button was clicked If (e.Button = MouseButtons.Right) Then hti = grid.HitTest(e.X, e.Y) Select Case hti.Type Case DataGrid.HitTestType.None MessageBox.Show("Background") Case DataGrid.HitTestType.Cell MessageBox.Show("Cell - Row:" & hti.Row & ", Col: " & hti.Column) Case DataGrid.HitTestType.ColumnHeader MessageBox.Show("Column header " & hti.Column) Case DataGrid.HitTestType.RowHeader MessageBox.Show("Row header " & hti.Row) Case DataGrid.HitTestType.ColumnResize MessageBox.Show("Column seperater " & hti.Column) Case DataGrid.HitTestType.RowResize MessageBox.Show("Row seperater " & hti.Row) Case DataGrid.HitTestType.Caption MessageBox.Show("Caption") Case DataGrid.HitTestType.ParentRows MessageBox.Show("Parent row") End Select End If End Sub
How about reshuffling or moving DataGrid columns? Reshuffling a DataGrid's columns is a simple trick. You need to find which column you want to reshuffle. You can do this by using the column name or column index. In this sample, you'll use the column index.
How about reading information about a DataGridTableStyle and its columns? The following code reads information about a grid's tables and their names:
Dim gridStyle As DataGridTableStyle For Each gridStyle In DataGrid1.TableStyles infoStr = "Table Name: " + gridStyle.MappingName Dim colStyle As DataGridColumnStyle For Each colStyle In gridStyle.GridColumnStyles infoStr = "Column: " + colStyle.MappingName Next Next
Let's see this in action. Create a Windows application, add a DataGrid control, two Button controls, two Label controls, two TextBox controls, and a ListBox control. Next, set their properties and positions. The final form looks like Figure 7-9. As you can see, to exchange two columns, you enter column index in both text boxes and click the Exchange Columns button.
Figure 7-9: Column reshuffling form
Now let's write the code. As usual, you first define some variables:
' Developer defined variables Private conn As SqlConnection = Nothing Private ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Private sql As String = Nothing Private ds As DataSet = Nothing Private adapter As SqlDataAdapter = Nothing
Next, add a new method called FillDataGrid, which fills the DataGrid from the Customers table of the Northwind database. You call the FillDataGrid method from the form's Load event handler (see Listing 7-27). You can also see from the FillDataGrid method, this code adds DataGridTableStyle to each DataTable in a DataSet.
Listing 7-27: The FillDataGrid and Form_Load Methods
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load FillDataGrid() End Sub Private Sub FillDataGrid() sql = "SELECT * FROM Customers" conn = New SqlConnection(connectionString) adapter = New SqlDataAdapter(sql, conn) ds = New DataSet("Customers") adapter.Fill(ds, "Customers") DataGrid1.DataSource = ds.Tables(0).DefaultView ' By default there is no DataGridTableStyle object. ' Add all DataSet table's style to the DataGrid Dim dTable As DataTable For Each dTable In ds.Tables Dim dgStyle As DataGridTableStyle = New DataGridTableStyle() dgStyle.MappingName = dTable.TableName DataGrid1.TableStyles.Add(dgStyle) Next ' DataGrid settings DataGrid1.CaptionText = "DataGrid Customization" DataGrid1.HeaderFont = New Font("Verdana", 12) End Sub
Caution | What if you don't add DataGridTableStyle? By default, the DataGrid doesn't have any DataGridTableStyle and uses the default DataGridTableStyle. To make this program work, you must add it manually. |
Now you write code on the Exchange Columns button click event handler (see Listing 7-28). As you can see, you need to make sure that the text boxes aren't empty. After that you call the ReshuffleColumns method, which actually moves the columns from one position to another.
Listing 7-28: Exchanging the Button Click Handler
Private Sub ExchangeColsBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles ExchangeColsBtn.Click If (TextBox1.Text.Length < 1) Then MessageBox.Show("Enter a number between 0 to 19") TextBox1.Focus() Return ElseIf (TextBox2.Text.Length < 1) Then MessageBox.Show("Enter a number between 0 to 19") TextBox1.Focus() Return End If ' Get column 1 and column 2 indexes Dim col1 As Integer = Convert.ToInt32(TextBox1.Text) Dim col2 As Integer = Convert.ToInt32(TextBox2.Text) ' Exchange columns ReshuffleColumns(col1, col2, "Customers", DataGrid1) End Sub
As mentioned earlier, moving column positions in a grid involves resetting a DataGridTableStyle. As you can see from Listing 7-29, you read the current DataGridTableStyle and create a new DataGridTableStyle. Next, you copy the entire current DataGridTableStyle including the two columns that you want to exchange and then change positions of these columns. Next, you remove the current DataGridTableStyle from the DataGrid and apply the new DataGridTableStyle by using the DataGrid.TableStyles.Remove and DataGrid.TableStyles.Add methods.
Listing 7-29: The ReshuffleColumns Method
Private Sub ReshuffleColumns(ByVal col1 As Integer, _ ByVal col2 As Integer, ByVal mapName As String, ByVal grid As DataGrid) Dim existingTableStyle As DataGridTableStyle = grid.TableStyles(mapName) Dim counter As Integer = existingTableStyle.GridColumnStyles.Count Dim NewTableStyle As DataGridTableStyle = New DataGridTableStyle() NewTableStyle.MappingName = mapName Dim i As Integer For i = 0 To counter - 1 Step +1 If i <> col1 And col1 < col2 Then NewTableStyle.GridColumnStyles.Add _ (existingTableStyle.GridColumnStyles(i)) End If If i = col2 Then NewTableStyle.GridColumnStyles.Add _ (existingTableStyle.GridColumnStyles(col1)) End If If i <> col1 And col1 > col2 Then NewTableStyle.GridColumnStyles.Add _ (existingTableStyle.GridColumnStyles(i)) End If Next ' Remove the existing table style and add new style grid.TableStyles.Remove(existingTableStyle) grid.TableStyles.Add(NewTableStyle) End Sub
Reading information about a DataGrid's columns using a DataGridColumnStyle is simple. You just read the GridColumnStyleCollection using the GridColumnStyles property of DataGridTableStyle. Listing 7-30 reads a DataGrid's column styles and adds them to the ListBox control.
Listing 7-30: Getting a DataGrid Columns' Style Using DataGridColumnStyle
Private Sub GetInfoBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles GetInfoBtn.Click Dim infoStr As String = "Visible Rows: " + _ DataGrid1.VisibleRowCount.ToString() ListBox1.Items.Add(infoStr) infoStr = "Visible Cols: " + _ DataGrid1.VisibleColumnCount.ToString() ListBox1.Items.Add(infoStr) infoStr = "Total Rows: " + _ ds.Tables(0).Rows.Count.ToString() ListBox1.Items.Add(infoStr) infoStr = "Total Cols: " + _ ds.Tables(0).Columns.Count.ToString() ListBox1.Items.Add(infoStr) ' Get all table styles in the Grid and Column Styles ' which returns table and column names Dim gridStyle As DataGridTableStyle For Each gridStyle In DataGrid1.TableStyles infoStr = "Table Name: " + gridStyle.MappingName ListBox1.Items.Add(infoStr) Dim colStyle As DataGridColumnStyle For Each colStyle In gridStyle.GridColumnStyles infoStr = "Column: " + colStyle.MappingName ListBox1.Items.Add(infoStr) Next Next End Sub
Now run the application and enter 1 in the Column 1 box and enter 2 in the Column 2 box and then click the Exchange Columns buttons. You'll see both columns switched their positions. Now if you click the Get Grid Columns and Tables Info button, the output looks like Figure 7-10.
Figure 7-10: Getting a DataGrid control's column styles
Listing 7-31 returns the column name when a user right-clicks a DataGrid column header.
Listing 7-31: Getting a DataGrid Column Header Name
Private Sub DataGrid1_MouseDown(ByVal sender As Object, _ ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGrid1.MouseDown Dim str As String = Nothing Dim pt As Point = New Point(e.X, e.Y) Dim hti As DataGrid.HitTestInfo = DataGrid1.HitTest(pt) ' If right mouse button clicked If e.Button = MouseButtons.Right Then If hti.Type = DataGrid.HitTestType.ColumnHeader Then Dim gridStyle As DataGridTableStyle = _ DataGrid1.TableStyles("Customers") str = gridStyle.GridColumnStyles(hti.Column).MappingName.ToString() MessageBox.Show("Column Header " + str) End If End If ' If left mouse button clicked If e.Button = MouseButtons.Left Then If hti.Type = DataGrid.HitTestType.Cell Then str = "Column: " + hti.Column.ToString() str += ", Row: " + hti.Row.ToString() MessageBox.Show(str) End If End If End Sub
Now you'll learn a few more uses of a DataGrid control. Hiding a DataGrid column is simply a job of finding the right column and setting its Width property to 0. For an example, see the TotalDataGrid sample that comes with the downloads from www.apress.com.
To make your program look better, you'll create a right-click pop-up menu, as shown in Figure 7-11.
Figure 7-11: Pop-up menu on DataGrid right-click menu
To create a pop-up menu, you declare a ContextMenu and four MenuItem objects as sortAscMenu, sortDescMenu, findMenu, and hideMenu. You also define two more variables to store the current DataGridColumnStyle and column name as follows:
Private curColName As String = Nothing Private curColumnStyle As DataGridColumnStyle
If DataGrid.HitTestType is ColumnHeader, you add menu items and get the current column, as shown in Listing 7-32. In this listing, you simply store the current DataGridColumnStyle and the name of the column.
Listing 7-32: Getting the Current DataGridColumnStyle
Case DataGrid.HitTestType.ColumnHeader ' Add context menus popUpMenu = New ContextMenu() popUpMenu.MenuItems.Add("Sort ASC") popUpMenu.MenuItems.Add("Sort DESC") popUpMenu.MenuItems.Add("Find") popUpMenu.MenuItems.Add("Hide Column") Me.ContextMenu = popUpMenu Me.BackColor = Color.Sienna sortAscMenu = Me.ContextMenu.MenuItems(0) sortDescMenu = Me.ContextMenu.MenuItems(1) findMenu = Me.ContextMenu.MenuItems(2) hideMenu = Me.ContextMenu.MenuItems(3) ' Find the Column header name Dim gridStyle As DataGridTableStyle = _ dtGrid.TableStyles("Customers") curColName = gridStyle.GridColumnStyles _ (hti.Column).MappingName.ToString() curColumnStyle = gridStyle.GridColumnStyles(hti.Column)
Finally, you write the Find menu button click event handler and set curColumnStyle.Width to 0:
Private Sub hideMenuHandler(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles hideMenu.Click curColumnStyle.Width = 0 End Sub
By default a DataGrid provides you with sorting options when you click a DataGrid column. But there may be occasions when you don't want to use the default behavior and instead want to implement your own custom sorting.
In Figure 7-11, you saw the Sort ASC and Sort DESC menu options. As you probably remember from Chapter 3 and Chapter 4, sorting is easy to implement in a DataView. To sort a DataView, you simply set the Sort property of the DataView to the column name and to ASC or DESC for ascending and descending sorting, respectively. Listing 7-33 shows the Sort ASC and Sort DESC menu event handler code.
Listing 7-33: Sorting a DataGrid Control's Columns
Private Sub SortAscMenuHandler(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles sortAscMenu.Click Dim dv As DataView = ds.Tables("Customers").DefaultView dv.Sort = curColName + " ASC" dtGrid.DataSource = dv End Sub Private Sub SortDescMenuHandler(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles sortDescMenu.Click Dim dv As DataView = ds.Tables("Customers").DefaultView dv.Sort = curColName + " DESC" dtGrid.DataSource = dv End Sub
Move methods are one of the features of the ADO recordset that don't appear in ADO.NET. A recordset provides MoveFirst, MoveNext, MovePrevious, and MoveLast methods to move to the first, next, previous and last record in a recordset (respectively). In this example, you'll implement move functionality in a DataGrid control.
Listing 7-34 implements move functionality in a custom recordset class called CustRecordSet.vb. (We already discussed how you can use BindingContext to move the current pointer from one position to another.) In this code, CreateRecordSet simply fills and binds a DataSet to the grid. The FirstRecord, PrevRecord, NextRecord, and LastRecord methods set the current position of the pointer to the first row, current row -1, current row +1, and the last row (respectively).
Listing 7-34: CustRecordSet.vb
Imports System.Data.SqlClient Public Class CustRecordSet Private dataAdapter As SqlDataAdapter = Nothing Private dataSet As DataSet = Nothing Private dtGrid As DataGrid = Nothing Private frm As Form = Nothing Private mapName As String = Nothing Public Sub CreateRecordSet(ByVal conn As SqlConnection, _ ByVal sql As String, ByVal grid As DataGrid, ByVal curForm As Form, _ ByVal tableName As String) Me.dataAdapter = New SqlDataAdapter(sql, conn) Me.dataSet = New DataSet("Customers") Me.dataAdapter.Fill(Me.dataSet, "Customers") dtGrid = grid frm = curForm mapName = tableName dtGrid.DataSource = Me.dataSet dtGrid.DataMember = "Customers" End Sub Public Sub FirstRecord() If frm.BindingContext(Me.dataSet, mapName) Is Nothing Then Return End If frm.BindingContext(Me.dataSet, mapName).Position = 0 End Sub Public Sub PrevRecord() If frm.BindingContext(Me.dataSet, mapName) Is Nothing Then Return End If frm.BindingContext(Me.dataSet, mapName).Position -= 1 End Sub Public Sub NextRecord() If frm.BindingContext(Me.dataSet, mapName) Is Nothing Then Return End If frm.BindingContext(Me.dataSet, mapName).Position += 1 End Sub Public Sub LastRecord() If frm.BindingContext(Me.dataSet, mapName) Is Nothing Then Return End If frm.BindingContext(Me.dataSet, mapName).Position = _ frm.BindingContext(Me.dataSet, mapName).Count - 1 End Sub End Class
Note | In this example, the table name is Customers.You may want to customize the name so it can work for any database table. |
Now create a Windows application and add a DataGrid control and four Button controls (Move First, Move Next, Move Previous, and Move Last). The form's Load event calls FillDataSet, which creates a new CustRecordSet object and calls its CreateRecordSet method, which in turn fills data in a DataGrid control and binds a DataSet with the DataGrid control (see Listing 7-35).
Listing 7-35: Creating a Custom Recordset
' form load Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load FillDataGrid() End Sub ' Fill DataGrid Private Sub FillDataGrid() sql = "SELECT * FROM Customers" conn = New SqlConnection(connectionString) recordSet = New CustRecordSet() recordSet.CreateRecordSet(conn, sql, DataGrid1, Me, "Customers") End Sub
Now on the button click event handlers, simply call CustRecordSet's FirstRecord, PrevRecord, NextRecord, and LastRecord methods, as shown in Listing 7-36.
Listing 7-36: Moving Record Button Click Event Handlers
' Move First button click Private Sub MoveFirstBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MoveFirstBtn.Click recordSet.FirstRecord() End Sub ' Move Previous button click Private Sub MovePrevBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MovePrevBtn.Click recordSet.PrevRecord() End Sub ' Move next button click Private Sub MoveNextBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MoveNextBtn.Click recordSet.NextRecord() End Sub ' Move last button click Private Sub MoveLastBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MoveLastBtn.Click recordSet.LastRecord() End Sub
The final application looks like Figure 7-12.
Figure 7-12: DataGrid navigation system
Tip | You can implement the same functionality on a DataGrid control's right-click menu by adding four menu items that allow users to move to the first, next, previous, and last records of a DataGrid. You can even develop your own DataGrid component with sorting, searching, and navigating features. |
You just saw how to implement custom sorting in a DataGrid control. After sorting, searching is one more basic requirement of database-driven applications. There are two methods to implement search in a DataGrid control:
Using the SELECT statement
Using a DataSet and DataView
You already used search functionality in a connected environment using the SQL SELECT statement. Do you remember using a SELECT statement with a WHERE clause? In a WHERE clause, you passed the criteria specifying the data for which you're looking. If you want to search in multiple tables, you construct a JOIN query with WHERE clause. You can even search for a keyword using the SELECT...LIKE statement, which was discussed in "The DataView in Connected Environments" section of Chapter 4.
You use the SELECT statement in a DataAdapter, which reads data based on the SELECT statement and the criteria passed in it. However, using this method for searching may not be useful when you're searching data frequently—especially when you're searching data in a DataGrid. We suggest not using this method when searching data in an isolated application and there's no other application updating the data. Why? The main reason is that every time you change the SELECT statement, you need to create a new DataAdapter and fill the DataSet when you change the SELECT statement. This method is useful when there are multiple applications updating the data simultaneously and you want to search in the latest updated data.
The DataTable and DataView objects provide members that can filter data based on criteria. (See "The DataView" section in Chapter 3 for more information.) You can simply create a DataView from a DataSet, set a DataView's RowFilter property to the search criteria, and then bind the DataView to a DataGrid, which will display the filtered records.
Tip | Using the same method, you can implement a Search or Find feature in a DataGrid control. You can also provide a Search option on a DataGrid control's header so that you know on which column a user has clicked. |
The new application looks like Figure 7-13. Obviously, the Search button searches the column entered in the Column Name text box for a value entered in the Value text box.
Figure 7-13: Implementing search functionality in a DataGrid control
Note | If you search for a string, use a singe quote (') before and after the string. |
We discuss the Save method functionality in the following section.
After creating a Windows application and adding controls to the form, define following variables:
' Developer defined variables Private conn As SqlConnection = Nothing Private connectionString As String = _ "Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=MCB;" Private sql As String = Nothing Private searchView As DataView = Nothing Dim adapter As SqlDataAdapter = Nothing Dim ds As DataSet = Nothing
Now add the FillDataGrid method, which fills the DataGrid and creates a DataView called searchView (see Listing 7-37).
Listing 7-37: FillDataGrid Method
' Fill DataGrid Private Sub FillDataGrid() sql = "SELECT * FROM Orders" conn = New SqlConnection(connectionString) adapter = New SqlDataAdapter(sql, conn) ds = New DataSet("Orders") adapter.Fill(ds, "Orders") DataGrid1.DataSource = ds.Tables("Orders") searchView = New DataView(ds.Tables("Orders")) Dim cmdBuilder As SqlCommandBuilder = _ New SqlCommandBuilder(adapter) ' Disconnect. Otherwise you would get ' Access violations when try multiple operations conn.Close() conn.Dispose() End Sub
Now, the next step is to set a RowFilter of searchView based on the values entered in the Column Name and Value text fields. Listing 7-38 shows the code for the Search button. As you can see, the code sets the RowFilter of searchView and binds it to the DataGrid to display the filtered data.
Listing 7-38: Seach Button Click Event Handler
Private Sub SearchBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles SearchBtn.Click If (TextBox1.Text.Equals(String.Empty)) Then MessageBox.Show("Enter a column name") TextBox1.Focus() Return End If If (TextBox2.Text.Equals(String.Empty)) Then MessageBox.Show("Enter a value") TextBox1.Focus() Return End If ' Construct a row filter and apply on the DataView Dim str As String = TextBox1.Text + "=" + TextBox2.Text searchView.RowFilter = str ' Set DataView as DataSource of DataGrid DataGrid1.DataSource = searchView End Sub
At this time, if you run the application, the data from the Orders table is filled in the DataGrid If you enter EmployeeID in the Column Name text box and 6 in the Value field and then click the Search button, the filtered data looks like Figure 7-14.
Figure 7-14: Filtered data after searching
As you learned earlier, the DataGrid control is one of the most powerful, flexible, and versatile controls available in Windows Forms. It has an almost unlimited number of properties and methods. You can add new records, update records, and delete existing records on a DataGrid with little effort, and you can easily save the affected data in a database.
When a DataGrid control is in edit mode (the default mode), you can simply add a new record by clicking the last row of the grid and editing the column values. You can update data by changing the existing value of cells. You can delete a row by simply selecting a row and clicking the Delete button.
In the previous example, you used a Save Changes button on a form (see Figure 7-14). Now just write the code in Listing 7-39 on the Save Changes button click to save the data.
Listing 7-39: Saving Updated Data in a Data Source from a DataGrid Control
Private Sub SaveBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles SaveBtn.Click Dim changeDS As DataSet = New DataSet() ' Data is modified If (ds.HasChanges(DataRowState.Modified)) Then changeDS = ds.GetChanges(DataRowState.Modified) Dim changedRecords As Integer changedRecords = adapter.Update(changeDS, "Orders") If (changedRecords > 0) Then MessageBox.Show(changedRecords.ToString() & _ " records modified.") End If End If ' Data is deleted If (ds.HasChanges(DataRowState.Deleted)) Then changeDS = ds.GetChanges(DataRowState.Deleted) Dim changedRecords As Integer changedRecords = adapter.Update(changeDS, "Orders") If (changedRecords > 0) Then MessageBox.Show(changedRecords.ToString() & _ " records deleted.") End If End If ' Data is added If (ds.HasChanges(DataRowState.Added)) Then changeDS = ds.GetChanges(DataRowState.Added) Dim changedRecords As Integer changedRecords = adapter.Update(changeDS, "Orders") If (changedRecords > 0) Then MessageBox.Show(changedRecords.ToString() & _ " records added.") End If End If ds.AcceptChanges() DataGrid1.Refresh() End Sub
As you can see from Listing 7-39, you simply get the modified, deleted, and updated changes in a new DataSet by calling the DataSet.GetChanges method and save the changes by calling the DataAdapter.Update method. In the end, you accept the changes by calling DataSet.AcceptChanges and refresh the DataGrid control by calling the DataGrid.Refresh method.