The DataGrid: Super Data-Bound Control

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.

click to expand
Figure 7-6: The DataGrid's parent items and background

click to expand
Figure 7-7: The DataGrid's parts

Understanding the DataGrid Class Members

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.

Table 7-2: The DataGrid Class Properties




Indicates whether navigation is allowed. True or false. Both get and set.


Indicates whether sorting is allowed. True or false. Both get and set.


Background color of alternative rows.


Background color of the grid.


Color of the nonrow area of the grid. This is the background color if the grid has no rows.


Style of the border.


Background color of caption.


Font of caption.


Foreground color of caption.


Caption text.


Indicates whether caption is visible.


Indicates whether column headers are visible.


Returns current selected cell.


Index of the selected row.


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.


Represents the data source such as a DataSet, a DataTable, or Ilist.


Index of the first visible column.


FlatMode. Type of FlatMode enumeration.


Foreground color.


Color of grid lines.


Style of grid lines.


Background color of column headers.


Font of column headers.


Foreground color of column headers.


Value of the specified cell.


Color of the text that you can click to navigate to a child table.


Link color changes to when the mouse moves over it.


Background color of parent rows. Parent rows are rows that allow you to move to child tables.


Foreground color of parent rows.


Label style of parent rows.


Indicates whether parent rows are visible.


Default width of columns in pixel.


Default height of rows in pixels.


Indicates whether grid is read only.


Indicates whether row header is visible.


Width of row headers.


Background color of selected rows.


Foreground color of selected rows.


Table style. DataGridTableStyle type.


Total number of visible columns.


Total number of visible rows.


Protected. Returns the horizontal scroll bar of the grid.


Protected. Returns the horizontal scroll bar of the grid.


Protected. Returns the CurrencyManager of the grid.

Table 7-3 describes the DataGrid class methods.

Table 7-3: The DataGrid Class Methods




Starts the editing operation


Begins the initialization of grid that is used on a form or used by other components


Collapses children if a grid has parent and child relationship nodes expanded


Ends the editing operation


Ends grid initialization


Expands children if grid has children in a parent/child relation


Returns a rectangle that specifies the four corners of the selected cell


Gets information when clicking on the grid


True if node of the specified row is expanded; otherwise false


True if specified row is selected; otherwise false


Navigates to the table previously displayed in the grid


Navigates to the table specified by the row and relation name


Resets the AlternatingBackColor property to the default color


Resets background color to default


Resets grid lines color to default


Resets header background to default


Resets header font to default


Resets header foreground color to default


Resets link color to default


Resets selection background color to default


Resets selection foreground color to default


Selects a specified row


Sets the DataSource and DataMember properties


Unselects a specified row

Besides the methods described in Table 7-3, the DataGrid class provides some protected methods (see Table 7-4).

Table 7-4: The DataGrid Class Protected Methods




Cancels the current edit operation and rolls back all changes


Listens for the horizontal scroll bar's scroll event


Listens for the vertical scroll bar's scroll event


Listens for the caption's Back button clicked event


Raises the BorderStyleChanged event


Raises the CaptionVisibleChanged event


Raises the DataSourceChanged event


Raises the FlatModeChanged event


Raises the Navigate event


Raises the ParentRowsLabelStyleChanged event


Raises the ParentRowsVisibleChanged event


Raises the ReadOnlyChanged event


Raises the RowHeaderClick event


Raises the Scroll event


Raises the ShowParentDetailsButtonClick event


Processes keys for grid navigation


Gets a value indicating whether the Tab key should be processed


Turns off selection for all rows that are selected

Exploring the DataGrid Helper Objects

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.

Understanding the DataGrid and DataGrid Column Styles

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.

click to expand
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.

Using the DataGridTableStyle Class

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:


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.

Using the GridColumnStyles Property

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.

Using the GridTableStyleCollection Members

The GridTableStyleCollection is a collection of DataGridTableStyle. The TableStyle property of DataGrid represents and returns a collection of DataGridTableStyle objects as a GridTableStylesCollection object.


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.

Constructing and Adding a DataGridStyle

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

start example
 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 
end example

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

start example
 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 
end example

Using the DataGridColumnStyle Class

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.

Table 7-5: The DataGridColumnStyle Properties




