Using SQL and AQS Approaches to Query the Index
There are several ways to use Windows Search to query the index. This topic outlines Advanced Query Syntax (AQS) and Structured Query Language (SQL) based approaches.
This topic is organized as follows:
SQL Based Queries
SQL is a text language that defines queries. SQL is common across many different database technologies. Windows Search uses SQL, implements a subset of it, and extends it by adding elements to the language. The Windows Search SQL used by Windows Search extends portions of the standard SQL-92 and SQL-99 database query syntax to enhance its usefulness with text-based searches. All features of Windows Search SQL are compatible with Windows Search on Windows XP and Windows Server 2003, and later.
For more information about using Windows Search SQL syntax, see Querying the Index with Windows Search SQL Syntax and Overview of Windows Search SQL Syntax.
The following approaches for querying the index are SQL based.
Using OLE DB
The Object Linking and Embedding Database (OLE DB) is a Component Object Model (COM) API that enables you to access different types of data stores uniformly, including non-relational databases like spreadsheets. OLE DB separates the data store from the application accessing it through a set of abstractions that include the Shell data source, session, command, and rowsets. An OLE DB provider is a software component that implements the OLE DB interface to provide data to applications from a particular data store.
You can access the Windows Search OLE DB provider programmatically by using the OleDbConnection and OleDbSession objects in C# and by using the OLE DB support built into Active Template Library (ATL) in C++ (via atlclidb.h). The only property that has to be set is the provider string.
You can use the following string:
provider=Search.CollatorDSO;EXTENDED PROPERTIES="Application=Windows"
Alternatively, you can get the connection string by calling ISearchQueryHelper::get_ConnectionString. See Using ISearchQueryHelper for an example.
Note
The Windows Search OLE DB provider is read-only, and support SELECT and GROUP ON statements. INSERT and DELETE statements are not supported.
#include <atldbcli.h>
CDataSource cDataSource;
hr = cDataSource.OpenFromInitializationString(L"provider=Search.CollatorDSO.1;EXTENDED PROPERTIES=\"Application=Windows\"");
if (SUCCEEDED(hr))
{
CSession cSession;
hr = cSession.Open(cDataSource);
if (SUCCEEDED(hr))
{
CCommand<CDynamicAccessor, CRowset> cCommand;
hr = cCommand.Open(cSession, pszSQL);
if (SUCCEEDED(hr))
{
for (hr = cCommand.MoveFirst(); S_OK == hr; hr = cCommand.MoveNext())
{
for (DBORDINAL i = 1; i <= cCommand.GetColumnCount(); i++)
{
PCWSTR pszName = cCommand.GetColumnName(i);
// do something with the column here
}
}
cCommand.Close();
}
}
}
For more information on OLE DB, see OLE DB Programming Overview. For information on the .NET Framework Data Provider for OLE DB, see the System.Data.OleDb Namespace documentation.
Using ADO and ADO.NET
Microsoft ActiveX Data Objects (ADO) and ADO.NET enable you to write client applications to access and manipulate data in a database server through a provider. Windows Search is a read-only technology: you can retrieve data using Desktop Search but you can't change data using Windows Search. You can, however, pass the results of a search over to a technology that can change data.
The following code examples demonstrate how to open a connection to the data source, create and open a RecordSet with a Windows Search SQL SELECT statement, and get the URLs of the five largest files from the index.
Note
For information on how to obtain the connection string, see Querying the Index with ISearchQueryHelper, and ISearchQueryHelper::get_ConnectionString.
ADO and VBScript
'To run this snippet, save it to a file and run it using cscript.exe from a command line.
'Running the .vbs file with Windows Script Host may cause dialog boxes to open for each item returned from the index.
On Error Resume Next
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordSet.Open "SELECT Top 5 System.ItemPathDisplay FROM SYSTEMINDEX WHERE scope='file:' ORDER BY System.Size DESC", objConnection
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("System.ItemPathDisplay")
objRecordset.MoveNext
Loop
ADO and C++
#import "msado15.dll" rename_namespace("ADO") rename("EOF", "EndOfFile") implementation_only
ADO::_ConnectionPtr connection = NULL;
HRESULT hr = connection.CreateInstance(__uuidof(ADO::Connection));
if (SUCCEEDED(hr))
{
ADO::_RecordsetPtr recordset = NULL;
hr = recordset.CreateInstance(__uuidof(ADO::Recordset));
if (SUCCEEDED(hr))
{
connection->CursorLocation = ADO::adUseClient;
hr = connection->Open(L"Provider=Search.CollatorDSO;Extended Properties='Application=Windows';",
L"", L"", ADO::adConnectUnspecified);
if (SUCCEEDED(hr))
{
hr = recordset->Open("SELECT Top 5 System.ItemPathDisplay FROM SYSTEMINDEX WHERE scope='file:' ORDER BY System.Size DESC",
connection.GetInterfacePtr(), ADO::adOpenForwardOnly, ADO::adLockReadOnly, ADO::adCmdText);
if (SUCCEEDED(hr))
{
while (!recordset->EndOfFile)
{
_variant_t var;
var = recordset->Fields->GetItem(L"System.ItemPathDisplay")->GetValue();
std::cout << static_cast<char *>(_bstr_t(var.bstrVal)) << std::endl;
recordset->MoveNext();
};
recordset->Close();
}
connection->Close();
}
}
}
ADO and VisualBasic
First add a reference to ADODB in your project
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
con = New ADODB.Connection
rst = New ADODB.Recordset
Dim sConString As String
Dim sSQLString As String
sConString = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
con.Open(sConString)
sSQLString = "SELECT Top 5 System.ItemPathDisplay FROM SYSTEMINDEX
WHERE scope='file:'
ORDER BY System.Size DESC"
rst = con.Execute(sSQLString)
Do Until (rst.EOF)
Print(1, rst("System.ItemPathDisplay").Value)
rst.MoveNext
Loop
rst.Close
rst = Nothing
con.Close
con = Nothing
ADO.NET and C#
string query = @"SELECT Top 5 System.ItemPathDisplay FROM SYSTEMINDEX
WHERE scope='file:'
ORDER BY System.Size DESC";
using (OleDbConnection objConnection =
new OleDbConnection
("Provider=Search.CollatorDSO.1;Extended?Properties='Application=Windows';"))
{
objConnection.Open();
OleDbCommand cmd = new OleDbCommand(query, objConnection);
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
Console.Write(rdr.GetName(i));
Console.Write('\t');
}
while (rdr.Read())
{
Console.WriteLine();
for (int i = 0; i < rdr.FieldCount; i++)
{
Console.Write(rdr[i]);
Console.Write('\t');
}
}
Console.ReadKey();
}
}
Using SQL in Local and Remote Queries
You can execute your queries either locally or remotely. A local query using the FROM clause is shown in the following example. A local query queries the local SystemIndex catalog only.
FROM SystemIndex
A remote query using the FROM clause is shown in the following example. Adding ComputerName transforms the previous example into a remote query.
FROM [<ComputerName>.]SystemIndex
By default, Windows XP and Windows Server 2003 do not have Windows Search installed. Only Windows Search 4 (WS4) provides remote query support. Previous versions of Windows Desktop Search (WDS), such as 3.01 and earlier, do not support remote querying. With Windows Explorer you can query the local index of a remote computer for file system items (items handled by the "file:" protocol).
To retrieve an item by remote query, the item must meet the following requirements:
- Be accessible via Universal Naming Convention (UNC) path.
- Exist on the remote computer to which the client has access.
- Have its security set to allow the client to have Read access.
Windows Explorer has features for sharing items including a "Public" share (\\Machine\Public\...) in the Network and Sharing Center, and a "Users" share (\\Machine\Users\...) for items shared through the Sharing Wizard. After you share the folder(s), you can query the local index by specifying the remote computer's machine name in the FROM clause, and a UNC path on the remote machine in the SCOPE clause, as shown in the following example:
SELECT System.ItemName FROM MachineName.SystemIndex WHERE SCOPE='file://MachineName/<path>'
AQS Based Queries
AQS is the default query syntax used by Windows Search to query the index and to refine and narrow search parameters. While AQS is primarily user facing, it can be used by developers to build queries programmatically. In Windows 7 canonical AQS was introduced, and must be used in Windows 7 and later, to programmatically generate AQS queries. For detailed information on AQS, see Using Advanced Query Syntax Programmatically.
The following approaches for querying the index are AQS based.
Using ISearchQueryHelper
You can develop a component or helper class to query the index by using the ISearchQueryHelper interface, which enables you to take advantage of some features of the system and simplify your use of Windows Search. This interface helps you:
- Obtain an OLE DB connection string to connect to the Windows Search database.
- Convert user queries from AQS to Windows Search SQL.
This interface is implemented as a helper class to ISearchCatalogManager and is obtained by calling ISearchCatalogManager::GetQueryHelper, as shown in the following C++ example.
HRESULT GetQueryHelper(ISearchQueryHelper **ppQueryHelper)
{
*ppQueryHelper = NULL;
// Create an instance of the search manager
ISearchManager *pSearchManager;
HRESULT hr = CoCreateInstance(__uuidof(CSearchManager), NULL, CLSCTX_LOCAL_SERVER, IID_PPV_ARGS(&pSearchManager));
if (SUCCEEDED(hr))
{
// Get the catalog manager from the search manager
ISearchCatalogManager *pSearchCatalogManager;
hr = pSearchManager->GetCatalog(L"SystemIndex", &pSearchCatalogManager);
if (SUCCEEDED(hr))
{
// Get the query helper from the catalog manager
hr = pSearchCatalogManager->GetQueryHelper(ppQueryHelper);
pSearchCatalogManager->Release();
}
pSearchManager->Release();
}
return hr;
}
To implement the ISearchQueryHelper interface, see Using the ISearchQueryHelper Interface and the ISearchQueryHelper reference topic.
Note
Legacy Microsoft Windows Desktop Search (WDS) 2x compatibility: On computers running Windows XP and Windows Server 2003 and later, ISearchDesktop is deprecated. Instead, developers should use ISearchQueryHelper to get a connection string, parse the user's query into SQL, and then query through OLE DB.
Using the search-ms Protocol
The search-ms application protocol is a convention for starting an application, like Windows Explorer, to query the Windows Search index. It enables queries to be built with parameter-value arguments, including property arguments, previously saved searches, Advanced Query Syntax (AQS), Natural Query Syntax (NQS), and language code identifiers (LCIDs) for both the indexer and the query itself.
For detailed information on the search-ms protocol syntax, see Querying the Index with the search-ms Protocol.