RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Exploring Secrets of BindingSource Filters

Learn how to give users fast DataGridView filtering power so they can hone in on the data they're truly interested in.

isual Studio gives developers the extraordinary capability to build a database application with a working graphical user interface using just a few mouse clicks. The canonical form of this with VS2005 uses the DataGridView control. Populating the DataGridView requires a round-trip to the database every time the underlying query needs to be changed due to user actions. The time lag between the request and a response may be a fraction of a second—but could easily be several seconds, depending on network load, database load, etc. This article discusses a technique to dynamically update the underlying query of a DataGridView virtually instantaneously. The perceived performance difference is dramatic (akin to the difference between browser-embedded JavaScript updating versus page refreshing from a web server).

The magic is buried in the Filter property of the BindingSource of the DataGridView control. Before you see how to effectively use that property, it's worth doing some rapid application development to create a basic demonstration framework. The first sample application is a simple form with a DataGridView connected to a SQL Server database and a TextBox. Text entered in the TextBox is injected into the query underlying the DataGridView, updating the DataGridView results on each keystroke. The TextChanged event of the TextBox updates the BindingSource filter of the DataGridView. But from a user's point of view, the application acts like a cumulative selection filter.

First Sample Application
I'll walk through creating the project for this first application step-by-step. Advanced users will be able to skim through it rapidly.

Create a new project called "FilterDemo" in Visual Studio 2005. In the new project, open the Data Sources pane and then select the "Add New Data Source…" link as shown in Figure 1 (or you can use the menu item Data—> Add New Data Source).

Figure 1. Adding a New Data Source: The figure shows a new project with a blank form, and the Data Sources pane opened.
Adding a New Data Source to your Project
To hook up a database connection, you need a database. To be able to show some dramatic effects in the final product, I've used the standard Microsoft AdventureWorks database. If you don't have it, you can download it here, and install it before continuing.

To create a connection, after selecting "Add New Data Source link…" you'll see the Data Source Configuration Wizard, which begins with the Data Source selection dialog (see Figure 2).

Figure 2. Selecting a Data Source: Select the Database choice for your data source.
Figure 3. Select a Data Connection: If you have an existing data connection to AdventureWorks, select it; otherwise, select "New Connection..." to create a new one.
Figure 4. The Add Connection Dialog: In this dialog you enter the parameters for a database connection. In this case the server is a local instance of SqlServer2005 Express (.\SQLEXPRESS) that requires Windows Authentication.
If you have previously defined data connections on your system, selecting "Next" takes you to the Data Connection selection dialog, where you can select an existing connection (see Figure 3). If you do not have a connection to the AdventureWorks database, select "New Connection…" to get to the Add Connection dialog (see Figure 4). If you have no predefined data sources, you'll see the Add Connection dialog immediately.

Use the Test Connection button to make sure you can connect to the database; When you have a successful connection, click OK to close the dialog. You'll see the new connection string in the Data Connection selection dialog dropdown near the top of Figure 3. In that dialog, when you select a connection string, it appears in the text field near the bottom, letting you view the parameters clearly. This connection string is read-only; if you wish to modify its components you must edit the connection by opening the Server Explorer (View—> Server Explorer), opening the context menu for the appropriate connection, and selecting Modify Connection. The Modify Connection dialog (identical to the Add Connection dialog in Figure 4) defines the components of the connection string; some from the main dialog; some from the sub-dialog accessible through the Advanced… button.

Clicking Next from the data connection dialog takes you to the final dialog in the wizard, the Database Object Selection dialog shown in Figure 5.

Figure 5. Data Objects Selection: Leave all boxes unchecked and just click Finish here.
Figure 6. Database Connection: A connection to the AdventureWorks database has been added to the FilterDemo project.
Just click the Finish button in this dialog. VS will warn you that the DataSet is empty; that is OK for now. That will close the dialog and return you to the VS designer. You should see the new data source in the Data Sources pane (see Figure 6).

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