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 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 e SqlCommand. 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:

Visual Basic 2010 Express Edition
SQL Server 2008 Express
Banco de dados Northwind.mdf

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:

2 Combobox – cboClientes e cboFuncionarios
5 TextBox – txtID, txtProduto (ReadOnly=True), txtPreco (ReadOnly=True), txtQtde, txtSubtotal (ReadOnly=True)
2 ListView – ListView1 e ListView2
2 TextBox – txtItems e txtTotal
4 TextBox – txtNome, txtEndereco, txtCidade e txtRegiao
2 Buttons – btnSalvar e btnNovo

Os dois controles Combobox deverão 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 menu 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 três 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 duas 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 duas 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:

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

O código das rotinas é exibido 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:

ColumnWidthChanged – Rearranja a largura da coluna do controle ListView1;
KeyUp – Verifica se foi pressionada a tecla Del ou ESC e conforme o caso, atualiza os totais ou limpa as caixas de textos;
SelectedIndexChanged – Chama a rotina ExibeDetalhe()

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 – que serão 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 que, 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

Fonte

VB .NET – Atualizando tabelas relacionadas com ADO .NET

Criando o projeto e definindo as classes do domínio

1. Definindo as classes do domínio

Classe NWProduto

Classe NWFuncionario

Veja também: