Aula 07 ADO.NET

Prévia do material em texto

Desenvolvimento de Software
Acesso a Dados – ADO.NET
Prof. Henrique Mota
mota.henrique@gmail.com
http://www.henriquemota.com.br
 
Banco de Dados
 
Banco de Dados
• A maioria das aplicações exigem um armazenamento de 
dados estruturado e persistente: 
– E-Commerce: registrar um pedido, entregar um pedido etc.
– Recursos Humanos: dados de pessoal, salários e benefícios.
– Vendas: pedidos, produtos, cobranças e pagamentos.
– CRM: dados de e sobre clientes.
• Essas necessidades de armazenamento variam de acordo 
com o tipo de aplicativo:
– Processamento de Transações (Transaction Processing - OLTP)
– Processamento Analítico (Business Intelligence/Data Warehouse 
- OLAP)
– Web Sites e Enterprise Portals
 
REQUISITOS
• Permitir armazenar, consultar e modificar dados
• Permitir mover, copiar e transformar dados
• Permitir executar backup e recuperação de dados
• Garantir a integridade dos dados
• Ser “escalável” e estar sempre disponível:
– Atender a um grande número de usuários
– Armazenar e processar enormes volumes de dados
– Possuir alto desempenho com baixo tempo de resposta
• Ser seguro
• Permitir o desenvolvimento de aplicativos
Banco de Dados
 
Evolução da Tecnologia
• Baseado em arquivos
• Hierárquico
• Rede
• Relacional
• Orientado a Objetos
• XML (eXtensible Markup Language)
Banco de Dados
 
• Tabela (relação, entidade)
– Uma coleção de dados sobre 
uma coisa específica 
– Organizada em linhas e colunas
• Coluna (atributo, campo)
– Descreve parte de uma entidade (p.ex. Nome)
– Tem um tipo de dado (p.ex. integer, character, binary)
– Pode ser nulo
• Linha (tupla, registro)
– Uma única instância de dados em uma tabela
– Cada linha é única
ID Nome SobreNome
1 José Silva
2 Maria Santos
Tabelas
 
• As tabelas podem ser relacionadas através de dados 
chave: chave-primária x chave-estrangeira (p.ex. um 
livro possui um autor)
– Chave-primária (primary-key ou PK)
• Garante a unicidade de uma linha
• Pode ser composta por uma ou mais colunas
• Assegura a integridade da entidade
– Chave-estrangeira (foreign-key ou FK)
• Estabelece um relacionamento lógico entre tabelas
• Uma ou mais colunas de uma tabela que coincidem com 
a chave-primária de outra tabela
• Integridade referencial
Relacionando dados
 
O diagrama, esquema ou modelo relacional mostra as 
tabelas, colunas, chaves-primárias, chaves-estrangeiras 
e relacionamentos de um Banco de Dados.
1
∞
N
Modelo de Dados
Livro
IDLivro
IDAutor
Título
Gênero
Autor
IDAutor
Nome
Sobrenome
Relacionando dados
 
Chave-primária PK
Chave-estrangeira FK
Tabela Livro
Tabela Autor
PK/FK Relacionamento
IDAutor Nome Sobrenome
1 José Silva
2 Maria Santos
IDLivro IDAutor Título Gênero
1 2 Minha vida como DBA Autobiografia
2 1 Database Handbook Referência
Relacionando dados
 
Um-para-Um (1:1)
• Uma linha da tabela X casa com uma linha 
da tabela Y
• Um Livro tem no máximo e no mínimo um 
Registro ISBN
Um-para-Muitos (1:M)
• Uma linha na tabela X casa com 0 ou + 
linhas na tabela Y
• Uma Editora publica um ou mais Livros
Muitos-para-Muitos (M:N)
• 1 ou + linhas na tabela X casam com 1 ou 
+ linhas na tabela Y
• Um Autor escreve um ou mais Livros;
• Um Livro é escrito por um ou mais Autores.
Autor
M N
Livro
Editora
1 M
Livro
Livro
1 1
Registro ISBN
Tipos de relacionamentos
 
