Aula 3 - SQL Avançado e Dapper

Aula 3 • Projeto 9 • Sistemas de Informação

SQL Avançado e Segurança

Estrutura SQL, Transações ACID, Performance e Segurança com Dapper.

💾 SQL DDL/DML ⚡ Performance 🔒 Segurança 🔌 Dapper (.NET)

Prof. Afonso Brandão • 2 horas • Fevereiro 2025

Autoestudos

Materiais rápidos para fixar o conteúdo

DevMedia

5 minutos • Como conectar ao PostgreSQL no Entity Framework

Microservices Using ASP.NET Core

MongoDB e Docker Container

Diagrama Entidade Relacionamento

Aprofundamento com PDF acadêmico

Modelagem Lógica Orientada a Agregados

TCC: Mapeamento para SGBDs NoSQL

📋 Agenda da Aula

Fundamentos de SQL: DDL, DML, DCL, TCL
Performance: Índices e Planos de Execução
Objetos de Banco: Views e Triggers
Transações ACID
Segurança: SQL Injection vs Parameterized Queries
Integração .NET com Dapper e melhores práticas
Mapeamento lógico orientado a agregados para NoSQL
Exercício prático e Desafio Final

🧪 Como o SQL funciona?

O Optimizer decide o plano de execução. A engine (executor) apenas executa o plano escolhido.

🧠 O que é o Optimizer?

Analisa a query e usa estatísticas do banco.
Compara alternativas (índices, ordem de joins, métodos de acesso).
Gera o plano físico mais barato (custo estimado).

🧭 Optimizer passo a passo

Fluxograma do processo de otimização

O optimizer testa caminhos possíveis e escolhe o plano com menor custo estimado. A engine só executa.

🧠 Categorias do SQL

🏗️ DDL (Data Definition Language)

          /* Criar Tabela */
          CREATE TABLE Medico (
          Id INT PRIMARY KEY,
          Nome VARCHAR(100) NOT NULL,
          CRM VARCHAR(20) UNIQUE
          );

          /* Alterar Estrutura */
          ALTER TABLE Medico ADD Especialidade VARCHAR(50);

          /* Adicionar Constraint (Integridade Referencial) */
          ALTER TABLE Consulta
          ADD CONSTRAINT FK_Marcar
          FOREIGN KEY (Id_Medico) REFERENCES Medico(Id);

          /* Remover Tabela */
          DROP TABLE Log_Temp;
        

📝 DML (Data Manipulation Language)

Básico

              INSERT INTO Medico (Id, Nome, CRM)
              VALUES (1, 'House', 'CRM-SP-123');

              UPDATE Medico
              SET Especialidade = 'Diagnóstico'
              WHERE Id = 1;

              DELETE FROM Medico
              WHERE Id = 1;
            

Consulta

              SELECT M.Nome, COUNT(C.Id) as Total
              FROM Medico M
              JOIN Consulta C ON M.Id = C.Id_Medico
              GROUP BY M.Nome
              HAVING COUNT(C.Id) > 10
              ORDER BY Total DESC;
            

⚡ Performance: Índices e Planos

Índices

Estruturas (B-Tree, Hash) que aceleram a busca, mas custam na escrita.

              CREATE INDEX idx_medico_crm
              ON Medico (CRM);
            

Execution Plan

Como o banco decide executar a query (Table Scan vs Index Seek).

              EXPLAIN ANALYZE
              SELECT * FROM Medico
              WHERE CRM = '12345';
            

👁️ Objetos: Views e Triggers

Views (Tabelas Virtuais)

Simplificam queries complexas e controle de acesso.

              CREATE VIEW vw_ResumoMedico AS
              SELECT M.Nome, E.Nome as Especialidade
              FROM Medico M
              JOIN Especialidade E ON ...
            

Triggers (Gatilhos)

Executam lógica automática em eventos (INSERT/UPDATE).

              CREATE TRIGGER trg_Audit
              AFTER UPDATE ON Medico
              FOR EACH ROW
              INSERT INTO Log...
            

🔒 DCL e TCL: Controle e Transações

ACID

Atomicidade: todas as operações da transação acontecem juntas; se uma falhar, nada é persistido.
Consistência: regras do negócio e do banco (constraints) são respeitadas antes e depois da transação.
Isolamento: transações simultâneas não “vazam” estado intermediário entre si.
Durabilidade: após o COMMIT, os dados permanecem mesmo com queda de energia ou crash.

