VB.NET 2005 - Criando Objetos básicos de Dados com ADO.NET

Tudo possui um alicerce sobre o qual se sustenta. Sem querer filosofar sobre o tema o desenvolvimento de sistemas também deve ter alicerces sobre os quais o desenvolvedor se apóia para criar uma solução de TI.

Neste artigo vou mostrar algumas tarefas básicas que podem ser consideradas os rudimentos que qualquer desenvolvedor deve conhecer para desenvolver aplicações usando Visual Basic 2005.

Para testar os projetos tratados neste artigo você vai precisar do Visual Basic 2005 Express Edition e do SQL Server 2005 Express.

Em todos os projetos iremos acessar uma base de dados SQL Server chamada : cadastro.mdf. Os dados serão extraídos de uma tabela chamada Clientes. A base de dados e a tabela foram criadas usando o SQL Server 2005.  A seguir estou reproduzindo novamente os passos para criação destes artefatos.

Nota: Para saber mais leia também os artigos a seguir:

Criando a base de dados no SQL Server 2005 Express

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

- 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 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.

A string de conexão com o banco de dados usada será armazenada usando o recurso My.Settings conforme os seguintes passos:

  1. Clique com o botão direito sobre My Project na janela Solution Explorer e selecione Open.
  2. Na janela a seguir selecione Settings e informe um nome para a string de conexão e para a string SQL na coluna Name. Eu usei os nomes connSQL. Salve a operação. Pronto ! já podemos recuperar esta informação a partir de My.Settings em tempo de execuçã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 e digite alguns valores conforme abaixo:

1- Preenchendo um DataGridView com um DataReader

Se a sua aplicação necessita apenas exibir dados na forma de uma tabela, uma solução que não consome muito recursos é usar um grid de somente-leitura que deverá ser preenchido via código.

Você já deve saber que o DataGridView substituiu o DataGrid das versões  do VS 2002 e VS 2003. O controle DataGridView preenchido por um DataReader pode ser comparado com o controle Grid do VB6.

Para obter os dados fonte de dados vamos usar o método ExecuteReader. O método ExecuteReader envia um CommandText (uma instrução SQL ou stored Procedure) para a conexão definida e cria um DataReader.

Nota: O método ExecuteXmlReader envia uma instrução SQL/Stored Procedure (CommandText) para a conexão definida e cria um objeto XmlReader. Leia o artigo:  VB.NET  2005 -  Lendo XML a partir de XMLReader.

Abra ou crie um novo projeto no VB 2005 com o nome dgvReader e no formulário padrão inclua um componente DataGridView e um componente Button.

A seguir inclua o seguinte código no evento Click do Botão de comando:

 Private Sub btnDgv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDgv.Click

        'Preecher um DataGridView de somente-leitura usando um SqlDataReader
        Dim cnClientes As SqlConnection = New SqlConnection(My.Settings.connSQL)

        Try
            Dim strSql As String = "SELECT * FROM Clientes"
            Dim cmdGrid As New SqlCommand(strSql, cnClientes)

            cmdGrid.CommandType = CommandType.Text
            cnClientes.Open()

            'cria um DataReader com o nome de drDgv
            Dim drDgv As SqlDataReader = cmdGrid.ExecuteReader
            Dim intCol As Integer

            With drDgv 
                'se há linhas no datareader então executa o código
                If .HasRows Then
                    dgvReader.Rows.Clear()
                    'Inclui uma definição para a colunao: CampoNome, e ColunaNome
                    For intCol = 0 To .FieldCount - 1
                        dgvReader.Columns.Add(.GetName(intCol), .GetName(intCol))
                    Next
                    'Define a largura da coluna com base na largura do cabeçalho
                    dgvReader.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

                     'percorre o datareader enquanto houver dados
                     While .Read
                          'Obtem os dados como em um objeto array
                          Dim objCelulas(intCol) As Object
                          .GetValues(objCelulas)
                          'Inclui uma linha de cada vez no datagridview
                          dgvReader.Rows.Add(objCelulas)
                      End While

                     'fecha o datareader
                    .Close()
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
           'esse código sempre será executado fechando a conexão
            cnClientes.Close()
        End Try
    End Sub

A execução do projeto terá a seguinte tela como resultado:

Note que você pode usar um recurso do DataGridView:

Clicar nas colunas para ordenar os dados.

A cada clique a ordem se alterna de descendente para ascendente.

2- Retornando uma única linha de dados (DataRow)

As vezes você quer somente obter uma única linha do arquivo. E como podemos fazer isto ?

