Public/Get-OleDbConnection.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
function Get-OleDbConnection {
    <#
    .SYNOPSIS
    Returns a connection to an OleDbSource
 
    .DESCRIPTION
    Returns a connection to an OleDbSource
 
    .OUTPUTS
    [System.Data.OleDb.OleDbConnection]
 
    .PARAMETER DataSource
    This highly dependant on the provider to be used. For MDB, DBF, EXCEL, etc, it's a file path. For RDBMS like SQL Server or MySQL, it will be a server hostname. Other variations are possible.
 
    .PARAMETER ConnectionString
    If the caller provides a connection string, it will be used instead of the datasource
 
    .PARAMETER Provider
    Which OleDb provider should be used?
 
    .PARAMETER ExtendedProperties
    Some providers use a bevy of 'extended properties' in the connection string, this is a bucket into which you can throw them.
    The exact capability and syntax is provider-specific.
    The are usually key-value pairs in the format "Prop=something;AnotherProp=SomethingElse".
    No attempt is made to validate these properties or even validate the string. The first indication of trouble is usually that the Open() call will fail.
 
    .PARAMETER Credential
    Use alternative credentials. Accepts credential objects provided by Get-Credential.
 
    .EXAMPLE
    try {
        $cn = Get-OleDbConnection -DataSource 'c:\fpdata' -Provider 'vfpoledb'
        # do stuff
    }
    catch {
        # re-throw
        Throw
    }
    finally {
        # clean up. This step is key or you risk leaking resources
        $cn.Close()
        $cn.Dispose()
 
    }
 
    .LINK
    https://www.connectionstrings.com/
 
    #>

    [OutputType([System.Data.OleDb.OleDbConnection])]
    param (
        [Parameter(
            ParameterSetName = 'WithConnectionString',
            Mandatory = $true
        )]
        [string] $ConnectionString,

        [Parameter(
            ParameterSetName = 'WithDataSource',
            Mandatory = $true
        )]
        [string] $DataSource,

        [Parameter(
            ParameterSetName = 'WithDataSource',
            Mandatory = $true
        )]
        [string] $Provider,

        [Parameter(
            ParameterSetName = 'WithDataSource'
        )]
        [string] $ExtendedProperties,

        [Parameter(
            ParameterSetName = 'WithDataSource'
        )]
        [System.Management.Automation.PSCredential] $Credential
    )

    switch ($PSCmdlet.ParameterSetName) {
        'WithConnectionString' {
            [string] $connString = $ConnectionString
        }
        'WithDataSource' {
            $builder = New-Object System.Data.OleDb.OleDbConnectionStringBuilder
            $builder."Data Source" = $DataSource
            $builder."Provider" = $Provider
            
            if ($ExtendedProperties) {
                $builder."Extended Properties" = $ExtendedProperties
            }
            if ($Credential) {
                $builder["User ID"] = $Credential.UserName
                $builder["Password"] = $Credential.GetNetworkCredential().Password
            }
            else {
                $builder["Trusted_Connection"] = $true
            }

            [string] $connString = $builder.ConnectionString
        }
    }

    Try {
        $OleDbConn = New-Object System.Data.OleDb.OleDbConnection($connString)
        $OleDbConn.Open()
        $OleDbConn
    }

    Catch {
        Throw
    }
}