Em bancos tradicionais, ACID é o que garante confiança em operações críticas (pagamentos, estoque, transferência).

          BEGIN TRANSACTION;
          UPDATE Conta SET Saldo = Saldo - 100 WHERE Id = 1;
          UPDATE Conta SET Saldo = Saldo + 100 WHERE Id = 2;
          COMMIT; -- Ou ROLLBACK se der erro
        

🔌 .NET + Dapper

O que é Dapper?

Um "Micro-ORM" (Object-Relational Mapper) leve para .NET.

Extremamente rápido (quase igual ADO.NET puro).
Use SQL puro (você tem controle total).
Mapeia resultados do banco diretamente para objetos C#.

📦 Projeto de Exemplo

Acesse o código-fonte: github.com/afonsolelis/aula_dotnet_cars

          dotnet add package Dapper
          dotnet add package Npgsql (para PostgreSQL)
        

☠️ PERIGO: SQL Injection

⚠️ NÃO FAÇA ISSO!

Concatenar strings diretamente na query permite que atacantes executem comandos maliciosos.

          // CÓDIGO VULNERÁVEL
          string sql = "SELECT * FROM Users WHERE Name = '" + userInput + "'";
          var user = connection.Query(sql);

          // Se userInput for: "'; DROP TABLE Users; --"
          // O banco executa: SELECT * FROM Users WHERE Name = ''; DROP TABLE Users; --'
        

🛡️ SOLUÇÃO: Parameterized Queries

✅ USE SEMPRE PARÂMETROS!

O banco trata o input como dado literal, nunca como comando executável.

          // CÓDIGO SEGURO COM DAPPER
          string sql = "SELECT * FROM Users WHERE Name = @Name";

          // O Dapper envia o valor de forma segura
          var user = connection.Query(sql, new { Name = userInput });
        

Como funciona?

O Dapper envia o SQL (com @Name) e os dados separadamente para o banco.
O banco compila o plano de execução com o placeholder.
Os dados são inseridos apenas na execução, tratados estritamente como texto/número.
Mesmo que o usuário digite '; DROP TABLE..., isso será buscado como um nome de usuário bizarro, e não executado.

🛠️ Dapper: CRUD Exemplo

          using var conn = new NpgsqlConnection(connectionString);

          // SELECT (Query)
          var medico = await conn.QueryFirstOrDefaultAsync(
            "SELECT * FROM Medico WHERE Id = @Id", new { Id = 1 });

            // INSERT (Execute)
            var sqlInsert = "INSERT INTO Medico (Nome, CRM) VALUES (@Nome, @Crm)";
            var rows = await conn.ExecuteAsync(sqlInsert, new { Nome = "Ana", Crm = "123" });

            // UPDATE
            var sqlUpdate = "UPDATE Medico SET Nome = @Nome WHERE Id = @Id";
            await conn.ExecuteAsync(sqlUpdate, new { Nome = "Ana Silva", Id = 1 });
        

🚀 Dapper Avançado

Transações

using var transaction = conn.BeginTransaction();
              try {
              conn.Execute(sql1, params, transaction);
              conn.Execute(sql2, params, transaction);
              transaction.Commit();
              } catch {
              transaction.Rollback();
              }

Multi-Mapping (Joins)

var sql = @"SELECT * FROM Medico M
              JOIN Hospital H ON M.HospitalId = H.Id";

              var list = conn.Query<Medico, Hospital, Medico>(
              sql,
              (medico, hospital) => {
              medico.Hospital = hospital;
              return medico;
              },
              splitOn: "Id" // Onde divide as tabelas
              );

🏢 Além do ER: O Modelo Corporativo

O Mapa Estratégico

Modelagem Corporativa não é sobre criar tabelas; é sobre mapear os ativos de informação da empresa.

Camadas (Zachman)

Contextual (Why): Objetivos de negócio.
Conceitual (What): Entidades de negócio (ex: "Cliente" vs "Lead").
Lógico (How): Atributos e chaves.
Físico (Where): DDL, PKs, Tipos.

O Erro Comum

Focar direto no físico e criar "silos de dados" que não conversam entre áreas.

