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

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

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

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

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.

iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent=?", (stmt: ECSqlStatement) => {
  stmt.bindNavigation(1, { id: "0x132" });
  // ...
});

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.

iModel.withPreparedStatement("SELECT ECInstanceId FROM bis.Element WHERE Parent.Id=?", (stmt: ECSqlStatement) => {
  stmt.bindId(1, "0x132");
  // ...
});

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

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location=?", (stmt: ECSqlStatement) => {
  stmt.bindStruct(1, { street: "7123 Main Street", zip: 30211 });
  // ...
});

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE Location=?", (stmt: ECSqlStatement) => {
  stmt.bindValues([{ street: "7123 Main Street", zip: 30211 }]);
  // ...
});

Binding to individual struct members

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);
  // ...
});

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

iModel.withPreparedStatement("SELECT Name FROM myschema.Company WHERE PhoneNumbers=?", (stmt: ECSqlStatement) => {
  stmt.bindArray(1, ["+16134584201", "+16134584202", "+16134584222"]);
  // ...
});

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:

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:

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

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 idValue: ECSqlValue = stmt.getValue(0);
    const classIdValue: ECSqlValue = stmt.getValue(1);
    const parentValue: ECSqlValue = stmt.getValue(2);
    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.

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) {
    const classIdValue: ECSqlValue = stmt.getValue(0);
    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
...