• Mais complexo
• Resulta em tabelas muito grandes ( com dados 
repetidos)
• Difícil de assegurar a integridade de dados
• Solução: criar uma terceira tabela
– A terceira tabela contém a chave-primária das duas tabelas 
originais em uma chave composta.
– Os dados são repetidos na terceira tabela, mas não nas 
duas tabelas originais.
Autor LivroMM Autoria1 1
Relacionamento M:N
 
Relacionamento M:N
Os dados são duplicados aqui
 
Normalização
• O processo de dividir poucas tabelas grandes em muitas tabelas pequenas
• Meta: minimizar redundância de dados, maximizar precisão
• Melhora o desempenho para atualizações
• Desejável em aplicações transacionais
Desnormalização
• O processo de combinar muitas tabelas pequenas em poucas tabelas 
grandes.
• Meta: melhorar o desempenho
• Introduz redundância de dados
• Melhora o desempenho para consultas
• Desejável em aplicações analíticas e data warehouse
Normalização
 
• Uma junção é um modo de combinar dados de 
múltiplas tabelas, normalmente utilizando os 
relacionamentos entre chaves-primárias e chaves-
estrangeiras.
$25
$8
$5
$10
Custo
AngloDVD
Anglo
PontualVHS
Game
Pontual
Distrib.
CD
Produto
Tabela Produto
Anglo
Pontual
Distrib.
José P.
Maria A.
Contato
MG
SP
Estado
Tabela Distribuidor
Junções
 
• O resultado de uma junção natural.
Maria A.SPPontual$10CD
Maria A.SPPontual$5VHS
Anglo
Anglo
Distrib.
MG
MG
Estado
José P.
José P.
Contato
$25
$8
Custo
DVD
Game
Produto
Junções
 
• Linguagem padrão para acesso a um banco de dados 
relacional, padronizada pelo American National 
Standards Institute (ANSI); SQL-92
• Aberta, mas não realmente:
– As funções mais comuns são geralmente as mesmas 
entre os diversos produtos existentes.
– A maioria dos produtos têm extensões proprietárias.
• Subconjuntos da SQL
– Data Definition Language (DDL)
– Data Manipulation Language (DML)
– Data Control Language (DCL)
Structured Query Language
 
• Usada para criar e modificar objetos de banco de 
dados.
CREATE DATABASE Livraria
CREATE TABLE Livro
(
 LivroID INT IDENTITY(1,1) PRIMARY KEY,
 Titulo VARCHAR(40) NOT NULL,
 DatPub DATE NOT NULL,
 Descricao VARCHAR(80),
 Genero INT NOT NULL
)
DDL Exemplos
 
• Selecionar dados a serem consultados.
SELECT * FROM Autor
SELECT AutorID, Nome, SobreNome 
 FROM Autor
SELECT AutorID, Nome, SobreNome, Telefone 
 FROM Autor
 WHERE Cidade = ‘São Paulo’
SELECT Nome, SobreNome, Telefone 
 FROM Autor
 WHERE AutorID = 249
DML Exemplos
 
INSERT INTO Livro
 (Titulo, DatPub, Descricao, Genero)
VALUES
 (‘Projeto de Banco de Dados’, GETDATE(), 
 ‘Como construir um Banco de Dados’, 3)
UPDATE Autor
 SET Telefone = ‘3091-1234’
 WHERE AutorID = 5
DELETE FROM Autor
 WHERE AutorID = 5
• Inserir, atualizar e excluir dados.
DML Exemplos
 
• Configura opções de segurança em objetos de banco 
de dados.
GRANT INSERT, UPDATE, DELETE
 ON Autor
 TO Maria, José
REVOKE CREATE TABLE FROM José
DENY ALL
 ON Autor, Livro
 TO Salete
DCL Exemplos
 
• Uma view é uma tabela virtual.
• Abstrai as estruturas das tabelas relacionadas.
• Abstrai uma consulta possivelmente complexa.
• Fornece abstração quanto à segurança.
• No MS SQL Server, uma view pode ser:
– Indexada
– Atualizada (alteração e inserção de dados)
Visões
 
CREATE VIEW Pedidos AS
 SELECT p.PedidoID, c.Nome
 FROM Cliente c
 INNER JOIN Pedido p 
ON c.ClienteID = p.ClienteID
 ORDER BY p.PedidoID
Exemplo de View
 
