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:

• DeletaEmployee:

CREATE PROCEDURE DeletaEmployee
@EmployeeID int
AS
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
GO

• AtualizaEmployee:

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

• GetTodosEmployees:

CREATE PROCEDURE GetTodosEmployees
AS
SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees
GO

• ContaEmployees

CREATE PROCEDURE ContaEmployees
AS
SELECT COUNT(EmployeeID) FROM Employees
GO

• GetEmployee

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

• O método ContaEmployee()

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.