Recomendações para API RESTful em .NET

Versão e Frameworks Recomendados

• .NET 8 (LTS – suporte até nov/2026)
• ASP.NET Core Web API
• Oracle.ManagedDataAccess.Core (driver Oracle oficial)
• CsvHelper (leitura otimizada de CSV)

Design Patterns e Arquitetura

• Clean Architecture / Vertical Slice Architecture
• Repository Pattern (acesso a dados)
• Unit of Work (transações)
• CQRS (separar commands/queries)
• Result Pattern (tratamento de erros)

Estrutura do Projeto

CsvImportApi/
├── src/
│   ├── CsvImportApi.Api/              # Controllers, Middleware
│   ├── CsvImportApi.Application/      # Services, DTOs, Interfaces
│   ├── CsvImportApi.Domain/           # Entities, Value Objects
│   └── CsvImportApi.Infrastructure/   # Repositories, Oracle Context
└── tests/

Código Completo da API

1. CsvImportApi.Api/Controllers/CsvImportController.cs

using CsvImportApi.Application.DTOs;
using CsvImportApi.Application.Services;
using Microsoft.AspNetCore.Mvc;

namespace CsvImportApi.Api.Controllers;

[ApiController]
[Route("api/[controller]")]
public class CsvImportController : ControllerBase
{
    private readonly ICsvImportService _csvImportService;
    private readonly ILogger<CsvImportController> _logger;

    public CsvImportController(ICsvImportService csvImportService, ILogger<CsvImportController> logger)
    {
        _csvImportService = csvImportService;
        _logger = logger;
    }

    [HttpPost("upload")]
    [RequestSizeLimit(100_000_000)] // 100MB
    [RequestFormLimits(MultipartBodyLengthLimit = 100_000_000)]
    public async Task<IActionResult> UploadCsv(IFormFile file, CancellationToken cancellationToken)
    {
        if (file == null || file.Length == 0)
            return BadRequest(new { error = "Arquivo não fornecido ou vazio" });

        if (!file.FileName.EndsWith(".csv", StringComparison.OrdinalIgnoreCase))
            return BadRequest(new { error = "Apenas arquivos CSV são permitidos" });

        try
        {
            var tableName = Path.GetFileNameWithoutExtension(file.FileName);
            
            using var stream = file.OpenReadStream();
            var result = await _csvImportService.ImportCsvAsync(stream, tableName, cancellationToken);

            return Ok(result);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Erro ao processar arquivo CSV");
            return StatusCode(500, new { error = "Erro interno ao processar arquivo", details = ex.Message });
        }
    }
}

2. CsvImportApi.Application/DTOs/ImportResultDto.cs

namespace CsvImportApi.Application.DTOs;

public class ImportResultDto
{
    public string TableName { get; set; } = string.Empty;
    public int TotalRows { get; set; }
    public int SuccessfulRows { get; set; }
    public int FailedRows { get; set; }
    public TimeSpan ProcessingTime { get; set; }
    public List<ErrorDetail> Errors { get; set; } = new();
}

public class ErrorDetail
{
    public int LineNumber { get; set; }
    public string ErrorMessage { get; set; } = string.Empty;
    public Dictionary<string, string> RowData { get; set; } = new();
}

3. CsvImportApi.Application/Services/ICsvImportService.cs

using CsvImportApi.Application.DTOs;

namespace CsvImportApi.Application.Services;

public interface ICsvImportService
{
    Task<ImportResultDto> ImportCsvAsync(Stream csvStream, string tableName, CancellationToken cancellationToken);
}

4. CsvImportApi.Application/Services/CsvImportService.cs

using System.Collections.Concurrent;
using System.Diagnostics;
using CsvHelper;
using CsvHelper.Configuration;
using CsvImportApi.Application.DTOs;
using CsvImportApi.Infrastructure.Repositories;
using System.Globalization;

namespace CsvImportApi.Application.Services;

public class CsvImportService : ICsvImportService
{
    private readonly IBulkInsertRepository _bulkInsertRepository;
    private readonly ILogger<CsvImportService> _logger;
    private const int BATCH_SIZE = 1000;
    private const int MAX_DEGREE_OF_PARALLELISM = 4;

