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 5

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 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
           UpdateParam(usernameTextBox, "User ID");
           UpdateParam(passwordTextBox, "Password");
           UpdateParam(databaseTextBox, "Initial Catalog");
           UpdateParam(serverTextBox, "Data Source");
           return connDict;
           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.

Michael Sorens is a freelance software engineer, spreading the seeds of good design wherever possible, including through his open-source web site, teaching (University of Phoenix plus community colleges), and writing (contributed to two books plus various articles). With BS and MS degrees in computer science and engineering from Case Western Reserve University, he has worked at Fortune 500 firms and at startups, using C#, SQL, XML, XSL, Java, Perl, C, Lisp, PostScript, and others. His favorite project: designing and implementing the world's smallest word processor, where the medium was silicon, the printer "head" was a laser, and the Declaration of Independence could literally fit on the head of a pin. You can discuss this or any other article by Michael Sorens here.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date