VB .NET - Atualizando tabelas relacionadas com ADO .NET

Este artigo faz uma revisão dos conceitos da tecnologia ADO .NET através de um exemplo prático.

Vamos ao nosso cenário...

Vamos partir de um modelo de dados simples e que já esta pronto para uso dessa forma vamos usar o banco de dados Northwind.mdf;

Este banco de dados possui diversas tabelas relacionadas mas nosso interesse estará focado nas tabelas: Orders, Orders Details, Suppliers, Products, Customers e Employees.

Abaixo vemos o relacionamento existente entre essas tabelas:

Nosso objetivo será atualizar as tabelas Orders e Orders Details com informações obtidas de um formulário que simulará o envio de um pedido e seus detalhes. Embora as demais tabelas não sejam atualizadas iremos precisar de suas informações.

Neste artigo eu vou mostrar como realizar esta tarefa usando ADO .NET e seus objetos e na segunda parte irei mostrar como fica a mesma tarefa sendo realizada com os recursos do LINQ to SQL.

Além de revisarmos os conceitos relacionados com os objetos básicos da ADO .NET como DataReader, DataAdapter, SqlCommand, etc. Iremos trabalhar com classes, transações e instruções SQL parametrizadas.

Como temos o modelo de dados já pronto eu vou iniciar mostrando como será o formulário de entrada de dados usado na aplicação.

Os recursos necessários para acompanhar este artigo são:

Criando o projeto e definindo as classes do domínio

Abra o Visual Basic 2010 Express Edition e crie um novo projeto do tipo Windows Forms Application com o nome NW_Pedidos;

Altere o nome do formulário padrão para frmSQL.vb e inclua os seguintes controles a partir da ToolBox no formulário:

Os dois controles Combobox deverá ser preenchidos quando o formulário for carregado permitindo que o usuário selecione o cliente e o funcionário;

A seguir o usuário deverá informar o código do produto e ao teclar ENTER será realizada uma busca e o nome do produto e seu preço unitário será exibido, bastando ao usuário informar a quantidade desejada para que o valor total seja calculado e o novo pedido exibido no controle ListView;

A seguir o usuário deve informar os dados do destinatário : Nome, Endereço, Cidade e Região e clicar no botão Salvar Pedido para persistir os dados do novo pedido e seus detalhes.

Este cenário é muito frequente em aplicações comerciais.

Vamos então mostrar como implementar o código das funcionalidades envolvidas nesta operação.

1- Definindo as classes do domínio

Vamos definir as classes Products, Employees, Customers, Orders e Orders Details

- Classe NWProduto

No meu Project clique em Add Class e selecione o template Class informando o nome NWProduto;

Digite o código abaixo na classe NWProdutos; nele estamos definindo 3 variáveis com o mesmo nome dos campos da tabela Products e o método getProdutoPorId() que retorna uma lista de produtos;

Public Class NWProduto

    Public ProductID As Integer
    Public ProductName As String
    Public ProductPrice As Decimal

    Public Shared Function getProdutoPorId(ByVal productID As String) As List(Of NWProduto)
        Dim produtos As New List(Of NWProduto)
        Dim CN As New SqlClient.SqlConnection(strConexao)
        Dim CMD As New SqlClient.SqlCommand
        Dim RDR As SqlClient.SqlDataReader

        If IsNumeric(productID) Then
            CMD.CommandText = "SELECT * FROM products WHERE ProductID=@ProductID"
            CMD.CommandType = CommandType.Text
            CMD.Parameters.AddWithValue("@ProductID", productID)
            CMD.Connection = CN
            CN.Open()
            RDR = CMD.ExecuteReader
            Dim prod As NWProduto = Nothing

            If RDR.Read Then
                prod = New NWProduto
                prod.ProductID = RDR.GetInt32(RDR.GetOrdinal("ProductID"))
                prod.ProductName = RDR.GetString(RDR.GetOrdinal("ProductName"))
                prod.ProductPrice = RDR.GetDecimal(RDR.GetOrdinal("UnitPrice"))
                produtos.Add(prod)
            End If
        Else
            CMD.CommandText = "SELECT * FROM Products WHERE ProductName LIKE '%' + @ProductID + '%'"
            CMD.CommandType = CommandType.Text
            CMD.Parameters.AddWithValue("@ProductID", productID)
            CMD.Connection = CN
            CN.Open()
            RDR = CMD.ExecuteReader
      
            While RDR.Read
                Dim prod As New NWProduto
                prod.ProductID = RDR.GetInt32(RDR.GetOrdinal("ProductID"))
                prod.ProductName = RDR.GetString(RDR.GetOrdinal("ProductName"))
                prod.ProductPrice = RDR.GetDecimal(RDR.GetOrdinal("UnitPrice"))
                produtos.Add(prod)
            End While

        End If
        CN.Close()
        Return produtos
    End Function