SELECT * 
FROM Pedidos 
WHERE Nome = 'Meu Melhor Cliente'
PedidoID Nome
101 Meu Melhor Cliente
137 Meu Melhor Cliente
…
Exemplo de View
 
• Um grupo de comandos SQL que é armazenado e 
executado dentro do banco de dados.
• Não faz parte do SQL padrão.
• Fornece grande desempenho.
• Pode controlar acesso a dados.
• Pode aceitar parâmetros.
• Pode retornar diversos tipos de dados:
– Parâmetros de saída
– Valores de retorno
– Conjuntos de dados de retorno
Stored Procedures
 
CREATE PROCEDURE HistPedidos @ClienteID nchar(5)
AS
 SELECT NomeProduto, Total=SUM(Quantidade)
 FROM Produto P, [Itens Pedidos] IP, 
 Pedido P, Cliente C
 WHERE C.ClienteID = @ClienteID
 AND C.ClienteID = P.ClienteID
 AND P.PedidoID = IP.PedidoID
 AND IP.ProdutoID = P.ProdutoID
 GROUP BY NomeProduto
Stored Procedure
 
exec HistPedidos ‘ACNIELSEN'
NomeProduto Total
Papel A4 6
Cartucho HP 692C 21
... ...
Stored Procedure
 
• Use o comando RETURN para retornar o status
– 0 é o padrão no MS SQL Server
– Só pode ser numérico
• Use parâmetros OUTPUT para retornar resultados
RETURN 1
CREATE PROCEDURE MinhaProcedure @ValRetorno INT OUTPUT
...SELECT @ValRetorno = NomeColuna FROM Tabela
Stored Procedure
 
• Como as stored procedures, triggers são conjuntos de 
comandos SQL que são armazenados e executados dentro do 
banco de dados.
• Porém, não são diretamente chamados por um usuário.
• São executadas quando uma determinada modificação de 
dados ocorrer (INSERT, UPDATE ou DELETE)
• Garante a execução de regras de negócio.
• FOR AFTER: a trigger é executada depois que a ação de 
atualização especificada for completada.
• FOR INSTEAD OF: a trigger é executada no lugar da ação de 
atualização especificada.
Triggers
 
• Transação: uma seqüência de comandos SQL que 
constitua uma unidade lógica de trabalho.
• Deve aderir às propriedades ACID:
– Atômica: todos os comandos são executados com sucesso 
ou todos falham.
– Consistente: deve deixar os dados em um estado 
consistente quando completada.
– Isolada: não pode ver as modificações feitas por outras 
transações simultâneas.
– Durável: deve ser permanente quando completada, até no 
caso de queda do sistema.
Transações
 
• Níveis de isolamento
– Read Uncommitted
– Read Committed
– Repeatable Read
– Serializable
– Tradeoffs (concorrência vs. integridade de dados)
• Bloqueios
– Asseguram a integridade de transações e a consistência do 
banco de dados.
– Evitam que os usuários vejam “dados fantasmas”.
– Podem resultar em deadlocks (paralisações).
Concorrência
 
ODBC (Open Database Connectivity)
– Interoperabilidade para vários sistemas gerenciadores de banco de 
dados (DBMS).
– API largamente aceita.
– Usa SQL como linguagem de acesso a dados.
DAO (Data Access Objects)
– Interface de programação para bancos de dados JET/ISAM.
– Usa automação (ActiveX, OLE automation).
RDO (Remote Data Objects)
– Versão mais sofisticada e otimizada do ODBC.
– Desenvolvido especialmente para bancos de dados cliente/servidor.
Acesso a Dados
 
OLE DB
– Amplo acesso a dados, relacionais e outros.
– Construído através da tecnologia COM
– Não restrito à SQL para a recuperação de dados
– Pode utilizar drives ODBC.
– Interface de baixo-nível (C++).
ADO (ActiveX Data Objects)
– Interface simples, orientada a objetos e baseada em 
componentes.
– Fornece um modelo de programação para o OLE DB 
acessível fora do C++.
Acesso a Dados
 
ADO
ODBC Provider Simple Provider Native Provider
OLE DB 
Provider
ODBC
ODBC Driver
Text
File
Database Database
OLE DB 
Provider
Mainframe
OLE DB
Aplicação
ADO Tradicional
 
