ASP .NET - Criando uma componente de acesso a dados (VB.NET)

ASP .NET - Criando uma componente de acesso a dados (VB.NET)

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 mas 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 pertinente. É 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 e 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, o cliente 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 de projeto de separar o código em uma classe distinta que pode ser usada em várias páginas. Esta classe pode então 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 a 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 incluído 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, a fim 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 deveremos 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 no banco de dados. 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 quando 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 raros. Ex: 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 a 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 embora não esteja em conflito. Outro problema mais significativo com a atualização essa estratégia é que ela leva a grandes declarações SQL ineficientes. Você pode implementar a mesmo estratégia de forma mais eficaz usando timestamps.

Exemplo de 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 ele 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 assim 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 (caso em que devem ser incorporadas 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. atualização Match-all, atualização baseada no timestamp e a atualização baseada na mudança de valores dos campos são exemplos de concorrência otimista.

Na concorrência otimista 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 de acordo se elas ocorrerem.

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 agora criar a aplicação ASP .NET Web Forms que irá usar o componente.

Aguarde a segunda parte deste artigo: ASP .NET - Usando um componente de acesso a dados (VB.NET)

Joã 14:6 Respondeu-lhe Jesus: Eu sou o caminho, e a verdade, e a vida; ninguém vem ao Pai, senão por mim.

Joã 12:46 Eu, que sou a luz, vim ao mundo, para que todo aquele que crê em mim não permaneça nas trevas.

Joã 10:9 Eu sou a porta; se alguém entrar a casa; o filho fica entrará e sairá, e achará pastagens.

Joã 6:35 Declarou-lhes Jesus. Eu sou o pão da vida; aquele que vem a mim, de modo algum terá fome, e quem crê em mim jamais terá sede.

Joã_15:1 Eu sou a videira verdadeira, e meu Pai é o viticultor.

Joã 10:11 Eu sou o bom pastor; o bom pastor dá a sua vida pelas ovelhas.

Joã_13:13 Vós me chamais Mestre e Senhor; e dizeis bem, porque eu o sou.

Joã 10:14 Eu sou o bom pastor; conheço as minhas ovelhas, e elas me conhecem,
Joã 10:15 assim como o Pai me conhece e eu conheço o Pai; e dou a minha vida pelas ovelhas.

Joã 11:25 Declarou-lhe Jesus: Eu sou a ressurreição e a vida; quem crê em mim, ainda que morra, viverá;

Referências:

José Carlos Macoratti