Você pode incluir a cláusula CommandBehavior.SingleRow ao objeto SqlDataReader para retornar a primeira linha do conjunto de registros definido por uma instrução SQL ou stored procedure.O código a seguir retorna a primeira linha da tabela Clientes do banco de dados cadastro.mdf se uma cláusula WHERE não estiver definida.

Se você definir uma cláusula WHERE será retornada a primeira linha que atender ao critério definido pela cláusula..

Incluindo a propriedade CommandBehavior.CloseConnection fará com que a conexão seja fechada de forma automática quando você fechar o objeto SqlDataReader.

Vou aproveitar o projeto anterior e incluir mais um botão de comando(Button) e um TextBox para exibir os dados retornados.

 Private Sub btnLinhaUnica_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinhaUnica.Click

        'defina a conexão com a base de dados
        Dim cnClientes As SqlConnection = New SqlConnection(My.Settings.connSQL)

        Try

            'abre a conexao
            cnClientes.Open()

            'Define um objeto SqlCommand
            Dim strSQL As String = "SELECT Codigo,Nome,Cidade,Estado FROM Clientes"

            'Podemos usar um critério com a cláusula WHERE
            'strSQL += " WHERE Codigo = 75"
            Dim cmdRow As SqlCommand = New SqlCommand(strSQL, cnClientes)
            cmdRow.CommandType = CommandType.Text

            'cria um datareader extraindo uma única linha data tabela
            Dim drLinha As SqlDataReader = cmdRow.ExecuteReader(CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)

            With drLinha
                'verifica se há linhas no datareader
                If .HasRows Then
                    'inicia a leitura
                    .Read()
                    'vou obter o nome, a cidade e o estado e exibir no textbox
                    Dim Nome As String = .GetString(1)
                    Dim cidade As String = .GetString(2)
                    Dim Estado As String = .GetString(3)
                    txtLinha.Text = Nome + " " + cidade + " " + Estado
                End If
         'Fecha o DataReader e a conexao
                .Close()
            End With
        Catch ex As Exception
            MsgBox(ex.Message + ex.StackTrace)
        Finally
            'Se a conexão ainda estive aberta fecha
            cnClientes.Close()
        End Try
    End Sub

A seguir temos o resultado da execução do projeto.

3- Retornando um valor escalar

O método ExecuteScalar retorna o valor da primeira coluna da primeira linha do conjunto de registros. Ele é o meio mais rápido de você acessar e obter uma informação de qualquer fonte de dados.

A forma mais comum de usar este método é para retornar uma linha e uma coluna da fonte de dados ou para obter valores únicos das seguintes funções SQL agregadas : COUNT, SUM, MIN ou MAX.

Geralmente o roteiro básico consiste em efetuar as seguintes etapas:

  ' especifica a fonte de dados (conexão com meu SQL Server)
   Dim Conexao as new SqlConnection ("server=( local )\NetSDK; trusted_connection=yes; database=" )

   ' define  uma consulta SQL (conta quantos clientes são do estado de SP)
   Dim consulta as string = "SELECT COUNT ( * ) FROM Clientes WHERE Estado='SP' "

   ' inicia o objeto command especificando a consulta e a conexão
   Dim comando as new SqlCommand ( consulta, Conexao)

   ' Abre a conexão com a fonte de dados
   Conexao.Open ( )

   ' executa o comando (Você tem que efetuar a conversão de dados para inteiro)
   Dim contador as int = Ctype ( comando .ExecuteScalar ( ), int )

   ' fecha a conexão
   Conexao.Close ( )

O exemplo que vou mostrar aproveita a estrutura do projeto já criado anteriormente e usa este método para retornar a quantidade de registros da tabela Clientes.

Vou incluir um botão de comando e uma Label(lblTotal) para exibir o resultado no formulário do projeto. A seguir inclua o código abaixo no evento Click do botão de comando:

Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotal.Click

        'defina a conexão com a base de dados
        Dim cnClientes As SqlConnection = New SqlConnection(My.Settings.connSQL)
        Try
            'abre a conexao
            cnClientes.Open()
            'Define um objeto SqlCommand usando uma instrução SQL com uma função agregada COUNT
            Dim strSQL As String = "SELECT COUNT(*) FROM Clientes"

            'cria um objeto Command para a conexão usando a instrução SQL definida
            Dim cmdScalar As SqlCommand = New SqlCommand(strSQL, cnClientes)
            cmdScalar.CommandType = CommandType.Text

            'executa o comando retornando o total de registros da tabela Clientes
            Dim total As Integer = CInt(cmdScalar.ExecuteScalar)

            lblTotal.Text = total + " registros."

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cnClientes.Close()
        End Try

    End Sub

