Managing DataGrid Properties

As mentioned previously, the DataGrid control is likely to be a popular tool for displaying data in your database applications. This is because it is a very rich control with lots of flexibility. For example, an earlier sample ( Form5 in the NavigationandAssignment project) showed how to dynamically populate the control and toggle the display of alternating background colors. This section demonstrates how to program four more DataGrid control properties in two sample applications. This section also demonstrates how to process the CurrentCellChanged event for the DataGrid control. The two samples in this section aim to familiarize you with programming the DataGrid control on a Windows form. However, the second sample illustrates how to combine a series of basic features that are highly useful for reading values from a grid to perform computations and facilitate user navigation around the grid cells .

Note  

If after finishing these samples you want more advanced coverage of programming DataGrid controls, I urge you to use the Data Form Wizard to create forms with one to two DataGrid controls. Then, view the code behind the form. Take special of the code in the Windows Form Designer region.

The ReadOnly and AllowSorting Properties

This section describes a sample that demonstrates how to dynamically manipulate the ReadOnly and the AllowSorting properties for a DataGrid control. The ReadOnly property for a DataGrid control determines whether a user can edit the values in the cells of a DataGrid control. By default, users can edit the content of cells. However, you can dynamically change this property s setting at run time ”for example, after a user clicks a button ”to protect the values in a DataGrid control.

Although the ReadOnly property enables and disables the editing of individual cells, you manipulate the ReadOnly property for all the cells in a DataGrid control at once. The AllowSorting property determines whether you can click a column header in a DataGrid control to sort the rows by that column. The AllowSorting property is True by default. Unless the AllowSorting property is False , users can toggle the sort order of the rows in a DataGrid control by clicking a column header successively. The first click sorts in ascending order, and the second click reverses the order. After that, successive clicks continue to toggle the sort order between the two directions.

Note  

If the DataGrid control cells point back at a table in a local dataset, changes to those cells persist for the current session ” namely, the lifetime of the form with the DataGrid control. You can persist the DataGrid cell values beyond the lifetime of the form that contains a DataGrid control by storing the cells in a nonvolatile medium, such as an Access database file.

Figure 6-22 presents a design view of the sample form for this application, Form2 in the ManagingGrids project. Form1 in the project is a switchboard form that lets a user move to either of the two forms in this section. The tray below Form2 indicates the ADO.NET variables for making a connection to the Ten Most Expensive Products query in the Northwind database. You can create these ADO.NET variables with the help of Server Explorer and by generating a dataset from the resulting data adapter.

Figure 6-22: The Design view of a form with three buttons to control and monitor the formatting of a DataGrid control

Three buttons along the top of the form allow users to manipulate and examine properties for the DataGrid control that appears below them. Button1 opens a message box that reports the current value of the ReadOnly property for the DataGrid control. This property setting is initially False , but users can toggle it with Button2 . Button3 allows you to set the AllowSorting property to True . When a DataGrid initially opens, the AllowSorting property is True by default, but the Form2_Load procedure sets the AllowSorting property to False . Button3 does not enable toggling. Therefore, once a user switches sorting on with Button3 , the form continues to support dynamic sorting by the user for its lifetime. Closing and reopening the form disables sorting again until a user clicks Button3 .

The code behind Form2 includes four event procedures: one for each button, plus a form load event procedure. The form s load event procedure performs four kinds of tasks . These start by setting the first table in the local dataset ” Tables (0) ” to the DataSource property for DataGrid1 . The application initially disables sorting. Therefore, the second block sets the DataGrid control s AllowSorting property to False . Next , the code assigns Text property settings to the buttons. The event procedure sets the Text property for Button2 to True . The Text property for this button indicates what the ReadOnly property will be after a click to the button. Because the ReadOnly property is False by default, the initial setting of the button is True . Clicking the button switches the Text property s setting to False . The last block of code in the load event procedure lengthens the form and DataGrid control to display all 10 products without the need for a vertical scroll bar.

The Button1 click event procedure might be the most straightforward of the three procedures, but all the button click event procedures are easy to follow. The event procedure for Button1 presents a message box displaying the ReadOnly property setting for DataGrid1 as a string value.

