VB .Net - Cadastro de Clientes com foto utilizando SQL

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