Crud 4 Capas VB.NET y MYSQL

En esta ocasion elaboraremos un proyecto en VB.NET con el modelo 4 capas.
Deberán crear una solución, con 4 proyectos uno para cada capa(cliente,datos,entidad y negocio) y agregar las referencias correspondientes para poder utilizarlas.
Referencias:
Capa cliente->con capa negocio y entidad
Capa Negocio-> con Capa datos y entidad
Capa Datos-> con capa entidad
Capa Entidad-> Esta no necesita de referencias, pues es la respresentacion de los campos de la tabla.

Adicionalmente se creará una clase "coneccion" para hacer uso de  mysql.

Esta es la tabla a utlizar =)

 

A continuación, dejaré el código =) espero les sirva
Codigo del Formulario

Imports Capa_Entidad
Imports Capa_Negocio

Public Class Form1
    Private id_persona As Integer = 0
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        limpiar_todo()
    End Sub
    Private Sub carga_dgv()
        Try
            Dim dt As New DataTable
            Dim per As New NPersona
            dt = per.carga_listado_clientes()
            If dt.Rows.Count <> 0 Then
                Me.dgv_personas.DataSource = dt
                Me.dgv_personas.Columns(0).Visible = False
            Else
                MessageBox.Show("No hay datos en la bd", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message, "Información", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Private Sub limpiar_todo()
        Me.dgv_personas.DataSource = ""
        Me.txt_apellidos.Text = ""
        Me.txt_nombres.Text = ""
        Me.txt_cedula.Text = ""
        Me.txt_celular.Text = ""
        Me.txt_email.Text = ""
        Me.cmb_sexo.Text = ""
        carga_dgv()
        Me.itemeditar.Enabled = False
        Me.itemeliminar.Enabled = False
        Me.itemguardar.Enabled = True
        id_persona = 0
    End Sub
    Private Sub dgv_personas_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv_personas.CellMouseClick
        Dim fila As Integer = e.RowIndex
        If fila >= 0 Then
            id_persona = Me.dgv_personas.Rows(fila).Cells(0).Value
            Me.txt_cedula.Text = Me.dgv_personas.Rows(fila).Cells(1).Value
            Me.txt_nombres.Text = Me.dgv_personas.Rows(fila).Cells(2).Value
            Me.txt_apellidos.Text = Me.dgv_personas.Rows(fila).Cells(3).Value
            Me.txt_email.Text = Me.dgv_personas.Rows(fila).Cells(4).Value
            Me.txt_celular.Text = Me.dgv_personas.Rows(fila).Cells(5).Value
            Me.cmb_sexo.Text = Me.dgv_personas.Rows(fila).Cells(6).Value
            Me.itemguardar.Enabled = False
            Me.itemeditar.Enabled = True
            Me.itemeliminar.Enabled = True
        End If
    End Sub

    Private Sub itemlimpiar_Click(sender As Object, e As EventArgs) Handles itemlimpiar.Click
        limpiar_todo()
    End Sub

    Private Sub itemguardar_Click(sender As Object, e As EventArgs) Handles itemguardar.Click
        Try
            Dim per As New Persona
            per.Id = Nothing
            per.Apellido = Me.txt_apellidos.Text
            per.Nombre = Me.txt_nombres.Text
            per.Cedula = Me.txt_cedula.Text
            per.Celular = Me.txt_celular.Text
            per.Email = Me.txt_email.Text
            per.Sexo = Me.cmb_sexo.Text
            per.Estado = "A"
            'llamamos a guardar de Npersona-->capa negocio
            Dim nper As New NPersona
            nper.nguardar(per)
            MessageBox.Show("Persona almacenada correctamente", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information)
            limpiar_todo()
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub itemeditar_Click(sender As Object, e As EventArgs) Handles itemeditar.Click
        Try
            Dim per As New Persona
            per.Id = id_persona
            per.Apellido = Me.txt_apellidos.Text
            per.Nombre = Me.txt_nombres.Text
            per.Cedula = Me.txt_cedula.Text
            per.Celular = Me.txt_celular.Text
            per.Email = Me.txt_email.Text
            per.Sexo = Me.cmb_sexo.Text
            per.Estado = "A"
            Dim nper As New NPersona
            nper.nactualizar(per)
            MessageBox.Show("Persona actualizada correctamente", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information)
            limpiar_todo()
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub itemeliminar_Click(sender As Object, e As EventArgs) Handles itemeliminar.Click
        Try
            Dim nper As New NPersona
            nper.neliminar(id_persona)
            MessageBox.Show("Persona eliminada correctamente", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information)
            limpiar_todo()
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
End Class

Capa Negocio

Imports Capa_Entidad
Imports Capa_Datos
Public Class NPersona
    Public Function carga_listado_clientes() As DataTable
        Try
            Dim dt As New DataTable
            Dim datosper As New DPersona
            datosper.listado_personas.Fill(dt)
            Return dt
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function
    Public Sub nguardar(ByVal per As Persona)
        Try
            If (campos_completos(per)) Then
                Dim dper As New DPersona
                dper.guardar_personas(per)
            Else
                Throw New Exception("Todos los campos son requeridos")
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
    Public Sub nactualizar(ByVal per As Persona)
        Try
            If (campos_completos(per)) Then
                Dim dper As New DPersona
                dper.actualizar_personas(per)
            Else
                Throw New Exception("Todos los campos son requeridos")
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
    Public Sub neliminar(id_per)
        Try
            Dim dper As New DPersona
            dper.eliminar_personas(id_per)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
    Private Function campos_completos(ByVal per As Persona) As Boolean
        Dim resp As Boolean = True
        If per.Apellido = "" Or per.Nombre = "" Or per.Cedula = "" Or per.Celular = "" Or per.Email = "" Or per.Sexo = "" Then
            resp = False
        End If
        Return resp
    End Function
End Class

Capa Datos

Imports Capa_Entidad
Imports MySql.Data
Imports MySql.Data.Types
Imports MySql.Data.MySqlClient

Public Class DPersona
    Public cmd As MySqlCommand
    Public da As New MySqlDataAdapter
    Public Function listado_personas() As MySqlDataAdapter
        Try
            Dim con As New coneccion
            con.conectar_bd()
            Dim da As MySqlDataAdapter
            cmd = New MySqlCommand
            cmd.CommandText = "Select *  from persona where estado='A'"
            cmd.CommandType = CommandType.Text
            cmd.Connection = con._con
            da = New MySqlDataAdapter(cmd)
            con.cerrar_coneccion()
            Return da
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function
    Public Sub guardar_personas(ByVal per As Persona)
        Try
            Dim con As New coneccion
            Dim query As String = "insert into persona values(?par0,?par1,?par2,?par3,?par4,?par5,?par6,?par7)"
            con.conectar_bd()
            cmd = New MySqlCommand
            cmd.CommandText = query
            cmd.CommandType = CommandType.Text
            cmd.Connection = con._con
            cmd.Parameters.AddWithValue("?par0", CInt(per.Id))
            cmd.Parameters.AddWithValue("?par1", per.Cedula)
            cmd.Parameters.AddWithValue("?par2", per.Nombre)
            cmd.Parameters.AddWithValue("?par3", per.Apellido)
            cmd.Parameters.AddWithValue("?par4", per.Email)
            cmd.Parameters.AddWithValue("?par5", per.Celular)
            cmd.Parameters.AddWithValue("?par6", per.Sexo)
            cmd.Parameters.AddWithValue("?par7", per.Estado)
            cmd.ExecuteNonQuery()
            con.cerrar_coneccion()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
    Public Sub actualizar_personas(ByVal per As Persona)
        Try
            Dim con As New coneccion
            Dim query As String = "update persona set cedula=?par1,nombre=?par2,apellido=?par3,email=?par4,celular=?par5,sexo=?par6,estado=?par7 Where id_persona=?par0"
            con.conectar_bd()
            cmd = New MySqlCommand
            cmd.CommandText = query
            cmd.CommandType = CommandType.Text
            cmd.Connection = con._con
            cmd.Parameters.AddWithValue("?par0", CInt(per.Id))
            cmd.Parameters.AddWithValue("?par1", per.Cedula)
            cmd.Parameters.AddWithValue("?par2", per.Nombre)
            cmd.Parameters.AddWithValue("?par3", per.Apellido)
            cmd.Parameters.AddWithValue("?par4", per.Email)
            cmd.Parameters.AddWithValue("?par5", per.Celular)
            cmd.Parameters.AddWithValue("?par6", per.Sexo)
            cmd.Parameters.AddWithValue("?par7", per.Estado)
            cmd.ExecuteNonQuery()
            con.cerrar_coneccion()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
    Public Sub eliminar_personas(ByVal id_per As Integer)
        Try
            Dim con As New coneccion
            Dim query As String = "update persona set estado=?par1 Where id_persona=?par0"
            con.conectar_bd()
            cmd = New MySqlCommand
            cmd.CommandText = query
            cmd.CommandType = CommandType.Text
            cmd.Connection = con._con
            cmd.Parameters.AddWithValue("?par0", id_per)
            cmd.Parameters.AddWithValue("?par1", "E")
            cmd.ExecuteNonQuery()
            con.cerrar_coneccion()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
End Class

Clase coneccion

Public Class coneccion
    Public _con As MySql.Data.MySqlClient.MySqlConnection
    Private _cadena As String
    Public Sub conectar_bd()
        Dim _estado As String = ""
        Try
            _cadena = ("server=localhost; database=bd_ejemplo_jpa; user id=root; password=")
            _con = New MySql.Data.MySqlClient.MySqlConnection(_cadena)
            _con.Open()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
    Public Sub cerrar_coneccion()
        _con.Close()
    End Sub
End Class

Capa Entidad

Public Class Persona
    Private _id_persona As Integer
    Private _cedula As String
    Private _nombre As String
    Private _apellido As String
    Private _email As String
    Private _celular As String
    Private _sexo As String
    Private _estado As String

    Public Property Id As Integer
        Get
            Return _id_persona
        End Get
        Set(value As Integer)
            _id_persona = value
        End Set
    End Property

    Public Property Cedula As String
        Get
            Return _cedula
        End Get
        Set(value As String)
            _cedula = value
        End Set
    End Property

    Public Property Nombre As String
        Get
            Return _nombre
        End Get
        Set(value As String)
            _nombre = value
        End Set
    End Property
    Public Property Apellido As String
        Get
            Return _apellido
        End Get
        Set(value As String)
            _apellido = value
        End Set
    End Property
    Public Property Email As String
        Get
            Return _email
        End Get
        Set(value As String)
            _email = value
        End Set
    End Property
    Public Property Celular As String
        Get
            Return _celular
        End Get
        Set(value As String)
            _celular = value
        End Set
    End Property
    Public Property Sexo As String
        Get
            Return _sexo
        End Get
        Set(value As String)
            _sexo = value
        End Set
    End Property
    Public Property Estado As String
        Get
            Return _estado
        End Get
        Set(value As String)
            _estado = value
        End Set
    End Property
End Class