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.
|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.
shows the default configuration file for the QueryBrowserBasic project.
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"
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
,e.[Title] AS [JobTitle]
,sp.[Name] AS [StateProvinceName]
,cr.[Name] AS [CountryRegionName]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] =
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] =
|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.|
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.