• ADO foi projetado através de um modelo conectado, 
firmemente acoplado.
– Apropriado para arquiteturas cliente/servidor.
• Principalmente relacional (XML é hierárquico).
• Porém, o projeto dos objetos não foi bem definido:
– Há muitas formas para se fazer a mesma coisa.
– Os objetos tentam fazer coisas demais.
• Não foi originalmente projetado para trabalhar em 
ambiente distribuído, de múltiplas camadas.
ADO Tradicional
 
• Projeto mais bem acabado.
• Altamente escalável através de um modelo 
desconectado e robusto.
• Rico suporte a XML (hierárquico além do relacional).
• Acesso de dados sobre HTTP.
• Mantém o modelo de programação do ADO.
• Mantém o ADO disponível via interoperabilidade .NET 
vs. COM.
ADO.NET
 
• Combina ADO e OLEDB em uma só camada.
• Cada provedor contém um conjunto de classes que 
implementa interfaces comuns.
• Implementações de provimento gerenciável:
– ADO: fornece acesso a qualquer fonte de dados compatível 
com OLE DB.
– SQL Server: fornece desempenho otimizado quando for 
usado o MS SQL Server ou o Oracle.
– Exchange: recupera e atualiza dados no Microsoft 
Exchange.
ADO.NET - Providers
 
SQL Managed Provider
SQL Server
Database
ADO.NET
ADO Managed Provider
OLE DB 
Provider
Database
Aplicação
ADO.NET – Providers
 
Estilo de Acesso a Dados
Somente-para-frente, somente-leitura:
– A aplicação envia uma consulta e depois lê e processa os 
resultados.
– Cursor tipo “mangueira” ou “duto” de comunicação
– Objeto DataReader
Desconectado:
– A aplicação envia uma consulta e depois recupera e 
armazena os resultados para processamento.
– Minimiza tempo conectado ao banco de dados.
– Objeto DataSet
 
Componente chave de formulários Web.
Uso flexível e fácil:
– Liga ou anexa uma propriedade de um controle a uma 
informação em qualquer tipo de fonte de dados.
– Fornece controle sobre como os dados vêm e voltam. 
– Controles simples para exibir um único valor.
– Controles complexos para exibir estruturas de dados.
Ligações de Dados
 
• Cria uma conexão única com uma fonte de dados (data 
source).
• Implementada pelas classes SqlDbConnection e 
OleDbConnection
• Funcionalidade:
– Abre e fecha conexões
– Inicia transações
• A classe IDbTransaction fornece métodos Commit e Rollback
• Usada em conjunto com objetos IDbCommand e 
IDataAdapter.
• Propriedades adicionais, métodos e coleções dependem do 
provedor de dados.
IDbConnection Interface
 
Representa um comando a ser enviado para a fonte de dados 
(data source).
– Normalmente, mas não necessariamente em SQL.
Implementado pelas classes OleDbCommand e SqlCommand.
Funcionalidade:
– Define o comando a ser executado
– Executa o comando
– Passa e recupera parâmetros
– Cria uma versão compilada do comando
– ExecuteReader retorna linhas
– ExecuteNonQuery não retorna nada
– ExecuteScalar retorna um único valor
Propriedades adicionais, métodos e coleções dependem do 
provedor de dados.
IDBCommand Interface
 
• Representa um conjunto de comandos SQL e uma 
conexão de banco de dados que são usados para 
Preenchimento o DataSet
• Implementado pelas classes SqlDataReader e 
OleDbDataReader.
• Criado através do método ExecuteReader da 
classe IDbCommand.
• Operações associadas com o objeto 
IDbConnection são desabilitadas até que o leitor 
seja fechado.
IDbDataReader Interface
 
string sConnString = “Provider=SQLOLEDB.1;” +
 “User ID=sa;Initial Catalog=MeuBanco;” +
 “Data Source=MeuServidor”;
OleDbConnection sqlCon = new OleDbConnection(sConnString);
sqlCon.Open();
string sQueryString = “SELECT NomeCliente FROM Cliente”;
OleDbCommand sqlCmd = new OleDbCommand(sQueryString, sqlCcon);
OleDbDataReader sqlReader = sqlCmd.ExecuteReader();
while (sqlReader.Read()) {
 Console.WriteLine(sqlReader.GetString(0));
}
sqlReader.Close();
sqlCon.Close();
DataReader Exemplo
 