🧩 O Dilema: Monolito vs Bounded Contexts

"Um Modelo Único para Todos"? 🚫

A tentativa de criar um único modelo canônico global geralmente falha.

Bounded Contexts (DDD)

O conceito de "Produto" muda de contexto:

Vendas: Preço, Promoção, SKU.
Logística: Peso, Dimensões, Lote.

Não force uma unificação prematura. Modele contextos explícitos.

Integration Patterns

ACL (Anti-Corruption Layer): Traduz modelos entre sistemas.
Canonical Model: Útil apenas para mensageria entre sistemas díspares.

🧭 O que é DDD (Domain-Driven Design)

Conceito

DDD é uma abordagem para modelar software a partir do domínio de negócio.
Usa uma linguagem comum entre time técnico e negócio.
Organiza o sistema por contextos claros, evitando confusão.

Importância para o negócio

Reduz retrabalho: modelo reflete o que o negócio realmente faz.
Facilita evolução: mudanças em uma área não quebram as outras.
Melhora velocidade de entrega com menos risco.

Resumo: DDD alinha produto, times e dados — e diminui “traduções” erradas entre áreas.

⚖️ Governança e Stewardship

Quem é o dono do dado?

Data Steward

Responsável pelo significado e qualidade do dado em um domínio.

Data Dictionary

Glossário vivo. "O que significa 'Churn' para o Marketing?"

Lineage

Rastreabilidade. De onde veio esse dado e quem o consome?

Governança moderna não é bloqueio, é enabler para Self-Service Analytics.

🚀 Padrões Modernos: Mesh & Vault

🌐 Data Mesh

Descentralização total.

Data as a Product: Cada time expõe seus dados como APIs/datasets bem documentados.
Federated Governance: Regras globais, execução local.

🏦 Data Vault 2.0

Modelagem para Data Warehouses ágeis.

Hubs: Chaves de negócio (imutáveis).
Links: Relacionamentos (flexíveis).
Satellites: Atributos descritivos e histórico (auditável).

Churn: percentual de clientes que deixam de usar o produto em um período (ex: mês). É um dos indicadores mais críticos para o negócio.

🧠 Dicas de Arquiteto (Pro Tips)

✅ Modele Capacidades, não Telas: Telas mudam toda semana, o Core Business dura anos.
✅ Reverse Engineering: Use ferramentas (PowerDesigner, erwin, DBeaver) para documentar legados. Nunca confie na documentação antiga.
✅ Schema as Code: Versionamento de banco (Liquibase/Flyway) é obrigatório. O modelo evolui com o código.
✅ Não normalizar demais em Analytics: Star Schema (Kimball) ainda é rei para leitura rápida em BI.

🧱 NoSQL Orientado a Agregados: O Porquê

Foco no negócio, não no diagrama físico

Problema que o NoSQL resolve

Escala de leitura/escrita sem dor operacional.
Times independentes entregando mais rápido.
Dados modelados por fluxos de negócio (ex: Compra, Entrega, Suporte).

Agregado (DDD)

Conjunto de entidades tratadas como uma unidade de consistência.

Regra: tudo que muda junto, fica junto.
Boundary: um agregado por transação.
Owner: um time/dono de domínio.

NoSQL não é “anti-relacional”. É pró-fluxo de negócio e pró-escala.

🗺️ Mapeamento Lógico → NoSQL (Passo a Passo)

1. Parta do negócio

Capacidades: Compra, Entrega, Faturamento.
Eventos-chave: pedido criado, pago, entregue.
KPIs: conversão, SLA, churn.

2. Modele agregados

Escolha a raiz (ex: Pedido).
Inclua o que precisa de consistência local.
Separe o que é “referência” (ex: Cliente, Produto).

3. Pense por acesso

Consultas mais comuns definem a forma do dado.
Evite joins distribuídos.
Duplicação controlada é aceitável.

4. Defina contrato

Schema lógico + versionamento.
Ownership por domínio.
Integração via eventos.

🧩 Exemplo: Agregado "Pedido"

Visão de negócio

Um pedido é uma unidade de compra.
Status muda em etapas (criado → pago → enviado).
Precisa de leitura rápida no atendimento.

Evite quebrar o pedido em 6 tabelas e depender de joins em tempo real.

Documento NoSQL

              Pedido {
              id: "P-12093",
              cliente: { id: "C-77", nome: "Ana" },
              itens: [
              { sku: "A12", nome: "Teclado", qtd: 1, preco: 220 }
              ],
              total: 220,
              status: "pago",
              atualizadoEm: "2026-02-09T10:30:00Z"
              }
            

Benefício: atendimento responde em uma leitura, sem depender de múltiplos serviços.

⚖️ Tradeoffs & Regras de Ouro

Tradeoffs

Consistência eventual em integrações.
Duplicação de dados com controle.
Relatórios complexos vão para Analytics.

Regras de Ouro

Um agregado = uma transação.
Evite joins entre domínios.
Modelo é decidido pelos fluxos críticos do negócio.
Documente o contrato de dados (schema lógico + eventos).

Resultado: velocidade de entrega com governança mínima viável.

🌳 Modelagem NoSQL Orientada a Agregados

Do modelo lógico ao esquema físico executável

NoSQL não precisa ser "modelagem no improviso". Podemos trazer a disciplina do mundo relacional para o NoSQL usando agregados.

Modelo Lógico como Árvore

Essa árvore vira um contrato estrutural que melhora manutenção, integração e qualidade dos dados.

🛡️ $jsonSchema: Contrato Executável no MongoDB

De "schemaless" para "schema opcional, mas executável"

O MongoDB materializa o contrato com o validador nativo $jsonSchema, aplicado na criação/modificação de coleções.

db.createCollection("author", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["_id", "name", "address"],
      properties: {
        _id:     { bsonType: "objectId" },
        name:    { bsonType: "string" },
        email:   { bsonType: "string" },
        address: {
          bsonType: "object",
          required: ["street"],
          properties: {
            street: { bsonType: "string" },
            number: { bsonType: "string" },
            zip:    { bsonType: "string" }
          }
        },
        books: {
          bsonType: "array",
          items: { bsonType: "objectId" }
        }
      }
    }
  },
  validationAction: "error",
  validationLevel: "moderate"
});

Recursos do $jsonSchema

bsonType — tipagem (string, objectId, array, object...)
required — campos obrigatórios
properties — objetos aninhados
oneOf — disjunções (composição lógica)

Comportamento

Todo insert/update precisa satisfazer o schema
validationAction: error (rejeita) ou warn (loga)
validationLevel: strict ou moderate

🔢 Cardinalidade → $jsonSchema

A cardinalidade não é só documentação: vira restrição ativa no MongoDB

Cardinalidade	bsonType	required?	Extras
0–1	`"object"` ou tipo simples	Nao	Campo opcional
1–1	`"object"` ou tipo simples	Sim	Entra em `required`
0–N	`"array"` + `items`	Nao	Sem `minItems`
1–N	`"array"` + `items`	Sim	`minItems: 1`

Referências entre Agregados

0–1 / 1–1: campo objectId isolado
0–N / 1–N: array de objectId

Identificadores: tipados como objectId, normalmente required
Referência ≠ embedding: o agregado referenciado vive em outra coleção

📐 Exemplo: Cardinalidades em $jsonSchema

Author (raiz) → Address (1–1) e Books (0–N)

Modelo Lógico

$jsonSchema gerado

{
  bsonType: "object",
  required: ["_id", "name", "address"],
  properties: {
    _id:  { bsonType: "objectId" },
    name: { bsonType: "string" },
    address: {
      bsonType: "object",          // 1-1
      required: ["street"],
      properties: {
        street: { bsonType: "string" },
        number: { bsonType: "string" },
        zip:    { bsonType: "string" }
      }
    },
    books: {
      bsonType: "array",           // 0-N
      items: { bsonType: "objectId" }
    }
  }
}

⚙️ Adaptação para .NET com MongoDB.Driver

Classes C# como Aggregate Root + validator no startup

O mesmo conceito se aplica em aplicações C# com o driver oficial MongoDB.Driver:

1. Classes de Dominio

public class Author  // Aggregate Root
{
    [BsonId]
    public ObjectId Id { get; set; }

    [BsonElement("name")]
    public string Name { get; set; } = "";

    [BsonElement("email")]
    public string? Email { get; set; }  // 0-1

    [BsonElement("address")]
    public Address Address { get; set; } // 1-1
        = new();

