discontinuedPackage connect_database has been discontinued.

bulkInsert method

Future<Result<int>> bulkInsert(String tableName, List<Map<String, dynamic>> rows, { int? batchSize, })

Implementation

Future<Result<int>> bulkInsert(
    String tableName, List<Map<String, dynamic>> rows,
    {int? batchSize}) async {
  if (rows.isEmpty) return const Success(0);

  // Use optimal batch size for database type if not specified
  final optimalBatchSize = batchSize ?? _getOptimalBatchSize(odbc.type);

  final columns = rows.first.keys.toList();
  final columnsStr = columns.join(', ');

  int totalAffected = 0;
  int batchCount = (rows.length / optimalBatchSize).ceil();

  debugPrint(
      'BulkInsert: Starting ${rows.length} records in $batchCount batches of $optimalBatchSize');

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

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

    final valuesBuffer = StringBuffer();

    for (var j = 0; j < batch.length; j++) {
      final row = batch[j];
      if (j > 0) valuesBuffer.write(', ');

      valuesBuffer.write('(');
      for (var k = 0; k < columns.length; k++) {
        if (k > 0) valuesBuffer.write(', ');
        final col = columns[k];
        final val = row[col];

        if (val == null) {
          valuesBuffer.write('NULL');
        } else if (val is num) {
          valuesBuffer.write(val.toString());
        } else if (val is bool) {
          valuesBuffer.write(val ? '1' : '0');
        } else if (val is DateTime) {
          // Format: YYYY-MM-DD HH:MM:SS.mmm (milliseconds only for SQL Server DATETIME)
          final year = val.year.toString().padLeft(4, '0');
          final month = val.month.toString().padLeft(2, '0');
          final day = val.day.toString().padLeft(2, '0');
          final hour = val.hour.toString().padLeft(2, '0');
          final minute = val.minute.toString().padLeft(2, '0');
          final second = val.second.toString().padLeft(2, '0');
          final ms = val.millisecond.toString().padLeft(3, '0');
          valuesBuffer.write("'$year-$month-$day $hour:$minute:$second.$ms'");
        } else {
          valuesBuffer.write("N'");
          valuesBuffer.write(val.toString().replaceAll("'", "''"));
          valuesBuffer.write("'");
        }
      }
      valuesBuffer.write(')');
    }

    final sql = 'INSERT INTO $tableName ($columnsStr) VALUES $valuesBuffer';

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

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

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