ASP .NET - LINQ To SQL e Stored Procedures I

Neste artigo irei mostrar como realizar o acesso a dados em camadas com Stored Procedures e o objeto ObjectDataSource do ASP .NET, e, em seguida irei mostrar como o LINQ To SQL pode ser usado para diminuir o nosso trabalho.

Você vai precisar dos seguintes recursos para acompanhar este artigo:

Você já parou para pensar que vamos usar duas ferramentas Micro$oft totalmente funcionais e até agora você não colocou a mão no bolso. (Como as coisas mudam...)

Criando o banco de dados , tabelas e Stored Procedures

Para criar o banco de dados, a tabela e sua estrutura e as stored procedures irei usar o SQL Server Management Studio Express (SSMS). Veja como obter, instalar e usar o SSMS em meu artigo: .NET  - Usando o SQL Server Management Studio

Após abrir o SSMS e efetuar a conexão com o seu Servidor SQL Server 2005 clique com o botão direito do mouse sobre o objeto DataBase e selecione a opção New DataBase... e na janela New DataBase informe o nome do banco de dados Clientes e clique no botão Add.

Com o banco de dados Escola.mdf criado expanda os objetos deste banco de dados e selecione o objeto Table e clicando com o botão direito do mouse. Será aberta a interface para definir os nomes das colunas, os tipos de dados e indicar se o campo permite null ou não.

A tabela Alunos irá possuir os seguintes campos : alunoID, aLunoNome, alunoEmail e ativo . Esta estrutura simplificada visa tornar o artigo mais compreensível e fácil para os iniciantes e para os iniciados.  Veja abaixo o tipo de dados e o tamanho correspondente bem como a a definição de chave primária para o campo AlunoID e sua definição como um campo do tipo Identity o que faz com que ele seja incrementado de forma automática pelo SGBD.

Nota: Para ver o script gerado pelo SQL Server que criou a tabela clique com o botão direito sobre a tabela selecione Script Table as -> CREATE to, conforme figura abaixo, e script é exibido a direita:

O próximo passo será criar as stored procedures no banco de dados mas antes precisamos definir cada uma delas de acordo com a funcionalidade que iremos implementar.

As stores procedures irão realizar as tarefas de selecionar, incluir, alterar e editar um registro, logo , teremos quatro stored procedures:

  1. sp_SelectAllEmails - Selecione todos os registros da tabela Emails;
  2. sp_InsertEmails - Inclui registros na tabela Emails;
  3. sp_UpdateEmails - atualiza registros da tabela Emails;
  4. sp_DeleteEmails - exclui registros da tabela Emails;

Após definirmos cada uma das stored procedures para criar cada uma delas basta expandir o nó para o item Programmability e clicar com o botão direito do mouse sobre o item Stored Procedures e selecionar a opção New Stored Procedure. 

1- Procedimento armazenado para Incluir um novo Aluno :

CREATE PROCEDURE sp_InsertEmails
        @AlunoNome varchar(50),
        @AlunoEmail varchar(50),
        @Ativo bit, 
        @AlunoId int OUTPUT
AS
INSERT INTO Emails
                    (alunoNome,alunoEmail,ativo)
                    VALUES
                    (@alunoNome,@aluntoEmail,@ativo)
 		 A estrutura da Stored Procedure(SP) é a seguinte :

1- Cria a SP chamada procInsertAluno usando o comando CREATE PROCEDURE

2- Cria o parâmetro de saída (OUTPUT) AlunoId

3- Cria os parâmetros de entrada: Nome, Endereco e Email

4- Define a instrução SQL que será executada. No caso a inclusão de dados na tabela Alunos via INSERT INTO.

Lembrando que :

- Um parâmetro deve ter um nome único e iniciar com o símbolo @ (arroba) e ter a definição do seu tipo de dado (data type).

- Os parâmetro de entrada (INPUT) recebem dados do programa que chamou a stored procedure

- Acrescentando a palavra OUTPUT na definição do parâmetro a rotina pode retornar o valor atual do parâmetro para o programa que chamou a SP. (No nosso exemplo estará sendo retornado o código do cliente que foi gerado automaticamente)

Incluindo a seguir o código no SSMS e clicando no botão Execute iremos obter a mensagem : Command(s) Completed successfully indicando que a SP foi criada com sucesso.

Para verificar clique com o botão direito do mouse sobre o objeto Stored Procedure e selecione Refresh. A SP recém-criada deverá ser exibida e podemos ver também os parâmetros usados conforme figura abaixo:

sp_InsertEmails

O procedimento é o mesmo para as demais stored procedures com alterações somente nos parâmetros e instrução SQL usada. Veja a seguir as demais stored prcoedures criadas:

sp_UpdateEmails
sp_DeleteEmail
sp_SelectAllEmails

Já temos o banco de dados , a tabela e as stored procedures criadas. Vamos então criar as camadas de acesso a dados e negócio.

Abra o Visual Web Developer 2008 Express e  clique a opção Create web Site ou File New Web Site e crie um projeto chamado cadEmails ;

O projeto CadEmails do tipo ASP .NET Web Site;