O resultado pode ser visto como na figura a seguir:

4- Executando consultas que não retornam dados

Haverá ocasiões que não será necessário obter dados como retorno na execução de suas tarefas com base de dados. Geralmente estes casos envolvem as consultas ou stores procedures que atualizam tabelas realizando operações de Inclusão, Atualização e Exclusão (INSERT, UPDATE e DELETE).

Para realizar tais operações você usa o método ExecuteNonQuery.  Ele executa uma instrução Transact-SQL contra uma conexão retornando o número de linhas afetadas. Vejamos alguns exemplos aplicados a tabela Clientes do banco de dados Cadastro.mdf usado neste artigo:

Private Sub btnOperacoes_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOperacoes.Click

        'define a conexão com a base de dados
        Dim cnClientes As SqlConnection = New SqlConnection(My.Settings.connSQL)

        Dim registrosAfetados As Integer
        Try

            cnClientes.Open()

            'Define e executa o comando  INSERT via SqlCommand inclui um novo registro na tabela Clientes
            'O campo Codigo é do tipo identity e por este motivo é incluído automaticamente pelo SQL Server
            Dim strSQL As String = "INSERT Clientes (Nome,Endereco,Cidade,Estado,Email,Nascimento,Comentarios) " + _
                                               "VALUES ('TESTE', 'Rua Teste , 100','Lins','SP','teste@t.com','05/05/1975','teste')"
            Dim cmdTarefa As SqlCommand = New SqlCommand(strSQL, cnClientes)
            cmdTarefa.CommandType = CommandType.Text
            registrosAfetados = cmdTarefa.ExecuteNonQuery

            'Exibe os dados no DataGridView
            carregaDataGridView()
            Application.DoEvents()
            MsgBox("Clique OK para continuar com as operações...")

            'Define executa o comando UPDATE via SqlCommand alterando o nome do registro de codigo igual a 25
            strSQL = "UPDATE Clientes SET Nome = 'NOME ALTERADO' WHERE Codigo = 25"
            cmdTarefa.CommandText = strSQL
            registrosAfetados += cmdTarefa.ExecuteNonQuery

            'Exibe os dados no DataGridView
            carregaDataGridView()
            Application.DoEvents()
            MsgBox("Clique OK para continuar com as operações...")

            'Define e executa o comando DELETE via SqlCommand excluindo o registro de código igual a 25
            strSQL = "DELETE FROM Clientes WHERE Codigo = 25"
            cmdTarefa.CommandText = strSQL
            registrosAfetados += cmdTarefa.ExecuteNonQuery

            carregaDataGridView()

        Catch ex As Exception
            MsgBox(ex.Message + " " + ex.StackTrace)
        Finally
            'fecha a conexão sempre
            cnClientes.Close()
            Dim strMsg As String
            If registrosAfetados = 3 Then
                strMsg = "INSERT, UPDATE, e DELETE foram executados com sucesso..."
            Else
                strMsg = "Ocorreu uma falha nas operações de INSERT e/ou UPDATE e/ou DELETE."
            End If
        End Try
    End Sub

O código mostra que estamos executando 3 operações básicas na tabela Clientes usando comandos SQL:

  1.  Dim strSQL As String = "INSERT Clientes (Nome,Endereco,Cidade,Estado,Email,Nascimento,Comentarios) " + _
                                        "VALUES ('TESTE', 'Rua Teste , 100','Lins','SP','teste@t.com','05/05/1975','teste')"
  2.  strSQL = "UPDATE Clientes SET Nome = 'NOME ALTERADO' WHERE Codigo = 25"
  3.  strSQL = "DELETE FROM Clientes WHERE Codigo = 25"

Para efetivar as operações usamos o método ExecuteNonQuery e obtemos o número de registros afetados pela instrução SQL. No caso estamos incluindo, atualizando e excluindo apenas um registro, logo, no total teremos 3 registros afetados, um para cada operação.

Nota: Para as instruções UPDATE, INSERT, e DELETE o valor retornado é o número de linhas afetadas pelo comando. Para outros tipos de instruções o valor retornado é -1.

Ao final verificamos se o total de registros afetados é igual a 3 , o que indica que todos as operações foram realizadas com sucesso.

Abaixo temos a tala obtida quando a execução dos comandos SQL via ExecuteNonQuery.

Como acabamos de mostrar criamos objetos básicos de acesso a dados sem a utilização de assistentes para realizar tarefas básicas geralmente necessárias quando do tratamento da informação.

Pegue o projeto completo aqui : objBasicosDados.zip

Bom estudo e até o próximo artigo VB.NET...

José Carlos Macoratti