Page 3 of 7
Making the connection
The strongly typed SearchResults DataSet is an in-memory version of our database, but to get some data to store in it we need to make the connection to an external database.
This is achieved using a suitable connection object. As we are working with an OleDB data provider we need an OleDbConnection object – there is a specific connection object for all of the traditional data providers.
In most cases it is possible to use the same Wizard that created the DataSet to create the connection object but in this case, because the database doesn't actually exist as a file on disk, we have to do the job manually.
Fortunately this isn't difficult. The connection is specified by a "connection string" which you usually have to look up. In this case all we need is (enter the code into the button's click event handler):
string connectionString =
Now we can create the connection object and store the connection string in it:
OleDbConnection SearchConnect =
The connection object defines which database is being used and the DataAdaptor is responsible for transferring data to and from the database using the connection object. In particular the DataAdaptor is the place that you store a SQL query that can then be used to extract a subset of the database into a suitable DataSet object.
As we are working with an OLE DB data provider we need an OleDbDataAdaptor; as in the case of the connection object there is a special adaptor for each type of data provider:
OleDbDataAdapter SearchAdpt =
"SELECT Top 5 System.FileName
FROM SYSTEMINDEX", SearchConnect);
Notice that we have included a SQL query string to specify the selection of the index dataset we want to return.
If you know SQL you will recognise the SELECT statement. Top 5 just returns the first five records and only the System.FileName column is returned from the table called SYTEMINDEX. Later we can create some more interesting queries but this is sufficient to show that it all works.
Everything is ready to retrieve the data but we still need somewhere to store it. An instance of the SearchResults dataset is easy to create:
SearchResults SearchData =
To fill it with data that matches the query specified in the DataAdatptor we use the Fill method:
There are Update and Delete methods which modify the database using the contents of the DataSet but the search index is read-only so they naturally don't work.
If you run the program now and click the button then the query will be run on the index and the names of the first five folders/files returned.
Of course it isn't easy to check that this is what is happening but it is easy to add a command that accesses the name stored in the first record:
string temp = SearchData.Table.
The generated DataSet has properties corresponding to each table it contains. So SearchData.Table
specifies the table we created, called "Table", confusing but unavoidable as this is what the search engine calls its table. Next we can use the index to select the first row of the table, the rows are numbered starting from zero. Finally we can select the column corresponding to System.FileName using ._System_FileName.
The only complication here is that the search index uses full stops in the names of its fields and ADO can't use these within the names of properties. To solve the problem all full stops are converted to underscores. If you now put a break point in the program just after the line that sets Temp you can stop the program an examine its contents.
A more sophisticated way of examining the results is next on the agenda.