1- Criando a camada de Acesso a dados

Vamos criar a camada de acesso a dados, para isso teremos que incluir uma classe e definir o código para acessar os dados no SQL Server.

Clique com o botão direito do mouse sobre o nome do projeto e selecione a opção Add New Item selecionando na janela o template Class com o nome DAL.

Será apresenta a tela abaixo solicitando a confirmação para incluir o arquivo na pasta App_Code. Confirme.

A seguir inclua o seguinte código no arquivo DAL.vb :

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Namespace cadEmails.DAL

    Public Class AcessoBD
        Private cmd As IDbCommand = New SqlCommand()
        Private strConnectionString As String = ""
        Private handleErrors As Boolean = False
        Private strLastError As String = ""

        Public Sub New()
            Dim objConnectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("cadEmailConnectionString")
            strConnectionString = objConnectionStringSettings.ConnectionString
            Dim cnn As New SqlConnection()
            cnn.ConnectionString = strConnectionString
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
        End Sub

        Public Function ExecuteReader() As IDataReader
            Dim reader As IDataReader = Nothing
            Try
                Me.Open()
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return reader
        End Function

        Public Function ExecuteReader(ByVal commandtext As String) As IDataReader
            Dim reader As IDataReader = Nothing
            Try
                cmd.CommandText = commandtext
                reader = Me.ExecuteReader()
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return reader
        End Function
 
        Public Function ExecuteNonQuery() As Integer
            Dim i As Integer = -1
            Try
                Me.Open()
                i = cmd.ExecuteNonQuery()
                Me.Close()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function
 
        Public Function ExecuteNonQuery(ByVal commandtext As String) As Integer
            Dim i As Integer = -1
            Try
                cmd.CommandText = commandtext
                i = Me.ExecuteNonQuery()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function

        Public Function ExecuteDataSet() As DataSet
            Dim da As SqlDataAdapter = Nothing
            Dim ds As DataSet = Nothing
            Try
                da = New SqlDataAdapter()
                da.SelectCommand = CType(cmd, SqlCommand)
                ds = New DataSet()
                da.Fill(ds)
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return ds
        End Function
 
        Public Function ExecuteDataSet(ByVal commandtext As String) As DataSet
            Dim ds As DataSet = Nothing
            Try
                cmd.CommandText = commandtext
                ds = Me.ExecuteDataSet()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return ds
        End Function
 
        Public Property CommandText() As String
            Get
                Return cmd.CommandText
            End Get
            Set(ByVal value As String)
                cmd.CommandText = value
                cmd.Parameters.Clear()
            End Set
        End Property
 
        Public ReadOnly Property Parameters() As IDataParameterCollection
            Get
                Return cmd.Parameters
            End Get
        End Property
 
        Public Sub AddParameter(ByVal paramname As String, ByVal paramvalue As Object)
            Dim param As SqlParameter = New SqlParameter(paramname, paramvalue)
            cmd.Parameters.Add(param)
        End Sub
 
        Public Sub AddParameter(ByVal param As IDataParameter)
            cmd.Parameters.Add(param)
        End Sub
 
        Public Property ConnectionString() As String
            Get
                Return strConnectionString
            End Get
            Set(ByVal value As String)
                strConnectionString = value
            End Set
        End Property
 
        Private Sub Open()
            cmd.Connection.Open()
        End Sub
 
        Private Sub Close()
            cmd.Connection.Close()
        End Sub
 
        Public Property HandleExceptions() As Boolean
            Get
                Return handleErrors
            End Get
            Set(ByVal value As Boolean)
                handleErrors = value
            End Set
        End Property
 
        Public ReadOnly Property LastError() As String
            Get
                Return strLastError
            End Get
        End Property
 
        Public Sub Dispose()
            cmd.Dispose()
        End Sub

   End Class
End Namespace

Eu defini o namespace CadEmails.DAL e a classe AccessoBD com diversos métodos que executam operações no banco de dados retornando objetos DataReader, DataSet, inteiros, etc.

O construtor da classe obtém a string de conexão com o banco de dados do arquivo Web.config usando a classe ConfigurationManager:

 Public Sub New()
            Dim objConnectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("cadEmailConnectionString")
            strConnectionString = objConnectionStringSettings.ConnectionString
            Dim cnn As New SqlConnection()
            cnn.ConnectionString = strConnectionString
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
        End Sub

Vou aproveitar e definir a string de conexão no arquivo Web.Config. Abra o arquivo e inclua a string abaixo:
 

<connectionStrings>

      <add name="cadEmailConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=Escola;Integrated Security=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

2- Criando a camada de Negócios

Agora vamos definir a camada de negócios.

Clique com o botão direito do mouse sobre o nome do projeto e selecione a opção Add New Item selecionando na janela o template Class com o nome BAL.

Será apresenta a tela abaixo solicitando a confirmação para incluir o arquivo na pasta App_Code. Confirme.

A seguir inclua o seguinte código no arquivo BAL.vb;

Imports System.Data
Imports System.Data.SqlClient
Imports cadEmails.DAL