End Class

O método getProdutoPorID retorna uma lista de produtos pelo código informado;

- Classe NWFuncionario

Repita o procedimento feito para criar a classe NWProduto e crie a classe NWFuncionario;

Nesta classe definimos 2 variáveis, o método GetTodosFuncionarios() que retorna uma lista de funcionários e sobrescrevemos o método ToString() que agora retorna o nome do funcionário.

Public Class NWFuncionario

    Public EmployeeID As Integer
    Public EmployeeName As String
    Public Overrides Function ToString() As String
        Return EmployeeName
    End Function

    Public Shared Function GetTodosFuncionarios() As List(Of NWFuncionario)
        Dim CMD As New SqlClient.SqlCommand
        Dim Funcionarios As New List(Of NWFuncionario)
        CMD.CommandText = "SELECT * FROM Employees"
        CMD.CommandType = CommandType.Text
        Dim CN As New SqlClient.SqlConnection(strConexao)
        CMD.Connection = CN
        CN.Open()
        Dim RDR As SqlClient.SqlDataReader
        RDR = CMD.ExecuteReader()
        While RDR.Read
            Dim Emp As New NWFuncionario
            Emp.EmployeeID = RDR.GetInt32(RDR.GetOrdinal("EmployeeID"))
            Emp.EmployeeName = RDR.GetString(RDR.GetOrdinal("LastName")) & " " & RDR.GetString(RDR.GetOrdinal("FirstName"))
            Funcionarios.Add(Emp)
        End While
        RDR.Close()
        Return Funcionarios

    End Function
End Class

- Classe NWCliente

Repita o procedimento feito para criar a classe NWProduto e crie a classe NWCliente;

Nesta classe vamos definir 2 variáveis, sobrescrever a função ToString e definir o método GetTodosClientes que retorna uma lista de Clientes; 

Public Class NWCliente
    Public CustomerID As String
    Public CustomerName As String
    Public Overrides Function ToString() As String
        Return CustomerName
    End Function

    Public Shared Function GetTodosClientes() As List(Of NWCliente)
        Dim CMD As New SqlClient.SqlCommand
        Dim Clientes As New List(Of NWCliente)
        CMD.CommandText = "SELECT * FROM Customers"
        CMD.CommandType = CommandType.Text
        Dim CN As New SqlClient.SqlConnection(strConexao)
        CMD.Connection = CN
        CN.Open()
        Dim RDR As SqlClient.SqlDataReader
        RDR = CMD.ExecuteReader()
        While RDR.Read
            Dim Cust As New NWCliente
            Cust.CustomerID = RDR.GetString(RDR.GetOrdinal("CustomerID"))
            Cust.CustomerName = RDR.GetString(RDR.GetOrdinal("CompanyName"))
            Clientes.Add(Cust)
        End While
        RDR.Close()
        Return Clientes
    End Function

End Class

- Classe NWPedido

Repita o procedimento feito para criar a classe NWProduto e crie a classe NWPedido;

Note que no interior da classe NWpedido temos a definição da classe NWPedidoDetalhes que representa os detalhes de um pedido;

O método SalvarPedido() recebe um novo pedido e realiza uma transação para salvar o pedido na tabela Orders e os seus detalhes tabela Order Details:

