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 3

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.

Dynamic Cell Formatting
The other useful technique mentioned at the beginning of this article is to be able to display data and highlight values that are of particular interest to the user, such as (for example) displaying negative numbers in red. The second key rule for dynamic data manipulation is: You can format the contents of a DataGridView cell dynamically on demand.

Figure 7. The QueryBrowserBruteFormatting Application: This sample application includes some instructions on how to explore brute-force formatting.
If you look in the solution explorer pane with the QueryBrowser solution open, you will notice that there are several different projects. You've already examined the QueryBrowserBasic project. Now, execute the QueryBrowserBruteFormatting program, and your screen should look like Figure 7. It contains the same output area at the top, input area beneath that, with new controls along the bottom edge, but in addition to the Execute button there are fields where you can enter three parameters and an Update button to apply those parameters to the result set. The final addition is the instruction box shown in Figure 7.

As you have probably surmised, this project will illustrate brute-force formatting, a term that should serve as foreshadowing of evil and ugly things about to happen—nothing malicious or damaging—it's only an aesthetic evil, a programming practice that you should shy away from. After you see the problems it can cause, you won't want to use it anyway—and I'll show you a better method a little later on—but it's worth reading through this method, not only so that you can avoid this type of thought process when solving your own programming problems, but also to gain a basic understanding of the processing tasks involved in dynamic cell formatting.
Figure 8. Initial Results: Here's the unadorned result set displayed after you enter a query and press the Execute button.

To get started, enter a query of any sort that will return some integers and some floating-point numbers. The suggested query in the instruction box will do that:

   select top 500 * from Sales.SalesOrderDetail
The result returns 500 records as shown in Figure 8.

In the Effects pane at the bottom, enter OrderQty for the column name, and 4 for the threshold, and then press the Update button. This instructs the browser to highlight any cells in the OrderQty column that contain a quantity less than 4. Figure 9 shows the results.

Figure 9. Highlighted Results: The result set displays highlighted results based on the user's inputs.
There are several important questions to consider during your design of this type of user interface:

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

A. Scrolling, as it turns out, is safe. The cells scroll off, and scroll back, unchanged.

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

A. Sorting causes a cell-repainting event to occur. As soon as you click a column heading to sort, the highlight vanishes. This is true regardless of which column you select—the highlighted one or any other. You must manually press Update again to have your highlighting reapplied. (Note that each time you manually update the effects, the data will be highlighted in a different color so you have a visual cue that a re-application of the highlight has occurred.)

Q. What happens if you refresh the query?

A. Just as with sorting, a refresh particularly requires cell repainting—values may have changed since your last query! So here again, you lose the highlighting.

Q. How long does formatting take?

A. If the screen is frozen for more than a fraction of a second while your program does some processing, the user may find the time lag cumbersome at best or decide to return your program at worst. Notice in Figure 9 that the sample query restricts the result set to just 500 rows (via the "top 500" clause). If you delete that phrase and re-execute the query you will have in excess of 121,000 rows. Applying highlighting to that result set takes about 13 seconds—much too long for a user interface to be frozen!

Author's Note: If you experiment with this, you might want to return to just 500 or so rows to reduce the time lag from highlighting.

Q. Is the formatting just formatting? Or to put that another way, is the formatting destructive to the data?

Figure 10. Formatting Precision: By specifying a precision value, you can change the actual contents of a cell.
A. To answer this question, try another experiment, this time using the precision field in the Effects box at the bottom of the form. Specify "UnitPrice" as the column name; that field contains floating-point values. Enter a threshold value of your choice—perhaps 30—and this time also enter a precision, specifying the number of significant digits you want to retain, I recommend retaining two. When you press the Update button (see Figure 10), not only does the application highlight cells that are below the threshold, but it also rounds the value of every cell in the column to two decimal places.

Here, then, you have changed not only the cell formatting, but the cell value as well. You can see in Figure 9 that several entries originally had values with four decimal places. When the precision was changed to two, the extra precision was lost. In other words, this type of formatting is destructive; if you change the precision back to three, for example, you can't get back the extra digit without requerying the database. Finally, try the full query again, to return to 121,000 rows. Now when you press Update the screen freezes for about 75 seconds here! Clearly the performance needs improvement.

