Imports System.Data.SqlClient
Imports System.Data

Class CTelefonoDAL
  Private strConexion As String

  Public Sub New()
    ' Obtener la cadena de conexión
    strConexion = "Data Source=.\sqlexpress;" &
      "Initial Catalog=bd_telefonos; Integrated Security=True"
  End Sub

  Public Sub New(strCon As String)
    ' Establecer la cadena de conexión especificada
    strConexion = strCon
  End Sub

  Public Function ObtenerFilaTfnos(tfno As String) As CTelefonoBO
    Try
      Using Conexion As New SqlConnection(strConexion)
        Dim OrdenSql As New SqlCommand("stproObtenerFilaTfnos", Conexion)
        OrdenSql.CommandType = CommandType.StoredProcedure
        ' Parámetros
        OrdenSql.Parameters.AddWithValue("@telefono", tfno)
        ' Abrir la base de datos
        Conexion.Open()
        Dim lector As SqlDataReader = OrdenSql.ExecuteReader()
        If lector.Read() Then
          Dim fila As New CTelefonoBO(DirectCast(lector("nombre"), String), DirectCast(lector("direccion"), String), DirectCast(lector("telefono"), String), DirectCast(lector("observaciones"), String))
          Return fila
        End If
        Return Nothing
      End Using
    Catch err As SqlException
      Throw New ApplicationException("Error SELECT telefonos por ID")
    End Try
  End Function

  Public Function ObtenerFilasTfnos() As ColCTelefonos
    Try
      Using Conexion As New SqlConnection(strConexion)
        Dim OrdenSql As New SqlCommand("stproObtenerFilasTfnos", Conexion)
        OrdenSql.CommandType = CommandType.StoredProcedure
        ' Crear una colección para todos los teléfonos
        Dim colTelefonos As New ColCTelefonos()
        ' Abrir la base de datos
        Conexion.Open()
        Dim lector As SqlDataReader = OrdenSql.ExecuteReader()
        While lector.Read()
          Dim fila As New CTelefonoBO(DirectCast(lector("nombre"), String), DirectCast(lector("direccion"), String), DirectCast(lector("telefono"), String), DirectCast(lector("observaciones"), String))
          colTelefonos.Add(fila)
        End While
        Return colTelefonos
      End Using
    Catch err As SqlException
      Throw New ApplicationException("Error SELECT telefonos")
    End Try
  End Function

  Public Function InsertarFilaTfnos(bo As CTelefonoBO) As String
    Try
      Using Conexion As New SqlConnection(strConexion)
        Dim OrdenSql As New SqlCommand("stproInsertarFilaTfnos", Conexion)
        OrdenSql.CommandType = CommandType.StoredProcedure
        ' Parámetros
        OrdenSql.Parameters.AddWithValue("@nombre", bo.Nombre)
        OrdenSql.Parameters.AddWithValue("@direccion", bo.Direccion)
        OrdenSql.Parameters.AddWithValue("@telefono", bo.Telefono)
        OrdenSql.Parameters.AddWithValue("@observaciones", bo.Observaciones)
        ' Abrir la base de datos
        Conexion.Open()
        OrdenSql.ExecuteNonQuery()
        Return DirectCast(OrdenSql.Parameters("@telefono").Value, String)
      End Using
    Catch err As SqlException
      Throw New ApplicationException("Error INSERT telefonos")
    End Try
  End Function

  Public Sub BorrarFilaTfnos(tfno As String)
    Try
      Using Conexion As New SqlConnection(strConexion)
        Dim OrdenSql As New SqlCommand("stproBorrarFilaTfnos", Conexion)
        OrdenSql.CommandType = CommandType.StoredProcedure
        ' Parámetros
        OrdenSql.Parameters.AddWithValue("@telefono", tfno)
        ' Abrir la base de datos
        Conexion.Open()
        OrdenSql.ExecuteNonQuery()
      End Using
    Catch err As SqlException
      Throw New ApplicationException("Error DELETE telefonos")
    End Try
  End Sub

  Public Sub ActualizarNomDirTfnObs(bo As CTelefonoBO, tfnoAntiguo As String)
    Try
      Using Conexion As New SqlConnection(strConexion)
        Dim OrdenSql As New SqlCommand("stproActualizarNomDirTfnObs", Conexion)
        OrdenSql.CommandType = CommandType.StoredProcedure
        ' Parámetros
        OrdenSql.Parameters.AddWithValue("@nombre", bo.Nombre)
        OrdenSql.Parameters.AddWithValue("@direccion", bo.Direccion)
        OrdenSql.Parameters.AddWithValue("@tfnoAntiguo", tfnoAntiguo)
        OrdenSql.Parameters.AddWithValue("@tfnoNuevo", bo.Telefono)
        OrdenSql.Parameters.AddWithValue("@observaciones", bo.Observaciones)
        ' Abrir la base de datos
        Conexion.Open()
        OrdenSql.ExecuteNonQuery()
      End Using
    Catch err As SqlException
      Throw New ApplicationException("Error INSERT telefonos")
    End Try
  End Sub

  Public Sub ActualizarNomDirObs(bo As CTelefonoBO)
    Try
      Using Conexion As New SqlConnection(strConexion)
        Dim OrdenSql As New SqlCommand("stproActualizarNomDirObs", Conexion)
        OrdenSql.CommandType = CommandType.StoredProcedure
        ' Parámetros
        OrdenSql.Parameters.AddWithValue("@nombre", bo.Nombre)
        OrdenSql.Parameters.AddWithValue("@direccion", bo.Direccion)
        OrdenSql.Parameters.AddWithValue("@telefono", bo.Telefono)
        OrdenSql.Parameters.AddWithValue("@observaciones", bo.Observaciones)
        ' Abrir la base de datos
        Conexion.Open()
        OrdenSql.ExecuteNonQuery()
      End Using
    Catch err As SqlException
      Throw New ApplicationException("Error INSERT telefonos")
    End Try
  End Sub
End Class