Public Class NWPedido

    Public Class NWPedidoDetalhes
        Public ProductID As Integer
        Public ProductPrice As Decimal
        Public ProductQuantity As Integer
        Public ProductDiscount As Decimal
    End Class

    Public OrderDate As Date
    Public EmployeeID As Integer
    Public CustomerID As String
    Public Detalhes As New List(Of NWPedidoDetalhes)

    Public Shared Function SalvarPedido(ByVal novoPedido As NWPedido) As Integer
        Dim CN As New SqlClient.SqlConnection(strConexao)
        Dim CMD As New SqlClient.SqlCommand
        CN.Open()
        CMD.Connection = CN
        Dim TR As SqlClient.SqlTransaction = CN.BeginTransaction
        CMD.Transaction = TR
        CMD.CommandText = "INSERT Orders (OrderDate, EmployeeID, CustomerID) VALUES (@orderDate, @employeeID, @customerID);SELECT Scope_Identity()"
        CMD.Parameters.AddWithValue("@orderDate", Today)
        CMD.Parameters.AddWithValue("@employeeID", novoPedido.EmployeeID)
        CMD.Parameters.AddWithValue("@customerID", novoPedido.CustomerID)
        Dim OrderID As Int32
        Try
            OrderID = System.Convert.ToInt32(CMD.ExecuteScalar)
        Catch ex As Exception
            TR.Rollback()
            CN.Close()
            Throw ex
        End Try
        For Each det As NWPedidoDetalhes In novoPedido.Detalhes
            CMD.CommandText = "INSERT [Order Details] (OrderID, ProductID, UnitPrice, Quantity) VALUES(@OrderID, @productID, @price, @quantity)"
            CMD.Parameters.Clear()
            CMD.Parameters.AddWithValue("@orderID", OrderID)
            CMD.Parameters.AddWithValue("@productID", det.ProductID)
            CMD.Parameters.AddWithValue("@price", det.ProductPrice)
            CMD.Parameters.AddWithValue("@quantity", det.ProductQuantity)
            Try
                CMD.ExecuteNonQuery()
            Catch ex As Exception
                TR.Rollback()
                CN.Close()
                Throw ex
            End Try
        Next
        TR.Commit()
        CN.Close()
        Return OrderID
    End Function

End Class

Definindo o código do projeto

Vamos incluir um módulo no projeto; No menu Project selecione Add New Item e selecione o template Module informando o nome Conexao.vb , a seguir defina nele a nossa string de conexão para que a mesma seja visível em todo o projeto conforme abaixo:

Module Conexao
    Public strConexao As String = "Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
End Module

A nossa string de conexão aponta para o banco de dados Northwind.mdf do SQL Server no servidor local SQLExpress;

Agora que temos as classes com os métodos definidos e a string de conexão podemos partir para criar as funcionalidades que irão fazer a aplicação funcionar.

No início do formulário frmSQL vamos definir um objeto Connection usando a string de conexão definida para se conectar com o banco dados:

Dim CN As New SqlClient.SqlConnection(strConexao)

No evento Load do formulário vamos incluir o código para carregar os dois controles ComboBox:

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cboFuncionarios.DisplayMember = "EmployeeName"
        cboFuncionarios.ValueMember = "EmployeeID"

        For Each func As NWFuncionario In NWFuncionario.GetTodosFuncionarios
            cboFuncionarios.Items.Add(func)
        Next

        cboClentes.DisplayMember = "CompanyName"
        cboClentes.ValueMember = "CustomerID"
        For Each cli As NWCliente In NWCliente.GetTodosClientes
            cboClentes.Items.Add(cli)
        Next
    End Sub

Vamos criar agora as seguintes rotinas:

  1. AdicionarProdutos() - inclui as informações do controle TextBox no controle ListView e chama a rotina AtualizaTotal();
  2. AtualizaTotal() - Percorre o ListView e totaliza os produtos;
  3. LimpaTextBox() - Limpa as caixas de texto;

O código das rotinas é exibida abaixo:

Private Sub AdicionarProduto()
        Dim LI As New ListViewItem
        LI.Text = txtID.Text
        LI.SubItems.Add(txtProduto.Text)
        LI.SubItems.Add(txtPreco.Text)
        LI.SubItems.Add(txtQtde.Text)
        LI.SubItems.Add(txtSubtotal.Text)
        ListView1.Items.Add(LI)
        AtualizaTotal()
    End Sub

    Private Sub AtualizaTotal()
        Dim items As Integer
        Dim total As Decimal
        For Each LI As ListViewItem In ListView1.Items
            items += Integer.Parse(LI.SubItems(3).Text)
            total += Decimal.Parse(LI.SubItems(4).Text)
        Next
        txtItems.Text = items.ToString
        txtTotal.Text = total.ToString("#,###.00")
    End Sub

    Private Sub LimpaTextBox()
        txtID.Text = ""
        txtProduto.Text = ""
        txtPreco.Text = ""
        txtQtde.Text = ""
        txtSubtotal.Text = ""
    End Sub

