VB.NET 2005 - Acesso a dados no SQL Server via código

O assunto de hoje é o acesso a dados no SQL Server (vou usar a versão Express) feita totalmente via código sem a utilização dos assistentes do VB2005.

Embora a nova versão tenha trazido muitas melhorias, o código criado nas versões anteriores é compatível com a nova versão, e, isto quer dizer que os conceitos para acesso a dados aprendidos podem ser usados sem problema algum.

Vou apresentar o assunto de uma forma básica de maneira que um nível mínimo de conhecimentos sobre ADO.NET será exigido para acompanhar este artigo.

O acesso a dados no VB2005

A nova versão do VB 2005 (leia-se também VS.NET 2005) trouxe também um nova versão da ADO.NET, a versão 2.0, que contém as classes para acesso a dados na plataforma .NET. Foram muitos os aperfeiçoamentos, mas a espinha dorsal continha a mesma, ou seja, o roteiro básico continha o seguinte :

Em diversos artigos anteriores eu mostrei como você pode fazer o acesso a fonte de dados de uma forma simples e rápida usando os Assistentes da nova versão. Dependendo da complexidade da aplicação muitas vezes não é necessário escrever uma linha de código sequer, os assistentes fazem tudo para você. Acontece que você paga um preço por esta comodidade: o desempenho.

Uma aplicação desenvolvida usando os assistentes raramente é usada em produção por que ela não tem um bom desempenho devido ao código gerado pelo assistente. Creio que na maioria das vezes, se você quiser um desempenho melhor terá deixar os assistentes de lado e escrever você mesmo o código. É o que eu vou mostrar como fazer aqui.

O único recurso que eu vou utilizar e que usa um assistente será a criação de uma base de dados no SQL Server 2005 Express. Isto realmente facilita muito e não afeta o objetivo do artigo.

Para acompanhar este artigo você vai precisar dos seguintes recursos devidamente instalados:

Criando a base de dados no SQL Server 2005 Express

- Abra o VB 2005 Express Edition e crie um novo projeto chamado appAcessoBD.

- Se a janela Data Sources não estiver visível Ative-a no menu Data->Show Data Sources.

- Clique no link Add New Data Source e na janela Data Source Configuration Wizard selecione DataBase.

Nota:  Poderíamos criar a base de dados clicando o botão direito do mouse sobre o nome do projeto e selecione Add->New Item  e na janela de templates selecionar o item SQL Database informando o nome cadastro.mdf para a base de dados a ser criada.

Clique no botão Next> na caixa de combinação selecione a conexão com a base cadastro.mdf. Expandindo a Connection String você verá a string de conexão que será usada para a conexão.

Ao clicar no botão Next> irá surgir a seguinte mensagem de aviso. Ela pergunta se você deseja copiar o arquivo da base de dados cadastro.mdf para o seu projeto. Clique em Não(No) para não salvar o arquivo no seu projeto usando assim o local original onde o mesmo foi criado.

Nota: Veja artigo VB.NET  2005 -  TableAdapater não atualiza os dados  para maiores detalhes sobre o assunto.

Clicando no botão Next> o próximo passo será salvar a string de conexão no arquivo de configuração da aplicação.(Você pode ver a o valor clicando em My Project ->Settings)

Neste ponto você deve clicar no botão Cancel para cancelar a operação pois não vamos criar um Data Source. Fizemos os passos acima somente para criar a base de dados e obter a string de conexão.

Criando a tabela Clientes

A próxima etapa será criar uma nova tabela chamada Clientes na base de dados cadastro.mdf.

- Abra a janela do DataBase Explorer e expanda os objetos para a conexão Cadastro.mdf.

- Selecione a opção Table e clique com o botão direito do mouse selecionando a opção Add New Table

 

A seguir informe conforme a figura abaixo os nomes dos campos e o tipo de dados. Ao terminar, salve o trabalho. Retornando a janela DataBase Explorer veremos a tabela Clientes e seus respectivos campos criados.

Se desejar pode incluir valores diretamente na tabela. Para isto clique sobre a tabela Clientes e selecione a opção Show Data Table.

Vamos criar a interface no formulário Windows

Altere o nome do formulário para frmAppbd.vb e inclua os seguintes controles:

  • GroupBox

  • Label

  • TextBox

  • Button

O leiaute é exibido na figura ao lado.

Iremos implementar as seguintes funcionalidades:

  1. Incluir um novo Cliente- (Novo)

  2. Alterar dados do Cliente - (Atualiza)

  3. Excluir dados de um Cliente - (Elimina)