Imagine that somewhere along the path of your experimentation, the Fix button suddenly appeared next to the Execute button. Press the Fix button to magically fix the sorting problem. (The button will disable itself when you do since you need to press it only once.) Now click on any column heading. The highlighting seemingly remains but changes color (the sample application applies highlighting in a different color each time so you can tell when it occurs). In actuality the highlighting was still wiped out, but then re-applied just after the sort operation. Click again on any column heading and the color will change yet again.

How Dynamic Formatting Works
Now that you have seen the user perspective of the QueryBrowserBruteFormatting project, here's how it works inside. The basic QueryBrowserBasic project is present essentially unchanged in this project—the constructor, the Load event handler, the input query field's TextChanged handler, and the Execute button's Click event handler. But the QueryBrowserBruteFormatting.cs file contains a new handler for the Update button's Click event, which calls a Highlight method. The Highlight method cleanly handles changing the cursor, disabling the Update button, changes the highlight color, and then walks through the DataGridView one row at a time calling CheckRow for each row. The CheckRow method examines the cell in the user-specified column to see if it should be highlighted and/or trimmed per the user-specified parameters. Here's the code that accomplishes the dynamic highlighting and precision adjustment.

   private void updateButton_Click(object sender, EventArgs e)
   private void Highlight()
       Cursor = Cursors.WaitCursor;
       updateButton.Enabled = false;
       colorIndex = (colorIndex + 1) % 
           color.Length; // pick a new color
       for (int i = 0; i < qDataGridView.Rows.Count; i++) 
          { CheckRow(i); }
       updateButton.Enabled = true;
       Cursor = Cursors.Default;
   private void CheckRow(int rowNum)
       DataGridViewCell cell = qDataGridView[
           columnNameTextBox.Text, rowNum];
       double result;
       if (double.TryParse(cell.Value.ToString(), out result))
           if (thresholdTextBox.TextLength > 0 && 
               result < double.Parse(thresholdTextBox.Text))
               cell.ToolTipText = string.Format(
                   "new tooltip for cell[{0},{1}]",
                   cell.RowIndex, cell.ColumnIndex);
               cell.Style.Font = new Font(
               cell.Style.BackColor = color[colorIndex];
           if (precisionTextBox.TextLength > 0)
               cell.Value = Math.Round(result,
The Fix button retains highlighting across a re-sort operation simply by adding an event handler to the CellContentClick event of the DataGridView:

   private void fixButton_Click(object sender, EventArgs e)
       qDataGridView.CellContentClick +=
           new DataGridViewCellEventHandler(
       fixButton.Enabled = false; // only use this button once
The added event handler does exactly the same thing that happens when users manually ask for an update—it calls the Highlight method when users click a column header, invoking a sort:

   void qDataGridView_fix_CellContentClick(
      object sender, DataGridViewCellEventArgs e)
       // only hilight if the a column header cell has been clicked
       if (e.RowIndex == -1) { Highlight(); } 
So what is so terrible about this approach? There are a couple of problems.

The first problem is that the fix is a kludge: a patch applied to mask a symptom, rather than treating the cause. That is, I observed that the sorting caused a loss of highlighting, so I intercepted the code at the point sorting occurred and made it look better. Furthermore, the fix is deficient (deliberately so to make this point). There is at least one other case where the highlight disappears—when the query is refreshed. So you could modify the fix-installer (fixButton_click) to also correct that situation. But what if there are other cases that you just have not seen yet? Those are called "bug reports"—things you hear from your customers!

The next problem is the time lag that you saw earlier: the window locks up during highlighting for unacceptably long times. The cause is obvious—the Highlight method examines every single row of the result set every time you need to apply a highlight. It would be far more efficient to check only the cells that are currently visible to the user. That amounts to typically 20 or 30 rows… maybe even 100 rows if you really squeeze data onto the screen. But you could process even 100 rows virtually instantaneously. That's certainly possible with the current project, but modifying the code would add yet more code that you have to maintain, and still doesn't address the problem of other cases that you might not yet have seen causing the highlight to disappear. There is a better way.

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