ECSQL Code Examples
The code examples do not explicitly show examples using the ECDb class. However, the code examples work likewise for ECDb. Just replace IModelDb.withPreparedStatement in the code examples with ECDb.withPreparedStatement.
Also see frequently used ECSQL queries for the specific ECSQL queries that app backends and services often run.
Parameter Bindings
Binding per parameter
Positional parameters
Named parameters
Binding to all parameters at once
See section "ECSQL parameter types in iTwin.js" to learn which types to use for the parameters when binding all parameters at once.
Positional parameters
Named parameters
Navigation properties
Navigation properties are structs made up of the Id of the related instance and the backing ECRelationshipClass. The NavigationBindingValue interface is used to bind values to navigation property parameters.
Because of the struct nature of navigation properties, you can also use its members in the ECSQL. The two following examples illustrate this by specifying the Id member of a navigation property.
Struct properties
You can either parameterize a struct property as a whole or parameterize individual members of the struct. See "Struct properties in ECSQL" for the ECSQL background.
The ECSQL examples used in this section refer to the sample ECSchema in "Struct properties in ECSQL".
Binding structs as a whole
Binding to individual struct members
The two ECSQL examples used in this section amount to the same results.
Array properties
See "Array properties in ECSQL" for the ECSQL background.
The ECSQL examples used in this section refer to the sample ECSchema in "Array properties in ECSQL".
Working with the query result
The current row of the query result can be retrieved in two ways:
- as a whole as JavaScript literal (adhering to the ECSQL row format)
- column by column (using the ECSqlValue API as returned from ECSqlStatement.getValue)
The column by column approach is more low-level, but gives you more flexible access to the data in the row. For example, ECClassIds are turned into class names in the ECSQL row format. Using the ECSqlValue API allows you to retrieve ECClassIds as Id64s.
Rows as a whole
The following example is intended to illustrate the ECSQL row format:
Output
Note how the ECProperties used in the ECSQL are converted to members of the JavaScript literal and how their names are transformed according to the rules described in the ECSQL row format.
The following example illustrates how to work with the ECSQL row JavaScript literal:
Output
ECInstanceId | ClassName | Parent Id | Parent RelClassName | LastMod |
---|---|---|---|---|
0x312 | StructuralPhysical.Slab | 0x433 | BisCore.PhysicalElementAssemblesElements | 2018-02-03T13:43:22Z |
0x313 | StructuralPhysical.Slab | 0x5873 | BisCore.PhysicalElementAssemblesElements | 2017-11-24T08:21:01Z |
... |
Column by column
Output
ECInstanceId | ClassName | Parent Id | Parent RelClassName | LastMod |
---|---|---|---|---|
0x312 | StructuralPhysical.Slab | 0x433 | BisCore.PhysicalElementAssemblesElements | 2018-02-03T13:43:22Z |
0x313 | StructuralPhysical.Slab | 0x5873 | BisCore.PhysicalElementAssemblesElements | 2017-11-24T08:21:01Z |
... |
The sample is code is intentionally verbose to better illustrate the semantics of the API.
The following example illustrates the flexibility of the column by column approach by preserving the ECClassId as id instead of having it converted to a class name.
Output
ECClassId | Parent RelECClassId |
---|---|
0x120 | 0x154 |
0x120 | 0x154 |
... |
Last Updated: 30 November, 2023