ith the visual designer of Visual Studio 2005 it’s simple and straightforward to stir together a database connection, a SQL query, and a DataGridView control to get a Windows WYSIWYG application. Toss in a few buttons, a dollop of labels, and a text box or two, and you can provide a functional application for your users without too much effort. There are two important aspects of database programming that cannot be covered by the basic designer mechanisms, however.

The first is when you want to use a single DataGridView to display completely unrelated query results in different circumstances?queries that may have different data types for each column or even a different number of columns. In a sample application that contains only an edit window and a DataGridView, you will see how to let users arbitrarily display the results of any queries they wish in a single DataGridView that adapts dynamically to the data display requirements for any particular query.

The second aspect that the basic designer doesn’t cover works with both conventional, static DataGridViews or the dynamic ones just described?tailoring the format or the content of individual cells of your DataGridView in a formulaic fashion (analogous to conditional formatting in Excel).

The Query Browser Solution
The Visual Studio solution QueryBrowser that accompanies this article contains multiple projects that all use Microsoft’s standard AdventureWorks database. If you do not have that database, you can download it here and install it before continuing. Alternatively, you can use a different database by altering the connection string in the configuration file for any particular sample project (e.g. QueryBrowserBasic.exe.config) located in the same directory with the executable (e.g. ?QueryBrowserQueryBrowserBasicinDebug) appropriately.

	?
Figure 1. Dynamic tip #1: Change your server or database merely by editing the project’s configuration file, and then restart the program?no recompilation is needed.

You do not have to rebuild the project to change either the server or the database, as long as you don’t change database type (such as changing from Sql Server to Oracle). You may alter the content of the connection string to point to either a different database (the default shows AdventureWorks) or a different server (the default shows a SqlServer 2005 Express instance running on the local machine, i.e. “.SQLEXPRESS”). Any SqlServer 2000 or SqlServer 2005 database will work. However, do not alter the name of the connection string in the code (the code uses the name DBConnectionString) or you will have to rebuild the projects.

You will see later how to move even this bit of dynamic manipulation into the program’s user interface.

Figure 1 shows the default configuration file for the QueryBrowserBasic project.

Executing QueryBrowser
With a connection to the database of your choice, executing the QueryBrowserBasic.exe program displays the UI shown in Figure 2.

	?
Figure 2. The QueryBrowserBasic program: This sample application presents a very simple user interface with an output pane on top and an input pane on bottom with a single button control.

Enter this line of code in the bottom window and press Execute:

	?
Figure 3. Dynamic Queries: A query to list the available tables run against two different databases yields different results?showing how simple it is to point to a different server or database by editing the configuration file.

   SELECT name as "TableName"       from sysobjects       where xtype in ('U','V')       order by name   

This query returns the user tables and views in the AdventureWorks database. To illustrate the dynamic flexibility available just from editing the single line of the configuration file mentioned above, Figure 3 shows two renditions of the query?one with the database set to “AdventureWorks” and the other with the database set to “master” (a database available in any SqlServer instance).

Here’s the first key rule for dynamic data manipulation: You can dynamically change the contents of a bound DataGridView to display an arbitrary SQL query.

Experiment with the QueryBrowserBasic program; try any query you like, and you’ll see that the number of columns and their data types are completely dynamic, based on the query results. Here are some samples you can run against the AdventureWorks database:

      SELECT * FROM Production.Location   -----------------------   SELECT * FROM Production.Product   -----------------------   SELECT atype.Name, count(*)   FROM Sales.CustomerAddress addr, Person.AddressType atype   WHERE addr.AddressTypeID = atype.AddressTypeID   GROUP BY atype.Name   -----------------------   SELECT        e.[EmployeeID]       ,c.[Title]       ,c.[FirstName]       ,c.[MiddleName]       ,c.[LastName]       ,c.[Suffix]       ,e.[Title] AS [JobTitle]        ,c.[Phone]       ,c.[EmailAddress]       ,c.[EmailPromotion]       ,a.[AddressLine1]       ,a.[AddressLine2]       ,a.[City]
