insertRow method

Future<MssqlWriteOutcome> insertRow(MssqlWriteAssignments values, { bool readRow = true, })

Inserts one row and reads back what the server stored.

values may be empty only when the table can fill every column itself, in which case the statement becomes INSERT … DEFAULT VALUES.

Implementation

Future<MssqlWriteOutcome> insertRow(
  MssqlWriteAssignments values, {
  bool readRow = true,
}) async {
  final strategy = readRow
      ? _insertReadback()
      : (readback ?? MssqlWriteReadback.none);
  var insert = MssqlInsert.intoParts(binding.nameParts);
  if (values.isEmpty) {
    insert = insert.defaultValues();
  } else {
    insert = insert.values(values.toOperands());
  }

  switch (strategy) {
    case MssqlWriteReadback.storedRow:
      final statement = insert
          .returning(MssqlOutputClause.inserted(_readableColumnNames))
          .compile(dialect: dialect);
      final rows = await session.queryTypedRows(
        statement.sql,
        parameters: statement.parameters,
      );
      _note();
      return MssqlWriteOutcome(
        affectedRows: rows.length,
        affectedRowsSource: MssqlAffectedRowsSource.outputRows,
        rows: rows,
      );

    case MssqlWriteReadback.keyCapture:
      final capture = _captureColumns('insert');
      final statement = insert
          .returning(
            MssqlOutputClause.inserted(
              capture.map((c) => c.name),
              into: MssqlOutputTarget.variable(
                _captureVariable,
                columns: capture.map((c) => c.name),
              ),
            ),
          )
          .compile(dialect: dialect);
      final rows = await session.queryTypedRows(
        '${_declareCapture(capture)} ${statement.sql}; '
        '${_selectCaptured(capture)}',
        parameters: statement.parameters,
      );
      _note();
      return MssqlWriteOutcome(
        affectedRows: rows.length,
        affectedRowsSource: MssqlAffectedRowsSource.outputRows,
        rows: rows,
      );

    case MssqlWriteReadback.identityOnly:
      final identity = binding.column(binding.identityColumn!)!;
      final statement = insert.compile(dialect: dialect);
      // CAST to the identity column's own declared type. SCOPE_IDENTITY()
      // is decimal(38, 0), which reaches Dart as a double or as text
      // depending on the connection's decimal mode; converting on the
      // server means the value arrives as the integer it always was
      // instead of being reconstructed from a float that cannot hold it.
      final rows = await session.queryTypedRows(
        '${statement.sql}; SELECT CAST(SCOPE_IDENTITY() AS '
        '${mssqlSqlTypeDeclaration(identity)}) AS '
        '${MssqlSql.quoteIdentifier(identity.name)};',
        parameters: statement.parameters,
      );
      _note();
      return MssqlWriteOutcome(
        affectedRows: 1,
        affectedRowsSource: MssqlAffectedRowsSource.singleRowStatement,
        rows: rows,
      );

    case MssqlWriteReadback.none:
      final statement = insert.compile(dialect: dialect);
      await session.execute(statement.sql, parameters: statement.parameters);
      _note();
      return const MssqlWriteOutcome(
        affectedRows: 1,
        affectedRowsSource: MssqlAffectedRowsSource.singleRowStatement,
      );
  }
}