    public CsvImportService(IBulkInsertRepository bulkInsertRepository, ILogger<CsvImportService> logger)
    {
        _bulkInsertRepository = bulkInsertRepository;
        _logger = logger;
    }

    public async Task<ImportResultDto> ImportCsvAsync(Stream csvStream, string tableName, CancellationToken cancellationToken)
    {
        var stopwatch = Stopwatch.StartNew();
        var result = new ImportResultDto { TableName = tableName };
        var errors = new ConcurrentBag<ErrorDetail>();

        try
        {
            var config = new CsvConfiguration(CultureInfo.InvariantCulture)
            {
                HasHeaderRecord = true,
                MissingFieldFound = null,
                BadDataFound = null
            };

            using var reader = new StreamReader(csvStream);
            using var csv = new CsvReader(reader, config);

            // Lê o header
            await csv.ReadAsync();
            csv.ReadHeader();
            var headers = csv.HeaderRecord ?? throw new InvalidOperationException("CSV sem header");

            // Lê todos os registros em batches
            var allRecords = new List<Dictionary<string, string>>();
            int lineNumber = 2; // Linha 1 é o header

            while (await csv.ReadAsync())
            {
                try
                {
                    var record = new Dictionary<string, string>();
                    foreach (var header in headers)
                    {
                        record[header] = csv.GetField(header) ?? string.Empty;
                    }
                    record["_LineNumber"] = lineNumber.ToString();
                    allRecords.Add(record);
                }
                catch (Exception ex)
                {
                    errors.Add(new ErrorDetail
                    {
                        LineNumber = lineNumber,
                        ErrorMessage = $"Erro ao ler linha: {ex.Message}",
                        RowData = new Dictionary<string, string>()
                    });
                }
                lineNumber++;
            }

            result.TotalRows = allRecords.Count;

            // Processa em batches usando multi-threading
            var batches = allRecords
                .Select((record, index) => new { record, index })
                .GroupBy(x => x.index / BATCH_SIZE)
                .Select(g => g.Select(x => x.record).ToList())
                .ToList();

            var parallelOptions = new ParallelOptions
            {
                MaxDegreeOfParallelism = MAX_DEGREE_OF_PARALLELISM,
                CancellationToken = cancellationToken
            };

            await Parallel.ForEachAsync(batches, parallelOptions, async (batch, ct) =>
            {
                try
                {
                    await _bulkInsertRepository.BulkInsertAsync(tableName, batch, ct);
                    Interlocked.Add(ref result.SuccessfulRows, batch.Count);
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "Erro ao inserir batch");
                    
                    // Tenta inserir linha por linha para identificar erros específicos
                    foreach (var record in batch)
                    {
                        try
                        {
                            await _bulkInsertRepository.BulkInsertAsync(tableName, new List<Dictionary<string, string>> { record }, ct);
                            Interlocked.Increment(ref result.SuccessfulRows);
                        }
                        catch (Exception innerEx)
                        {
                            var lineNum = int.Parse(record["_LineNumber"]);
                            var rowData = new Dictionary<string, string>(record);
                            rowData.Remove("_LineNumber");

                            errors.Add(new ErrorDetail
                            {
                                LineNumber = lineNum,
                                ErrorMessage = innerEx.Message,
                                RowData = rowData
                            });
                            Interlocked.Increment(ref result.FailedRows);
                        }
                    }
                }
            });

            result.Errors = errors.OrderBy(e => e.LineNumber).ToList();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Erro crítico no processamento do CSV");
            throw;
        }
        finally
        {
            stopwatch.Stop();
            result.ProcessingTime = stopwatch.Elapsed;
        }

        return result;
    }
}

5. CsvImportApi.Infrastructure/Repositories/IBulkInsertRepository.cs

namespace CsvImportApi.Infrastructure.Repositories;

public interface IBulkInsertRepository
{
    Task BulkInsertAsync(string tableName, List<Dictionary<string, string>> records, CancellationToken cancellationToken);
}

6. CsvImportApi.Infrastructure/Repositories/OracleBulkInsertRepository.cs

using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Text;

namespace CsvImportApi.Infrastructure.Repositories;

public class OracleBulkInsertRepository : IBulkInsertRepository
{
    private readonly string _connectionString;
    private readonly ILogger<OracleBulkInsertRepository> _logger;

    public OracleBulkInsertRepository(IConfiguration configuration, ILogger<OracleBulkInsertRepository> logger)
    {
        _connectionString = configuration.GetConnectionString("OracleDb") 
            ?? throw new InvalidOperationException("Connection string não configurada");
        _logger = logger;
    }

    public async Task BulkInsertAsync(string tableName, List<Dictionary<string, string>> records, CancellationToken cancellationToken)
    {
        if (!records.Any()) return;

        using var connection = new OracleConnection(_connectionString);
        await connection.OpenAsync(cancellationToken);

        using var transaction = connection.BeginTransaction();
        try
        {
            // Remove o campo auxiliar _LineNumber
            var columns = records.First().Keys.Where(k => k != "_LineNumber").ToList();
            
            // Monta o comando INSERT com array binding (bulk insert otimizado)
            var sql = BuildBulkInsertCommand(tableName, columns, records.Count);

            using var command = new OracleCommand(sql, connection);
            command.Transaction = transaction;
            command.ArrayBindCount = records.Count;

            // Configura os parâmetros com array binding
            foreach (var column in columns)
            {
                var parameter = new OracleParameter
                {
                    ParameterName = $":{column}",
                    OracleDbType = OracleDbType.Varchar2,
                    Size = 4000
                };

                var values = records.Select(r => r.ContainsKey(column) ? r[column] : (object)DBNull.Value).ToArray();
                parameter.Value = values;
                
                command.Parameters.Add(parameter);
            }

            await command.ExecuteNonQueryAsync(cancellationToken);
            await transaction.CommitAsync(cancellationToken);

            _logger.LogInformation($"Inseridos {records.Count} registros na tabela {tableName}");
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync(cancellationToken);
            _logger.LogError(ex, $"Erro ao inserir batch na tabela {tableName}");
            throw;
        }
    }

    private string BuildBulkInsertCommand(string tableName, List<string> columns, int recordCount)
    {
        var columnList = string.Join(", ", columns);
        var parameterList = string.Join(", ", columns.Select(c => $":{c}"));
        
        return $"INSERT INTO {tableName} ({columnList}) VALUES ({parameterList})";
    }
}

7. CsvImportApi.Api/Program.cs

using CsvImportApi.Application.Services;
using CsvImportApi.Infrastructure.Repositories;

var builder = WebApplication.CreateBuilder(args);

// Configuração de serviços
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Dependency Injection
builder.Services.AddScoped<ICsvImportService, CsvImportService>();
builder.Services.AddScoped<IBulkInsertRepository, OracleBulkInsertRepository>();

// Configuração de upload de arquivos grandes
builder.Services.Configure<Microsoft.AspNetCore.Http.Features.FormOptions>(options =>
{
    options.MultipartBodyLengthLimit = 100_000_000; // 100MB
});

var app = builder.Build();

// Pipeline HTTP
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

8. appsettings.json

{
  "ConnectionStrings": {
    "OracleDb": "User Id=seu_usuario;Password=sua_senha;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

9. CsvImportApi.Api.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="CsvHelper" Version="30.0.1" />
    <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="23.4.0" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
  </ItemGroup>

</Project>

Recursos Implementados

✅ Multi-threading: Parallel.ForEachAsync com controle de paralelismo
✅ Batch Insert: Oracle Array Binding (até 1000 registros por batch)
✅ Tratamento de erros: Captura linha específica e retorna no JSON
✅ Performance: CsvHelper + StreamReader otimizado
✅ Clean Architecture: Separação de responsabilidades
✅ Logging: ILogger integrado
✅ Cancellation Token: Suporte a cancelamento de operações

Exemplo de Response

{
  "tableName": "order",
  "totalRows": 5000,
  "successfulRows": 4998,
  "failedRows": 2,
  "processingTime": "00:00:03.4521234",
  "errors": [
    {
      "lineNumber": 150,
      "errorMessage": "ORA-01400: cannot insert NULL into (\"SCHEMA\".\"ORDER\".\"ID\")",
      "rowData": {
        "id": "",
        "customer": "John Doe",
        "amount": "100.50"
      }
    }
  ]
}