?
Figure 4. Displaying Arbitrary Queries: The QueryBrowser application dynamically tailors the DataGridView component to display the results of arbitrary queries, even adjusting the column type as shown by the boolean fields that display as checkbox columns in the second query.
       ,sp.[Name] AS [StateProvinceName]        ,a.[PostalCode]       ,cr.[Name] AS [CountryRegionName]        ,c.[AdditionalContactInfo]   FROM [HumanResources].[Employee] e       INNER JOIN [Person].[Contact] c        ON c.[ContactID] = e.[ContactID]       INNER JOIN          [HumanResources].[EmployeeAddress] ea        ON e.[EmployeeID] = ea.[EmployeeID]        INNER JOIN [Person].[Address] a        ON ea.[AddressID] = a.[AddressID]       INNER JOIN [Person].[StateProvince] sp        ON sp.[StateProvinceID] =          a.[StateProvinceID]       INNER JOIN [Person].[CountryRegion] cr        ON cr.[CountryRegionCode] =          sp.[CountryRegionCode];   

Figure 4 shows the results of running all four queries. Observe that the column headers, number of columns, column types, and even the column formats change to suit each query.

Building a Dynamic DataGridView
So how much work is it to dynamically reconfigure a DataGridView from an arbitrary query at run-time? Given a result set containing an arbitrary number of columns of varying types, here are the tasks that you have to perform:

• Count the columns in the result set and determine the data type of each column.
  

  	?
  Figure 5. Design-Time Process: The typical process for displaying data in a form starts by using the data set designer to specify the tables and fields to use, generating a data set and table adapter, and dragging that data set onto the form designer surface to automatically generate and connect the necessary controls.

• Determine how to render each column from its data type (e.g. string, number, checkbox, etc.).
• Connect the results to cells in the DataGridView.
• Connect the BindingNavigator (the toolstrip at the top of the window) to the new result set, providing interactive navigation controls to the user.

You can approach these tasks first from the “conventional” design-time-process context where you specify what the form will display using the visual designer. Figure 5 shows the typical steps in the conventional design process.

To create a dynamic (run-time) DataGridView, you must perform essentially all the same work that VS does for you using design-time composition. As you may surmise, you need quite a bit of intricate and complex code to do all that?running into many hundreds of lines. But thanks to the support and wizardry of Visual Studio 2005, 99 percent of that code is generated for you automatically. Indeed, for the design-time version, 99.99 percent is done automatically?;it takes only one line of exposed code. See the sidebar “Learning Resources” for good tutorial links.

