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 2

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.

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.

Comment and Contribute