The Button2 click event procedure works in coordination with the Button1_Click procedure. The event procedure for Button2 toggles the ReadOnly property for DataGrid1 and the Text property for Button2 . As mentioned, the ReadOnly property is initially False . If the user clicks Button2 immediately after opening the form, the DataGrid control on the form becomes ReadOnly . (The ReadOnly property setting becomes True .) At this point, a user cannot edit the values in any grid cell. However, clicking Button2 again restores the ability to edit cell values in a DataGrid control. Successive clicks to Button2 toggle between these two states. By clicking Button1 , a user can verify the ReadOnly status of the form s DataGrid control.

The Button3_Click event procedure prompts the user with a message box asking whether to enable sorting. As you have learned, the form s load event procedure disables the capability. If the user replies Yes to the prompt in the message box, the procedure sets the AllowSorting property for DataGrid1 to True . After that, sorting is available for columns in the grid control until a user closes the session.

 Private Sub Form2_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Populate DataGrid1. OleDbDataAdapter1.Fill(DsTenMostExpensiveProducts1, _ "Ten Most Expensive Products") DataGrid1.DataSource = _ DsTenMostExpensiveProducts1.Tables(0) Disable ability to sort columns. DataGrid1.AllowSorting = False Assign Text property values to buttons. Button1.Text = "ReadOnly?" Button2.Text = "True" Button3.Text = "Sorting OK?" Adjust form and control height to display 10 items without scrolling. Me.Height += 40 DataGrid1.Height += 40 End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Display ReadOnly property value for DataGrid1. MsgBox(DataGrid1.ReadOnly.ToString) End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Toggle the Text property of Button2 and ReadOnly property value for DataGrid1. If Button2.Text = "True" Then DataGrid1.ReadOnly = True Button2.Text = "False" Else DataGrid1.ReadOnly = False Button2.Text = "True" End If End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Enable sorting only if user is sure it is OK. If MsgBox("Are you sure?", _ MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo) = _ MsgBoxResult.Yes Then DataGrid1.AllowSorting = True End If End Sub 

The CurrentCell and CurrentRowIndex Properties

The previous sample has no screens illustrating its performance because its purpose was to demonstrate programming techniques, such as initializing properties for the DataGrid control on a Windows form. The DataGrid control for a Windows form has an extensive collection of properties. For example, a sample in the Navigation and Data Source Assignment Issues section earlier in this chapter demonstrated how to program the AlternatingBackColor property.

This section will illustrate how to manage navigation across a grid and read values from the grid with the CurrentCell and CurrentRowIndex properties. You can view the full list of DataGrid control properties for Windows forms in the Microsoft Visual Studio .NET documentation. Use the guidelines for programming control properties in this chapter as you start to program other DataGrid control properties and even properties for other controls.

Note  

When you have a specific topic to look up in the Visual Studio .NET documentation, it can be helpful to use the Index tab. If this tab is not open in the documentation window, choose Help, Index. Then, type the specific term you seek in the Look For box and press Enter. For example, if you type DataGrid , you will see many help topics, but one of them will be DataGrid class (System.Windows.Forms) . Nested within that topic will be several subtopics, including one named properties . Clicking this topic will open a Help page with an exhaustive list of DataGrid class properties for Windows forms.

The sample application for this section programs the CurrentCell and CurrentRowIndex properties to facilitate two types of functions for a grid on a Windows form. The first type of function facilitates navigation between rows. Although it is relatively easy to move around a DataGrid control on a form by clicking a cell in one row or another, the ease of this approach diminishes as the number of rows increases . For example, scrolling to the last row in a grid with thousands of rows can be tedious . The second type of function demonstrates how to extract values from any subset of cells on the current row in a grid. The sample application computes the extended price for a row from a DataGrid control with a DataSource property set to a table in a dataset initially populated from the Order Details table in the Northwind database.

The Design View of Form3

Figure 6-23 shows the form for the application, which is Form3 in the ManagingGrids project. Five Button controls and a TextBox control at the top of the form help to manage navigation across the rows in the DataGrid control at the bottom of the form. A text box between the second and third buttons in the top row of controls provides feedback on the current row in the total set of rows for the DataGrid control. Users can also use the text box along with the far right button to navigate to a new row.

Figure 6-23: The Design view of a form for controlling navigation and reading grid cell values via the programming of the CurrentCell and CurrentRowIndex properties

