Backend ECSQL Code Examples

For working directly with an iModel from the frontend, an IModelDb or ECDb object is used.

This page contains example code that can be used for working with both the IModelDb and ECDb classes. In the examples, the identifier iModel is used as an object that could be either of those classes.

The examples below focus on the use of the withPreparedStatment method which works identically in both IModelDb and ECDb. Both classes have a method withPreparedStatment method.

Also see the general ECSQL Code Examples for more and different examples that use the createQueryReader method. All of the references to an iModel object in those examples can be considered an IModelDb or ECDb object.

Also see frequently used ECSQL queries for the some specific ECSQL queries that app backends and services often run.

Parameter Bindings

Binding per parameter

Positional parameters

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=? AND LastMod>=?", (stmt: ECSqlStatement) => {
  stmt.bindString(1, "MyCode");
  stmt.bindDateTime(2, "2018-01-01T12:00:00");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // do something with the query result
  }
});

Named parameters

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=:code AND LastMod>=:lastmod", (stmt: ECSqlStatement) => {
  stmt.bindString("code", "MyCode");
  stmt.bindDateTime("lastmod", "2018-01-01T12:00:00Z");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // do something with the query result
  }
});

Binding to all parameters at once

See ECSQL parameter types in iTwin.js to learn which types to use for the parameters when binding all parameters at once.

Positional parameters

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=? AND LastMod>=?", (stmt: ECSqlStatement) => {
  stmt.bindValues(["MyCode", "2018-01-01T12:00:00Z"]);

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // do something with the query result
  }
});

Named parameters

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=:code AND LastMod>=:lastmod", (stmt: ECSqlStatement) => {
  stmt.bindValues({ code: "MyCode", lastmod: "2018-01-01T12:00:00Z" });

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // do something with the query result
  }
});

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.

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent=?", (stmt: ECSqlStatement) => {
  stmt.bindNavigation(1, { id: "0x132" });
  // ...
});

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([{ id: "0x132" }]);
  // ...
});

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.

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x132");
  // ...
});

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindValues(["0x132"]);
  // ...
});

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

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location=?", (stmt: ECSqlStatement) => {
  stmt.bindStruct(1, { street: "7123 Main Street", zip: 30211 });
  // ...
});

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([{ street: "7123 Main Street", zip: 30211 }]);
  // ...
});

Binding to individual struct members

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location.Street=? AND Location.Zip=?", (stmt: ECSqlStatement) => {
  stmt.bindString(1, "7123 Main Street");
  stmt.bindInteger(2, 32443);
  // ...
});

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location.Street=? AND Location.Zip=?", (stmt: ECSqlStatement) => {
  stmt.bindValues(["7123 Main Street", 32443]);
  // ...
});

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".

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE PhoneNumbers=?", (stmt: ECSqlStatement) => {
  stmt.bindArray(1, ["+16134584201", "+16134584202", "+16134584222"]);
  // ...
});

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE PhoneNumbers=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([["+16134584201", "+16134584202", "+16134584222"]]);
  // ...
});

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:

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {

  stmt.bindId(1, "0x113");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    const row: any = stmt.getRow();
    console.log(JSON.stringify(row));
  }
});

Output

{id: "0x312", className: "StructuralPhysical.Slab", parent: {id: "0x433", relClassName: "BisCore.PhysicalElementAssemblesElements"}, lastMod: "2018-02-03T13:43:22Z"}

{id: "0x313", className: "StructuralPhysical.Slab", parent: {id: "0x5873", relClassName: "BisCore.PhysicalElementAssemblesElements"}, lastMod: "2017-11-24T08:21:01Z"}

...

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:

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {

  stmt.bindId(1, "0x113");

  console.log("ECInstanceId | ClassName | Parent Id | Parent RelClassName | LastMod");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    const row: any = stmt.getRow();
    const id: Id64String = row.id;
    const className: string = row.className;
    const parent: NavigationValue = row.parent;
    const lastMod: string = row.lastMod;

    console.log(`${id}|${className}|${parent.id}|${parent.relClassName}|${lastMod}`);
  }
});

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

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x113");

  console.log("ECInstanceId | ClassName | Parent Id | Parent RelClassName | LastMod");

  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // eslint-disable-next-line @typescript-eslint/no-deprecated
    const idValue: ECSqlValue = stmt.getValue(0);
    // eslint-disable-next-line @typescript-eslint/no-deprecated
    const classIdValue: ECSqlValue = stmt.getValue(1);
    // eslint-disable-next-line @typescript-eslint/no-deprecated
    const parentValue: ECSqlValue = stmt.getValue(2);
    // eslint-disable-next-line @typescript-eslint/no-deprecated
    const lastModValue: ECSqlValue = stmt.getValue(3);

    const id: Id64String = idValue.getId();
    const className: string = classIdValue.getClassNameForClassId();
    const parent: NavigationValue = parentValue.getNavigation();
    const lastMod: string = lastModValue.getDateTime();

    console.log(`${id}|${className}|${parent.id}|${parent.relClassName}|${lastMod}`);
  }
});

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.

// eslint-disable-next-line @typescript-eslint/no-deprecated
iModel.withPreparedStatement("SELECT ECClassId,Parent.RelECClassId FROM bis.Element WHERE Model.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x113");

  console.log("ECClassId | Parent RelECClassId");
  while (stmt.step() === DbResult.BE_SQLITE_ROW) {
    // eslint-disable-next-line @typescript-eslint/no-deprecated
    const classIdValue: ECSqlValue = stmt.getValue(0);
    // eslint-disable-next-line @typescript-eslint/no-deprecated
    const parentRelClassIdValue: ECSqlValue = stmt.getValue(1);

    const classId: string = classIdValue.getId();
    const parentRelClassId: string = parentRelClassIdValue.getId();

    console.log(`${classId}|${parentRelClassId}`);
  }
});

Output

ECClassId	Parent RelECClassId
0x120	0x154
0x120	0x154
...