Agora vamos tratar os seguintes eventos do controle ListView1:

O código do tratamento para esses eventos é exibido a seguir:

 Private Sub ListView1_ColumnWidthChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnWidthChangedEventArgs) Handles ListView1.ColumnWidthChanged
        txtItems.Left = ListView1.Left + ListView1.Columns(0).Width + ListView1.Columns(1).Width + ListView1.Columns(2).Width
        txtItems.Width = ListView1.Columns(3).Width
        txtTotal.Left = txtItems.Left + txtItems.Width
        txtTotal.Width = ListView1.Columns(4).Width
    End Sub

    Private Sub ListView1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ListView1.KeyUp
        If e.KeyCode = Keys.Delete And ListView1.SelectedItems.Count > 0 Then
            ListView1.SelectedItems(0).Remove()
            AtualizaTotal()
        End If
        If e.KeyCode = Keys.Escape Then
            LimpaTextBox()
            txtID.Focus()
        End If
    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged
        ExibeDetalhe()
    End Sub

A rotina ExibeDetalhe() obtém os dados do controle ListView e exibe-os nos controle TextBox:

 Private Sub ExibeDetalhe()
        If ListView1.SelectedItems.Count > 0 Then
            txtID.Text = ListView1.SelectedItems(0).Text
            txtProduto.Text = ListView1.SelectedItems(0).SubItems(1).Text
            txtPreco.Text = ListView1.SelectedItems(0).SubItems(2).Text
            txtQtde.Text = ListView1.SelectedItems(0).SubItems(3).Text
            txtSubtotal.Text = ListView1.SelectedItems(0).SubItems(4).Text
        End If
    End Sub

Vamos tratar o evento KeyUp do controle TxtID para que após o usuário digitar um código de produto ao pressionar a tecla ENTER seja feita uma busca na tabela Products e seja obtido os dados do produto e esses dados sejam exibidos no formulário:

Private Sub txtID_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtID.KeyUp
        If e.KeyCode = Keys.Enter Then
            If txtID.Text.Length > 0 Then
                Dim P As NWProduto
                Dim todosProdutos As List(Of NWProduto) = NWProduto.getProdutoPorId(txtID.Text.Trim)
                If todosProdutos.Count = 1 Then
                    txtProduto.Text = todosProdutos(0).ProductName
                    txtPreco.Text = todosProdutos(0).ProductPrice.ToString("#.00")
                    txtQtde.Focus()
                Else
                    ListView2.Items.Clear()
                    For Each P In todosProdutos
                        Dim LI As New ListViewItem
                        LI.Text = P.ProductID
                        LI.SubItems.Add(P.ProductName)
                        LI.SubItems.Add(P.ProductPrice.ToString("#,###.00"))
                        ListView2.Items.Add(LI)
                    Next
                    If ListView2.Items.Count > 0 Then
                        ListView2.Visible = True
                        ListView2.Items(0).Selected = True
                        ListView2.Capture = True
                        ListView2.Focus()
                    Else
                        txtID.Clear()
                        txtID.Focus()
                    End If
                End If
            End If
        End If
        If e.KeyData = Keys.Down Then
            If ListView1.Items.Count > 0 Then
                LimpaTextBox()
                ListView1.Items(0).Selected = True
                ListView1.Focus()
            End If
        End If
    End Sub

