Students To Business
[Link], SQL & Pattern
Objetos do [Link]
[Link], SQL & Pattern
Modelo de objetos do [Link]
DataSet
.NET Data Provider DataTableCollection
DataTable
TableAdapter
DataRowCollection
SelectCommand
DataColumnCollection
InsertCommand
ConstraintCollection
UpdateCommand
DeleteCommand DataRelationCollection
XML
Banco de
Dados
[Link], SQL & Pattern
Providers
• Conjunto de classes especializadas que
implementam a interface de acesso ao dados;
• Existe por padrão no Framework, providers
específicos para cada tipo de banco de dados;
• SQL Provider: acesso à banco de dados do SQL Server
• Oracle Provider: acesso à banco de dados do Oracle Database
• OleDB Provider: acesso à banco de dados com driver OleDB
• ODBC Provider: acesso à banco de dados com driver ODBC
• Providers específicos oferecem aumento da
performance.
[Link], SQL & Pattern
Modelo [Link]
WindowsForm
DataAdapter DataSet
WebForm
Connection Command
Classes
WebServices
DataReader
Provider
[Link]
Consumidores
[Link], SQL & Pattern
Objeto Connection
• Utilizado para fazer a conexão com o banco de
dados;
• Existe um para cada tipo de provider;
• Permite o pooling de conexões;
• A conexão é feita através de uma string de
conexão.
SqlConnection
SqlConnection cn
cn == new
new SqlConnection();
SqlConnection();
[Link]
[Link] == "Data
"Data Source=localhost;"
Source=localhost;" ++
"Integrated
"Integrated Security=SSPI;"
Security=SSPI;" ++
"Initial
"Initial Catalog=Northwind";
Catalog=Northwind";
[Link], SQL & Pattern
Objeto Command
• Utilizado para a execução de comandos em
Transact-SQL;
• Pode ser criado através de uma conexão:
SqlCommand cmd = [Link]();
• Para definir o comando a ser executado utilize a
propriedade CommandText:
[Link] = "SELECT * FROM Tabela";
[Link], SQL & Pattern
Objeto Command
• Como existem diferentes tipos de comandos,
existem também diferentes tipos de execução:
• ExecuteReader()
• Retorna um objeto DataReader, com o resultado da consulta
• ExecuteNonQuery()
• Não retorna nenhum tipo de dado, apenas o número de
células afetadas
• ExecuteScalar()
• Retorna um único valor (object) escalar
• ExecuteXMLReader
• Retorna um XMLReader
[Link], SQL & Pattern
Command-ExecuteNonQuery
• Exemplo de uso :
SqlConnection
SqlConnection cnn
cnn == new
new SqlConnection(
SqlConnection(
"Data
"Data Source=localhost;"
Source=localhost;" ++
"Initial
"Initial Catalog=Videos;Integrated
Catalog=Videos;Integrated
Security=True");
Security=True");
SqlCommand
SqlCommand cmdcmd == new
new SqlCommand("UPDATE
SqlCommand("UPDATE titulos
titulos SET
SET
Nome
Nome == 'Sem
'Sem Nome'
Nome' WHERE
WHERE Nome
Nome == ''",
''", cnn);
cnn);
[Link]();
[Link]();
int
int qtidade
qtidade =[Link]();
=[Link]();
[Link](qtidade
[Link](qtidade ++ "" linhas
linhas foram
foram
afetadas.");
afetadas.");
[Link]();
[Link]();
[Link], SQL & Pattern
Objeto Command - ExecuteScalar
• Exemplo de uso :
SqlConnection
SqlConnection cnn
cnn == new
new SqlConnection(
SqlConnection(
"Data
"Data Source=localhost;"
Source=localhost;" ++
"Initial
"Initial Catalog=Videos;Integrated
Catalog=Videos;Integrated
Security=True");
Security=True");
SqlCommand
SqlCommand cmd
cmd == new
new SqlCommand("SELECT
SqlCommand("SELECT COUNT(*)
COUNT(*)
FROM
FROM titulos",
titulos", cnn);
cnn);
[Link]();
[Link]();
object
object qtidade
qtidade == [Link]();
[Link]();
[Link]("Existem
[Link]("Existem "" ++ (int)qtidade
(int)qtidade ++ ""
títulos");
títulos");
[Link]();
[Link]();
[Link], SQL & Pattern
Objeto DataReader
• Utilizado para leitura de dados
• Existe um para cada tipo de provider
• Vantagens:
• Velocidade
• Usado para leitura de grandes quantidades de dados
• Desvantagens:
• Não permite a modificação dos dados lidos
• Permite somente leitura seqüencial
[Link], SQL & Pattern
Objeto DataReader
• Inicia sempre na posição BOF (Begin Of File);
• Para ler o conteúdo de um campo específico
utilize:
SqlDataReader dr = [Link]();
string str = dr["NomeDoCampo"];
• Para ir para o próximo registro, utilize:
[Link]();
[Link], SQL & Pattern
Objeto DataReader
• Exemplo de uso (1):
SqlConnection
SqlConnection cn
cn == new
new SqlConnection("Data
SqlConnection("Data Source=localhost;"
Source=localhost;" ++
"Integrated
"Integrated Security=SSPI;"
Security=SSPI;" ++
"Initial
"Initial Catalog=Northwind");
Catalog=Northwind");
SqlCommand
SqlCommand cmd
cmd == new
new SqlCommand("select
SqlCommand("select ** from
from Customers",
Customers", cn);
cn);
[Link]();
[Link]();
SqlDataReader
SqlDataReader dr
dr == [Link]();
[Link]();
while
while ([Link]())
([Link]()) {{
[Link](dr["CompanyName"]);
[Link](dr["CompanyName"]);
}}
[Link]();
[Link]();
[Link]();
[Link]();
[Link], SQL & Pattern
Objeto DataReader
• Exemplo de uso (2):
SqlConnection
SqlConnection cncn == new
new SqlConnection("Data
SqlConnection("Data Source=localhost;“+
Source=localhost;“+
"Integrated
"Integrated Security=SSPI;"
Security=SSPI;" ++
"Initial
"Initial Catalog=Northwind");
Catalog=Northwind");
SqlCommand
SqlCommand cmd
cmd == new
new SqlCommand("select
SqlCommand("select ** from
from Customers",
Customers", cn);
cn);
[Link]();
[Link]();
DataTable
DataTable dt
dt == new
new DataTable();
DataTable();
[Link]([Link]());
[Link]([Link]());
[Link]();
[Link]();
[Link], SQL & Pattern
Objeto DataAdapter
• Responsável pela comunicação entre um objeto
DataSet e o banco de dados;
• Específico para cada tipo de provider;
• Agrega objetos do tipo Command, facilitando a
inserção, atualização e remoção de registros no
banco de dados;
• Possui métodos específicos para popular e
atualizar um DataSet.
[Link], SQL & Pattern
Objeto DataAdapter
• Exemplo de uso :
SqlConnection
SqlConnection cnn
cnn == new
new SqlConnection(
SqlConnection(
"Data
"Data Source=localhost;"
Source=localhost;" ++
"Initial
"Initial Catalog=Videos;Integrated
Catalog=Videos;Integrated
Security=True");
Security=True");
SqlCommand
SqlCommand cmd
cmd == new
new SqlCommand("SELECT
SqlCommand("SELECT ** FROM
FROM
titulos",
titulos", cnn);
cnn);
SqlDataAdapter
SqlDataAdapter da da == new
new SqlDataAdapter(cmd);
SqlDataAdapter(cmd);
DataSet
DataSet ds
ds == new
new DataSet();
DataSet();
[Link](ds);
[Link](ds);
[Link], SQL & Pattern
CommandBuilder
• Permite a criação automática de objetos do tipo
Command, através de um DataAdapter;
• Pode ser utilizado somente quando os dados a
serem modificados não são resultados de
consultas mais complexas, utilizando cláusulas
como por exemplo, Join;
• Ao ser chamado, o CommandBuilder:
• Conecta-se à base de dados e obtém o esquema da tabela;
• Prepara os comandos correspondentes, usando a lista de
campos obtida;
• Instancia objetos Parameter, de acordo com o tipo de cada
campo.
[Link], SQL & Pattern
CommandBuilder
• Exemplo de uso (1):
SqlConnection
SqlConnection cn
cn == new
new SqlConnection("Data
SqlConnection("Data Source=localhost;"
Source=localhost;" ++
"Integrated
"Integrated Security=SSPI;"
Security=SSPI;" ++
"Initial
"Initial Catalog=Northwind");
Catalog=Northwind");
SqlCommand
SqlCommand cmd
cmd == new
new SqlCommand("SELECT
SqlCommand("SELECT ** FROM
FROM Customers",
Customers", cn);
cn);
SqlDataAdapter
SqlDataAdapter dada == new
new SqlDataAdapter(cmd);
SqlDataAdapter(cmd);
SqlCommandBuilder
SqlCommandBuilder cb cb == new
new SqlCommandBuilder(da);
SqlCommandBuilder(da);
[Link]
[Link] == [Link]();
[Link]();
[Link]
[Link] == [Link]();
[Link]();
[Link]
[Link] == [Link]();
[Link]();
[Link]([Link]["Clientes"]);
[Link]([Link]["Clientes"]);
[Link]();
[Link]();
[Link], SQL & Pattern
CommandBuilder
• Exemplo de uso (2):
SqlConnection
SqlConnection cn
cn == new
new SqlConnection("Data
SqlConnection("Data Source=localhost;"
Source=localhost;" ++
"Integrated
"Integrated Security=SSPI;"
Security=SSPI;" ++
"Initial
"Initial Catalog=Northwind");
Catalog=Northwind");
SqlCommand
SqlCommand cmd
cmd == new
new SqlCommand("SELECT
SqlCommand("SELECT ** FROM
FROM Customers",
Customers", cn);
cn);
SqlDataAdapter
SqlDataAdapter dada == new
new SqlDataAdapter(cmd);
SqlDataAdapter(cmd);
SqlCommandBuilder
SqlCommandBuilder cb cb == new
new SqlCommandBuilder(da);
SqlCommandBuilder(da);
[Link]([Link]["Clientes"]);
[Link]([Link]["Clientes"]);
[Link]();
[Link]();
[Link], SQL & Pattern
Objeto DataSet
• Utilizado para acesso em geral a um banco de
dados;
• Independente de provider;
• Trabalha desconectado do banco de dados;
• Os dados são armazenados dentro de uma
coleção de objetos DataTable;
• Mantém os relacionamentos existentes no banco
de dados, entre os DataTables.
[Link], SQL & Pattern
Objeto DataSet
• Preenchimento de um DataSet:
SqlDataAdapter da;
da = new SqlDataAdapter("SELECT * FROM Clientes");
DataSet ds = new DataSet("Clientes");
[Link](ds, "Clientes");
• Atualização do DataSet no Banco de dados:
[Link](ds);
[Link], SQL & Pattern
Objeto DataSet
• Exemplo de uso:
SqlConnection cn = new SqlConnection("Data Source=localhost;" +
"Integrated Security=SSPI;" +
"Initial Catalog=Northwind");
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet("Customers");
[Link](ds, "Customers");
foreach (DataRow dr in [Link]["Customers"].Rows){
[Link]("{0}:{1}", dr[0], dr["ContactName"]);
[Link], SQL & Pattern
Objeto DataTable
• Representa uma tabela do banco de dados;
• Incorporado ao DataSet;
• Podem ter restrições, como chave primárias;
• Podem se relacionar entre si, da mesma maneira
que no banco de dados;
• Possui método para executar filtragens e
ordenações;
• Para isso utiliza comandos SQL, que seriam
utilizados nas cláusulas Select e Order By.
[Link], SQL & Pattern
Objeto DataTable
• Filtragem
DataRow[] rows;
rows = [Link]["Customers"].Select("CustomerID LIKE 'A%'");
• Ordenação
DataRow[] rows;
rows = [Link]["Clientes"].Select("Country = 'Brazil'",
"CustomerName DESC");
[Link], SQL & Pattern
SQL Básico
[Link], SQL & Pattern
Operadores e Cláusulas
= igual
<> diferente
* todos
where onde
[Link], SQL & Pattern
CLIENTE NOTA_FISCAL
PK CLI_COD PK NF_NRO
CLI_NOME NF_DATA
CLI_END NF_CLI
I1 CLI_CNPJ NF_CFOP
FK1 CLI_COD
PRODUTO ITEM_NF
PK PROD_COD PK,FK2 NF_NRO
PK ITEM_COD
PROD_DESC
PROD_IPI ITEM_VALOR
PROD_UNIT ITEM_QTD
PROD_QTD FK1 PROD_COD
[Link], SQL & Pattern
Inserindo Dados
• Inserindo dados na tabela cliente
INSERT INTO Cliente (CLI_NOME,CLI_END, CLI_CNPJ)
VALUES ('Nome do Cliente', 'Rua XXXXXX‘, 99999)
• Inserindo dados na tabela cliente
INSERT INTO Cliente VALUES ('Nome do Cliente', 'Rua
XXXXXX‘, 99999)
[Link], SQL & Pattern
Selecionando Dados
• Todas as colunas da tabela cliente
SELECT * FROM CLIENTE
• Coluna com o nome do cliente e endereço da
tabela cliente.
SELECT CLI_NOME, CLI_END FROM CLIENTE
• Todos as Colunas da tabela cliente onde o nome
comece com Marc
SELECT * FROM Cliente WHERE CLI_NOME LIKE
‘Marc%’
[Link], SQL & Pattern
Selecionando Dados
• Todos as Colunas da tabela cliente onde o nome
contenha “dri”.
SELECT * FROM CLIENTE WHERE CLI_NOME LIKE
'%dri%'
• Todos as Colunas da tabela cliente onde o código
é igual a 2
SELECT * FROM CLIENTE WHERE CLI_COD = 2
[Link], SQL & Pattern
Apagando Dados
• Deleta o cliente com o código 2.
DELETE FROM CLIENTE WHERE CLI_COD = 2
[Link], SQL & Pattern
Atualizando dados
• Atualiza dados do cliente 2.
UPDATE Cliente SET
CLI_NOME =‘Fulano’,
CLI_END = ‘Av YYY’,
CLI_CNPJ = 111111
WHERE COD_CLIENTE = 2
[Link], SQL & Pattern
INNER JOIN
• Junção natural de duas tabelas
SELECT CLIENTE.CLI_NOME,
NOTA_FISCAL.NF_DATA
FROM CLIENTE
INNER JOIN NOTA_FISCAL
ON CLIENTE.CLI_COD = NOTA_FISCAL.CLI_COD
[Link], SQL & Pattern
Stored Procedures
SPs
[Link], SQL & Pattern
Stored Procedures
• Os slides a seguir demonstram como podemos
criar uma SP chamada spTarefasSel que
seleciona registros na tabela:
[Link], SQL & Pattern
Stored Procedures
Verificação e Deleção de uma SP
IF EXISTS (SELECT * FROM sysobjects WHERE type
= 'P' AND name = 'spTarefasSel')
BEGIN
PRINT 'Dropping Procedure spTarefasSel'
DROP PROCEDURE spTarefasSel
END
GO
[Link], SQL & Pattern
Stored Procedures
Criação da SP
CREATE PROCEDURE spTarefasSel (
@idTar INT = null,
@nomeTar VARCHAR(100) = null,
@completaTar BIT = null)
AS
[Link], SQL & Pattern
Stored Procedures
Definição do SQL a ser executado pela SP
DECLARE @sql varchar(8000)
SET @sql = '
SELECT
idTar,
nomeTar,
completaTar
FROM Tarefas
WHERE 1 = 1'
[Link], SQL & Pattern
Stored Procedures
Adição de novos critérios de filtragem
IF (@idTar IS NOT NULL)
SET @sql = @sql + ' AND idTar = ' +
CONVERT(VARCHAR,@idTar)
IF (@nomeTar IS NOT NULL)
SET @sql = @sql + ' AND nomeTar LIKE ''%' +
@nomeTar + '%'''
IF (@completaTar IS NOT NULL)
SET @sql = @sql + ' AND completaTar = ' +
CONVERT(VARCHAR,@completaTar)
[Link], SQL & Pattern
Stored Procedures
• Execução das instruções definidas
EXECUTE(@sql)
GO
[Link], SQL & Pattern
Data Access Layer
DAL
[Link], SQL & Pattern
Connection String
• É uma string estática, de somente leitura,
responsável por informar o nome do servidor,
nome do banco de dados, usuário e senha para
acessar a base de dados utilizada pela aplicação.
[Link], SQL & Pattern
ExecuteNonQuery
• É responsável pela execução de comandos SQL
para manipulação dos dados no banco de dados.
• São comandos como DELETE e UPDATE que não
retornam nenhum resultado de SELECT e não
utilizam nenhum parâmetro de saída.
• Possui duas definições:
• Recebendo um CommandType (Text ou StoredProcedure), uma
string com a instrução SQL (Query ou nome do procedimento) e
um vetor de parâmetros SQL,
• Ou recebendo, além destes parâmetros, uma transação SQL. A
transação é utilizada para garantir a atomicidade de vários
comandos, ou seja, fazer com que um conjunto de comandos
sejam executados ou descartados totalmente.
[Link], SQL & Pattern
ExecuteNonQueryCmd
• A única diferença entre este método e o anterior,
é que este método é utilizado para comandos que
possuem um parâmetro de retorno.
• Ao invés de retornar um número inteiro, ele
retorna um SqlCommand que contém todos os
parâmetros da Stored Procedure, inclusive com
os valores de saída atualizados.
• No método Insert ele é utilizado para retornar
para o objeto o código recém-inserido no banco
de dados, quando existe um atributo auto-
numerado.
[Link], SQL & Pattern
ExecuteReader
• Ele é utilizado para retornar um DataReader para
a classe.
• Uma particularidade deste método é que, devido
ao fato de trabalhar conectado ao banco de
dados, não podemos fechar a conexão.
• Para que a conexão não fique aberta após a
leitura, é utilizada a cláusula
[Link].
[Link], SQL & Pattern
ExecuteReaderDs
• Semelhante ao método ExecuteReader, porém
este retorna os dados para um DataSet, coleção
de dados em memória.
• Devido a isso é utilizado para trabalhar de forma
desconectada do banco de dados.
[Link], SQL & Pattern
ExecuteScalar
• Bastante utilizado para retornar quantidades e
próximos códigos a serem inseridos no banco.
[Link], SQL & Pattern
Usando DAL com SPs
• INSERT:
• [Link](trans,
[Link], “SPINSERT”, parms);
• UPDATE:
• [Link](trans,
[Link], “SPUPDATE”, parms);
• DELETE:
• [Link](trans,
[Link], “SPDELETE”, parms);
OBS.: SPINSERT, SPUPDATE e SPDELETE são Stored Procedures
[Link], SQL & Pattern
Usando DAL com SPs
• Carregando um DataReader:
• SqlDataReader dr =
[Link]([Link],
“SPSELECT”, parms);
• Carregando um DataView:
• DataView dv =
new DataView([Link](trans,
[Link], “SPSELECT”, null).Tables[0]);
OBS.: SPSELECT é uma Stored Procedure
[Link], SQL & Pattern
Pattern de Desenvolvimento
[Link], SQL & Pattern
Mapeamento Relacional / Objeto
• Cada tabela é mapeada para um objeto
• Cada campo de uma tabela é mapeado para um
atributo/propriedade.
• Além dos atributos do banco de dados, existe um
atributo de controle denominado persisted do tipo
bool
private bool persisted;
[Link], SQL & Pattern
Pattern de Desenvolvimento
[Link], SQL & Pattern
Construtores
• Quando um objeto é instanciado a partir de um
construtor vazio (default), esta instância recebe
false para o seu atributo persisted;
Isto permite deduzir que este objeto não foi persistido no banco.
• Quando um objeto é instanciado a partir de um
construtor que recebe sua chave primária, esta
instância recebe true para o seu atributo
persisted e o(s) valor(es) passado(s) para o(s)
atributo(s) referente(s);
Isto permite deduzir que este objeto já foi persistido no banco.
[Link], SQL & Pattern
Métodos
• Insert Privado
• Persiste os atributos da instância do objeto para seu respectivo
mapeamento no banco de dados.
• Update Privado
• Altera os valores persistidos no banco de dados de acordo com
os atributos da instância do objeto.
• Save
• Encapsula os métodos Insert e Update, sendo assim o método
se encarrega em inserir ou atualizar os dados no banco de
dados com base na informação do atributo persisted
• Delete Método Estático
• Apaga um registro do banco de dados de acordo com a chave
primária passada para o método.
[Link], SQL & Pattern
Métodos
• LoadDataReader Método Estático
• Retorna um DataReader contendo um conjunto de registros da
tabela mapeada pela classe
• LoadDataView Método Estático
• Retorna um DataView, uma visão para uma DataTable,
contendo um conjunto de registros da tabela mapeada pela
classe
[Link], SQL & Pattern
• LoadObject Método Estático
• Retorna uma nova instância do objeto já preenchida com os
valores persistidos no banco de dados, bastando para isso
passar como parâmetro a chave do registro.
• CompleteObject
• Completa uma instância existente, preenchendo todos os
atributos de acordo com o banco de dados.
[Link], SQL & Pattern
Exercício
[Link], SQL & Pattern
[Link], SQL & Pattern