Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Globalization
Imports System.IO
Public Class bbdd
    Public Shared Function EjeMySqlHex(BD As DbConnection, sqlh As String) As String
        Try
            Dim sSQL = Extensiones.StringExtensions.ConvierteDeRoman8AWindows(Extensiones.HexToString(sqlh))
            BD.Open()
            Dim mc As New SqlCommand(sSQL, BD)
            Return "Nº Registros afectados: " & mc.ExecuteNonQuery.ToString
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            Try
                BD.Close()
            Catch
            End Try
        End Try
    End Function
    Public Shared Function ObtieneDataTable(ByVal dbConnection As DbConnection, ByVal sqlQuery As String) As DataTable
        ' Crear el objeto DataTable para almacenar los resultados

        Dim ds As New DataSet
        Try
            ' Crear un objeto DbCommand con la instrucción SQL y la conexión proporcionada
            Using dbCommand As DbCommand = dbConnection.CreateCommand()
                dbCommand.CommandText = sqlQuery

                ' Abrir la conexión si no está abierta
                If dbConnection.State <> ConnectionState.Open Then
                    dbConnection.Open()
                End If

                ' Crear un DbDataAdapter para llenar el DataTable
                Using dbDataAdapter As DbDataAdapter = DbProviderFactories.GetFactory(dbConnection).CreateDataAdapter()
                    dbDataAdapter.SelectCommand = dbCommand
                    dbDataAdapter.FillSchema(ds, SchemaType.Mapped, "TABLA")
                    dbDataAdapter.Fill(ds, "TABLA")
                End Using
            End Using
        Catch ex As Exception
            ' Manejar cualquier excepción que pueda ocurrir
            Console.WriteLine("Error: " & ex.Message)
        Finally
            ' Asegurarse de cerrar la conexión si está abierta
            If dbConnection.State = ConnectionState.Open Then
                dbConnection.Close()
            End If
        End Try

        ' Devolver el DataTable con los resultados
        Return ds.Tables("TABLA")
    End Function
    Public Shared Function LeeMysql(ByVal bd As DbConnection, ByVal sqlQuery As String) As String
        Try
            Dim sw As New System.Text.StringBuilder
            Dim sSQL As String = ""
            Dim i As Integer

            sSQL = Extensiones.StringExtensions.ConvierteDeRoman8AWindows(Extensiones.HexToString(sqlQuery))
            Dim dt As DataTable = ObtieneDataTable(bd, sSQL)
            Dim dr As DataRow
            Dim dc As DataColumn
            Dim iInicioBuffer As Integer = 1
            Dim sLinea As String = "NUMREG=" & dt.Rows.Count & "|"
            Dim Tipo As String
            Dim Tamaño As Integer
            For Each dc In dt.Columns
                Select Case dc.DataType
                    Case GetType(String)
                        Tipo = "X"
                        Tamaño = dc.MaxLength
                    Case GetType(DateTime), GetType(Date)
                        Tipo = "F"
                        Tamaño = 20
                    Case Else
                        Tipo = "N"
                        Tamaño = 20
                End Select
                If Tamaño <= 1024 Then
                    sLinea &= dc.ColumnName & "(" & Tipo & iInicioBuffer.ToString & ";" & Tamaño.ToString & ")|"
                    iInicioBuffer += Tamaño
                End If
            Next
            sw.AppendLine(sLinea)
            For Each dr In dt.Rows
                sLinea = ""
                For Each dc In dt.Columns
                    Select Case dc.DataType
                        Case GetType(String)
                            If Not dr(dc) Is DBNull.Value Then
                                sLinea &= dr(dc).ToString.PadRight(dc.MaxLength, " ")
                            Else
                                sLinea &= "".PadRight(dc.MaxLength, " ")
                            End If
                        Case GetType(DateTime), GetType(Date)
                            Dim sFecha As String = "null"
                            Dim dfecha As DateTime
                            If Not dr(dc) Is DBNull.Value AndAlso DirectCast(dr(dc), DateTime) <> DateTime.MinValue Then
                                dfecha = dr(dc)
                                sFecha = dfecha.ToString("yyyy-MM-dd HH:mm:ss")
                            End If
                            sLinea &= sFecha.PadRight(20, " ")
                        Case GetType(Byte())
                        Case GetType(Double)
                            If Not dr(dc) Is DBNull.Value Then
                                Dim doble As Double = dr(dc)
                                sLinea &= doble.ToString(CultureInfo.InvariantCulture).PadRight(20, " ")
                            Else
                                sLinea &= "null".PadRight(20, " ")
                            End If
                        Case Else
                            If Not dr(dc) Is DBNull.Value Then
                                sLinea &= dr(dc).ToString.PadRight(20, " ")
                            Else
                                sLinea &= "null".PadRight(20, " ")
                            End If
                    End Select
                Next
                sw.AppendLine(sLinea)
            Next
            Return sw.ToString
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        End Try
    End Function
    Public Shared Function LeeMysqlByteArray(ByVal bd As DbConnection, ByVal sqlQuery As String) As Byte()
        Try
            Dim ms As New MemoryStream
            Dim sw As New IO.StreamWriter(ms)
            Dim sSQL As String = ""
            Dim i As Integer

            sSQL = Extensiones.StringExtensions.ConvierteDeRoman8AWindows(Extensiones.HexToString(sqlQuery))
            Dim dt As DataTable = ObtieneDataTable(bd, sSQL)
            Dim dr As DataRow
            Dim dc As DataColumn
            Dim iInicioBuffer As Integer = 1
            Dim sLinea As String = "NUMREG=" & dt.Rows.Count & "|"
            Dim Tipo As String
            Dim Tamaño As Integer
            For Each dc In dt.Columns
                Select Case dc.DataType
                    Case GetType(String)
                        Tipo = "X"
                        Tamaño = dc.MaxLength
                    Case GetType(DateTime), GetType(Date)
                        Tipo = "F"
                        Tamaño = 20
                    Case Else
                        Tipo = "N"
                        Tamaño = 20
                End Select
                If Tamaño <= 1024 Then
                    sLinea &= dc.ColumnName & "(" & Tipo & iInicioBuffer.ToString & ";" & Tamaño.ToString & ")|"
                    iInicioBuffer += Tamaño
                End If
            Next
            sw.WriteLine(sLinea)
            For Each dr In dt.Rows
                sLinea = ""
                For Each dc In dt.Columns
                    Select Case dc.DataType
                        Case GetType(String)
                            If Not dr(dc) Is DBNull.Value Then
                                sLinea &= dr(dc).ToString.PadRight(dc.MaxLength, " ")
                            Else
                                sLinea &= "".PadRight(dc.MaxLength, " ")
                            End If
                        Case GetType(DateTime), GetType(Date)
                            Dim sFecha As String = "null"
                            Dim dfecha As DateTime
                            If Not dr(dc) Is DBNull.Value AndAlso DirectCast(dr(dc), DateTime) <> DateTime.MinValue Then
                                dfecha = dr(dc)
                                sFecha = dfecha.ToString("yyyy-MM-dd HH:mm:ss")
                            End If
                            sLinea &= sFecha.PadRight(20, " ")
                        Case GetType(Byte())
                        Case GetType(Double)
                            If Not dr(dc) Is DBNull.Value Then
                                Dim doble As Double = dr(dc)
                                sLinea &= doble.ToString(CultureInfo.InvariantCulture).PadRight(20, " ")
                            Else
                                sLinea &= "null".PadRight(20, " ")
                            End If
                        Case Else
                            If Not dr(dc) Is DBNull.Value Then
                                sLinea &= dr(dc).ToString.PadRight(20, " ")
                            Else
                                sLinea &= "null".PadRight(20, " ")
                            End If
                    End Select
                Next
                sw.WriteLine(sLinea)
            Next
            sw.Close()
            Return ms.ToArray
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        End Try
    End Function
End Class