Além disto iremos implementar a navegação pelos registros usando os botões de comando.

As funcionalidades serão implementadas usando instruções SQL : INSERT INTO, UPDATE/SET e DELETE/FROM.

Vale a pena falar um pouco sobre a navegação pelos registros. Se você conhece a ADO já sabe que para efetuar a navegação em um Recordset criado tínhamos a disposição os métodos : MoveFirst, MoveLast, MoveNext e MovePrevious. A ADO.NET não inclui estes métodos para navegação pois o objeto DataTable esta sempre na memória e a ADO.NET usa o acesso via array.

Para acessar um registro (linha) em uma tabela (DataTable) podemos fazer o seguinte :

Como exemplo para utilização deste recurso A MSDN mostra o código abaixo:

  Dim ds As New DataSet()
   Dim da As SqlDataAdapter
   Dim cn As New SqlConnection("server=myserver;integrated security=sspi;database=northwind")
   da = New SqlDataAdapter("select * from customers", cn)
   da.Fill(ds, "customers")

   Dim x As Integer
   'para frente
   For x = 1 To ds.Tables(0).Rows.Count - 1
       Console.WriteLine(ds.Tables(0).Rows(x).Item("customerid").ToString)
   Next

   'para trás
   For x = ds.Tables(0).Rows.Count - 1 To 1 Step -1
       Console.WriteLine((ds.Tables(0).Rows(x).Item("customerid").ToString))
   Next

E agora vamos ao código

Como estaremos fazendo o acesso a uma base de dados SQL Server iremos usar a classe SqlClient e para isto usaremos a importação:

Imports System.Data.sqlclient

A seguir iremos definir as variáveis objeto usadas na aplicação:

Private dt As DataTable

Private da As SqlDataAdapter

Private connBD As SqlConnection

Private ds As DataSet

Private registro As Integer

Private strcon As String

 

1- Código do Evento Load do formulário

 

Private Sub frmAppBD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'string de conexão com o SQL Server 2005 Express local
strcon = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Documents and Settings\Macoratti.MACORATI\Meus documentos\Cadastro.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Dim strSQL As String = "Select * from Clientes order by codigo"


Try

  'cria um dataset , preenche e carrega a tabela clientes
   Dim conBD As New SqlConnection(strcon)
  ds = New DataSet
  da = New SqlDataAdapter(strSQL, strcon)
  da.Fill(ds, "Clientes")
  dt = ds.Tables("Clientes")

  If dt.Rows.Count > 0 Then

    registro = 0

    btnPrimeiro_Click(Nothing, Nothing)

  Else

    registro = -1

    btnAtualizar.Enabled = False

    btnEliminar.Enabled = False

  End If

Catch ex As Exception

   MessageBox.Show("Erro: " & ex.Message, "Sem conexão", MessageBoxButtons.OK, MessageBoxIcon.Error)

   Exit Sub

End Try

End Sub

Obs: Neste código você deve alterar a string de conexão para a usada na sua máquina local

Estamos efetuando a conexão com a base de dados clientes selecionando todos os registros e preenchendo o DataSet.

Em seguida obtemos o DataTable e verificamos se há registros na tabela (If dt.Rows.Count > 0 ). Se houver registro definimos a variável registro como igual a zero e acionamos o evento Click do botão de navegação para ir para o primeiro registro.

O código para cada um dos quatro botões de navegação é o seguinte :

Private Sub btnUltimo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUltimo.Click

  registro = dt.Rows.Count - 1

  exibirDados(registro)

End Sub
 

Private Sub btnProximo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProximo.Click

  Dim i As Integer = dt.Rows.Count - 1

  registro = registro + 1

  If registro > i Then registro = i

  exibirDados(registro)

End Sub


Private Sub btnPrimeiro_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrimeiro.Click

  registro = 0

  exibirDados(registro)

End Sub


Private Sub btnAnterior_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnterior.Click

  registro = registro - 1

  If registro < 0 Then registro = 0

  exibirDados(registro)

End Sub

 

O código acima trabalhar com a variável registro que indica qual a posição atual do registro; usando a rotina exibirDados() os dados são exibidos no formulário para aquele registro.

 

Código da rotina exibirDados

 

Esta rotina obtém os dados do registro indicado pela posição do parâmetro m e exibe nas caixas de texto os valores da base de dados. É usado um objeto DataRow que obtém os registros da linha (rows): Dim dr As DataRow = dt.Rows(m).

 

Private Sub exibirDados(ByVal m As Integer)
 

Dim i As Integer = dt.Rows.Count - 1


If m < 0 OrElse i < 0 Then Exit Sub