    [BsonElement("books")]
    public List<ObjectId> Books { get; set; }
        = new();                         // 0-N
}

public class Address  // Entidade local
{
    [BsonElement("street")]
    public string Street { get; set; } = "";

    [BsonElement("number")]
    public string? Number { get; set; }

    [BsonElement("zip")]
    public string? Zip { get; set; }
}

2. Mapeamento C# → MongoDB

C#	MongoDB
`string Name`	required + string
`string? Email`	opcional (0–1)
`Address Address`	required + object (1–1)
`List<ObjectId>`	array (0–N)

Fluxo

Modelagem: classes C# representam o dominio
Contrato: $jsonSchema reforça regras no banco
Execução: driver serializa/deserializa documentos

A aplicação modela com classes; o schema valida no banco; o driver garante persistência consistente.

🚀 Aplicando o Validator via C#

Migração inicial no startup da aplicação

var client = new MongoClient("mongodb://localhost:27017");
var db = client.GetDatabase("meuApp");

// Define o $jsonSchema como BsonDocument
var schema = new BsonDocument {
  { "bsonType", "object" },
  { "required", new BsonArray { "_id", "name", "address" } },
  { "properties", new BsonDocument {
    { "_id",     new BsonDocument("bsonType", "objectId") },
    { "name",    new BsonDocument("bsonType", "string") },
    { "email",   new BsonDocument("bsonType", "string") },
    { "address", new BsonDocument {
      { "bsonType", "object" },
      { "required", new BsonArray { "street" } },
      { "properties", new BsonDocument {
        { "street", new BsonDocument("bsonType", "string") },
        { "number", new BsonDocument("bsonType", "string") },
        { "zip",    new BsonDocument("bsonType", "string") }
      }}
    }},
    { "books", new BsonDocument {
      { "bsonType", "array" },
      { "items",    new BsonDocument("bsonType", "objectId") }
    }}
  }}
};

// Cria coleção com validator
await db.CreateCollectionAsync("authors",
  new CreateCollectionOptions {
    Validator = new BsonDocument("$jsonSchema", schema),
    ValidationAction = DocumentValidationAction.Error,
    ValidationLevel  = DocumentValidationLevel.Moderate
  });

O que acontece?

InsertOneAsync(author) falha se Address ausente (1–1)
InsertOneAsync(author) aceita sem Books (0–N)
Validação estrutural no banco, sem duplicar lógica na app

Alternativa: collMod

Para alterar o validator de uma coleção existente, use o comando collMod:

db.RunCommand(new BsonDocument {
  { "collMod", "authors" },
  { "validator", new BsonDocument(
    "$jsonSchema", schema
  )}
});

📦 Entrega da Sprint: Avaliação

UML + C4 com visão integrada

Objetivo da Avaliação

Avaliar a concepção inicial dos diagramas UML de sequência e classes, além da visualização total com base no diagrama de componentes do modelo C4, considerando possíveis mudanças nas próximas sprints.

Critérios de Avaliação

Diagrama de Classes (30%)

Estrutura e Organização (15%): classes bem estruturadas, atributos e métodos claros, relacionamentos bem representados.
Coerência e Consistência (15%): alinhamento com requisitos e contexto do sistema.

Diagrama de Sequência (30%)

Clareza e Detalhamento (15%): interações e mensagens compreensíveis ao longo do tempo.
Correção e Precisão (15%): fluxo de mensagens correto e completo.

Diagrama de Componentes (C4) (40%)

Visualização Global (20%): visão clara do sistema e componentes principais.
Integração com UML (20%): coerência entre níveis de abstração e visão coesa.

Observações

Flexibilidade: diagramas são iniciais e devem evoluir nas próximas sprints.
Feedback contínuo: foco em pontos fortes e oportunidades de melhoria.

Resultados Esperados

Classes bem estruturadas e coerentes.
Sequência clara e detalhada.
C4 com visão global e integração com UML.
Adaptabilidade dos diagramas ao feedback.

🏁 Desafio Final

Modelagem de Dados (SQL ou NoSQL)

1. Foquem em modelar o dado conforme o negócio (macro visão).
2. Escolham SQL ou NoSQL (vocês já têm um PostgreSQL disponível).
3. Finalizem a modelagem em sala.
4. Cada grupo apresenta sua modelagem de forma macro.