discontinuedPackage connect_database has been discontinued.

bulkUpdate method

Future<Result<int>> bulkUpdate(String tableName, List<Map<String, dynamic>> updates, String whereColumn, { List<String>? updateColumns, int batchSize = 500, })

Bulk updates multiple records using CASE WHEN statement for efficient batch processing. tableName - The table to update updates - List of maps containing the data to update. Each map must contain whereColumn whereColumn - The column name to use as the identifier for updates (usually primary key) updateColumns - List of column names to update (if null, updates all columns except whereColumn) batchSize - Number of records to update per batch (default: 500)

Implementation

Future<Result<int>> bulkUpdate(
  String tableName,
  List<Map<String, dynamic>> updates,
  String whereColumn, {
  List<String>? updateColumns,
  int batchSize = 500,
}) async {
  if (updates.isEmpty) return const Success(0);

  final firstRow = updates.first;
  final columnsToUpdate =
      updateColumns ?? firstRow.keys.where((k) => k != whereColumn).toList();

  if (columnsToUpdate.isEmpty) {
    return Failure(Exception('No columns to update'));
  }

  int totalAffected = 0;
  int batchCount = (updates.length / batchSize).ceil();

  debugPrint(
      'BulkUpdate: Starting ${updates.length} records in $batchCount batches of $batchSize');

  for (var i = 0; i < updates.length; i += batchSize) {
    final end =
        (i + batchSize < updates.length) ? i + batchSize : updates.length;
    final batch = updates.sublist(i, end);

    debugPrint(
        'BulkUpdate: Processing batch ${((i / batchSize) + 1).toInt()}/$batchCount (${batch.length} records)');

    // Build CASE WHEN statements for each column
    final setClauses = <String>[];
    for (final column in columnsToUpdate) {
      final caseBuffer = StringBuffer('$column = CASE $whereColumn');

      for (final row in batch) {
        final id = row[whereColumn];
        final value = row[column];

        if (value == null) {
          caseBuffer.write(" WHEN $id THEN NULL");
        } else if (value is num) {
          caseBuffer.write(" WHEN $id THEN $value");
        } else if (value is bool) {
          caseBuffer.write(" WHEN $id THEN ${value ? 1 : 0}");
        } else if (value is DateTime) {
          final formatted = _formatDateTime(value);
          caseBuffer.write(" WHEN $id THEN $formatted");
        } else {
          final escaped = value.toString().replaceAll("'", "''");
          caseBuffer.write(" WHEN $id THEN N'$escaped'");
        }
      }

      caseBuffer.write(' END');
      setClauses.add(caseBuffer.toString());
    }

    // Build WHERE IN clause with all IDs
    final ids = batch.map((r) => r[whereColumn]).toList();
    final whereIds = _buildInClause(ids);

    final sql =
        'UPDATE $tableName SET ${setClauses.join(', ')} WHERE $whereColumn IN ($whereIds)';

    debugPrint('BulkUpdate: SQL length ${sql.length} characters');
    final result = await odbc.execute(sql);

    if (result.isSuccess()) {
      totalAffected += batch.length;
      debugPrint(
          'BulkUpdate: Batch succeeded, total affected: $totalAffected');
    } else {
      final error = result.exceptionOrNull();
      debugPrint('BulkUpdate error: $error');
      return Failure(error ?? Exception('Unknown error in Bulk Update'));
    }
  }

  debugPrint('BulkUpdate: Completed, total affected: $totalAffected');
  return Success(totalAffected);
}