Dim dr As DataRow = dt.Rows(m)


txtCodigo.Text = dr("Codigo")

txtNome.Text = dr("Nome")

txtEndereco.Text = dr("Endereco")

txtCidade.Text = dr("Cidade")

txtEstado.Text = dr("Estado")

txtNascimento.Text = dr("Nascimento")

txtEmail.Text = dr("Email")

txtComentarios.Text = dr("Comentarios")

btnAtualizar.Enabled = True

btnEliminar.Enabled = True


End Sub

 

Código do botão Novo

 

O código vinculado a este botão invoca a rotina incluirRegistro(dr) para inserir um novo registro na tabela Clientes.

 

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


 If btnNovo.Text = "Novo" Then

  For Each c As Control In Me.grpClientes.Controls

     If TypeOf c Is TextBox Then

     c.Text = ""

   End If

  Next

  btnAtualizar.Enabled = False

  btnEliminar.Enabled = False

  btnEncerrar.Text = "Cancela"

  btnNovo.Text = "Incluir"

  txtNome.Focus()

ElseIf btnNovo.Text = "Incluir" Then

  Dim dr As DataRow = dt.NewRow

  incluirRegistro(dr)

  btnNovo.Text = "Novo"

  btnEncerrar.Text = "Encerra"

  btnAtualizar.Enabled = True

  btnEliminar.Enabled = True

End If

End Sub

 

Ao clicar neste botão todos os campos do formulário que usam o controle TextBox são limpos, os botões Elimina e Atualiza são desabilitados , o texto do botão é alterado para Incluir e o foco é posto no primeiro campo

 

Após informar os dados nas caixas de texto o usuário poderá clicar no mesmo botão, que agora possui o texto de Incluir, desta forma é criada uma nova linha na tabela (Dim dr As DataRow = dt.NewRow) e a rotina para incluir o registro é chamada.

 

Código da rotina IncluirRegistro

 

Esta rotina é responsável pela inclusão dos dados na tabela. Para isto usamos uma instrução SQL INSERT INTO recebendo os valores dos parâmetros referente a cada campo da tabela das caixas de texto preenchidas.

 

A rotina atribuirDados(dr) é invocada justamente para obter os dados das caixas de texto.

 

Private Sub incluirRegistro(ByVal dr As DataRow)

Dim sqlInclui As String

Dim reg As Integer

Dim sqlComando As SqlCommand
 

atribuirDados(dr)


sqlInclui = "INSERT INTO Clientes (Nome,Endereco,Cidade,Estado,Nascimento,Email,Comentarios)VALUES ('" & txtNome.Text & "','" & txtEndereco.Text & "','" & txtCidade.Text & "','" & txtEstado.Text & "','" & txtNascimento.Text & "','" & txtEmail.Text & "','" & txtComentarios.Text & "')"


connBD = New SqlConnection(strcon)

sqlComando = New SqlCommand(sqlInclui, connBD)

da.InsertCommand = sqlComando


Try

  connBD.Open()

  reg = sqlComando.ExecuteNonQuery

  If reg > 0 Then

    MsgBox("Incluido com sucesso.")

    da.Update(ds, "Clientes")

    ds.Tables("Clientes").Reset()

    da.Fill(ds, "Clientes")

    dt = ds.Tables("Clientes")

  End If

