Public/Invoke-DbaseQuery.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
116
117
118
119
120
121
122
123
124
function Invoke-DBaseQuery {
    <#
    .SYNOPSIS
    Runs a query against DBase tables using the last official Visual Fox Pro OLEDB driver from Microsoft.
 
    .DESCRIPTION
    Runs a query against DBase tables using the last official Visual Fox Pro OLEDB driver from Microsoft.
 
    .PARAMETER Connection
    An OleDb connection to a DBase database. This can be useful (read: more efficient) if
    you need to execute many statements against a single data source. See the example.
 
    .PARAMETER Query
    What query should be executed?
 
    .PARAMETER DataSource
    Where is the DBase data?
 
    .PARAMETER As
    This determines the type of object returned to the caller or passed down the pipeline.
 
    Currently, the following object types are valid: "DataSet", "DataTable", "DataRow","SingleValue" or "NonQuery".
 
    Detail is "DataRow"
 
    .PARAMETER SqlParameters
    Specifies a hashtable of parameters for parameterized SQL queries. http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/
    The values should be in a hash, which is easily/invisibly cast to an IDictionary by PowerShell. See the example.
 
    .NOTES
    The last Visual Fox Pro driver released by Microsoft was 32 bit only. Microsoft did not relase 64 bit
    version of this driver.
 
    If you need 64 bit support, you may have successs using the "AWE" drivers available
    via Microsoft Office. (These used to be called "Jet", up until about office 2003 or 2007).
 
    Install the 64 bit drivers can be a problem if you are using 32 bit versions of the Office apps, which
    may people still do as Microsoft offered (and might still continue to offer) 32 bit as a default.
 
    Note that the ACE driver will let traverse subfolders. This may be handy or may be dangerous, depending on your point of view
    and how well locked down your stuff is. This works fine:
 
    Invoke-DBASEQuery -DataSource "c:" -Query "SELECT * FROM c:\users\somebody\my\secret\fox\database\empsalry"
 
    That ought to work with fileshares as well, ex: "SELECT * FROM \\BFS\someshare\users\somebody\my\secret\fox\database\empsalry"
 
    .EXAMPLE
    Invoke-DBaseQuery -Query "Select count(*) as countOf From dept" -datasource "c:\fpdata"
 
    .EXAMPLE
    Invoke-DBaseQuery -Query "Select top (1) * From dept order by deptid" -datasource "c:\fpdata"
 
    .EXAMPLE
    Invoke-DBaseQuery -Query "Select * From dept where deptid = '1234'" -datasource "c:\fpdata"
 
    .EXAMPLE
    Invoke-DBaseQuery -Query "create table dept (deptid int) " -datasource "c:\temp" -As 'NonQuery'
 
    .EXAMPLE
    try {
        $cn = Get-DBaseConnection -DataSource 'c:\fpdata'
        Invoke-DBaseQuery -Connection $cn -Query $Query1 -SqlP $Param1
        Invoke-DBaseQuery -Connection $cn -Query $Query2 -SqlP $Param2
        Invoke-DBaseQuery -Connection $cn -Query $Query3 -SqlP $Param3
    }
    catch {
        # re-throw
        Throw
    }
    finally {
        # clean up. This step is key or you risk leaking resources
        $cn.Close()
        $cn.Dispose()
    }
 
 
    #>

    param (
        [Parameter(
            ParameterSetName = 'WithConnection',
            Mandatory = $true
        )]
        [System.Data.OleDb.OleDbConnection] $Connection,

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

        [Parameter(Mandatory = $true)]
        [string] $Query,

        [ValidateSet("DataSet", "DataTable", "DataRow", "SingleValue", "NonQuery")]
        [string] $As = "DataRow",
        [System.Collections.IDictionary] $SqlParameters
    )

    try {

        switch ($PSCmdlet.ParameterSetName) {
            'WithDataSource' {
                $cn = Get-DbaseConnection -DataSource $DataSource
            }
            'WithConnection' {
                $cn = $Connection
            }
        }

        Invoke-OleDbQuery -As $As -Query $Query -SqlParameters $SqlParameters -Connection $cn

    }

    catch {
        throw
    }

    finally {
        if ($PSCmdlet.ParameterSetName -ne 'WithConnection') {
            $cn.Close()
            $cn.Dispose()
        }
    }
}