Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
Imports System.Data.EntityClient
Imports System.Data.SqlClient
Imports Oracle.ManagedDataAccess.Client
Imports System.Data.Objects

Public Class bbdd
    Public Shared Function SQLADatarow(ByVal Conexion As System.Data.Common.DbConnection, ByVal ClausulaSQL As String) As DataRow
        If Conexion.GetType Is GetType(OleDbConnection) Then
            Throw New Exception("Tipo no soportado")
        ElseIf Conexion.GetType Is GetType(MySqlConnection) Then
            Return SQLADatarowMysql(Conexion, ClausulaSQL)
        ElseIf Conexion.GetType Is GetType(OracleConnection) Then
            Return SQLADatarowOracle(Conexion, ClausulaSQL)
        Else
            Throw New Exception("Tipo no soportado")
        End If
    End Function
    Private Shared Function SQLADatarowOracle(ByVal Conexion As OracleConnection, ByVal ClausulaSQL As String) As DataRow
        Try
            Dim ad As OracleDataAdapter, dsDatos As New DataSet
            ad = New OracleDataAdapter(ClausulaSQL, Conexion)
            ad.Fill(dsDatos)
            If dsDatos.Tables(0).Rows.Count > 0 Then
                SQLADatarowOracle = dsDatos.Tables(0).Rows(0)
            Else
                SQLADatarowOracle = Nothing
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message & vbCrLf & "SQL: " & ClausulaSQL)
        End Try
    End Function
    Private Shared Function SQLADatarowMysql(ByVal Conexion As MySqlConnection, ByVal ClausulaSQL As String) As DataRow
        Try
            Dim ad As MySqlDataAdapter, dsDatos As New DataSet
            ad = New MySqlDataAdapter(ClausulaSQL, Conexion)
            ad.Fill(dsDatos)
            If dsDatos.Tables(0).Rows.Count > 0 Then
                SQLADatarowMysql = dsDatos.Tables(0).Rows(0)
            Else
                SQLADatarowMysql = Nothing
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message & vbCrLf & "SQL: " & ClausulaSQL)
        End Try
    End Function
    Public Shared Function EjecutaComandoMysql(ByVal Conexion As MySqlConnection, ClausulaSQL As String, Optional Parametros() As Object = Nothing) As Integer
        Dim bCerrar As Boolean
        Try
            If Conexion.State = ConnectionState.Closed Then
                bCerrar = True
                Conexion.Open()
            End If
            Dim i As Integer
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Do While ClausulaSQL.Contains("@")
                ClausulaSQL = ClausulaSQL.Split("@")(0) & "?" & i.ToString & ClausulaSQL.Split("@", 2, StringSplitOptions.None)(1)
                i += 1
            Loop
            'ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Dim mc As New MySqlCommand(ClausulaSQL, Conexion)
            Dim mp As MySqlParameter
            mc.Prepare()
            If Not Parametros Is Nothing Then
                For i = 0 To Parametros.Length - 1
                    Select Case Parametros(i).GetType
                        Case GetType(DateTime)
                            mp = New MySqlParameter("?" & i.ToString, MySqlDbType.DateTime)
                            mp.Value = Parametros(i)
                            mc.Parameters.Add(mp)
                        Case GetType(Boolean)
                            mp = New MySqlParameter("?" & i.ToString, MySqlDbType.Bit)
                            mp.Value = Parametros(i)
                            mc.Parameters.Add(mp)
                        Case Else
                            mp = New MySqlParameter("?" & i.ToString, Parametros(i))
                            mc.Parameters.Add(mp)
                    End Select

                Next
            End If
            Return mc.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            Try
                If bCerrar Then
                    Conexion.Close()
                End If
            Catch ex As Exception
            End Try
        End Try
    End Function
    Public Shared Function EjecutaComandoSql(ByVal Conexion As SqlConnection, ClausulaSQL As String, Optional Parametros() As Object = Nothing) As Integer
        Dim bCerrar As Boolean
        Try
            If Conexion.State = ConnectionState.Closed Then
                bCerrar = True
                Conexion.Open()
            End If
            Dim i As Integer
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Do While ClausulaSQL.Contains("@")
                ClausulaSQL = ClausulaSQL.Split("@")(0) & "?" & i.ToString & ClausulaSQL.Split("@", 2, StringSplitOptions.None)(1)
                i += 1
            Loop
            'ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Dim mc As New SqlCommand(ClausulaSQL, Conexion)
            Dim mp As SqlParameter
            mc.Prepare()
            If Not Parametros Is Nothing Then
                For i = 0 To Parametros.Length - 1
                    Select Case Parametros(i).GetType
                        Case GetType(DateTime)
                            mp = New SqlParameter("?" & i.ToString, MySqlDbType.DateTime)
                            mp.Value = Parametros(i)
                            mc.Parameters.Add(mp)
                        Case GetType(Boolean)
                            mp = New SqlParameter("?" & i.ToString, MySqlDbType.Bit)
                            mp.Value = Parametros(i)
                            mc.Parameters.Add(mp)
                        Case Else
                            mp = New SqlParameter("?" & i.ToString, Parametros(i))
                            mc.Parameters.Add(mp)
                    End Select

                Next
            End If
            Return mc.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            Try
                If bCerrar Then
                    Conexion.Close()
                End If
            Catch ex As Exception
            End Try
        End Try
    End Function

    Public Shared Function ObtienePrimeraFilaMysql(ByVal Conexion As MySqlConnection, ClausulaSQL As String, Optional Parametros() As Object = Nothing) As DataRow
        Dim bCerrar As Boolean
        Try
            If Conexion.State = ConnectionState.Closed Then
                bCerrar = True
                Conexion.Open()
            End If
            Dim i As Integer
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Do While ClausulaSQL.Contains("@")
                ClausulaSQL = ClausulaSQL.Split("@")(0) & "?" & i.ToString & ClausulaSQL.Split("@", 2, StringSplitOptions.None)(1)
                i += 1
            Loop
            Dim mc As New MySqlCommand(ClausulaSQL, Conexion)
            Dim mp As MySqlParameter
            mc.Prepare()
            If Not Parametros Is Nothing Then
                For i = 0 To Parametros.Length - 1
                    mp = New MySqlParameter("?" & i.ToString, Parametros(i))
                    mc.Parameters.Add(mp)
                Next
            End If
            Dim dr As MySql.Data.MySqlClient.MySqlDataReader = mc.ExecuteReader()
            Dim dt As New DataTable
            dt.Load(dr)
            If dt.Rows.Count > 0 Then
                Return dt(0)
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw ex
        Finally
            Try
                If bCerrar Then
                    Conexion.Close()
                End If
            Catch ex As Exception
            End Try
        End Try
    End Function
    Public Shared Function ObtienePrimeraFilaSQLServer(ByVal Conexion As Data.SqlClient.SqlConnection, ClausulaSQL As String, Optional Parametros() As Object = Nothing) As DataRow
        Dim bCerrar As Boolean
        Try
            If Conexion.State = ConnectionState.Closed Then
                bCerrar = True
                Conexion.Open()
            End If
            Dim i As Integer
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Do While ClausulaSQL.Contains("@")
                ClausulaSQL = ClausulaSQL.Split("@")(0) & "?" & i.ToString & ClausulaSQL.Split("@", 2, StringSplitOptions.None)(1)
                i += 1
            Loop
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Dim mc As New Data.SqlClient.SqlCommand(ClausulaSQL, Conexion)
            Dim mp As SqlParameter
            mc.Prepare()
            If Not Parametros Is Nothing Then
                For i = 0 To Parametros.Length - 1
                    mp = New SqlParameter("@" & i.ToString, Parametros(i))
                    mc.Parameters.Add(mp)
                Next
            End If
            Dim dr As SqlDataReader = mc.ExecuteReader()
            Dim dt As New DataTable
            dt.Load(dr)
            If dt.Rows.Count > 0 Then
                Return dt(0)
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw ex
        Finally
            Try
                If bCerrar Then
                    Conexion.Close()
                End If
            Catch ex As Exception
            End Try
        End Try
    End Function
    Public Shared Function ObtieneTablaMysql(ByVal Conexion As MySqlConnection, ClausulaSQL As String, Optional Parametros() As Object = Nothing) As DataTable
        Dim bCerrar As Boolean
        Try
            If Conexion.State = ConnectionState.Closed Then
                bCerrar = True
                Conexion.Open()
            End If
            Dim i As Integer
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Do While ClausulaSQL.Contains("@")
                ClausulaSQL = ClausulaSQL.Split("@")(0) & "?" & i.ToString & ClausulaSQL.Split("@", 2, StringSplitOptions.None)(1)
                i += 1
            Loop
            Dim mc As New MySqlCommand(ClausulaSQL, Conexion)
            Dim mp As MySqlParameter
            mc.Prepare()
            If Not Parametros Is Nothing Then
                For i = 0 To Parametros.Length - 1
                    mp = New MySqlParameter("?" & i.ToString, Parametros(i))
                    mc.Parameters.Add(mp)
                Next
            End If
            Dim dr As MySql.Data.MySqlClient.MySqlDataReader = mc.ExecuteReader()
            Dim dt As New DataTable
            '  dt.BeginLoadData()
            dt.Load(dr)
            '    dt.EndLoadData()
            Return dt
        Catch ex As Exception
            Throw ex
        Finally
            Try
                If bCerrar Then
                    Conexion.Close()
                End If
            Catch ex As Exception
            End Try
        End Try
    End Function


    Public Shared Function ObtieneTablaSqlServer(ByVal Conexion As SqlConnection, ClausulaSQL As String, Optional Parametros() As Object = Nothing) As DataTable
        Dim bCerrar As Boolean
        Try
            If Conexion.State = ConnectionState.Closed Then
                bCerrar = True
                Conexion.Open()
            End If
            Dim i As Integer
            ClausulaSQL = ClausulaSQL.Replace("?", "@")
            Do While ClausulaSQL.Contains("@")
                ClausulaSQL = ClausulaSQL.Split("@")(0) & "?" & i.ToString & ClausulaSQL.Split("@", 2, StringSplitOptions.None)(1)
                i += 1
            Loop
            Dim mc As New SqlCommand(ClausulaSQL, Conexion)
            Dim mp As SqlParameter
            mc.Prepare()
            If Not Parametros Is Nothing Then
                For i = 0 To Parametros.Length - 1
                    mp = New SqlParameter("?" & i.ToString, Parametros(i))
                    mc.Parameters.Add(mp)
                Next
            End If
            Dim dr As SqlDataReader = mc.ExecuteReader()
            Dim dt As New DataTable
            '  dt.BeginLoadData()
            dt.Load(dr)
            '    dt.EndLoadData()
            Return dt
        Catch ex As Exception
            Throw ex
        Finally
            Try
                If bCerrar Then
                    Conexion.Close()
                End If
            Catch ex As Exception
            End Try
        End Try
    End Function

    Private Shared Function ObtienePrimeraFilaMysql(ByVal Conexion As MySqlConnection, ClausulaSQL As String, Parametros() As MySql.Data.MySqlClient.MySqlParameter) As DataRow
        Try
            Dim ad As New MySqlDataAdapter(ClausulaSQL, Conexion)
            Dim ds As New DataSet
            ad.FillSchema(ds, SchemaType.Mapped)

            For Each par In Parametros
                ad.SelectCommand.Parameters.Add(par)
            Next
            ad.Fill(ds)
            Return ds.Tables(0).Rows(0)
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Shared Function GetMySQLType(ByVal sysType As Type) As MySqlDbType
        If sysType Is GetType(String) Then
            Return MySqlDbType.VarChar
        ElseIf sysType Is GetType(Integer) Then
            Return MySqlDbType.Int64
        ElseIf sysType Is GetType(Boolean) Then
            Return MySqlDbType.Byte
        ElseIf sysType Is GetType(Date) Then
            Return MySqlDbType.Date
        ElseIf sysType Is GetType(Char) Then
            Return MySqlDbType.Byte
        ElseIf sysType Is GetType(Decimal) Then
            Return MySqlDbType.Decimal
        ElseIf sysType Is GetType(Double) Then
            Return MySqlDbType.Double
        ElseIf sysType Is GetType(Single) Then
            Return MySqlDbType.LongBlob
        ElseIf sysType Is GetType(Byte()) Then
            Return MySqlDbType.Binary
        ElseIf sysType Is GetType(Guid) Then
            Return MySqlDbType.Guid
        Else
            Throw New Exception("Tipo no soportado")
        End If
    End Function


    Public Shared Function GeneraConnectionStringMySQLPasswordClara(Servidor As String, Esquema As String, usuario As String, password As String, Optional Puerto As Integer = 13306, Optional SegundosTimeout As Integer = 60, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "") As String
        Dim sCadenaDeConexion As String
        sCadenaDeConexion = "server=" & Servidor &
                           ";database=" & Esquema &
                           ";user=" & usuario &
                           ";Password=" & password &
                           ";port=" & Puerto.ToString &
                           ";default command Timeout=" & SegundosTimeout.ToString & ";" &
                           ";Connection Timeout=" & SegundosTimeout.ToString & ";"
        If Pooling Then sCadenaDeConexion &= "Pooling=True;"
        If SSL Then sCadenaDeConexion &= "SSL Mode=Required;"
        If FicheroCertificado <> "" Then sCadenaDeConexion &= "CertificateFile=" & FicheroCertificado & ";"
        If FicheroCertificado <> "" Then sCadenaDeConexion &= "CertificateFile=" & FicheroCertificado & ";"
        If ContraseñaCertificado <> "" Then sCadenaDeConexion &= "CertificatePassword=" & ContraseñaCertificado & ";"

        Return sCadenaDeConexion
    End Function

    Public Shared Function GeneraConnectionStringMySQL(Servidor As String, Esquema As String, usuario As String, passwordEnc As String, Optional Puerto As Integer = 13306, Optional SegundosTimeout As Integer = 60, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "") As String
        Dim cadenaDeConexion As String = ""
        Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim spassword As String = crypt.FEncS(passwordEnc, juegoDeCaracteresDeOrigen, juegoDeCaracteresDeDestino, -875421649)
        cadenaDeConexion = "server=" & Servidor &
                           ";database=" & Esquema &
                           ";user=" & usuario &
                           ";Password=" & spassword &
                           ";port=" & Puerto.ToString &
                           ";default command Timeout=" & SegundosTimeout.ToString & ";" &
                           ";Connection Timeout=" & SegundosTimeout.ToString & ";"
        If Pooling Then cadenaDeConexion &= "Pooling=True;"
        If SSL Then cadenaDeConexion &= "SSL Mode=Required;"
        If FicheroCertificado <> "" Then cadenaDeConexion &= "CertificateFile=" & FicheroCertificado & ";"
        If FicheroCertificado <> "" Then cadenaDeConexion &= "CertificateFile=" & FicheroCertificado & ";"
        If ContraseñaCertificado <> "" Then cadenaDeConexion &= "CertificatePassword=" & ContraseñaCertificado & ";"
        Return cadenaDeConexion
    End Function


    Public Shared Function GeneraConnectionStringOraclePasswordClara(Servidor As String, Esquema As String, usuario As String, password As String, Optional Puerto As Integer = 13306, Optional SegundosTimeout As Integer = 60, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "") As String
        Dim cadenaDeConexion As String
        If Pooling Then
            cadenaDeConexion = "DATA SOURCE=" & Servidor &
                          ":" & Puerto.ToString &
                         "/" & Esquema &
                         ";USER ID=" & usuario &
                         ";PASSWORD=" & password &
                         ";PERSIST SECURITY INFO=True;"
        Else
            cadenaDeConexion = "DATA SOURCE=" & Servidor &
                          ":" & Puerto.ToString &
                         "/" & Esquema &
                         ";USER ID=" & usuario &
                         ";PASSWORD=" & password &
                         ";POOLING=False" &
                         ";PERSIST SECURITY INFO=True;"
        End If
        Return cadenaDeConexion
    End Function

    Public Shared Function GeneraConnectionStringOracle(Servidor As String, Esquema As String, usuario As String, passwordEnc As String, Optional Puerto As Integer = 13306, Optional SegundosTimeout As Integer = 60, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "") As String
        Dim cadenaDeConexion As String = ""
        Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim spassword As String = crypt.FEncS(passwordEnc, juegoDeCaracteresDeOrigen, juegoDeCaracteresDeDestino, -875421649)
        If Pooling Then
            cadenaDeConexion = "DATA SOURCE=" & Servidor &
                            ":" & Puerto.ToString &
                           "/" & Esquema &
                           ";USER ID=" & usuario &
                           ";PASSWORD=" & spassword &
                           ";PERSIST SECURITY INFO=True;"
        Else
            cadenaDeConexion = "DATA SOURCE=" & Servidor &
                            ":" & Puerto.ToString &
                           "/" & Esquema &
                           ";USER ID=" & usuario &
                           ";PASSWORD=" & spassword &
                           ";POOLING=False" &
                           ";PERSIST SECURITY INFO=True;"
        End If
        Return cadenaDeConexion
    End Function


    Public Shared Function GeneraConnectionStringLocalDB(Servidor As String, FicheroMDF As String) As String
        Return "Server=" & Servidor & ";Integrated Security=true;AttachDbFileName=" & FicheroMDF & ";"
    End Function
    Public Shared Function GeneraConnectionStringSQLServer(Servidor As String, DataBase As String, usuario As String, passwordEnc As String, Optional Puerto As Integer = 1433) As String
        Dim cadenaDeConexion As String = ""
        'Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        'Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim spassword As String = crypt.FEncS(passwordEnc, "[JO1]", "[JD1]", -875421649)
        cadenaDeConexion = "Server=" & Servidor & ", " & Puerto.ToString &
                           ";Database=" & DataBase &
                           ";User Id=" & usuario &
                           ";Password=" & spassword & ";"
        Return cadenaDeConexion
    End Function
    Public Shared Function GeneraConnectionStringSQLServer(Servidor As String, DataBase As String, usuario As String, passwordEnc As String, Optional Puerto As Integer = 1433, Optional JuegoCaracteresOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-", Optional JuegoCaracteresDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-") As String
        Dim cadenaDeConexion As String = ""
        '    Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        '   Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim spassword As String = crypt.FEncS(passwordEnc, JuegoCaracteresOrigen, JuegoCaracteresDestino, -875421649)
        cadenaDeConexion = "Server=" & Servidor & ", " & Puerto.ToString &
                           ";Database=" & DataBase &
                           ";User Id=" & usuario &
                           ";Password=" & spassword & ";"

        Return cadenaDeConexion
    End Function
    Public Shared Function GeneraConnectionStringSQLServerPasswordClara(Servidor As String, DataBase As String, usuario As String, password As String, Optional Puerto As Integer = 1433) As String
        Dim cadenaDeConexion As String = ""
        cadenaDeConexion = "Server=" & Servidor & ", " & Puerto.ToString &
                           ";Database=" & DataBase &
                           ";User Id=" & usuario &
                           ";Password=" & password & ";"
        Return cadenaDeConexion
    End Function


    'Public Shared Function ConectarTsl5EntitySQLServer(ByVal DataSource As String, InitialCatalog As String, ByVal user As String, ByVal password As String, NombreMetadata As String) As tsl5Entities
    '    Return New tsl5Entities(ObtieneEntityConnectionStringSQLServer(DataSource, InitialCatalog, user, password, NombreMetadata))
    'End Function
    'Public Shared Function ConectarTsl5EntityMySQL(ByVal server As String, ByVal database As String, ByVal port As String, ByVal user As String, ByVal password As String, NombreMetadata As String, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "") As tsl5Entities
    '    Return New tsl5Entities(ObtieneEntityConnectionStringMysql(server, database, port, user, password, NombreMetadata, Pooling, SSL, FicheroCertificado, ContraseñaCertificado))
    'End Function
    'Public Shared Function ConectarTsl5EntityLocalDB(ByVal Datasource As String, ByVal Fichero As String, ByVal Database As String, NombreMetaData As String, Optional IntegratedSecurity As Boolean = True) As tsl5Entities
    '    Return New tsl5Entities(ObtieneEntityConnectionStringLocalDB(Datasource, Fichero, Database, NombreMetaData, IntegratedSecurity))
    'End Function

    Public Shared Function ObtieneEntityConnectionStringSQLServer(ByVal DataSource As String, InitialCatalog As String, ByVal user As String, ByVal password As String, NombreMetadata As String, Optional passwordclara As Boolean = False, Optional Puerto As Integer = 1433, Optional ConnectionTimeout As Integer = 60) As String


        ' Initialize the connection string builder for the underlying provider. 
        Dim sqlBuilder As New Data.SqlClient.SqlConnectionStringBuilder

        ' Set the properties for the data source. 
        sqlBuilder.DataSource = DataSource & "," & Puerto.ToString
        sqlBuilder.InitialCatalog = InitialCatalog
        sqlBuilder.PersistSecurityInfo = True
        sqlBuilder.MultipleActiveResultSets = True
        sqlBuilder.IntegratedSecurity = False
        sqlBuilder.ConnectTimeout = ConnectionTimeout

        If passwordclara Then
            sqlBuilder.Password = password
        Else
            sqlBuilder.Password = crypt.FEncS(password, "[JO1]", "[JD1]", -875421649)
        End If

        sqlBuilder.UserID = user

        ' Build the SqlConnection connection string. 
        Dim providerString As String = sqlBuilder.ToString()

        ' Initialize the EntityConnectionStringBuilder. 
        Dim entityBuilder As New EntityConnectionStringBuilder()

        'Set the provider name. 
        entityBuilder.Provider = "System.Data.SqlClient"

        ' Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = providerString


        ' Set the Metadata location. 
        entityBuilder.Metadata = "res://*/" & NombreMetadata & ".csdl|res://*/" & NombreMetadata & ".ssdl|res://*/" & NombreMetadata & ".msl"

        Return entityBuilder.ToString
    End Function

    Public Shared Function ObtieneEntityConnectionStringLocalDB(ByVal Datasource As String, ByVal Fichero As String, ByVal Database As String, NombreMetaData As String, Optional IntegratedSecurity As Boolean = True, Optional ConnectionTimeout As Integer = 60) As String

        Dim SqlBuilder As New Data.SqlClient.SqlConnectionStringBuilder
        SqlBuilder.DataSource = Datasource
        SqlBuilder.AttachDBFilename = Fichero
        SqlBuilder.InitialCatalog = Database
        SqlBuilder.IntegratedSecurity = IntegratedSecurity
        SqlBuilder.Enlist = True ' tsl5.cryp.FEncS(password, juegoDeCaracteresDeOrigen, juegoDeCaracteresDeDestino, -875421649)
        SqlBuilder.MultipleActiveResultSets = True
        SqlBuilder.ConnectTimeout = ConnectionTimeout

        ' Build the SqlConnection connection string. 
        Dim providerString As String = SqlBuilder.ToString()

        ' Initialize the EntityConnectionStringBuilder. 
        Dim entityBuilder As New System.Data.EntityClient.EntityConnectionStringBuilder()

        'Set the provider name. 
        entityBuilder.Provider = "System.Data.SqlClient"

        ' Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = providerString

        ' Set the Metadata location. 
        entityBuilder.Metadata = "res://*/" & NombreMetaData & ".csdl|res://*/" & NombreMetaData & ".ssdl|res://*/" & NombreMetaData & ".msl"
        Return entityBuilder.ToString()
    End Function
    Public Shared Function ObtieneEntityConnectionStringMysql(ByVal server As String, ByVal database As String, ByVal port As String, ByVal user As String, ByVal passwordEnc As String, NombreMetadata As String, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "", Optional ConnectionTimeout As UInteger = 60) As String
        Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"

        ' Initialize the connection string builder for the underlying provider. 
        Dim mySqlBuilder As New MySqlConnectionStringBuilder


        ' Set the properties for the data source. 
        mySqlBuilder.Server = server
        mySqlBuilder.Port = port
        mySqlBuilder.Database = database
        mySqlBuilder.Password = crypt.FEncS(passwordEnc, juegoDeCaracteresDeOrigen, juegoDeCaracteresDeDestino, -875421649)
        mySqlBuilder.UserID = user
        mySqlBuilder.ConnectionTimeout = ConnectionTimeout
        mySqlBuilder.Pooling = Pooling
        mySqlBuilder.ConvertZeroDateTime = True
        mySqlBuilder.SslMode = If(SSL, MySqlSslMode.Required, MySqlSslMode.None)
        If FicheroCertificado <> "" Then mySqlBuilder.CertificateFile = FicheroCertificado
        If ContraseñaCertificado <> "" Then mySqlBuilder.CertificatePassword = ContraseñaCertificado

        ' Build the SqlConnection connection string. 
        Dim providerString As String = mySqlBuilder.ToString()

        ' Initialize the EntityConnectionStringBuilder. 
        Dim entityBuilder As New EntityConnectionStringBuilder()

        'Set the provider name. 
        entityBuilder.Provider = "MySql.Data.MySqlClient"

        ' Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = providerString

        ' Set the Metadata location. 
        If NombreMetadata <> "" Then
            entityBuilder.Metadata = "res://*/" & NombreMetadata & ".csdl|res://*/" & NombreMetadata & ".ssdl|res://*/" & NombreMetadata & ".msl"
        End If

        Return entityBuilder.ToString

    End Function


    Public Shared Function ObtieneEntityConnectionStringMysqlReducido(ByVal server As String, ByVal database As String, ByVal port As String, ByVal user As String, ByVal passwordEnc As String, Optional Pooling As Boolean = False, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "", Optional ConnectionTimeout As UInteger = 60) As String
        Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"

        ' Initialize the connection string builder for the underlying provider. 
        Dim mySqlBuilder As New MySqlConnectionStringBuilder


        ' Set the properties for the data source. 
        mySqlBuilder.Server = server
        mySqlBuilder.Port = port
        mySqlBuilder.Database = database
        mySqlBuilder.Password = crypt.FEncS(passwordEnc, juegoDeCaracteresDeOrigen, juegoDeCaracteresDeDestino, -875421649)
        mySqlBuilder.UserID = user
        mySqlBuilder.ConnectionTimeout = ConnectionTimeout
        mySqlBuilder.Pooling = Pooling
        mySqlBuilder.SslMode = If(SSL, MySqlSslMode.Required, MySqlSslMode.None)
        If FicheroCertificado <> "" Then mySqlBuilder.CertificateFile = FicheroCertificado
        If ContraseñaCertificado <> "" Then mySqlBuilder.CertificatePassword = ContraseñaCertificado
        Dim providerString As String = mySqlBuilder.ToString()
        Return providerString

    End Function



    Public Shared Function ObtieneEntityConnectionStringOracle(ByVal server As String, ByVal database As String, ByVal port As String, ByVal user As String, ByVal passwordEnc As String, NombreMetadata As String, Optional Pooling As Boolean = True, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "", Optional ConnectionTimeOut As Integer = 60) As String
        Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"

        ' Initialize the connection string builder for the underlying provider. 
        Dim Builder As New OracleConnectionStringBuilder


        ' Set the properties for the data source. 

        Builder.DataSource = server & ":" & port & "/" & database

        Builder.Password = crypt.FEncS(passwordEnc, juegoDeCaracteresDeOrigen, juegoDeCaracteresDeDestino, -875421649)
        Builder.UserID = user
        Builder.ConnectionTimeout = ConnectionTimeOut
        Builder.Pooling = Pooling

        '    Builder.SslMode = If(SSL, MySqlSslMode.Required, MySqlSslMode.None)
        '   If FicheroCertificado <> "" Then Builder.CertificateFile = FicheroCertificado
        '  If ContraseñaCertificado <> "" Then Builder.CertificatePassword = ContraseñaCertificado


        ' Build the SqlConnection connection string. 
        Dim providerString As String = Builder.ToString()

        ' Initialize the EntityConnectionStringBuilder. 
        Dim entityBuilder As New EntityConnectionStringBuilder()

        'Set the provider name. 
        entityBuilder.Provider = "Oracle.ManagedDataAccess.Client" '"System.Data.EntityClient"

        ' Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = providerString

        ' Set the Metadata location. 
        entityBuilder.Metadata = "res://*/" & NombreMetadata & ".csdl|res://*/" & NombreMetadata & ".ssdl|res://*/" & NombreMetadata & ".msl"


        Return entityBuilder.ToString

    End Function
    Public Shared Function ObtieneEntityConnectionStringOraclePasswordClara(ByVal server As String, ByVal database As String, ByVal port As String, ByVal user As String, ByVal password As String, NombreMetadata As String, Optional Pooling As Boolean = True, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "", Optional ConnectionTimeOut As Integer = 60) As String
        Dim juegoDeCaracteresDeOrigen As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"
        Dim juegoDeCaracteresDeDestino As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.:/\-"

        ' Initialize the connection string builder for the underlying provider. 
        Dim Builder As New OracleConnectionStringBuilder


        ' Set the properties for the data source. 

        Builder.DataSource = server & ":" & port & "/" & database


        Builder.Password = password
        Builder.UserID = user
        Builder.ConnectionTimeout = ConnectionTimeOut
        Builder.Pooling = Pooling
        '    Builder.SslMode = If(SSL, MySqlSslMode.Required, MySqlSslMode.None)
        '   If FicheroCertificado <> "" Then Builder.CertificateFile = FicheroCertificado
        '  If ContraseñaCertificado <> "" Then Builder.CertificatePassword = ContraseñaCertificado


        ' Build the SqlConnection connection string. 
        Dim providerString As String = Builder.ToString()

        ' Initialize the EntityConnectionStringBuilder. 
        Dim entityBuilder As New EntityConnectionStringBuilder()

        'Set the provider name. 
        entityBuilder.Provider = "Oracle.ManagedDataAccess.Client" '"System.Data.EntityClient"

        ' Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = providerString

        ' Set the Metadata location. 
        entityBuilder.Metadata = "res://*/" & NombreMetadata & ".csdl|res://*/" & NombreMetadata & ".ssdl|res://*/" & NombreMetadata & ".msl"


        Return entityBuilder.ToString

    End Function



    Public Shared Function ObtieneEntityConnectionStringMysqlPasswordClara(ByVal server As String, ByVal database As String, ByVal port As String, ByVal user As String, ByVal password As String, NombreMetadata As String, Optional Pooling As Boolean = True, Optional SSL As Boolean = False, Optional FicheroCertificado As String = "", Optional ContraseñaCertificado As String = "") As String


        ' Initialize the connection string builder for the underlying provider. 
        Dim mySqlBuilder As New MySqlConnectionStringBuilder

        ' Set the properties for the data source. 
        mySqlBuilder.Server = server
        mySqlBuilder.Port = port
        mySqlBuilder.Database = database
        mySqlBuilder.Password = password
        mySqlBuilder.UserID = user
        mySqlBuilder.ConnectionTimeout = 60
        mySqlBuilder.Pooling = Pooling
        mySqlBuilder.SslMode = If(SSL, MySqlSslMode.Required, MySqlSslMode.None)
        If FicheroCertificado <> "" Then mySqlBuilder.CertificateFile = FicheroCertificado
        If ContraseñaCertificado <> "" Then mySqlBuilder.CertificatePassword = ContraseñaCertificado


        ' Build the SqlConnection connection string. 
        Dim providerString As String = mySqlBuilder.ToString()

        ' Initialize the EntityConnectionStringBuilder. 
        Dim entityBuilder As New EntityConnectionStringBuilder()

        'Set the provider name. 
        entityBuilder.Provider = "MySql.Data.MySqlClient"

        ' Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = providerString

        ' Set the Metadata location. 
        entityBuilder.Metadata = "res://*/" & NombreMetadata & ".csdl|res://*/" & NombreMetadata & ".ssdl|res://*/" & NombreMetadata & ".msl"


        Return entityBuilder.ToString

    End Function
    Public Shared Function FechaHoraMySQL(ByVal FechaHora As Date) As String
        Return FechaHora.Year.ToString & FechaHora.Month.ToString.PadLeft(2, "0") & FechaHora.Day.ToString.PadLeft(2, "0") & FechaHora.Hour.ToString.PadLeft(2, "0") & FechaHora.Minute.ToString.PadLeft(2, "0") & FechaHora.Second.ToString.PadLeft(2, "0")
    End Function
    Public Shared Function FechaMySQL(ByVal FechaHora As Date) As String
        Return FechaHora.Year.ToString & FechaHora.Month.ToString.PadLeft(2, "0") & FechaHora.Day.ToString.PadLeft(2, "0")
    End Function
    Public Shared Function AhoraMysql(bd As System.Data.Objects.ObjectContext) As DateTime
        Try
            Dim FechaServidor As DateTime = bd.ExecuteStoreQuery(Of DateTime)("select now() as Ahora").First
            Return FechaServidor
        Catch ex As Exception
            Return Now
        End Try
    End Function
    Public Shared Function ObtieneIPMysql(bd As System.Data.Objects.ObjectContext) As String
        Dim sConexion As String = bd.ExecuteStoreQuery(Of String)("select host from information_schema.processlist WHERE ID=connection_id()").First
        Dim sIP As String = sConexion.Split(":")(0)
        Return sIP
    End Function
    Public Shared Function ObtieneIPSqlServer(bd As System.Data.Objects.ObjectContext) As String
        Dim sConexion As String = bd.ExecuteStoreQuery(Of String)("SELECT  client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID").First
        Return sConexion
    End Function

    Public Shared Function AhoraSqlServer(bd As ObjectContext) As DateTime
        Try
            Dim FechaServidor As DateTime = bd.ExecuteStoreQuery(Of DateTime)("select  SYSDATETIME() as Ahora").First
            Return FechaServidor
        Catch ex As Exception
            Return Now
        End Try
    End Function
End Class