Agora vamos tratar o evento KeyUp do TextBox txtQtde de forma após informar a quantidade e pressionada a tecla ENTER seja calculado o total e as informações sejam exibidas no controle ListView configurando assim um novo pedido:

 Private Sub txtQty_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtQtde.KeyUp
        If txtProduto.Text.Trim = "" Then
            MsgBox("Informe o código ou nome do produto!")
            Exit Sub
        End If
        If e.KeyData = Keys.Enter Then
            Dim qtde As Integer
            Integer.TryParse(txtQtde.Text, qtde)
            If qty > 0 Then
                txtSubtotal.Text = (Decimal.Parse(txtPreco.Text) * qtde).ToString("#,###.00")
                AdicionarProduto()
                LimpaTextBox()
                txtID.Focus()
            Else
                txtQtde.Text = ""
            End If
        End If
        If e.KeyData = Keys.Escape Then
            LimpaTextBox()
            txtID.Focus()
        End If
    End Sub

No evento Click do botão Salvar Pedido temos o código que salva o pedido e seus detalhes :

 Private Sub btnSalvar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSalvar.Click
        Dim novoPedido As New NWPedido
        novoPedido.OrderDate = Today
        novoPedido.CustomerID = CType(cboClentes.SelectedItem, NWCliente).CustomerID
        novoPedido.EmployeeID = CType(cboFuncionarios.SelectedItem, NWFuncionario).EmployeeID
        For Each LI As ListViewItem In ListView1.Items
            Dim novoDetalhe As New NWPedido.NWPedidoDetalhes
            novoDetalhe.ProductID = LI.Text
            novoDetalhe.ProductPrice = System.Convert.ToDecimal(LI.SubItems(2).Text)
            novoDetalhe.ProductQuantity = System.Convert.ToInt32(LI.SubItems(3).Text)
            novoPedido.Detalhes.Add(novoDetalhe)
        Next
        Dim orderID As Integer
        Try
            orderID = NWPedido.SalvarPedido(novoPedido)
        Catch ex As Exception
            MsgBox("Falha ao gravar o pedido no banco de dados " & vbCrLf & ex.Message)
            Exit Sub
        End Try
        MsgBox("Pedido " & orderID & " gravado com sucesso !")
    End Sub

No evento Click do botão Novo Pedido apenas limpamos as caixas de texto e colocamos o foco no controle txtID:

    Private Sub btnNovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNovo.Click
        LimpaTextBox()
        txtID.Focus()
    End Sub

Para concluir vamos tratar os eventos KeyUp e LostFocus do controle ListView2:

 Private Sub ListView2_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ListView2.KeyUp
        If ListView2.Visible And ListView2.SelectedItems.Count = 1 Then
            If e.KeyCode = Keys.Enter Then
                txtID.Text = ListView2.SelectedItems(0).Text
                txtProduto.Text = ListView2.SelectedItems(0).SubItems(1).Text
                txtPreco.Text = System.Convert.ToDecimal(ListView2.SelectedItems(0).SubItems(2).Text).ToString("#,###.00")
                ListView2.Visible = False
                txtQtde.Focus()
            End If
        End If
    End Sub
    Private Sub ListView2_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.LostFocus
        ListView2.Visible = False
        If ListView2.Visible = True Then
            txtID.Focus()
        End If
    End Sub

Agora é só alegria...

Vamos executar o projeto e incluir um novo pedido...

Após informar os dados e clicar no botão - Salvar Pedido - teremos o seguinte resultado:

Dessa forma neste projeto tivemos que definir as nossas classes de negócio para poder implementar a funcionalidade desejada.

Na segunda parte do artigo iremos implementar a mesma funcionalidade usando o LINQ to SQL e comparar qual das duas é mais eficiente.

Pegue o projeto completo aqui:   NW_Pedidos.zip


Veja os Destaques e novidades do SUPER CD VB 2012 (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Veja mais sistemas completos para a plataforma .NET no Super CD .NET e no Super DVD .NET , confira...

Quer aprender C# ??

Chegou o Super DVD C# 2012 com exclusivo material de suporte e vídeo aulas com curso básico sobre C#
 

Col 1:9 Por esta razão, nós também, desde o dia em que ouvimos, não cessamos de orar por vós, e de pedir que sejais cheios do pleno conhecimento da sua vontade, em toda a sabedoria e entendimento espiritual;
Col 1:10 para que possais andar de maneira digna do Senhor, agradando-lhe em tudo, frutificando em toda boa obra, e crescendo no conhecimento de Deus,
Col 1:11 corroborados com toda a fortaleza, segundo o poder da sua glória, para toda a perseverança e longanimidade com gozo;

Referências:

José Carlos Macoratti