Catch ex As Exception

   MessageBox.Show("Erro : " & vbCrLf & ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

  connBD.Close()

End Try

End Sub

 

O código executada um comando SQL definido para incluir os registros usando o objeto Command. O método ExecuteNonQuery apenas retorna a quantidade de linhas incluídas. Estamos verificando isto usando a variável reg. Se reg for maior que 0 então a inclusão ocorreu; atualizamos o dataset , resetamos o datatable , preenchemos o dataset novamente e em seguida obtemos a tabela atualizada.

 

A rotina atribuirDados

 

Esta rotina apenas obtém os dados das caixas de texto e os atribuir ao datarow referente a cada campo da tabela. Observe que como a data de nascimento é do tipo datetime estamos fazendo a conversão usando uma string auxiliar para o formato ano/mês/dia que é aceito e convertido pelo SQL Server

 

Private Sub atribuirDados(ByVal dr As DataRow)


 Dim datatmp As Date

Dim data As String


dr("Nome") = txtNome.Text

dr("Endereco") = txtEndereco.Text

dr("Cidade") = txtCidade.Text

dr("Estado") = txtEstado.Text


If txtNascimento.Text.Length = 0 Or Not IsDate(txtNascimento.Text) Then

   dr("Nascimento") = DBNull.Value

Else

   datatmp = Convert.ToDateTime(txtNascimento.Text)

   Data = datatmp.Year.ToString & "-" & datatmp.Month.ToString & "-" & datatmp.Day.ToString

   dr("Nascimento") = data

End If
 

dr("Email") = txtEmail.Text

dr("Comentarios") = txtComentarios.Text

End Sub

 

Código do botão Atualiza

 

O código deste botão verifica se o registro é menor que zero ou se no datatable há registros. Se houver registro e registro for maior que zero então obtemos o datarow para o registro atual e invocamos a rotina atualizarRegistro(dr).

 

Private Sub btnAtualizar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAtualizar.Click

If registro < 0 OrElse registro > dt.Rows.Count - 1 Then Exit Sub

   Dim dr As DataRow = dt.Rows(registro)

   atualizarRegistro(dr)

End Sub

 

A rotina atualizarRegistro

 

Esta rotina efetua a atualização de dados da tabela clientes. Ela é idêntica a rotina incluirRegistro. A diferença é que a instrução SQL usada é UPDATE/SET.

 

Private Sub atualizarRegistro(ByVal dr As DataRow)


Dim sqlAltera As String

Dim reg As Integer

Dim sqlComando As SqlCommand


atribuirDados(dr)


sqlAltera = "UPDATE Clientes SET Nome = '" & txtNome.Text & "',Endereco= '" & txtEndereco.Text & "',Cidade = '" & txtCidade.Text & "',Estado = '" & txtEstado.Text & "',Nascimento = '" & txtNascimento.Text & "',Email = '" & txtEmail.Text & "',Comentarios = '" & txtComentarios.Text & "'"
 

connBD = New SqlConnection(strcon)

sqlComando = New SqlCommand(sqlAltera, connBD)

'define o sql para atualizar os dados

da.UpdateCommand = sqlComando


Try

  connBD.Open()

  reg = sqlComando.ExecuteNonQuery


  If reg > 0 Then

    MsgBox("Registro alterado com sucesso.")

    da.Update(ds, "Clientes")

    da.Fill(ds, "Clientes")

    dt = ds.Tables("Clientes")

  End If

Catch ex As Exception

   MessageBox.Show("Erro : " & vbCrLf & ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

  connBD.Close()

End Try

End Sub

 

Código do botão Elimina

 

Neste código se houver registro na tabela obtemos o código da caixa de texto , fazemos conversão e chamamos a rotina eliminarRegistro(dr).

 

Private Sub btnEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEliminar.Click


 If registro < 0 OrElse registro > dt.Rows.Count - 1 Then

    Exit Sub

Else

   Dim codigo As Integer

   codigo = CType(txtCodigo.Text, Integer)

   eliminarRegistro(codigo)

End If

End Sub

 

 

A Rotina eliminarRegistro

 

Esta rotina recebe o datarow atual e efetua a exclusão do registro usando a instrução SQL DELETE FROM. Para definir qual registro deve ser eliminado usamos o código do cliente que é recebido como parâmetro.

 

Private Sub eliminarRegistro(ByVal codigo As Integer)
 

Dim sqlElimina As String

Dim reg As Integer

Dim sqlComando As SqlCommand


sqlElimina = "DELETE FROM Clientes WHERE Codigo = " & codigo

connBD = New SqlConnection(strcon)

sqlComando = New SqlCommand(sqlElimina, connBD)

da.DeleteCommand = sqlComando


Try

   connBD.Open()

   reg = sqlComando.ExecuteNonQuery

   If reg > 0 Then

       MsgBox("Registro Excluído com sucesso.")

       da.Update(ds, "Clientes")

       ds.Tables("Clientes").Reset()

       da.Fill(ds, "Clientes")

       dt = ds.Tables("Clientes")

   End If

Catch ex As Exception

   MessageBox.Show("Erro : " & vbCrLf & ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

connBD.Close()

End Try

 

Observe que em todas as rotinas para manutenção dos dados temos a cláusula Finally na qual a conexão é fechada após a tarefa ser realizada com ou sem sucesso.

 

Você pode pegar o código completo do projeto aqui :  appAcessoBD.zip

 

A forma mostrada no artigo não é a única que pode ser usada, e ,  em um novo artigo estarei mostrando outras formas de realizar a mesma tarefa usando stored procedures.

 

Se você chegou até o fim já sabe como pode efetuar o acesso a manutenção básica de dados sem usar os assistentes do Windows fazendo tudo via código. Dá um pouco mais de trabalho mas o desempenho é melhor.

 

Até o próximo artigo VB.NET