public/Write-GzDbData.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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
function Write-GzDbData() {
<#
    .SYNOPSIS
    Writes data to a sql store.
 
    .DESCRIPTION
    Creates a single command that is used multiple times with different parameter sets to
    insert or update data into a sql store.
 
    .PARAMETER ConnectionString
    (Optional) The connection string is used to create and open the connection.
 
    .PARAMETER Connection
    (Optional) The connection object used to create the command and to execute the query.
    If the connection is not supplied, one is created, opened, and closed within in the cmdlet.
 
    .PARAMETER Transaction
    (Optional) The transaction object used to be applied to the command. If the `UseTransaction`
    switch is present a transaction is created and committed or rolleGzDback within in the cmdlet.
     
    .PARAMETER UseTransaction
    (Optional) The cmdlet will create a transaction from the connection if one
    is not supplied, for this query.
 
    .PARAMETER Query
    The SQL statement that will be excuted by this command object.
 
    .PARAMETER Parameters
    The parameters object is an Array of PsObject, Hashtable, or Array objects. The keys for the hashtable and the
    property names for the PsObject are prefixed with the parameter prefix and used as Sql Parameter names.
     
    For an Array, the prefix is prepend to the index for the Sql Parameter name.
 
    .PARAMETER ParameterPrefix
    (Optional) Defaults to '@'. The symbol used to notate a parameter in the SQL statement.
 
    .EXAMPLE
     $Connection | Write-GzDbData "INSERT INTO [People] (FirstNAme) Values (@FirstName)" -Parameters @(@{"FirstName" = "test"})
 
     .EXAMPLE
     $results = Write-GzDbData "INSERT INTO [People] (FirstNAme) Values (@FirstName)" -Parameters @(@{"FirstName" = "test"}) -ConnectionString "Data Source=(LocalGzDb)\MSSQLLocalGzDb;Integrated Security=True"
#>

    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $True, Position= 0)]
        [string] $Query,

        [Object] $Parameters,

        [Alias("c")]
        [string] $ConnectionString,

        [Alias("cn")]
        [string] $ConnectionStringName,

        [Alias("pn")]
        [String] $ProviderName = "Default",


        [Parameter(ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [System.Data.IDbTransaction] $Transaction,

        [Parameter(ValueFromPipeline= $True)]
        [System.Data.IDbConnection] $Connection,

        [Switch] $GetResults,

        [string] $ParameterPrefix = $null
    )

    # TODO: handle parameters better in the BEGIN block
    # TODO: wrap execution inside the PROCESS block
    # TODO: wrap up disposal insicd the END block

    if(!$Transaction -and !$Connection -and [string]::IsNullOrWhiteSpace($ConnectionString)) {
       if(![string]::IsNullOrWhiteSpace($ConnectionStringName)) {
            $ConnectionString = Get-GzDbConnectionString -Name $Name 
            if([String]::IsNullOrWhiteSpace($ConnectionString)) {
                throw "Could not find connection string for $Name"
            }
        } else {
            $ConnectionString = Get-GzDbConnectionString
        }
        if([string]::IsNullOrWhiteSpace($ConnectionString)) {
            $msg =  "The ConnectionString parameter or global connection string MUST "
            $msg += "be set before communicating with the Database SERVER." 
            throw [System.ArgumentException] $msg
        }
    } 


    $disposeTransaction = $false 
    $disposeConnection = $false;
    $closeConnection = $false 
    $Connection = $null 

    if($Transaction -ne $Null) {
        if($null -eq $Connection) {
            $Connection = $Transaction.Connection
        }
    } else {       
        $disposeTransaction = $true;        
 
        
        if($null -eq $Connection) {
            $factory = Get-GzDbProviderFactory $ProviderName
            $Connection = $factory.CreateConnection()
            $Connection.ConnectionString = $ConnectionString
            $Connection.Open()

            $disposeConnection = $true;
            $closeConnection = $True 
        } else {
            if($Connection.State -ne "Open") {
                $closeConnection = $True;
                if(!$Connection.ConnectionString) {
                    $Connection.ConnectionString = $ConnectionString;
                }
                $Connection.Open();
            }
        }
        
        $Transaction = $Connection.BeginTransaction()
    }


    $cmd = $null;
    try {
        

        $cmd = $Connection | New-GzDbCommand $Query
        $cmd.Transaction = $Transaction;
        $i = 0;
        $results = @()
        if(!($Parameters -is [Array])) {
            $Parameters = @($Parameters)
        }

        foreach($parameterSet in $Parameters) {
            if($i -eq 0) {
                $cmd | Add-GzDbParameter -Parameters $parameterSet -ParameterPrefix $ParameterPrefix
            } else {
                $cmd | Add-GzDbParameter -Parameters $parameterSet -ParameterPrefix $ParameterPrefix -Update:$True
            }
            $i++;
            $result = $cmd.ExecuteScalar();
            $results += $result 
        }

        
        # in case we're grabbing an Id or value.
        

        # if this class created the transaction, commit.
        if($disposeTransaction) {
            $Transaction.Commit()
        }

        if($GetResults.ToBool()) {
            return $results;
        }
        
    } catch {
        # if there is failure Rollback
        $Transaction.Rollback();
        Throw $_.Exception
    } finally {
        
        if($cmd) {
            $cmd.Dispose()
        }
        if($closeConnection) {
            $Connection.Close()
        }
        if($disposeTransaction) {
            $Transaction.Dispose()
        }
        if($disposeConnection) {
            $Connection.Dispose()
        }
    }
}