Namespace cadEmails.BAL

    Public Class Emails
        Public Sub New()
        End Sub
 
        Private _alunoID As Integer
        Private _alunoNome As String
        Private _alunoEmail As String
        Private _ativo As Boolean
 
        Public Property alunoNome() As String
            Get
                Return _alunoNome
            End Get
            Set(ByVal value As String)
                _alunoNome = value
            End Set
        End Property

        Public Property alunoID() As Integer
            Get
                Return _alunoID
            End Get
            Set(ByVal value As Integer)
                _alunoID = value
            End Set
        End Property
        Public Property alunoEmail() As String
            Get
                Return _alunoEmail
            End Get
            Set(ByVal value As String)
                _alunoEmail = value
            End Set
        End Property
        Public Property Ativo() As Boolean
            Get
                Return _ativo
            End Get
            Set(ByVal value As Boolean)
                _ativo = value
            End Set
        End Property
 
        Public Function IncluirEmails(ByVal email As Emails) As Integer
            Dim db As AcessoBD = New AcessoBD
            Dim p As SqlParameter = New SqlParameter("@alunoID", 0)
            p.Direction = ParameterDirection.Output
            db.AddParameter("@alunoNome", email.alunoNome)
            db.AddParameter("@alunoEmail", email.alunoEmail)
            db.AddParameter("@ativo", email.Ativo)
            db.AddParameter(p)
            Return db.ExecuteNonQuery("sp_InsertEmails")
        End Function


        Public Function atualizarEmails(ByVal email As Emails) As Integer
            Dim db As AcessoBD = New AcessoBD
            db.AddParameter("@alunoID", email.alunoID)
            db.AddParameter("@alunoNome", email.alunoNome)
            db.AddParameter("@alunoEmail", email.alunoEmail)
            db.AddParameter("@ativo", email.Ativo)
            Return db.ExecuteNonQuery("sp_UpdateEmails")
        End Function
 
        Public Shared Function excluirEmails(ByVal email As Emails) As Integer
            Dim db As AcessoBD = New AcessoBD
            db.AddParameter("@alunoID", email.alunoID)
            Return db.ExecuteNonQuery("sp_DeleteEmails")
        End Function
 
        Public Shared Function getEmailPorNome(ByVal nome As String) As Emails
            Dim db As AcessoBD = New AcessoBD
            db.Parameters.Add(New SqlParameter("@alunoNome", nome))
            Dim dr As SqlDataReader = CType(db.ExecuteReader("sp_GetEmailByNome"), SqlDataReader)
            If dr.HasRows Then
                Dim e As Emails = New Emails
                While dr.Read
                    e.alunoID = dr.GetInt32(dr.GetOrdinal("alunoID"))
                    e.alunoNome = dr.GetString(dr.GetOrdinal("alunoNome"))
                    e.alunoEmail = dr.GetString(dr.GetOrdinal("alunoEmail"))
                    e.Ativo = dr.GetBoolean(dr.GetOrdinal("ativo"))
                End While
                dr.Close()
                Return e
            Else
                dr.Close()
                Return Nothing
            End If
        End Function
 
        Public Function selecionaTodos() As DataSet
            Dim db As AcessoBD = New AcessoBD
            Return db.ExecuteDataSet("sp_SelectAllEmails")
        End Function
    End Class
End Namespace

 

Eu defini o namespace cadEmails.BAL e a classe Emails.

Na camada de negócio definimos os membros e propriedades da classe Emails e também os seguintes métodos:

3- Criando a camada de Apresentação

Nosso projeto deverá possuir a seguinte estrutura:

Selecione o arquivo Default.aspx e inclua um componente GridView e um componente FormView conforme a figura abaixo:

O controle GridView irá exibir os emails cadastrados permitindo a edição de um email e o controle FormView será usado para incluir um novo Email.

Em GridView Tasks selecione Choose Data Source e a seguir : <New data source...>

Na janela a seguir selecione o objeto ObjectDataSource e informe o nome oCadEmailDS.

Na janela Choose a Business Object selecione a  nossa cada de negócios definida no namespace cadEmailsBAL.Emails;

A seguir na janela Define Data Methods em cada uma das guias da janela selecione o método definido na camada de negócios que esta relacionado com a guia:

Com isso na camada de apresentação estamos acessando os métodos da camada de negócio que por sua vez irá acessar a camada de acesso a dados.

Repita o procedimento para o componente FormView atribuindo na opção Choose Data Source  o mesmo objeto oCadEmailDS criado anteriormente com o objeto ObjectDataSource.  Defina também a propriedade DefaultMode do controle como Insert.

Após realizar a formatação dos controles iremos ter no projeto :

Executando a página iremos obter o seguinte resultado:

Com isso concluímos essa primeira parte da criação de uma aplicação ASP .NET simples com utilização de camadas e usando stored procedures.

Na segunda parte do artigo iremos mostrar como podemos usar o LINQ To SQL e como ele pode nos ajudar.

Até o próximo artigo ... ASP .NET - LINQ To SQL e Stored Procedures II

Pegue o projeto completo aqui: cadEmails.zip

referências:

José Carlos Macoratti