The real question, then, is how many more lines of code do you need for run-time composition as opposed to design-time? Fortunately, it amounts to less than twenty lines; ten if you do not count the “container” code (method and class declarations). Here, for example, is the entire contents of QueryBrowserBasic.cs:

      using System;   using System.Collections.Generic;   using System.ComponentModel;   using System.Data;   using System.Drawing;   using System.Text;   using System.Windows.Forms;   using System.Data.SqlClient;   using CleanCodeControls.Forms;      namespace QueryBrowser   {      public partial class QueryBrowserBasic : Form      {            public QueryBrowserBasic()         {            InitializeComponent();         }            private void QueryBrowserForm_Load(object sender, EventArgs e)         {            // Connect all the controls on the BindingNavigator.            qBindingNavigator.BindingSource = qBindingSource;               // Connect the DataGridView to the data.            qDataGridView.DataSource = qBindingSource;         }            private void qTextBox_TextChanged(object sender, EventArgs e)         {            executeButton.Enabled = (qTextBox.TextLength > 0);         }            private void executeButton_Click(object sender, EventArgs e)         {            DataTable dataTable = new DataTable();            qBindingSource.DataSource = dataTable;            qBindingSource.DataMember = null;            SqlDataAdapter tableAdapter = new SqlDataAdapter(               qTextBox.SelectedOrAllText,               Properties.Settings.Default.DBConnectionString);            try { tableAdapter.Fill(dataTable); }            catch (SqlException ex)   { MessageBox.Show(ex.Server+": "+ex.Message); }         }         }   }

The preceding code consists of a class constructor and three event handlers. The constructor is standard boilerplate for any Windows form. The handler for the load event consists of only two lines, but they provide the critical connections between components that make everything “just work.” If you typically use the DataSet Designer to create a data set and then drag it onto a form, you know you automatically get a pre-wired DataGridView and a BindingNavigator. In this program, however, you are putting the pieces together manually so you must connect them yourself.

	?
Figure 6. BindingNavigator: You add a to the Form by dragging it from the toolbox onto the designer surface.

The first line assigns a BindingSource control (a non-visual control added through the visual designer) to the BindingSource property of the BindingNavigator (a displayed control added using the visual designer). The BindingNavigator is the toolstrip at the top of the window shown in Figure 6. That single line of code allows the buttons on the BindingNavigator to function correctly for the current result set and to change when you load a new result set.

The second line assigns that BindingSource control to the DataSource property of the DataGridView. That directs the DataGridView to actually display the result set from the executed query.

The second event handler, qTextBox_TextChanged, simply enables or disables the Execute button so that the user can press it only after entering some text.

The final event handler, invoked when the user presses the Execute button, has just a bit of work to do to put everything together: the SqlDataAdapter takes the text of the query and the connection string as inputs. Then you just call its Fill method with a container?in this case a DataTable?to populate with the result set. Finally, it associates the BindingSource control discussed earlier to this newly created DataTable.

One other tidbit to mention is that the qTextBox is an instance of a RichTextBox on steroids, called a SyntaxHilightTextBox. I use it here as a “bell or whistle” (your choice) that allows the user to execute just the selected portion of text, if the user has made a selection, otherwise to execute the entire text. This control is also responsible for the syntax highlighting as you type. An upcoming article will cover this control in more detail.

That concludes the basic QueryBrowser discussion; it could serve as a great foundation for your dynamic data applications. You should recognize that you could easily create the entire UI in code by creating the DataGridView, BindingNavigator, and BindingSource controls dynamically. Beyond displaying basic query data, though, you may want to format individual cells in a DataGridView, for example, highlighting values in a range, or setting the displayed numeric precision. That’s the subject of the rest of this article.

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!

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)   {       Highlight();   }      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(                   qDataGridView.DefaultCellStyle.Font,                    FontStyle.Bold);               cell.Style.BackColor = color[colorIndex];           }           if (precisionTextBox.TextLength > 0)           {               cell.Value = Math.Round(result,                   int.Parse(precisionTextBox.Text)).ToString();           }       }   }      

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(           qDataGridView_fix_CellContentClick);       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.

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.

Dynamic System Selection
At the beginning of this article you saw how to change the server and database from which the program retrieves data by simply editing the configuration file. That’s fine for the typical administrative user, but you probably don’t want average users doing raw edits to your configuration file. So the final project in this solution ? QueryBrowserChooser ? demonstrates how to bring this system-changing capability into the program proper and allow the user to dynamically change systems at will. More commonly, though, this technique also applies to allowing a user to specify his/her own login and password credentials.

	?
Figure 13. The QueryBrowserChooser Sample Application: QueryBrowserChooser allows your users to change login credentials as well as system specifications on the fly.

Figure 13 shows the opening screen of the QueryBrowserChooser project. It starts with the connection string defined in the configuration file, displaying this at the lower right. It also unpacks that connection string into its component parts and fills in initial values of the input boxes at the lower left and center. The username and password are blank here because the AdventureWorks database by default uses Windows authentication (your computer login credentials) rather than SqlServer authentication (where the database tracks and validates user names and passwords).

