Programação Orientada à Objetos Aula 15 -ADO.NET Conteúdo Visão Geral do ADO.NET O Namespace System.Data Arquitetura do ADO.NET Tipos de Acesso a Bancos de Dados no ADO.NET Conexões e Transações Execução de Comandos SQL e Stored Procedures Acesso a dados com DataReader Acesso a dados com DataAdapter Visão Geral do ADO.NET ADO.NET é a família de classes do framework .NET projetada para o acesso a dados É uma evolução da biblioteca ADO (ActiveX Data Objects) componentes COM de acesso a dados da plataforma Windows Provê o acesso orientado a conexões: Execução de comandos SQL e recuperação de dados com a classe DataReader Provê o acesso desconectado: Realização de operações desconectadas, mapeando um banco de dados relacional em um objeto da classe DataSet Visão Geral do ADO.NET Disponibiliza as classes necessárias para acesso e manipulação em BD e arquivos XML. Uma das vantagens do ADO .NET é a possibilidade de trabalharmos de forma desconectada do BD. As classes estão no namespace System.Data. Ainda será necessário um dos seguintes: System.Data.SqlClient System.Data.OleDb System.Data.OracleClient Visão Geral do ADO.NET O SqlClient é usado para acessar BDs SQL Server a partir da versão 7.0. O OleDb para Access e SQL Server anterior a versão 7.0. O OracleClient para BDs Oracle. Podem ser acessador outros BDs a partir de outros namespaces, mas estes devem ser instalados separadamente. Acesse os sites dos fabricantes para obter o namespace. Visão Geral do ADO.NET A Oracle disponibiliza classes para acesso ao BD que tem performance superior as da Microsoft. Provedores de Dados Quatro provedores são disponíveis para realizar o acesso a dados no ADO.NET SQLServer, Oracle, OLEDB e ODBC Provider ADO.NET ADO.NET SQLServer Oracle ODBC OLEDB SQL-data Non-SQL-data MS SQL Server, Oracle, Jet, Foxpro, ... Directory Services, Mail, Text, Video, ... Namespaces do ADO.NET System.Data System.Data.Common Classes compartilhadas pelos provedores de dados: DataAdapter, DBCommand, DBConnection, DBTransaction System.Data.SqlClient, System.Data.Oracle, System.Data.ODBC, System.Data.OleDB Classes genéricas de acesso a dados: DataSet, DataTable, DataRow, DataColumn, DataRelation, Constraint Namespaces dos provedores de dados System.Data.SqlTypes Tipos de dados do Sql Server Arquitetura do ADO.NET ADO.NET Content Components DataSet Tables DataTable Fill ADO.NET Managed Providers DataReader Transaction Data Adapter Command Relations DataRelation DataTable Connection Database ReadXml WriteXml Connection-oriented data flow XML file Connectionless data flow Update Tipos de Acesso Acesso orientado a conexão Mantém a conexão ativa com o banco de dados Os dados permanecem atualizados (up-to-date) Ideal para aplicações com pequenas transações e poucas operações concorrentes Acesso desconectado A conexão não fica ativa, é aberta e fechada Dados copiados para memória Alterações nos dados podem entrar em conflito na atualização e requerem tratamento adicional Ideal para aplicações com transações demoradas e com muitas operações em paralelo (web) Banco de Dados de Exemplo DER e Diagrama de Classes do banco de dados de exemplo create table [Veiculo]( [ID] [int] NOT NULL, [Modelo] [varchar](50) NULL, [Ano] [int] NULL, [Preco] [float] NULL) Banco de Dados Exemplo Insert insert into Veiculo values(@ID, @Modelo, @Ano, @Preco) Delete create procedure spVeiculoInsert (@ID int, @Modelo varchar(50), @Ano int, @Preco float) as create procedure spVeiculoDelete (@ID int) as delete from Veiculo where ID = @ID Update create procedure spVeiculoUpdate (@ID int, @Modelo varchar(50), @Ano int, @Preco float) as update Veiculo set Modelo = @Modelo, Ano = @Ano, Preco = @Preco where ID = @ID Acesso Orientado a Conexão DbConnection Representa um comando SQL DbTransaction Representa a conexão com o BD DbCommand .NET Application ADO.NET Connected Model DataReader DbCommand Command Representa uma transação DataReader Resultado de um comando SQL Permite o acesso seqüencial às linhas de uma tabela DbConnection Database DbTransaction Conexões com o Banco de Dados Descendentes de DbConnection são utilizados para realizar a conexão com o banco de dados OdbcConnection, OleDbConnection, OracleConnection, SqlConnection É necessário definir strings com os parâmetros de conexão de acordo com o banco de dados string connectionStr = "Data Source=Server;Initial Catalog=Database;Integrated Security=True"; string connectionStr = "Data Source=Server;Initial Catalog=Database;User ID=sa;Password=senha"; Conectando ao Servidor Conexão com o SQL Server utilizando autenticação do próprio banco de dados string connectionStr = "Data Source=Server;Initial Catalog=Database;User ID=sa;Password=senha"; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); ... Execução de comandos ... conn.Close(); Desconectando com TryFinnaly É necessário garantir o fechamento da conexão para liberar os recursos do banco de dados try { conn.Open(); ... } catch (Exception error) { ... } finally { conn.Close(); } Transações Transações garantem que um conjunto de alterações em um banco de dados seja realizado de forma atômica Descendentes de DbTransaction são usados para controlar as transações em um banco de dados O método Commit confirma a transação O método Rollback aborta a transação Exemplo conn.Open(); SqlTransaction tx = conn.BeginTransaction(); tx.Commit(); conn.Close(); Níveis de Isolamento ReadCommited ReadUncommited Dados não “commitados” são vistos por outras transações RepeatableRead Dados escritos em uma transação somente são vistos por outras transações após o commit Dados lidos em uma transação não podem ser alterados por outras transações Serializable Serializa o acesso aos dados no banco de dados Comandos Descendentes de DbCommand são responsáveis pela execução de comandos em um banco de dados OdbcCommand, OleDbCommand, OracleCommand, SqlCommand A propriedade CommandType do comando define o tipo de operação realizada Text – Permite definir um comando em linguagem SQL StoredProcedure – Permite executar um procedimento armazenado no banco TableDirect – Permite acessar diretamente uma tabela Exemplos de Comandos Text string select = "select * from Veiculo"; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); StoredProcedure SqlCommand cmd = new SqlCommand("spVeiculoInsert", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", obj.Id); TableDirect SqlCommand cmd = new SqlCommand("Veiculo", conn); cmd.CommandType = CommandType.TableDirect; Métodos de Execução Métodos utilizados na execução dos comandos ExecuteNonQuery Utilizado em comandos que não retornam um resultado: delete, insert, update Retorna um inteiro com o número de linhas afetadas ExecuteReader Utilizado em comandos select que retornam um conjunto de dados Retorna um objeto DataReader, utilizado para o acesso seqüencial aos dados ExecuteScalar Utilizado em comandos select que retornam um único valor Executando um StoredProcedure Inserção com execução de procedimento armazenado SqlConnection conn = new SqlConnection(connectionStr); SqlCommand cmd = new SqlCommand("spVeiculoInsert", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", obj.Id); cmd.Parameters.AddWithValue("@Modelo", obj.Modelo); cmd.Parameters.AddWithValue("@Ano", obj.Ano); cmd.Parameters.AddWithValue("@Preco", obj.Preço); conn.Open(); try { cmd.ExecuteNonQuery(); } finally { conn.Close(); } Lendo com DataReader Seleção com comando SQL retornando um DataReader Types.Veiculos objs = new Types.Veiculos(); SqlConnection conn = new SqlConnection(connectionStr); SqlCommand cmd = new SqlCommand("select * from Veiculo", conn); conn.Open(); SqlDataReader datareader = cmd.ExecuteReader(...); try { while (datareader.Read()) { Types.Veiculo obj = new Types.Veiculo(); obj.Id = Convert.ToInt32(datareader[0]); ... objs.Add(obj); } } finally { datareader.Close(); } Acesso Desconectado DbConnection .Net Application DbCommand Representa a conexão com o BD Representa um comando SQL DataAdapter Fill – Preenche o DataSet a partir do BD Update – Atualiza o BD a partir do DataSet Connection-less Model DataSet DataAdapter DbCommand DbConnection DataSet Armazena os dados do banco na memória Database Preenchendo o DataSet Usando o DataAdapter para preencher o DataSet SqlConnection conn = new SqlConnection(connectionStr); SqlCommand cmd = new SqlCommand("select * from Veiculo", conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "Veiculo"); Mostrando o DataSet em um DataGridView dataGridView1.DataSource = ds; dataGridView1.DataMember = "Veiculo"; conn.Close(); Atualizando o Banco de Dados Preparando o DataAdapter para atualizar o BD Recuperando o DataSet da interface SqlConnection conn = new SqlConnection(connectionStr); SqlCommand cmd = new SqlCommand("select * from Veiculo", conn); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(da); DataSet ds = (dataGridView1.DataSource as DataSet); Atualizando o Banco de Dados da.Update(ds, "Veiculo"); conn.Close();