More properties

Now that we have a basic search working doing more is just a matter of adding columns to the typed DataSet using the Wizard and building appropriate SQL queries. To retrieve rows you need the SELECT command which in general is:

 SELECT [TOP <positive integer>] 
                         <columns>
 FROM [machinename.]SystemIndex
  [WHERE <conditions>]
   [ORDER BY <column>]

You can leave out any of the clauses surrounded by square brackets to construct a valid SELECT.

There are a few additions to standard SQL and some things from standard SQL don't work for Desktop Search.

You can use the usual "wildcard" characters - that is * matches any number of characters and ? matches any single character.

You can't, however, use * to specify "return all columns" instead you have to specify explicitly which columns you want.

The WHERE clause can be used to specify what you are looking for and you can write logical expressions using AND, OR and NOT.

You can search on properties, i.e. columns in the database, or on the full text of an indexed file. For example, to find all files ending in "wma" you would use:

 SELECT System.FileName 
  FROM SYSTEMINDEX  
  WHERE System.FileExtension = '.wma'"

To search on the same criterion and return additional columns you would use something like:

 SELECT System.FileName, 
   System.ItemPathDisplay, 
        System.DateCreated 
  FROM SYSTEMINDEX 
  WHERE System.FileExtension = '.wma'

The biggest problem is discovering what properties are available. In principle you should be able to work with all of the "shell" properties which you can find listed at the Microsoft website (search for "Shell Properties"). In practice not all of them work and you simply have to use trial and error to see which do.

As an example of extending the search to additional properties change the definition of the DataAdaptor to read:

 OleDbDataAdapter SearchAdpt = 
   new OleDbDataAdapter(
    "SELECT System.FileName, 
             System.ItemPathDisplay, 
                System.DateCreated 
    FROM SYSTEMINDEX 
    WHERE System.FileExtension = '.doc'",
     SearchConnect);

This returns three columns, two strings (FileName and ItemPathDisplay) and a Date (DateCreated).

For this to work we need to modify the definition of the strongly typed DataSet. All you have to do is use the DataSet Designer to add the two new columns, making sure to use the exact names.

In addition you also have to set the DataType property of System.DateCreated to System.DateTime – you can pick this from the drop down list in the Properties window. In general you have to set the DataType and perhaps additional properties for any new columns you add unless the defaults happen to work. When you leave the Designer the definition of the DateSet is updated to include the new columns as appropriate new typed properties.

In many cases this is all you have to do but if you have controls bound to the typed DataSet then you need to regenerate the controls.

In the case of a Windows Forms project  you need to go to the Form Designer and delete DataSet and the BindingSource controls – named searchResults and tableBindingSource in this case- and then rebind the grid to Table in the DataSet.

In the case of a WPF project the procedure is the same but after recreating the DataGrid you have to make the same modifications as needed in the earlier example and you have to add the extra columns.

The first two are easy enough to do in code:

((DataGridTextColumn)tableDataGrid.
      Columns[0]).Binding = 
       new Binding("_System_FileName");
((DataGridTextColumn)tableDataGrid.
      Columns[1]).Binding = 
   new Binding("_System_ItemPathDisplay");

The date column is more difficult to do in code because it uses a DataTemplate. In this case the XAML has to be edited to read:

 <DataGridTemplateColumn x:Name=
  "_System_DateCreatedColumn" 
    Header="System.Date Created" 
                Width="SizeToHeader">
  <DataGridTemplateColumn.CellTemplate>
   <DataTemplate>
    <DatePicker SelectedDate=
  "{Binding Path=_System_DateCreated}" />
   </DataTemplate>
  </DataGridTemplateColumn.CellTemplate>
 </DataGridTemplateColumn>

In this case you have to remember to re-edit the XAML is the DataGrid is recreated.

Now when you run the program you will see the grid has three columns and you should see data in each of them.

<ASIN:0596159838>

<ASIN:0321658701>

<ASIN:0470502258>

<ASIN:0470191090>