Pressing Execute in this program takes the current values of the four input text boxes, recombines them into a valid connection string, updates that string on screen at the lower right, and then uses that string to connect to the database and sends your query. For demonstration purposes, the user interface of this “system chooser” is quite simple, just four text boxes that correspond to the main parts of the connection string.

Again, much of the code is similar to the earlier projects, but the code below is new; it implements credential handling, using a SqlConnectionStringBuilder class that makes the code for this project quite concise and tidy. A SqlConnectionStringBuilder takes a string representation of a connection string as input. It then provides the main parts of the connection string via both Dictionary-type accessors (e.g. connDict[“User ID”]) and predefined property accessors (e.g. connDict.UserID). The class can return its contents in a connection-string-compatible form by invoking its ToString() method.

The project needs just a few short methods. The form’s Load handler calls InitializeConnectionParams, which grabs the connection string from the configuration file, displays it on screen, and feeds it into a SqlConnectionStringBuilder.

   private void InitializeConnectionParams()   {       connStringLabel.Text = Properties.Settings.Default.DBConnectionString;       Connection = new SqlConnectionStringBuilder(connStringLabel.Text);   }   

The Connection property handles mapping the current values of the SqlConnectionStringBuilder into the form fields in both directions.

   private SqlConnectionStringBuilder Connection   {       get       {           UpdateParam(usernameTextBox, "User ID");           UpdateParam(passwordTextBox, "Password");           UpdateParam(databaseTextBox, "Initial Catalog");           UpdateParam(serverTextBox, "Data Source");           return connDict;       }          set       {           connDict = value;           usernameTextBox.Text = value.UserID;           passwordTextBox.Text = value.Password;           databaseTextBox.Text = value.InitialCatalog;           serverTextBox.Text = value.DataSource;       }   }    

The last method is the workhorse used repeatedly by the “getter” accessor of Connection. If a given form field has a value it is inserted in the dictionary. But if it does not have a value then it is explicitly deleted from the dictionary (carefully checking that it is, in fact, present before doing so).

   private void UpdateParam(TextBox textBox, string paramName)   {       if (textBox.TextLength > 0) { connDict[paramName] = textBox.Text; }       else if (connDict.ContainsKey(paramName)) { connDict.Remove(paramName); }   }

So credential and system handling leads to the third key rule for dynamic data manipulation: You can alter a bound DataGridView to display output from a different server or database, or to use different user credentials, by modifying the connection string.

So, you now have two ways to modify a DataGridView’s connection string: by editing the configuration file or by providing run-time controls.

Dynamic Data Control in Summary
The different aspects of dynamic data manipulation combine to form a cohesive whole as shown in Figure 14, which visualizes the process as a series of narrowing pipes.

	?
Figure 14. Dynamic Data Manipulation: The three levels of dynamic specification combine to provide a powerful tool set for developing applications.

You have the capability to route your data flow from different servers and from different databases within a server. You can run arbitrary queries to get specific views of the data in the selected database. Finally, you can use dynamic cell formatting to tailor the visual representation of the data to the needs of your applications and users.

For completeness, I should also mention the fourth axis of dynamic data control, that of applying a secondary query for additional in-memory filtering by row. I have covered this topic in-depth in another recent article on DevX entitled Exploring Secrets of BindingSource Filters. This technique allows you to provide not just dynamic but instantaneous row filtering capability based on a user’s actions and selections. Together with the tools discussed in the article you are reading now, these techniques provide a powerful set of tools for creating flexible, data-driven applications.

Summary of Key Principles for Dynamic Data Manipulation
To close, here are the key principles discussed in the article, all in one place for easy reference:

• You can dynamically change the contents of a bound DataGridView to display an arbitrary SQL query.
• You can dynamically format the contents of a DataGridView cell exactly when needed.
• You can alter a bound DataGridView to display output from a different server or database, or to use different user credentials, by modifying the connection string.