Connection String Builder

Imports System.Reflection

   Public Enum ConnectionClient
        'may eventually add other clients,but untill we actually support them, whats the point?
        <StringValue("")> NoClient = 0
        <StringValue("SQLNCLI10")> SqlClient10 = 1
        'this is the OLEDB provider for sql client 10
        <StringValue("Microsoft.Jet.OLEDB.4.0")> AccessJet4 = 2
        <StringValue("Microsoft.ACE.OLEDB.12.0")> AccessAce12 = 3
        <StringValue("Microsoft.Jet.OLEDB.4.0")> ExcelJet4 = 4
        <StringValue("Microsoft.ACE.OLEDB.12.0")> ExcelAce12 = 5
    End Enum

    Public Class StringValueAttribute
        Inherits Attribute

        Public Property Value As String
        Public Sub New(ByVal val As String)
            Value = val
        End Sub

    End Class

    Public Function GetEnumByStringValueAttribute(value As String, enumType As Type) As Object
        For Each val As [Enum] In [Enum].GetValues(enumType)
            Dim fi As FieldInfo = enumType.GetField(val.ToString())
            Dim attributes As StringValueAttribute() = DirectCast(fi.GetCustomAttributes(GetType(StringValueAttribute), False), StringValueAttribute())
            Dim attr As StringValueAttribute = attributes(0)
            If attr.Value = value Then
                Return val
            End If
        Next
        Throw New ArgumentException("The value '" & value & "' is not supported.")
    End Function

    Public Function GetEnumByStringValueAttribute(Of YourEnumType)(value As String) As YourEnumType
        Return CType(GetEnumByStringValueAttribute(value, GetType(YourEnumType)), YourEnumType)
    End Function

    Public Function GetEnumValue(Of YourEnumType)(p As YourEnumType) As String
        Return DirectCast(Attribute.GetCustomAttribute(ForValue(p), GetType(StringValueAttribute)), StringValueAttribute).Value
    End Function

    Private Function ForValue(Of YourEnumType)(p As YourEnumType) As MemberInfo
        Return GetType(YourEnumType).GetField([Enum].GetName(GetType(YourEnumType), p))
    End Function

    Public Class ConnectionStringBuilder
        Private _Client As ConnectionClient
        Private _ServerName As String
        Private _Database As String
        Private _DataSource As String
        Private _ConnectionString As String
        Private _connectionStringBuilt As Boolean
        Private _HDR As Boolean = True

        Public Property Client As ConnectionClient
            Get
                Return _Client
            End Get
            Set(value As ConnectionClient)
                _connectionStringBuilt = False
                If value = ConnectionClient.NoClient Then
                    _Client = value
                    ServerName = ""
                    Database = ""
                    DataSource = ""
                ElseIf value = ConnectionClient.SqlClient10 Then
                    _Client = value
                    DataSource = ""
                ElseIf value = ConnectionClient.AccessJet4 Or value = ConnectionClient.AccessAce12 Or value = ConnectionClient.ExcelJet4 Or value = ConnectionClient.ExcelAce12 Then
                    _Client = value
                    ServerName = ""
                    Database = ""
                Else
                    _Client = ConnectionClient.NoClient
                    _ServerName = ""
                    Database = ""
                    DataSource = ""
                End If
            End Set
        End Property

        Public Sub BuildConnectionString()
            Dim out As String = "Provider=" & GetEnumValue(Client)
            If Client = ConnectionClient.NoClient Then
                out = ""
            ElseIf Client = ConnectionClient.SqlClient10 Then
                If Not ServerName.Equals("") And Not Database.Equals("") Then
                    out += ";Server=" & ServerName & ";Database=" & Database & ";Trusted_Connection=yes;"
                Else
                    out = ""
                End If

            ElseIf Client = ConnectionClient.AccessJet4 Then
                If DataSource Like "*\*.mdb" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";User Id=admin;Password=;"
                Else
                    out = ""
                End If
            ElseIf Client = ConnectionClient.AccessAce12 Then
                If DataSource Like "*\*.mdb" Or DataSource Like "*\*.accdb" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";Persist Security Info=False;"
                Else
                    out = ""
                End If
            ElseIf Client = ConnectionClient.ExcelJet4 Then
                If DataSource Like "*\*.xls" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";Extended Properties=" & ControlChars.Quote & "Excel 8.0;HDR="
                    If HDR Then
                        out += "YES;"
                    Else
                        out += "NO;"
                    End If
                    out += "IMEX=1" & ControlChars.Quote & ";"
                Else
                    out = ""
                End If
            ElseIf Client = ConnectionClient.ExcelAce12 Then
                If DataSource Like "*\*.xls" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";Extended Properties=" & ControlChars.Quote & "Excel 8.0;HDR="
                    If HDR Then
                        out += "YES;"
                    Else
                        out += "NO;"
                    End If
                    out += ControlChars.Quote & ";"
                ElseIf DataSource Like "*\*.xlsx" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";Extended Properties=" & ControlChars.Quote & "Excel 12.0 Xml;HDR="
                    If HDR Then
                        out += "YES;"
                    Else
                        out += "NO;"
                    End If
                    out += "IMEX=1" & ControlChars.Quote & ";"
                ElseIf DataSource Like "*\*.xlsb" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";Extended Properties=" & ControlChars.Quote & "Excel 12.0;HDR="
                    If HDR Then
                        out += "YES;"
                    Else
                        out += "NO;"
                    End If
                    out += ControlChars.Quote & ";"
                ElseIf DataSource Like "*\*.xlsm" Then
                    out += ";Data Source=" & ControlChars.Quote & DataSource & ControlChars.Quote & ";Extended Properties=" & ControlChars.Quote & "Excel 12.0 Macro;HDR="
                    If HDR Then
                        out += "YES;"
                    Else
                        out += "NO;"
                    End If
                    out += "IMEX=1" & ControlChars.Quote & ";"
                Else
                    out = ""
                End If
            End If
            _connectionStringBuilt = True
            _ConnectionString = out
        End Sub

        Public ReadOnly Property ConnectionString As String
            Get
                If _connectionStringBuilt = False Then
                    BuildConnectionString()
                End If
                Return _ConnectionString
            End Get
        End Property

        Public Property ServerName As String
            Get
                Return _ServerName
            End Get
            Set(value As String)
                _connectionStringBuilt = False
                _ServerName = value
            End Set
        End Property

        Public Property Database As String
            Get
                Return _Database
            End Get
            Set(value As String)
                _connectionStringBuilt = False
                _Database = value
            End Set
        End Property

        Public Property DataSource As String
            Get
                Return _DataSource
            End Get
            Set(value As String)
                _connectionStringBuilt = False
                _DataSource = value
            End Set
        End Property

        Public Property HDR As Boolean
            Get
                Return _HDR
            End Get
            Set(value As Boolean)
                _connectionStringBuilt = False
                _HDR = value
            End Set
        End Property

        Public Sub New()
            Client = ConnectionClient.NoClient
        End Sub

        Public Sub New(ByVal argClient As ConnectionClient, argServername As String, argDatabase As String)
            Client = argClient
            ServerName = argServername
            Database = argDatabase
            BuildConnectionString()
        End Sub

        Public Sub New(ByVal argClient As ConnectionClient, argDataSource As String, Optional argHDR As Boolean = False)
            Client = argClient
            DataSource = argDataSource
            HDR = argHDR
            BuildConnectionString()
        End Sub
    End Class