To give you a feel for working with a dataset that is not tiny, the DataGrid control s data source is the Order Details table, which contains 2155 rows. The DsOrderDetails1 variable points at the DsOrderDetails dataset containing the Order Details table that a data adapter populates from the Northwind database. Between the navigation controls and the DataGrid control are a pair of text boxes with corresponding labels above them. The role of TextBox2 is to display the OrderID for the current row, and TextBox3 displays the extended price for the selected row in the DataGrid control. As mentioned earlier, extended price is the product of Quantity, UnitPrice, and 1 minus Discount.

Form3 in Action

The sample application supports three styles of navigation. Figure 6-24 reveals two screens from the application in operation. The top window depicts Form3 when the application opens but with the mouse pointer positioned over row 7 to click the OrderID column. Notice that the form initially opens Form3 with the OrderID column value selected on the first row. The bottom window shows Form3 after the click to the OrderID column in row 7. Notice the text box between the navigation buttons at the top of the form refreshes its contents from 1 of 2155 to 7 of 2155. In addition, the OrderID and Price boxes just above the grid change their values to reflect the current row.

Note  

By contrasting Figure 6-24 with Figure 6-23, you can see that the application formats the labels that tag TextBox2 and TextBox3 . The application resizes and repositions the labels relative to the text boxes and changes the text alignment for the labels. In addition, the contents of the two text boxes has formatting for alignment, and the box showing extended price applies a currency format to its contents.

Figure 6-24: The sample illustrating the use of the CurrentCell and CurrentRowIndex properties permits navigation via three techniques, including simply clicking a cell.

Figure 6-25 illustrates another means of navigating around the contents of the grid. The top window shows Form3 after a click to the >> button, but just before a click to the < button. The >> button navigates to the last row in the grid. The < button navigates to the previous row. Notice that no particular cell is selected. Instead, the top and bottom windows mark the selected item in the row selector on the left edge of the grid control.

Figure 6-25: The CurrentCell and CurrentRowIndex property sample enables navigation via buttons at the top of Form3 .

A third way to navigate to a row is by typing a row number into the text box between the < and > buttons. Figure 6-26 shows the next to the last row selected. However, the text box between the navigation buttons has the value 8 in it. (See the top window.) After clicking the Go To button, which is the far right navigation button, the current row transfers to row 8. Because of the direction of navigation, this row appears at the top of the grid. (See the bottom window.) However, you can confirm that it is the eighth row by comparing the column values for the top row in the bottom window of Figure 6-26 with the column values for the last row in the top window from Figure 6-24. The top window from Figure 6-24 shows the first eight rows in the grid, and its last row of column values matches the column values of the row navigated to by clicking the Go To button in the bottom window of Figure 6-26.

Figure 6-26: The CurrentCell and CurrentRowIndex property sample enables navigation when the user enters a row number in a text box and clicks a button.

Let s look at another interesting operational feature of the sample application. Clicking the very bottom row (which has an asterisk in the row selector) generates an error when the code behind the form attempts to compute extended price for the Price text box. This is because the inputs to the expression for extended price are Null for the very last row in the DataGrid control. The program intercepts the exception resulting from a computation on Null values and rolls the current row back to the last valid row. However, the program moves the selected column cell to the column selected in the very bottom row of the grid. Figure 6-27 illustrates this behavior. The top window shows the OrderID cell selected on row 2150. In addition, you can see the mouse pointer in the Quantity column of the very bottom row. Clicking at that point generates the exception that the code traps and repositions the currently selected cell to the Quantity column of row 2150. (See the bottom window.)

Figure 6-27: The CurrentCell and CurrentRowIndex property sample traps invalid navigation to the blank row at the bottom of the grid and repositions the currently selected cell.

The Code Behind Form3

The code behind the form contains three parts. The form load event is the first part. It sets up the form with data and navigates to the initial cell position. The second part handles navigating by clicking to a cell. This part can also process a click out of bounds to the very bottom row in the grid. The third part is a set of event procedures for using the navigation bar at the top of the form. In addition to these distinct parts , a module-level variable tracks the last legitimate row for recovering from an out-of-bounds move.

The intOldRow variable is the module-level variable that tracks the last legitimate row position. The variable is set initially in the load event procedure for Form3 . The first setting will be legitimate. Then, the application resets intOldRow after each successful move. When the .NET Framework throws an error for the computation of extended price because of an attempt to move out of bounds (the empty row at the very bottom of the grid), the event procedure handling a changed current cell recovers by using the last legitimate value in intOldRow to move to a valid row.

The form load event procedure starts by populating the grid with the Order Details table from the Northwind database. Then, the procedure sets the focus to the second column in the first row by setting the DataGrid control s CurrentCell property. The procedure instantiates a DataGridCell structure value pointing at the starting location for the cursor when the form opens. Next, the procedure saves the CurrentRowIndex property value in the intOldRow variable. The next task for the load event procedure is the resizing of the DataGrid control and the form. The code for this resizing effort precedes another block of code making Text and Height property assignments for the navigation buttons. The assignments for Button5 (the Go To button) are slightly different because this button needs a wider face than the other buttons. The Height settings make the buttons the same height as TextBox1 , which gives an even appearance to the navigation bar at the top of the form. The last segment of code in the form load event procedure manages the appearance of the labels for the TextBox controls holding the OrderID and Price for the currently selected row.

One of the most pivotal procedures in the code behind the form is the DataGrid1_CurrentCellChanged procedure. The .NET Framework raises the CurrentCellChanged event whenever the CurrentCell property for DataGrid1 changes. Users can change the current cell by clicking another cell or using one of the navigation buttons. In fact, initializing the CurrentCell property in the form load event procedure raises the CurrentCellChanged event. This procedure populates TextBox1 , TextBox2 , and TextBox3 . The procedure updates TextBox1 with the value of the current position through a call to the UpdatePositionIndicator procedure. The expression for TextBox3 can raise an exception because of Null values. Therefore, the DataGrid1_CurrentCellChanged procedure uses a Try Catch Finally statement to trap errors that result from the computation. Null values throw an InvalidCastException object. If the Try Catch Finally statement detects this object, it means the user clicked the empty cell at the bottom of the grid. Therefore, the procedure recovers in the Catch clause. The recovery technique sets the CurrentCell property for DataGrid1 . This action transfers control to the start of the DataGrid1_CurrentCellChanged procedure. A Catch clause for a SystemException object detects any other kind of error and exits the Try Catch Finally statement so that the user can recover manually. The Finally clause handles any legitimate move by resetting the value of the intOldRow variable.

The UpdatePositionIndicator procedure is short, but it is interesting for a couple of reasons. The position indicator for the current row is the Text property for TextBox1 . This text box shows two values ”one for the current row and the other for the total number of rows in the table behind DataGrid1 . The procedure uses the RowNumber property of the CurrentCell member of the grid to return the current row. Because RowNumber property values are zero-based but the row indicator in TextBox1 is one-based, the expression for the row number adds 1 to the RowNumber property. To compute the total number of rows in the grid, the procedure invokes the Count method for the Rows collection of the Order Details table in the local dataset. (The DsOrderDetails1 variable points at the dataset.)

All the remaining procedures in the application handle the navigation buttons. Button1 through Button4 correspond to the << through >> buttons. For example, the Button1_Click procedure moves to the first row by setting the CurrentRowIndex property to 0, which ultimately invokes the UpdatePositionIndicator procedure. The Button4_Click procedure is very similar, except that it sets the CurrentRowIndex property to the last row value, which is 1 less than the count of rows in the Order Details table. The Button2_Click and Button3_Click procedures also have a symmetry because they move the current row in opposite directions by a single row. These two procedures merely add or subtract 1 from the CurrentRowIndex property.

