Hands on: How to add a search feature to Microsoft Visual Studio

We recently showed two approaches to managing data in Visual Basic, using an SQL Server Compact Edition image catalogue as an example. The first approach involves constructing your own SQL statements, sending them to the database engine using SQLCeCommand and reading the results from a DataReader.

The second technique uses Visual Studio’s wizards to build a quick application; while it is less work than constructing SQL manually, it’s not obvious how to progress from there. The wizard generates five components consisting of a BindingNavigator, DataGridView, DataSet, TableAdapter and TableAdapterManager. So, how do you add features, such as search?

It is time to take a closer look at these objects. Internally Visual Basic, or rather the .Net Framework, is still using SQL and DataReaders, it is just that these higher-level components hide this from you. Here’s how it all hangs together.

The place to start is the DataSet, which is an in-memory database manager. When your application needs to read the data, it reads it from the DataSet. When it writes data, it writes it to the DataSet.

Since it is in memory, performance is generally excellent, although there are a couple of snags. First, since you are reading and writing data from an in-memory copy, it could be out of date. Second, loading a entire database into memory, which is what Visual Studio does by default, is inefficient for large databases.

A DataSet has no understanding of the connection to the source database manager or where its data comes from. Instead, the interaction with the database manager is done by the TableAdapter component. For example, the Fill method populates a DataSet and the Update method saves any changes or insertions from the DataSet back to the source database. Both these methods cause SQL commands to execute.

The latest Visual Studio, or VB Express, wraps the TableAdapter in a custom class, such as PCWImagesTableAdapter, and adds a TableAdapterManager to deal with multiple TableAdapters for joined tables. It is not necessary to modify this code. Instead, work with the visual DataSet designer to change the behaviour of the DataSet and TableAdapter.

Adding search
Here’s how to add a search feature. The first step is to add a text box and a button to the toolbar. The idea is that when you click the button, the DataGridView shows only records that match the search string somewhere in the Subject field of the database table.

There are two approaches you can take. If you loaded the entire database into the DataSet, it makes sense to search it. The easiest way is by setting the Filter property on the generated PCWImagesDataBindingSource object. Here is the code, which also updates a status bar with the number of hits:

Dim searchString As String = Me.txtSearch.Text.Trim
Me.PCWImagesBindingSource.Filter = “Subject LIKE ‘%” + searchString + “%’”
lbStatus.Text = Me.PCWImagesBindingSource.Count.ToString() + “ rows found”

The syntax for filter expressions is in the documentation for DataColumn.Expression.

Alternatively, you can restrict the data retrieved from the database. To do this, open the DataSet designer. You will see a grid with a section showing fields from the database, as well as another section headed PCWImagesTableAdapter. This has one entry defining the Fill query.

Right-click this row and choose Add query. Accept all the defaults in the wizard to create a new query called FillBy. Select this new row, then choose Properties, Parameters, Add. Add a parameter named Filter, change the type to String and click on OK.

Now right-click the query row again and choose configure. Add the following line to the SQL statement:
WHERE Subject LIKE @Filter

What you have done is to define a new SQL statement for populating the DataSet. Instead of retrieving all the rows, it restricts them according to the expression you supply. This means you can rewrite the search function like this:

Dim searchString As String = Me.txtSearch.Text.Trim
Me.PCWImagesTableAdapter.FillBy(PcwdataDataSet.PCWImages, “%” + searchString + “%”)
lbStatus.Text = Me.PcwdataDataSet.PCWImages.Count.ToString() + “ rows found”

If you do this, you will likely want to add an All records button that will clear the search. Either set the Filter to an empty string if you’re filtering the DataSet or re-query the database if you have used the second approach. For example:

Me.PCWImagesTableAdapter.Fill (Me.PcwdataDataSet.PCWImages)

Another point to watch is that any unsaved changes in the DataSet will be lost if it is refilled. You could use similar code to that given below for saving data on exit.