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

Leer, escribir y vaciar archivo de texto Vb.net

Vamos a usar un ejemplo con 3 cajas de texto. Simulando un número de factura, que al dar click en aumentar se incrementará en 1 el  número de la tercer caja de texto.

 

Hay funciones para verificar si la carpeta y archivo de texto existen, para crearlos en caso de que no, para borrar el contenido del archivo y volver a escribir, cabe recalcar que se trabajará con la primer linea del archivo.

Dudas o preguntas, comenten =) 

A continuación, el código.

 Private Sub btn_guardar_Click(sender As Object, e As EventArgs) Handles btn_guardar.Click
        If (Me.txt_serie1.Text = "" Or Me.txt_serie2.Text = "" Or Me.txt_numero_serie.Text = "") Then
            MsgBox("Faltan algunos campos")
        Else
            If verifica_existe_carpeta() Then
                If verifica_existe_archivo() Then
                    '   MsgBox("Archivo existe")
                    borra_info_fac()
                    escribir_numero_de_factura(Me.txt_serie1.Text, Me.txt_serie2.Text, Me.txt_numero_serie.Text)
                Else
                    '   MsgBox("Archivo no existe")
                    crear_archivo()
                    escribir_numero_de_factura(Me.txt_serie1.Text, Me.txt_serie2.Text, Me.txt_numero_serie.Text)
                End If
            Else
                ' MsgBox("Carpeta no existe")
                crear_carpeta()
                crear_archivo()
                escribir_numero_de_factura(Me.txt_serie1.Text, Me.txt_serie2.Text, Me.txt_numero_serie.Text)
            End If
            cargar_numero_fac()
        End If
    End Sub
    Private Function verifica_existe_carpeta() As Boolean 'verifica si existe el archivo
        Dim resp As Boolean = False
        Dim ruta As String = "C:\iconos"
        If Directory.Exists(ruta) = True Then
            resp = True
        End If
        Return resp
    End Function
    Private Function verifica_existe_archivo() As Boolean 'verifica si existe el archivo
        Dim resp As Boolean = False
        Dim ruta As String = "C:\iconos\num_serie.txt"
        If File.Exists(ruta) = True Then
            resp = True
        End If
        Return resp
    End Function
    Private Sub cargar_numero_fac() 'carga el numero de la factura
        If verifica_existe_carpeta() And verifica_existe_archivo() Then
            Dim objStreamReader As StreamReader
            Dim strLine As String
            objStreamReader = New StreamReader("C:\iconos\num_serie.txt")
            'extraemos la primer linea de texto
            strLine = objStreamReader.ReadLine
            objStreamReader.Close()
            If strLine = "" Then
                Me.txt_serie1.Text = "000"
                Me.txt_serie2.Text = "000"
                Me.txt_numero_serie.Text = "000000000"
            Else
                Dim Arreglo As String() = strLine.Split("-")
                Me.txt_serie1.Text = Arreglo(0)
                Me.txt_serie2.Text = Arreglo(1)
                Me.txt_numero_serie.Text = Arreglo(2)
            End If
        Else
            ' MsgBox("Carpeta y/o archivo no existe")
            Me.txt_serie1.Text = "000"
            Me.txt_serie2.Text = "000"
            Me.txt_numero_serie.Text = "000000000"
        End If
    End Sub
    Private Sub crear_carpeta()
        Dim ruta As String = "C:\"
        My.Computer.FileSystem.CreateDirectory(ruta & "\iconos")
    End Sub
    Private Sub crear_archivo()
        Dim ruta As String = "C:\iconos\num_serie.txt"
        Dim fi As FileInfo = New FileInfo(ruta)
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cargar_numero_fac()
    End Sub
    Private Sub escribir_numero_de_factura(ByVal serie1 As String, ByVal serie2 As String, ByVal num_serie As String)
        Dim ruta As String = "C:\iconos\num_serie.txt"
        Dim sw As StreamWriter
           sw = File.CreateText(ruta)
        sw.WriteLine(serie1 & "-" & serie2 & "-" & num_serie)
        sw.Flush()
        sw.Close()
    End Sub
    Private Sub borra_info_fac()
        Using file As New IO.StreamWriter("C:\iconos\num_serie.txt")
            file.Flush()
        End Using
    End Sub
    Private Sub incrementa_num_fac()
        ' extraemos contenido
        Dim objStreamReader As StreamReader
        Dim strLine As String
        objStreamReader = New StreamReader("C:\iconos\num_serie.txt")
        'Leemos la primer linea de texto
        strLine = objStreamReader.ReadLine
        'cerramos el archivo.
        objStreamReader.Close()
        Dim Arreglo As String() = strLine.Split("-")' separamos la cadena 
        Dim s1 As String = Arreglo(0)
        Dim s2 As String = Arreglo(1)
        Dim s3 As String = CStr(CInt(Arreglo(2)) + 1)'sumamos en 1
        '-------------borramos contenido
        borra_info_fac()
        '---------------actualiza
        escribir_numero_de_factura(s1, s2, s3)
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        incrementa_num_fac()
        cargar_numero_fac()
    End Sub