Public/Invoke-FoxProQuery.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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
function Invoke-FoxProQuery {
    <#
    .SYNOPSIS
    Runs a query against FoxPro tables using the last official Visual Fox Pro OLEDB driver from Microsoft.
 
    .DESCRIPTION
    Runs a query against FoxPro tables using the last official Visual Fox Pro OLEDB driver from Microsoft.
 
    .PARAMETER Connection
    An OleDb connection to a FoxPro 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 FoxPro data?
 
    .PARAMETER ExtendedProperties
    Extended property settings for FoxPro databases go here.
 
    .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.
 
    The VFP driver uses Datasource as the "root" of the database. You can specify tables in subdirectories.
    This means queries like these two, which point to the same tables, will work fine:
    SELECT * FROM emp ## Datasource is "c:\foo\subdir"
    SELECT * FROM .\subdir\emp ## Datasource is "c:\foo"
 
    This will not work on the ACE drivers, which will not let you traverse subdirectories.
 
    .EXAMPLE
    Invoke-FoxProQuery -Query "Select count(*) countOf From dept" -datasource "c:\fpdata"
 
    .EXAMPLE
    Invoke-FoxProQuery -Query "Select top (1) * From dept order by deptid" -datasource "c:\fpdata"
 
    .EXAMPLE
    Invoke-FoxProQuery -Query "Select * From dept where deptid = '1234'" -datasource "c:\fpdata"
 
    .EXAMPLE
    Invoke-FoxProQuery -Query "create table dept (deptid int) " -datasource "c:\temp" -As 'NonQuery'
 
    .EXAMPLE
    try {
        $cn = Get-FoxProConnection -DataSource 'c:\fpdata'
        Invoke-FoxProQuery -Connection $cn -Query $Query1 -SqlP $Param1
        Invoke-FoxProQuery -Connection $cn -Query $Query2 -SqlP $Param2
        Invoke-FoxProQuery -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(
            ParameterSetName = 'WithDataSource'
        )]
        [string] $ExtendedProperties,

        [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-FoxProConnection -DataSource $DataSource
            }
            'WithConnection' {
                $cn = $Connection
            }
        }

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

    Catch {
        Throw
    }

    finally {
        if ($PSCmdlet.ParameterSetName -ne 'WithConnection') {
            <#
            If Get-FoxProConnection failed, trying to close the connection here
            will generate a runtime error ("You cannot call a method on a
            null-valued expression."). Since we are in finally{}, this error
            will not be caught.
            Fix: test that $cn is a thing before trying to close the connection.
            #>

            if ($cn) {
                $cn.Close()
                $cn.Dispose()
            }
        }
    }
}