upsertRow method

Future<MssqlWriteOutcome> upsertRow({ required MssqlWriteAssignments matching, required MssqlWriteAssignments insertValues, required MssqlWriteAssignments updateValues, bool readRow = true, })

Updates the row matching identifies or inserts it, then reads it back.

The readback is a keyed SELECT rather than an OUTPUT clause: the upsert is a multi-statement batch whose branches are chosen at run time, so an OUTPUT on one of them says nothing about whether the other ran. matching is a unique key by definition — that is what makes it an upsert — so selecting by it names the same one row either way.

Implementation

Future<MssqlWriteOutcome> upsertRow({
  required MssqlWriteAssignments matching,
  required MssqlWriteAssignments insertValues,
  required MssqlWriteAssignments updateValues,
  bool readRow = true,
}) async {
  final statement = MssqlUpsert.intoParts(
    binding.nameParts,
    matching: matching.toOperands(),
    insertValues: insertValues.toOperands(),
    updateValues: updateValues.toOperands(),
  ).compile(dialect: dialect);
  if (!readRow) {
    await session.execute(statement.sql, parameters: statement.parameters);
    _note();
    return const MssqlWriteOutcome(
      affectedRows: 1,
      affectedRowsSource: MssqlAffectedRowsSource.singleRowStatement,
    );
  }
  var read = MssqlQuery.fromParts(binding.nameParts, ref: binding.sourceRef)
      .select(
        _readableColumns.map<MssqlExpression>((c) => Col(c.name)).toList(),
      );
  for (final entry in matching.entries) {
    final assigned = entry.value;
    read = read.where(
      assigned is MssqlBoundValue && assigned.value.value == null
          ? Col(entry.key).isNull()
          : Col(entry.key).eq(_matchOperand(assigned)),
    );
  }
  final select = _rebased(read.compile(dialect: dialect));
  final rows = await session.queryTypedRows(
    '${statement.sql} ${select.sql}',
    parameters: <String, Object?>{
      ...statement.parameters,
      ...select.parameters,
    },
  );
  _note();
  return MssqlWriteOutcome(
    affectedRows: 1,
    affectedRowsSource: MssqlAffectedRowsSource.singleRowStatement,
    rows: rows,
  );
}