Alignment of text in a column. Both get and set.


Returns the DataGridTableStyle object associated with the column.


Text of the column header. Both get and set.


Name used to map the column style to a data member. Both get and set.


You can set the column text when the column has null values using this property. Both get and set.


PropertyDescriptor object that determines the attributes of data displayed by the column. Both get and set.


Indicates if column is read only. Both get and set.


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.

Table 7-6: The DataGridColumnStyle Methods




Aborts the edit operation.


Suspends the painting operation of the column until the EndUpdate method is called.


If a column is not mapped to a valid property of a data source, this throws an exception.


Informs DataGrid that the user has start editing the column.


Completes the editing operation.


Notifies a column that it must relinquish the focus to the control it's hosting.


Prepares the cell for editing a value.


Resumes the painting of columns suspended by calling the BeginUpdate method.


Enters a DBNullValue into the column.


Returns the value in the specified row.


Returns the minimum height of a row.


Returns the height used for automatically resizing columns.


Automatic size.


Redraws the column.


Sets a value in the specified row.


Sets the DataGrid to which this column belongs.


Sets the DataGrid for the column.


Updates the value of a row.

Using the DataGridBoolColumn Class

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.

Table 7-7: The DataGridBoolColumn Properties




Represents whether null values are allowed in this column or not (both get and set)


Represents the actual value of column when the value of column is set to False (both get and set)


The actual value used when setting the value of the column to Value (both get and set)


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

start example
 Dim dgCol As DataGridBoolColumn dgCol = CType(dtGrid.TableStyles _ ("Customers").GridColumnStyles("Current"), DataGridBoolColumn) dgCol.TrueValue = True dgCol.FalseValue = False dgCol.NullValue = Convert.DBNull 
end example

Setting DataGrid Sorting and Navigation

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

start example
 ' 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 
end example

Setting DataGrid Coloring and Font Styles

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

start example
 ' 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) 
end example


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.

Setting Caption Properties

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

start example
 dtGrid.CaptionText = "Customized DataGrid" dtGrid.CaptionBackColor = System.Drawing.Color.Green dtGrid.CaptionForeColor = System.Drawing.Color.Yellow dtGrid.CaptionFont = New Font("Verdana", 10, FontStyle.Bold) 
end example

Seeing DataGridTableStyle and DataGridColumnStyle in Action

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

start example
 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 
end example

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

start example
 ' 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 
end example

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

start example
 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 
end example

Seeing HitTest in Action

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.

Using the DataGrid.HitTestInfo Class

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.

Table 7-8: The DaaGrid.HitTestType Enumeration




Returns True if the caption was clicked.


Returns True if a cell was clicked.


Returns True if a column header was clicked.


Represents the column border, the line between column headers.


Returns True if the background area was clicked.


The parent row displays information about the parent table of the currently displayed child table.


Returns True if the row header was clicked.


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

start example
 ' 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 
end example

Reshuffling DataGrid Columns

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.

click to expand
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

start example
   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 
end example


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

start example
 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 
end example

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

start example
 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 
end example

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

start example
 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 
end example

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.

click to expand
Figure 7-10: Getting a DataGrid control's column styles

Getting a Column Header Name

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

start example
   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 
end example

Hiding a DataGrid's Columns

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

To make your program look better, you'll create a right-click pop-up menu, as shown in Figure 7-11.

click to expand
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

start example
 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) 
end example

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 

Implementing Custom Sorting in a DataGrid

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

start example
 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 
end example

Building a DataGrid Record Navigation System

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

start example
 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 
end example


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

start example
 ' 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 
end example

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

start example
 ' 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 
end example

The final application looks like Figure 7-12.

click to expand
Figure 7-12: DataGrid navigation system


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.

Implementing Search in a DataGrid

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

Searching Using the SELECT Statement

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.

Searching Using a DataTable and DataView

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.


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.

click to expand
Figure 7-13: Implementing search functionality in a DataGrid control


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

start example
 ' 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 
end example

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

start example
 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 
end example

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.

click to expand
Figure 7-14: Filtered data after searching

Inserting, Updating, and Deleting Data through DataGrids

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

start example
 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 
end example

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.

Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214 © 2008-2017.
If you may any questions please contact us: