Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Exploring Secrets of the .NET DataGridView and Dynamic Data Manipulation : Page 4

Find out how to use a single DataGridView to display dynamic queries, adjusting columns automatically, and how to format individual cells exactly the way you want users to see them.




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Date: 1/31/2018 @ 2 p.m. ET

Dynamic Cell Formatting—A Better Way
The third project in the solution, QueryBrowserAutoFormatting.exe, has an interface identical to the QueryBrowserBruteFormatting project (see Figure 7) except for the form title. Run the program and follow this procedure to evaluate how well it satisfies the important design considerations discussed earlier:

  • Enter the same standard query (select top 500 * from Sales.SalesOrderDetail) and press Execute.
  • Set a threshold of 1000 and precision of 2 for UnitPrice then press Update.
  • Sort the results a few times by any column, clicking on different column headers. Exit this step leaving some highlighted cells in view (either by sorting or by scrolling).
  • Change the precision from two to three, then press Update again.
  • Press Execute again.
  • Scroll up and down.
So now back to the design considerations—make sure you agree with the answers:

Q. What happens if you scroll a cell out of view, then back in view?

A. Scrolling preserves highlighting and value changes.

Q. What happens if you click on a column heading to sort by that column?

A. Sorting preserves highlighting and value changes.

Q. What happens if you refresh the query?

Figure 11. Non-Destructive Formatting: In this sample application, cell formatting is non-destructive, as evidenced by changing the precision from two decimal places to three. Values that had a third decimal place suppressed now display it.
A. Refreshing the result preserves highlighting and value changes. (Of course, if the database has changed since the previous execution, there may be new values in the result set, but all current values meeting the criteria you specified will be highlighted or trimmed.)

Q. How long does formatting take?

A. No noticeable time lag; formatting is essentially instantaneous.

Q. Is the formatting just formatting—for example, does it preserve precision?

A. Yes! The formatting is non-destructive since you could increase the precision (e.g. from two to three) and then see more characters (see Figure 11).

This version of the project is significantly smaller than the previous one, less complicated, easier to maintain, and it suffers from none of the design drawbacks. To see how, look at the source code for QueryBrowserAutoFormatting.cs. Just as with the previous project, the constructor and first three methods are essentially unchanged from QueryBrowserBasic.cs. The parameter_TextChanged event handler is merely "window dressing", just as in the QueryBrowserBruteFormatting project, to enable or disable the Update button. The hideLabel_Click and instructionsPanel_MouseEnter event handlers are for displaying the instructions, and not relevant to the main focus. That leaves just two methods of real import, the updateButton_click handler and the qDataGridView_CellFormatting method (roughly equivalent to the CheckRow method from the previous project).

The beauty and elegance of this solution comes from the CellFormatting event, which occurs on each cell when it requires repainting. When does this occur? One case is when a cell just becomes visible. Right away you can see that this solves the time lag problem. Only visible cells fire the event. Repainting is also required when columns are sorted, when the result set is refreshed, or when scrolling the grid. The previous project applied a fix to the symptom rather than the cause. This project addresses the cause—no unanticipated repainting operations will affect your code. This code is also much easier to maintain over time. Here's the code:

private void qDataGridView_CellFormatting( object sender, DataGridViewCellFormattingEventArgs e) { if (qDataGridView.Columns[e.ColumnIndex].HeaderText .Equals(columnNameTextBox.Text)) { double result; if (double.TryParse(e.Value.ToString(), out result)) { if (thresholdTextBox.TextLength > 0 && result < double.Parse( thresholdTextBox.Text)) { qDataGridView[ e.ColumnIndex, e.RowIndex].ToolTipText = string.Format("new tooltip for cell[{0},{1}]", e.RowIndex, e.ColumnIndex); e.CellStyle.Font = new Font( qDataGridView.DefaultCellStyle.Font, FontStyle.Bold); e.CellStyle.BackColor = Color.Salmon; } if (precisionTextBox.TextLength > 0) { e.Value = Math.Round(result, int.Parse(precisionTextBox.Text)).ToString(); } } } else { /* Do NOT need to check for or remove prior highlighting when parameters change since all cells start with default settings. */ } }

Figure 12. DataGridViewCellFormattingEventArgs Properties and Methods: The argument to a cell-formatting event handler exposes several convenient properties and methods.
Because this handler is invoked for every cell that becomes visible, it must first confirm that the cell is in the column specified by the user. If not, it just returns; otherwise, it applies the threshold and precision limits.

The cell-formatting event itself provides several convenient hooks for processing the cell efficiently. Look for instances of e.something in the DataGridViewCellFormattingEventArgs parameter. You have access to the cell's coordinates (RowIndex and ColumnIndex), as well as its value (Value) and its style (CellStyle). Figure 12 shows all the available hooks.

The final method of interest handles the activation of the Update button:

private void updateButton_Click(object sender, EventArgs e) { updateButton.Enabled = false; qDataGridView.Refresh(); }

The first line of code just disables the button because it's only needed when the user makes a change to one of the input parameters (the handler that watches the user input re-enables the button when the user changes a value). The second line is the interesting one here—it calls the DataGridView.Refresh method, which forces a repaint, thus applying changes in the user-entered effects immediately. The explicit refresh is necessary here because it is a semantic action in my code; the cell formatting event automatically handles all system-induced repainting needs.

From this analysis, I can restate the second key technique for dynamic data manipulation more accurately as: You can dynamically format the contents of a DataGridView cell exactly when needed.

That concludes the discussion of dynamic formatting. In conjunction with the dynamic result sets discussed earlier, you can simplify your projects to use a single DataGridView that gives you fine dynamic control over your data presentation. The final section wraps back to the beginning; as promised, I'll show you how to change database types (e.g. from SQL Server to Oracle) dynamically as well.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date