Using Queries

A query gets a set of records from a table that is defined in the tool manifest. The query gets the records from the underlying data storage and returns them in a dataset. The dataset includes the columns specified in the query plus the system columns. The query can include a Where clause that specifies which records to retrieve and it can include an OrderBy clause that specifies the sort order for the returned records.

Query definitions resemble view definitions, but have a different root element. For example:

<?xml version="1.0" encoding="utf-8"?>
<Query Name="Table1Query" TableRef="Table1" xmlns="https://schemas.microsoft.com/spw/">
  <ColumnRefs>
    <ColumnRef Name="Column1" />
    <ColumnRef Name="Column2" />
  </ColumnRefs>
  <Where>
    <EndsWith>
      <ColumnRef Name="Column2" />
      <Value>ing</Value>
    </EndsWith>
  </Where>
  <OrderBy>
    <ColumnRef Name="Column2" Ascending="True" />
  </OrderBy>
</Query>

The Name in a Query is used to name the DataSet that is returned by Query.

The ColumnRefs, Where, and OrderBy syntax are identical to View. See Defining the Tool and Data Structure in the Manifest for more information. The ColumnRefs elements determine the user columns that are returned in the dataset. The Where clause selects the subset of records to be returned. The OrderBy elements determine the order of the records in the dataset.

Note

Any ColumnRef specified in a Query OrderBy clause must also be specified in the Query ColumnRefs clause.

The following table describes what query operators can be used for each data type.

Query Operator	String Data Type	Integer and Double Data Types	Boolean Data Type	Date-Time Data Type
Eq	Yes	Yes	Undefined	Yes
Neq	Yes	Yes	Undefined	Yes
Contains	Yes	Undefined	Undefined	Undefined
NotContains	Yes	Undefined	Undefined	Undefined
BeginsWith	Yes	Undefined	Undefined	Undefined
NotBeginsWith	Yes	Undefined	Undefined	Undefined
EndsWith	Yes	Undefined	Undefined	Undefined
NotEndsWith	Yes	Undefined	Undefined	Undefined
Gt	Undefined	Yes	Undefined	Yes
Geq	Undefined	Yes	Undefined	Yes
Lt	Undefined	Yes	Undefined	Yes
Leq	Undefined	Yes	Undefined	Yes
IsTrue	Undefined	Undefined	Yes	Undefined
IsFalse	Undefined	Undefined	Yes	Undefined

Note

All values in the query are specified as strings. Date-Time values must be specified in the date-time format returned for the current locale by the DateTime.ToString() method without any parameters. This format returns a Date-Time value with a precision of seconds. Because the underlying Date-Time values are stored with a precision of milliseconds, there is a limitation in queries for Date-Time values. Querying for a Date-Time value testing for equality will fail if the stored value has fractions of seconds. Instead of testing for equality, you should perform the following test: that the value is greater than or equal to the Date-Time value truncated to seconds and that it is less than the truncated value plus one second.

To optimize queries, indexes can be used. Indexes are defined in the manifest at data definition time. You should select indexes to improve the efficiency of runtime queries that you expect will be used. Indexes cannot be created at runtime. See Performance Trade-offs for more information about how to optimize performance.