The Button5_Click procedure works in tandem with the current Text property for TextBox1 . If the Button5_Click procedure discovers that the return value for the CInt function called on the Text property is in the range of valid rows for the DataGrid control, the procedure assigns the valid row number to the CurrentRowIndex property and resets the position indicator to reflect the current row position in the DataGrid control. If the Text property for TextBox1 does not point at a valid row number, the procedure reverts to resetting the CurrentRowIndex property to the last legitimate position. Before closing, the procedure updates the display in TextBox1 .

 Dim intOldRow As Integer Private Sub Form3_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Populate DataGrid1. OleDbDataAdapter1.Fill(DsOrderDetails1, _ "Order Details") DataGrid1.DataSource = _ DsOrderDetails1.Tables("Order Details") Position current cell in OrderID column of the first row and save row position. DataGrid1.CurrentCell = New DataGridCell(0, 1) intOldRow = DataGrid1.CurrentRowIndex Reset grid size to show data without horizontal scrolling and update form size for grid. DataGrid1.Size = _ New System.Drawing.Size(448, DataGrid1.Height) Me.Size = New System.Drawing.Size(470, 300) Set properties for navigation buttons. Button1.Height = TextBox1.Height Button2.Height = TextBox1.Height Button3.Height = TextBox1.Height Button4.Height = TextBox1.Height Button1.Text = "<<" Button2.Text = "<" Button3.Text = ">" Button4.Text = ">>" Set properties for Go To record button. Button5.Text = "Go to" Button5.Size = _ New System.Drawing. Size (42, TextBox1.Height) Text and TextAlign property settings for labels Label1.Text = "OrderID" Label2.Text = "Price" Label1.TextAlign = ContentAlignment.MiddleRight Label2.TextAlign = ContentAlignment.MiddleRight Position labels relative to corresponding text boxes. Label1.Height = TextBox2.Height Label1.Top = TextBox2.Top - Label1.Height Label2.Height = TextBox3.Height Label2.Top = TextBox3.Top - Label1.Height End Sub Private Sub DataGrid1_CurrentCellChanged( _ ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles DataGrid1.CurrentCellChanged Create a pointer for the DataGrid control. Dim dg As DataGrid dg = DataGrid1 Compute text box display showing current row relative to total number of rows. UpdatePositionIndicator(dg) Display the OrderID for the current row. TextBox2.Text = _ dg(dg.CurrentCell.RowNumber, 1).ToString TextBox2.TextAlign = HorizontalAlignment.Right Computation throws error if selection is out of bounds. Try Compute and display extended price for the current row. TextBox3.Text = _ Format( _ dg(dg.CurrentCell.RowNumber, 3) * _ dg(dg.CurrentCell.RowNumber, 4) * _ (1 - dg(dg.CurrentCell.RowNumber, 0)) _ , "C").ToString TextBox3.TextAlign = HorizontalAlignment.Right If error is InvalidCastException, reset position to last valid row; otherwise , display error. Catch er As System.InvalidCastException dg.CurrentCell = _ New DataGridCell(intOldRow, _ dg.CurrentCell.ColumnNumber) Catch er As System.Exception MsgBox(er.Message & vbCr & er.ToString & _ vbCr & "Reset row application manually.") Exit Try Finally In any event, save row position. intOldRow = dg.CurrentRowIndex End Try End Sub Sub UpdatePositionIndicator(ByVal dg As DataGrid) Update position display in TextBox1. TextBox1.Text = _ (dg.CurrentCell.RowNumber + 1).ToString & _ " of " & DsOrderDetails1. _ Tables("Order Details").Rows.Count TextBox1.TextAlign = HorizontalAlignment.Right End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Move to first row and save row position. DataGrid1.CurrentRowIndex = 0 intOldRow = DataGrid1.CurrentRowIndex End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Move backward one row if not already at first row, and save row position. If DataGrid1.CurrentRowIndex > 0 Then DataGrid1.CurrentRowIndex -= 1 intOldRow = DataGrid1.CurrentRowIndex End If End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Move forward one row if not already at last row, and save row position. If DataGrid1.CurrentRowIndex < DsOrderDetails1. _ Tables("Order Details").Rows.Count - 1 Then DataGrid1.CurrentRowIndex += 1 intOldRow = DataGrid1.CurrentRowIndex End If End Sub Private Sub Button4_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click Move to last row and save row position. DataGrid1.CurrentRowIndex = DsOrderDetails1. _ Tables("Order Details").Rows.Count - 1 intOldRow = DataGrid1.CurrentRowIndex End Sub Private Sub Button5_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button5.Click Set pointer for new row value. Dim int1 = CInt(TextBox1.Text) - 1 Go to designated row, and save row position or restore old row position. If int1 >= 0 And int1 <= DsOrderDetails1. _ Tables("Order Details").Rows.Count - 1 Then DataGrid1.CurrentRowIndex = int1 intOldRow = DataGrid1.CurrentRowIndex Else DataGrid1.CurrentRowIndex = intOldRow End If Compute text box display showing current row relative to total number of rows. UpdatePositionIndicator(DataGrid1) End Sub