A grande maioria das aplicações profissionais acessam um banco de
dados relacional e isso acrescenta uma certa complexidade à etapa de
desenvolvimento de software que pode determinar o sucesso ou o fracasso
de uma aplicação comercial.
Nas aplicações profissionais, o código de acesso ao banco de dados
nunca deve estar embutido diretamente na camada de apresentação; em
geral deve estar encapsulado em uma classe dedicada a essa tarefa.
Para realizar uma operação com o banco de dados nessa abordagem, o
cliente cria uma instância dessa classe e chama o método apropriado. É
assim que deve ser feito (ou pelo menos deveria…).
Neste artigo, eu mostro como criar um componente de acesso a dados
que segue esse modelo e que deve seguir alguns princípios básicos para
que ele seja encapsulado, otimizado eque seja executado em um processo
separado. Esses princípios básicos são listados a seguir:
- Abrir e fechar rapidamente as conexões: Abra a conexão de banco de
dados em cada chamada de método, e feche a conexão antes que o método
termine. As conexões nunca devem ser mantidas abertas entre as
requisições do cliente e ele não deve ter nenhum controle sobre como as
conexões são aberta ou liberadas. Se o cliente tiver essa habilidade,
ela introduz a possibilidade de que um conexão não seja fechada tão
rapidamente quanto possível, ou pode ser inadvertidamente deixada
aberta, o que dificulta a escalabilidade;
- Implementar o tratamento de erros: Utilize o tratamento de erros
para garantir que as conexões sejam fechadas mesmo que o comando SQL
gere uma exceção. Lembre-se, as conexões são um recurso finito, e
usá-los até mesmo por alguns segundos extras pode ter um efeito
importante sobre o desempenho global da sua aplicação;
- Não permitir consultas SQL que retornem grande volume de dados: Cada
consulta deve, criteriosamente, selecionar apenas as colunas de que
precisa. Além disso, você deve restringir os resultados com uma cláusula
WHERE sempre que possível. Por exemplo, ao recuperar registros de
pedidos, você pode impor um intervalo de datas mínimo (ou uma cláusula
SQL como TOP 1000). Sem estas garantias, o aplicativo pode funcionar bem
no começo, mas vai ter seu desempenho afetado à medida que o banco de
dados cresce;
- Seguir as práticas de projeto que não tratam estado: Aceite todas as
informações necessárias para um método em seus parâmetros e retorne
todos os dados recuperados através do valor de retorno. Se você criar
uma classe que mantém o estado, ela não poderá ser facilmente
implementada como um serviço web ou usada em um cenário de balanceamento
de carga. Além disso, se o componente da base de dados está alojado
fora do processo, cada chamada de método tem uma sobrecarga, e usando
várias chamadas para definir as propriedades levará muito mais tempo do
que invocar um único método com todas as informações como parâmetros.
Um projeto bom e simples para um componente de acesso a dados deve
usar uma classe separada para cada tabela de banco de dados (ou grupo
logicamente relacionado de tabelas). Os métodos comuns de acesso ao
banco de dados, tais como inserir, excluir e modificar um registro são
todos encapsulados em diferentes métodos sem estado. Finalmente, cada
chamada de banco de dados utiliza um procedimento armazenado específico.
Abaixo temos uma figura que mostra um esquema desse tipo de projeto em
camadas:
O exemplo que vamos mostrar neste artigo demonstra um componente de
banco de dados simples. Em vez de colocar o código de banco de dados na
página web, seguimos uma prática muito melhor, que consiste em separar o
código em uma classe distinta, que pode ser usada em várias páginas.
Esta classe pode ser compilada como parte de um componente separado, se
necessário. Além disso, a string de conexão é recuperada a partir da
seção <connectionStrings> do arquivo web.config, ao invés de ser
colocada diretamente no código.
O nosso componente de acesso aos dados será constituído de duas
classes: uma classe de pacote de dados, que envolve um único registro de
informações (conhecida como a classe de dados) e uma classe de
utilitário de banco de dados que realiza as operações de banco de dados
via código ADO.NET (conhecida como a classe de acesso aos dados).
Por questão de simplicidade, vamos usar um banco de dados conhecido, o
Northwind.mdf, e criar um componente para acessar esse banco de dados.
Com base nisso, vamos criar uma classe que acessa a tabela Employees e
que representa os funcionários da empresa Northwind.
Apenas para lembrar, segue abaixo a estrutura dessa tabela Employees:
Abra o Visual Web Developer 2010 Express Edition e crie um novo
projeto do tipo Class Library com o nome Employee.vb incluindo o código
abaixo neste arquivo:
Public Class Employee
Private m_employeeID As Integer
Private m_firstName As String
Private m_lastName As String
Private m_titleOfCourtesy As String
Public Property EmployeeID() As Integer
Get
Return m_employeeID
End Get
Set(ByVal value As Integer)
m_employeeID = value
End Set
End Property
Public Property FirstName() As String
Get
Return m_firstName
End Get
Set(ByVal value As String)
m_firstName = value
End Set
End Property
Public Property LastName() As String
Get
Return m_lastName
End Get
Set(ByVal value As String)
m_lastName = value
End Set
End Property
Public Property TitleOfCourtesy() As String
Get
Return m_titleOfCourtesy
End Get
Set(ByVal value As String)
m_titleOfCourtesy = value
End Set
End Property
Public Sub New(ByVal employeeID As Integer, ByVal firstName As String, ByVal lastName As String, ByVal titleOfCourtesy As String
)
Me.m_employeeID = employeeID
Me.m_firstName = firstName
Me.m_lastName = lastName
Me.m_titleOfCourtesy = titleOfCourtesy
End Sub
End Class
Observe que esta classe não inclui todas as informações que estão na
tabela Employees, afim de tornar o exemplo mais conciso, definimos
apenas algumas propriedades. Ao construir uma classe de dados, você pode
optar por utilizar o novo recurso das propriedades automáticas e assim
simplificar o código. Abaixo temos uma declaração usando este recurso
para a propriedade EmployeeID:
Public Property EmployeeID As Integer
Ao utilizar propriedades automáticas, a variável privada é gerada
automaticamente em tempo de compilação, assim você não vai saber o seu
nome. Em seu código, você deve sempre acessar a variável privada através
da procedimentos de propriedade. O compilador VB também adiciona o
código que obtém e define a variável privada. Nosso exemplo usa a
sintaxe antiga.
Definindo as Stored Procedures
Vamos agora criar os procedimentos armazenados que iremos usar em
nosso componente. Para isso, vamos usar o SQL Server Management Studio
2010 Express Edition (SSMS).
Obs: O meu SSMS esta localizado para português e por isso as mensagens estarão todas em português.
Após abrir o SSMS e efetuar o login, vamos selecionar o banco de
dados Northwind.mdf e o item Programação, clicando sobre ele com o botão
direito do mouse e selecionando a opção – Novo Procedimento Armazenado.
A seguir digite o código mostrada na figura abaixo para criar a stored
Procedure InserirEmployee que inclui um novo funcionário na tabela
Employees:
Para retornar o valor de um campo identity de uma nova linha incluída
no SQL Server, usamos uma stored procedure com um parâmetro de saída
(OUTPUT Parameter). Para realizar estas tarefas podemos usar três
funções Transact-SQL no SQL Server:
Função Descrição:
- SCOPE_IDENTITY – Retorna o último valor identity no escopo da execução atual (é o recomendado);
- @@IDENTITY – Contém o último valor identity gerado em qualquer
tabela na sessão atual. Pode ser afetado por Triggers e pode não conter o
valor identity que você espera;
- IDENTI_CURRENT – Retorna o último valor identity gerado para uma tabela específica em qualquer sessão e qualquer escopo.
Vamos repetir o procedimento acima e criar as seguintes stored procedures que serão usadas em nosso componente:
CREATE PROCEDURE DeletaEmployee
@EmployeeID int
AS
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
GO
CREATE PROCEDURE AtualizaEmployee
@EmployeeID int,
@TitleOfCourtesy varchar(25),
@LastName varchar(20),
@FirstName varchar(10)
AS
UPDATE Employees
SET TitleOfCourtesy = @TitleOfCourtesy,
LastName = @LastName,
FirstName = @FirstName
WHERE EmployeeID = @EmployeeID
GO
CREATE PROCEDURE GetTodosEmployees
AS
SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees
GO
CREATE PROCEDURE ContaEmployees
AS
SELECT COUNT(EmployeeID) FROM Employees
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int
AS
SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees
WHERE EmployeeID = @EmployeeID
GO
Ao final, devemos visualizar as stored procedures criadas no item Programação, conforme abaixo:
Definindo a classe de acesso aos dados
Agora que já temos as stored procedures no banco de dados, vamos
criar a classe utilitária que realizará as operações. Essa classe usa os
procedimentos armazenados que criamos anteriormente. Neste exemplo, a
classe de utilitário de dados terá o nome EmployeeDB. Ela encapsula todo
o código de acesso a dados ao banco de dados.
A seguir, temos um esboço da estrutura da nossa classe EmployeeDB:
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data
Public Class EmployeeDB
Private connectionString As String
Public Sub New()
'Obtém uma string de conexão do web.config.
connectionString = ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
End Sub
Public Sub New(ByVal connectionString As String)
' Define uma string de conexão
Me.connectionString = connectionString
.....
End Sub
Public Function InserirEmployee(ByVal emp As Employee) As Integer
End Function
Public Sub DeletaEmployee(ByVal employeeID As Integer)
End Sub
Public Sub AtualizaEmployee(ByVal emp As Employee)
End Sub
Public Function GetEmployee(ByVal employeeID As Integer) As Employee
End Function
Public Function GetEmployees() As List(Of Employee)
End Function
Public Function ContaEmployees() As Integer
End Function
End Class
Observe que a classe EmployeeDB usa métodos de instância, e não
métodos estáticos ou Shared. Isso ocorre porque, embora a classe
EmployeeDB não armazene qualquer estado do banco de dados, ela armazena a
string de conexão como uma variável de membro privado. Como essa classe
é uma classe de instância, a string de conexão pode ser recuperada toda
vez que a classe é criada, ao invés de cada vez que um método for
invocado. Essa abordagem torna o código mais claro e permite que ele
seja um pouco mais rápido (evitando a necessidade de ler o arquivo
web.config muitas vezes). No entanto, o benefício é pequeno, assim
poderíamos usar métodos estáticos na nossa classe sem grandes impactos.
Temos que incluir uma referência no projeto no menu Project -> Add
Reference e na guia .NET selecionar a opção System.Configuration. Dessa
forma, podemos usar a declaração Imports System.Configuration no
arquivo – bem como as declarações para acessar as classes do provider
SqlClient.
Vamos começar definindo o método InserirEmployee() com o código abaixo:
Public Function InserirEmployee(ByVal emp As Employee) As Integer
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("InserirEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 10))
cmd.Parameters("@FirstName").Value = emp.FirstName
cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 20))
cmd.Parameters("@LastName").Value = emp.LastName
cmd.Parameters.Add(New SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25))
cmd.Parameters("@TitleOfCourtesy").Value = emp.TitleOfCourtesy
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int))
cmd.Parameters("@EmployeeID").Direction = ParameterDirection.Output
Try
con.Open()
cmd.ExecuteNonQuery()
Return CInt(cmd.Parameters("@EmployeeID").Value)
Catch err As SqlException
' Substitui o erro com algo menos especifico
Throw New ApplicationException("Erro ao acessar os dados..")
Finally
con.Close()
End Try
End Function
O método aceita dados como um objeto de dados Employee. Quaisquer
erros são capturados, e os detalhes internos não são retornados ao
código de página da web. Isso impede que a página web forneça
informações que poderiam levar a possíveis explorações mal
intencionadas. Este seria também um local ideal para chamar um outro
método em um componente de log para relatar as informações completas em
um log de eventos ou em outro banco de dados.
Os métodos GetEmployee() e GetEmployees() retornam os dados
utilizando um único objeto Employee objeto ou uma lista de objetos
Employee; a seguir temos a implementação destes métodos:
- GetEmployee() – retorna um objeto Employee
Public Function GetEmployee(ByVal employeeID As Integer) As Employee
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("GetEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int))
cmd.Parameters("@EmployeeID").Value = employeeID
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
' Obtem a primeira linha
reader.Read()
Dim emp As New Employee(
CInt(reader("EmployeeID")),
DirectCast(reader("FirstName"), String),
DirectCast(reader("LastName"), String),
DirectCast(reader("TitleOfCourtesy"), String)
)
reader.Close()
Return emp
Catch err As SqlException
' Retorna um erro menos especifico
Throw New ApplicationException("Erro ao acessar os dados.")
Finally
con.Close()
End Try
End Function
- GetEmployees() – retorna uma lista de objetos Employee
Public Function GetEmployees() As List(Of Employee)
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("GetTodosEmployees", con)
cmd.CommandType = CommandType.StoredProcedure
' Cria uma coleção para todos os registros employee
Dim employees As New List(Of Employee)()
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Dim emp As New Employee(
CInt(reader("EmployeeID")),
DirectCast(reader("FirstName"), String),
DirectCast(reader("LastName"), String),
DirectCast(reader("TitleOfCourtesy"), String)
)
employees.Add(emp)
End While
reader.Close()
Return employees
Catch err As SqlException
' Retorna um erro menos especifico
Throw New ApplicationException("Data error.")
Finally
con.Close()
End Try
End Function
O método AtualizaEmployee() é importante pois vai determinar a estratégia do tratamento da concorrência em nossa aplicação.
Em qualquer aplicativo multiusuário, incluindo aplicações web, existe
a possibilidade de que mais de um usuário realize a sobreposição de
consultas e atualizações. Isso pode levar a uma situação potencialmente
confusa, onde dois usuários, que estão da posse do estado atual de uma
linha, tentem realizar atualizações divergentes.
A atualização do primeiro usuário sempre terá êxito. O sucesso ou o
fracasso da segunda atualização é determinada pela estratégia de
concorrência. Existem várias abordagens para a gestão da concorrência. A
coisa mais importante a entender é que você determina a sua estratégia
de concorrência pela forma como você define a atualização dos seus
dados, via comandos SQL UPDATE e DELETE (particularmente a forma como
você define a cláusula WHERE).
Vejamos os métodos mais usados:
1. Atualização Last-in-wins
Esta é uma forma menos restritiva de controle de concorrência que
sempre confirma a atualização (a menos que a linha original tenha sido
deletada). Nessa abordagem, toda vez que uma atualização é confirmada,
todos os valores são aplicados. Esse modelo faz sentido se colisões de
dados são raras. Exemplo: você pode usar com segurança esta abordagem se
houver apenas uma pessoa responsável pela atualização de um determinado
grupo de registros. Em geral, você implementa o modelo Last-in-wins
escrevendo uma cláusula WHERE que corresponde ao registro a ser
atualizado com base na sua chave primária.
O método AtualizaEmployee() utiliza a abordagem Last-in-wins e define
a atualização da seguinte forma: UPDATE Employees SET … WHERE
EmployeeID=@EmployeeID
2. Atualização Match-all
Para implementar esta estratégia, o seu comando UPDATE precisa usar
todos os valores que você deseja definir – além de todos os valores
originais. Você usa todos os valores originais para construir uma
cláusula WHERE que encontre o registro original. Dessa forma, se até
mesmo um único campo tiver sido modificado, o registro não vai
corresponder e a mudança não será bem sucedida. Um problema com este
abordagem é que as mudanças compatíveis não são permitidas. Por exemplo,
se dois usuários estão tentando modificar diferentes partes do mesmo
registro, a mudança do segundo usuário será rejeitada, mesmo que não
esteja em conflito. Outro problema mais significativo com a atualização
dessa estratégia é que ela leva a grandes declarações SQL ineficientes.
Você pode implementar a mesma estratégia de forma mais eficaz usando
timestamps.
Exemplode de declaração SQL com essa estratégia: UPDATE Employees SET
… WHERE EmployeeID=@EmployeeID AND FirstName=@OriginalFirstName AND
LastName=@OriginalLastName …
3. Atualização baseada em timestamp
A maioria dos sistemas de banco de dados suporta uma coluna
timestamp, que a fonte de dados atualiza automaticamente a cada vez que
uma mudança é realizada. Você não modifica a coluna timestamp
manualmente. No entanto, se você retornar o seu valor quando você
executar sua instrução SELECT, você pode usá-la na cláusula WHERE para a
instrução UPDATE. Dessa forma, você está garantindo a atualização do
registro somente se ele não foi modificado, assim como na abordagem
match-all. Ao contrário de atualização match-all, a cláusula WHERE é
menor e mais eficiente, porque ela só precisa de dois pedaços de
informação: a chave primária e o timestamp.
Exemplo de instrução SQL com essa abordagem: UPDATE Employees SET … WHERE EmployeeID=@EmployeeID AND TimeStamp=@TimeStamp
4. Atualização baseada na alteração de valores dos campos
Esta abordagem tenta aplicar apenas os valores alterados em um
comando UPDATE, permitindo que dois usuários façam alterações ao mesmo
tempo, se estas mudanças são para campos diferentes. O problema com esta
abordagem é que ela pode se tornar muito complexa, porque você precisa
se manter a par dos valores que mudaram (casos em que devem ser
incorporados na cláusula WHERE) e dos valores que não sofreram
alteração.
A abordagem last-in-wins é um exemplo de acesso de dados sem controle
de concorrência. A atualização Match-all, baseada no timestamp e a
atualização baseada na mudança de valores dos campos são exemplos de
concorrência otimista. Nela, o código não mantém bloqueios sobre os
dados que ele está usando. Em vez disso, sua estratégia é esperar que as
mudanças não se sobreponham, respondendo casoe elas ocorreram.
A concorrência pessimista, utilizada em transações, evita conflitos
de simultaneidade travando os registros em uso. A desvantagem é a
escalabilidade, pois outros usuários que tentam acessar os mesmos dados
deverão aguardar até a liberação do bloqueio.
Abaixo temos o método AtualizaEmployee:
Public Sub AtualizaEmployee(ByVal EmployeeID As Integer, ByVal firstName As String, ByVal lastName As String, ByVal titleOfCourtesy As String)
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("AtualizaEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 10))
cmd.Parameters("@FirstName").Value = firstName
cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 20))
cmd.Parameters("@LastName").Value = lastName
cmd.Parameters.Add(New SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25))
cmd.Parameters("@TitleOfCourtesy").Value = titleOfCourtesy
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int))
cmd.Parameters("@EmployeeID").Value = EmployeeID
Try
con.Open()
cmd.ExecuteNonQuery()
Catch err As SqlException
' Substitui o erro com algo menos especifico
Throw New ApplicationException("Erro ao acessar os dados.")
Finally
con.Close()
End Try
End Sub
- O método DeletaEmployee()
Public Sub DeletaEmployee(ByVal employeeID As Integer)
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("DeletaEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int))
cmd.Parameters("@EmployeeID").Value = employeeID
Try
con.Open()
cmd.ExecuteNonQuery()
Catch err As SqlException
' Retorna um erro menos especifico
Throw New ApplicationException("Erro ao acessar os dados.")
Finally
con.Close()
End Try
End Sub
Public Function ContaEmployees() As Integer
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("ContaEmployees", con)
cmd.CommandType = CommandType.StoredProcedure
Try
con.Open()
Return CInt(cmd.ExecuteScalar())
Catch err As SqlException
' Retorna um erro menos especifico
Throw New ApplicationException("Erro ao acessar os dados.")
Finally
con.Close()
End Try
End Function
Assim acabamos de criar todos os métodos do nosso componente de
acesso a dados para gerenciar os dados da tabela Employees. Vamos, no
próximo artigo, criar a aplicação ASP .NET Web Forms que irá usar o
componente.
25
