segunda-feira, 2 de abril de 2012
» »Unlabelled » VB .Net - Cadastro de Clientes com foto utilizando SQL

Anônimo
on
2 comentários
Figura: Estrutura da tabela SQL

Figura: Formulário Windows Form

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class Form1

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Todas as variaveis necessarias para a conexão com o banco de dados.
    '**************************************************************************************************************************************
    'Dim Str As String = "Data Source=" & My.Computer.Name & "\SQLEXPRESS;Initial Catalog=Tech;Integrated Security=True"
    Dim Str As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Tech.mdf;Database=Teck;Trusted_Connection=Yes;"
    Dim Da As New SqlDataAdapter
    Dim Dt As DataTable
    Dim Cn As New SqlConnection(Str)
    Dim Cmd As New SqlCommand

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Load do formulário, carrega todas as informações necessarias.
    '**************************************************************************************************************************************
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        DgvClientes.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        OpenFoto.Filter = "Todos(*.Jpg, *.Png, *.Jpeg, *.Bmp)|*.Jpg; *.Png; *.Jpeg; *.Bmp"

        PCarregaDados()
        PCarregaImagen()

        Me.DgvClientes.Columns(0).Visible = False
        Me.DgvClientes.Columns(1).Visible = False

        Me.CmbOrdenaRegistros.SelectedIndex = 0

        lblregistros.Text = "Total de " & Me.BindingContext(Me.Dt).Count & " registros."

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para procurar a foto do cliente.
    '**************************************************************************************************************************************
    Private Sub btnProcurarFoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcurarFoto.Click

        txtNome.Clear()

        If OpenFoto.ShowDialog = DialogResult.OK Then

            PicFoto.Image = System.Drawing.Image.FromFile(OpenFoto.FileName)

        Else

            MsgBox("Operação cancelada pelo usuário.")

        End If

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para carregar todos os registros.
    '**************************************************************************************************************************************
    Private Sub btnCarregaTodosregistros_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCarregaTodosregistros.Click

        PCarregaDados()
        PCarregaImagen()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para carregar os dados no datagridviwer
    '**************************************************************************************************************************************
    Private Sub PCarregaDados()
        Try
            With Cmd
                .CommandType = CommandType.Text
                .CommandText = "SELECT * from Imagens"
                .Connection = Cn
            End With

            With Da
                .SelectCommand = Cmd
                Dt = New DataTable
                .Fill(Dt)
                DgvClientes.DataSource = Dt
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Chama o procedimento para inserir dados.
    '**************************************************************************************************************************************
    Private Sub btnInserirRegistro_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInserirRegistro.Click

        PInseriRegistros()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para inserir um novo registro.
    '**************************************************************************************************************************************
    Private Sub PInseriRegistros()
        Try
            Cn.Open()

            Dim arrFilename() As String = Split(Text, "\")
            Array.Reverse(arrFilename)

            Dim ms As New MemoryStream
            PicFoto.Image.Save(ms, PicFoto.Image.RawFormat)

            Dim arrImage() As Byte = ms.GetBuffer

            With Cmd
                .CommandType = CommandType.Text
                .CommandText = ""
                .CommandText = "INSERT INTO [Imagens] ([Imagen], [nome], [SobreNome], [Cidade], [Estado], [DtNascimento]) VALUES (@Imagen, '" _
                               & txtNome.Text & "', '" & txtSobreNome.Text & "', '" & txtCidade.Text & "' , '" & txtEstado.Text & "', '" & txtDtNascimento.Text & "');" _
                               & "SELECT * FROM Imagens WHERE (Id = SCOPE_IDENTITY())"
                .Connection = Cn
                .Parameters.Add(New SqlParameter("@Imagen", SqlDbType.Image)).Value = arrImage
            End With

            Cmd.ExecuteNonQuery()
            PCarregaDados()
            MsgBox("Registro Inserido com Sucesso.", MsgBoxStyle.Information)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Cmd.Parameters.Clear()
            Cn.Close()
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Chama o procedimento para alterar registros.
    '**************************************************************************************************************************************
    Private Sub btnAlterar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        PAlteraRegistros()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para alterar um novo registro.
    '**************************************************************************************************************************************
    Private Sub PAlteraRegistros()
        Try
            Cn.Open()

            Dim arrFilename() As String = Split(Text, "\")
            Array.Reverse(arrFilename)
            Dim ms As New MemoryStream
            PicFoto.Image.Save(ms, PicFoto.Image.RawFormat)

            Dim arrImage() As Byte = ms.GetBuffer
            With Cmd
                .CommandType = CommandType.Text
                .CommandText = ""
                .CommandText = "UPDATE [Imagens] SET [Imagen] = @Imagen, [nome] = '" & txtNome.Text & "', [SobreNome] = '" & txtSobreNome.Text & "'  WHERE ([Id] = " & Me.lblCodigo.Text & ");" & _
                    "SELECT * FROM Imagens WHERE (Id = " & Me.lblCodigo.Text & ")"
                .Connection = Cn
                .Parameters.Add(New SqlParameter("@Imagen", SqlDbType.Image)).Value = arrImage
            End With

            Cmd.ExecuteNonQuery()
            PCarregaDados()
            MsgBox("Registro Alterado com Sucesso.", MsgBoxStyle.Information)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Cmd.Parameters.Clear()
            Cn.Close()
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Chama o procedimento para deletar registro e carregar a imagem do cliente.
    '**************************************************************************************************************************************
    Private Sub btnDeletar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeletar.Click

        PDeletadados()
        PCarregaImagen()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para deletar dados.
    '**************************************************************************************************************************************
    Private Sub PDeletadados()
        Try
            Cn.Open()

            With Cmd
                .CommandText = "Delete from Imagens where id = " & Me.lblCodigo.Text
                .Connection = Cn
            End With

            Cmd.ExecuteNonQuery()
            PCarregaDados()
            MsgBox("Registro deletado com Sucesso.", MsgBoxStyle.Information)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Cmd.Parameters.Clear()
            Cn.Close()
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Chama os procedimento altera dados e carrega imagem.
    '**************************************************************************************************************************************
    Private Sub DgvClientes_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgvClientes.CellEndEdit

        Me.txtNome.Text = Me.DgvClientes.CurrentCell.Value
        PAlteraRegistros()
        PCarregaImagen()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Chama o procedimento para carregar a imagem.
    '**************************************************************************************************************************************
    Private Sub DgvClientes_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DgvClientes.Click

        PCarregaImagen()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para carregar a imagem.
    '**************************************************************************************************************************************
    Private Sub PCarregaImagen()
        Try
            Dim ms As New MemoryStream(PExtraerImagen(CInt(DgvClientes.SelectedCells(0).Value)))
            PicFoto.Image = Image.FromStream(ms)

            Me.lblCodigo.Text = Me.DgvClientes.CurrentRow.Cells("id").Value
            Me.txtNome.Text = Me.DgvClientes.CurrentRow.Cells("nome").Value
            Me.txtSobreNome.Text = Me.DgvClientes.CurrentRow.Cells("SobreNome").Value
            Me.txtCidade.Text = Me.DgvClientes.CurrentRow.Cells("Cidade").Value
            Me.txtEstado.Text = Me.DgvClientes.CurrentRow.Cells("Estado").Value
            Me.txtDtNascimento.Text = Me.DgvClientes.CurrentRow.Cells("DtNascimento").Value

        Catch ex As Exception            
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Função para Extrair a imagem.
    '**************************************************************************************************************************************
    Function PExtraerImagen(ByVal Foto As Integer) As Byte()

        With Cmd
            .CommandType = CommandType.Text
            .CommandText = "Select Imagen From Imagens Where id = " & Foto
            .Connection = Cn
        End With

        With Cn
            .Open()
            Dim MyPhoto() As Byte = CType(Cmd.ExecuteScalar(), Byte())
            .Close()
            Return MyPhoto
        End With

    End Function

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para carregar a imagem default.
    '**************************************************************************************************************************************
    Private Sub btnLimparFoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLimparFoto.Click

        PicFoto.Image = Image.FromFile(Application.StartupPath & "\people.ico")

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para realizar a pesquisa.
    '**************************************************************************************************************************************
    Private Sub txtPesquisa_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPesquisa.TextChanged

        CmbOrdenaRegistros.SelectedIndex = 0
        PLocalizaDados()
        PCarregaImagen()

    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para localizar os dados.
    '**************************************************************************************************************************************
    Private Sub PLocalizaDados()
        Try

            Dim CmdSqlBuscaRegistro As String = "SELECT * from Imagens where nome like '%" & txtPesquisa.Text & "%'"

            With Cmd
                .CommandType = CommandType.Text
                .CommandText = CmdSqlBuscaRegistro
                .Connection = Cn
            End With

            With Da
                .SelectCommand = Cmd
                Dt = New DataTable
                .Fill(Dt)
                DgvClientes.DataSource = Dt
            End With

        Catch ex As Exception
            MsgBox("Não foram encontrados registros com este parâmetro.")
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Procedimento para ordenar os registros.
    '**************************************************************************************************************************************
    Private Sub POrdenaRegistro()
        Try

            Dim CmdSqlOrdenaRegistro As String = "SELECT * from Imagens where nome like '" & CmbOrdenaRegistros.Text & "%'"

            With Cmd
                .CommandType = CommandType.Text
                .CommandText = CmdSqlOrdenaRegistro
                .Connection = Cn
            End With

            With Da
                .SelectCommand = Cmd
                Dt = New DataTable
                .Fill(Dt)
                DgvClientes.DataSource = Dt
            End With

        Catch ex As Exception
            MsgBox("Não foram encontrados registros com este parâmetro.")
        End Try
    End Sub

    '**************************************************************************************************************************************
    'Data : 31/03/2012
    'Descrição : Chama os procedimento para locarlizar registros.
    '**************************************************************************************************************************************
    Private Sub CmbOrdena_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmbOrdenaRegistros.SelectedIndexChanged

        If CmbOrdenaRegistros.Text = "Nome por :" Then
        Else
            POrdenaRegistro()
            PCarregaImagen()
        End If

        If Me.DgvClientes.RowCount = 0 Then
            PicFoto.Image = Image.FromFile(Application.StartupPath & "\people.ico")
            MsgBox("Não foram encontrados registros com este parâmetro.")
        End If
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

        ' Vai para o primeiro registro...
        Me.BindingContext(Dt).Position = 0
        PCarregaImagen()

    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

        ' Volta um registro...
        Me.BindingContext(Dt).Position -= 1
        PCarregaImagen()

    End Sub


    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

        ' Avança um registro...
        Me.BindingContext(Dt).Position += 1
        PCarregaImagen()

    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

        ' Vai para o último registro...
        Me.BindingContext(Dt).Position = Dt.Rows.Count
        PCarregaImagen()

    End Sub

End Class

Link do Projeto para download: http://www.4shared.com/rar/tJtdpbea/Cadastro_Clientes_SQL.html
Espero ter ajudado e até mais...


Postado por Anônimo às 18:54
Share:

2 comentários:

  1. Felipe28 de abril de 2013 às 08:18

    Amigo faz um video ensinando como fazer.

    ResponderExcluir
  2. Anônimo29 de abril de 2013 às 07:53

    Bom dia Felipe,

    Assim que eu tiver um tempo aqui sobrando irei fazer um video explicando como criar o sistema de cadastro de cliente com Sql.

    Atenciosamente,
    Carlos Henrique Pereira - Analista de TI.

    ResponderExcluir

Assinar: Postar comentários (Atom)