• Provedor gerenciado para uso com provedores de 
dados OLEDB
– SQLOLEDB (SQL Server) – usa System.Data.SQL
– MSDAORA (Oracle)
– JOLT (Jet)
– OLEDB para provedores ODBC
• Classes OleDbConnection, OleDbCommand e 
OleDbDataReader
• Classes para tratamento de erros
• Classes para pool de conexões
System.Data.OleDb
 
• Provedor gerenciado nativo para o SQL Server
• Construído com TDS (Tabular Data Stream) para alto 
desempenho com o SQL Server
• Classes SqlConnection, SqlCommand e 
SqlDataReader.
• Classes para :
– Tratamento de erros
– Pool de conexões (implicitamente habilitadas por 
definição)
• System.Data.SqlTypes fornece classes para tipos 
de dados nativos do SQL Server.
System.Data.SqlClient
 
• Contém os principais classes do ADO.NET.
• O DataSet desconectado é sua classe central.
• Suporta todos os tipos de aplicativos:
– Internet
• ASP.NET (C#, VB.NET e JavaScript)
• XML
– Windows 
• C#
• VB.NET
System.Data
 
• Contém classes usadas por ou derivadas de 
provedores gerenciados:
– IDbConnection
– IDbCommand
– IDbDataReader
System.Data
 
• Uma coleção de tabelas
• Não possui conhecimento da fonte de dados
• Mantém todos os relacionamentos entre as tabelas
• Modelo de programação rico (possui objetos para 
tabelas, colunas, relacionamentos, e assim por 
diante)
• Lembra-se do estado original e atual dos dados
• Pode modificar dinamicamente dados e metadados
• O formato para serialização nativo é o XML
• Localizada no pacote System.Data
DataSet
 
DataSet
DataTable
DataRelation
DataRow
DataColumn
DataSet
 
• Objeto na memória representando uma tabela
– Colunas
– Linhas
• Esquema definido por coleção de colunas
• A integridade de dados é providaatravés de objetos 
Constraint
• Eventos públicos:
– Modificar / excluir linhas (registros)
– Modificar colunas
DataTable
 
• Bloco de construção fundamental de um esquema 
DataTable (contido na coleção de colunas).
• Define que tipo de dados pode ser aceito (via 
propriedade DataType).
• Outras propriedades importantes incluem 
AllowNull, Unique, e ReadOnly.
• Pode conter Constraints (coleção na DataTable).
• Pode conter Relações (coleção no DataSet).
DataColumn
 
• Representa dados em uma DataTable (contida em 
coleção de linhas).
• Segue o esquema definido por objetos DataColumns.
• Possui propriedades para determinar o estado das 
linhas (por exemplo, nova, alterada, excluída, e assim 
por diante).
• Todas as adições e modificações são atualizadas 
(“committed”) através do método AcceptChanges do 
objeto DataTable.
DataRow
 
• Relaciona duas DataTables via DataColumns.
• O valor DataType de ambas DataColumns deve ser 
idêntico.
• Atualizações podem ser cascateadas para 
DataTables filhas.
• As modificações que invalidam a relação não são 
permitidas.
DataRelation
 
• Popula ou envia atualizações para um DataSet
• Implementado por OleDbDataAdapter e 
SqlDataAdapter.
• Não conectado.
• Representa uma abordagem assíncrona.
• Um super conjunto de um objeto command.
• Contém quatro objetos de comandos padrão para: 
Select, Insert, Update, e Delete.
IDataAdapter Interface
 
string sConnString = “Persist Security Info=False;” +
 “User ID=sa;Initial Catalog=Northwind;” +
 “Data Source=MEUSERVIDOR”;
SqlConnection sqlCon = new SqlConnection(sConnString);
sqlCon.Open();
string sqlString = “SELECT NomeEmpresa FROM Cliente”;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
DataSet sqlDataSet = new DataSet();
sqlDataAdapter.SelectCommand = new SqlCommand(sqlString, sqlCon);
sqlDataAdapter.Fill(sqlDataSet);
slqCon.Close();
DataSet Exemplo
 
DataSet dataset = new DataSet();
dataset.DataSetName = “Autores”;
DataTable autor = new DataTable(“Autor”);
DataTable livro = new DataTable(“Livro”);
• Criação do DataSet
• Definição das tabelas
Criando um DataSet
 
• Definição das colunas 
• Definição das chaves
DataColumn id = autor.Columns.Add("ID", typeof(Int32));
id.AutoIncrement = true;
autor.PrimaryKey = new DataColumn[] {id};
DataColumn nome 
 = new autor.Columns.Add("Nome",typeof(String));
DataColumn isbn = livro.Columns.Add("ISBN", typeof(String));
livro.PrimaryKey = new DataColumn[] {isbn};
DataColumn titulo =livro.Columns.Add("Titulo",typeof(String));
DataColumn idautor=livro.Columns.Add(“idAutor”,typeof(Int32));
DataColumn[] foreignkey = new DataColumn[] {idautor};
Criando um DataSet
 
• Adição das tabelas no DataSet
dataset.Tables.Add (autor);
dataset.Tables.Add (livro);
Criando um DataSet
 
• Adição de dados e gravação do DataSet
DataRow vidal = autor.NewRow();
vidal["Nome"] = “Antonio Geraldo da Rocha Vidal";
autor.Rows.Add(vidal);
DataRelation autorlivro = new DataRelation(“AutorLivro", 
 
autor.PrimaryKey, foreignkey);
dataset.Relations.Add(autorlivro);
DataRow clipper = livro.NewRow();
clipper[“idAutor"] = vidal["ID"];
clipper["ISBN"] = "1000-XYZ";
clipper["Titulo"] = “Clipper 5.0";
livro.Rows.Add(clipper);
dataset.AcceptChanges();
Classes ADO.NET 
Criando um DataSet via Código
 
• DataSet tipados:
– Derivados da classe DataSet
– Usam esquemas XML para gerar novas classes
– Tabelas, colunas, etc. compiladas em novas classes
 DataSet não tipados:
 Não possuem esquema de construção automático
 Tabelas, colunas, etc. são expostas como coleções
DataSet.Customers.FirstName
DataSet.Tables[“Customers”].Rows[0][“FirstName”]
DataSets Tipados
 
• Classe Error
– Contém informações sobre um erro ou advertência 
retornada por uma fonte de dados.
– Criada e administrada pelas classes Errors
• Classe Errors
– Contém todos os erros gerados por um “adapter”
– Criada pela classe Exception
• Classe Exception
– Criada sempre que um erro acontece
– Sempre contém pelo menos uma instância de Erro
Erros e Exceções
 
try {
 DataTable minhaTabela = new DataTable();
 minhaTabela.Columns.Add(“minhaColuna”);
 minhaTable.Columns.Add(“minhaColuna”);
 // Oops!!
}
catch (DataException myException) {
 Console.WriteLine ("Mensagem: " + minhaException.Message + "\n" +
 “Fonte: " + minhaException.Source + "\n" +
 “Rastreamento: " + minhaException.StackTrace + "\n");
} 
Exemplo de Exceções
	Slide 1
	Slide 2
	Slide 3
	Slide 4
	Slide 5
	Slide 6
	Slide 7
	Slide 8
	Slide 9
	Slide 10
	Slide 11
	Slide 12
	Slide 13
	Slide 14
	Slide 15
	Slide 16
	Slide 17
	Slide 18
	Slide 19
	Slide 20
	Slide 21
	Slide 22
	Slide 23
	Slide 24
	Slide 25
	Slide 26
	Slide 27
	Slide 28
	Slide 29
	Slide 30
	Slide 31
	Slide 32
	Slide 33
	Slide 34
	Slide 35
	Slide 36
	Slide 37
	Slide 38
	Slide 39
	Slide 40
	Slide 41
	Slide 42
	Slide 43
	Slide 44
	Slide 45
	Slide 46
	Slide 47
	Slide 48
	Slide 49
	Slide 50
	Slide 51
	Slide 52
	Slide 53
	Slide 54
	Slide 55
	Slide 56
	Slide 57
	Slide 58
	Slide 59
	Slide 60
	Slide 61
	Slide 62