public/Invoke-DbCmd.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 |
function Invoke-DbCmd() { <# .SYNOPSIS Creates and invokes a new sql command using ExecuteNonQuery .DESCRIPTION Creates and invokes a new sql command using ExecuteNonQuery .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 rolledback 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 (Optional). The parameters object can be a PsObject, Hashtable, or an Array. 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 PS:/> $Connection | Invoke-DbCmd "DROP DATABASE FMG" .EXAMPLE Invoke-DbCmd "DROP DATABASE FMG" -ConnectionString "Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=True" #> [CmdletBinding()] Param( [Parameter(Mandatory = $true, Position = 0)] [String] $Query, [Object] $Parameters, [string] $ConnectionString, [Parameter(ValueFromPipeline = $True)] [System.Data.IDbConnection] $Connection, [Parameter(ValueFromPipeline = $True)] [System.Data.IDbTransaction] $Transaction, [Switch] $UseTransaction, [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(!$Connection -and !$Transaction -and [string]::IsNullOrWhiteSpace($ConnectionString)) { $ConnectionString = Get-DbConnectionString if([string]::IsNullOrWhiteSpace($ConnectionString)) { $msg = "The ConnectionString parameter or global connection string MUST " $msg += "be set before communicating with SQL SERVER." throw [System.ArgumentException] $msg } } $disposeTransaction = $false $disposeConnection = $false $closeConnection = $false $factory = Get-DbProviderFactory if($Transaction -ne $null) { if(!$Connection) { $Connection = $Transaction.Connection; } } elseif($UseTransaction.ToBool()) { $disposeTransaction = $true; $closeConnection = $true if(!$Connection) { $Connection = $factory.CreateConnection() $Connection.ConnectionString = $ConnectionString $Connection.Open() $closeConnection = $true $disposeConnection = $true } else { if($Connection.State -ne "Open") { if(!$Connection.ConnectionString) { $Connection.ConnectionString = $ConnectionString; } $Connection.Open() $closeConnection = $true } } $Transaction = $Connection.BeginTransaction() } else { if(!$Connection) { $Connection = $factory.CreateConnection() $Connection.ConnectionString = $ConnectionString $Connection.Open() $closeConnection = $true $disposeConnection = $true } else { if($Connection.State -ne "Open") { if(!$Connection.ConnectionString) { $Connection.ConnectionString = $ConnectionString; } $Connection.Open() $closeConnection = $true } } } $cmd = $null; $dr = $null; try { if($Transaction) { $cmd = $Transaction | New-DbCommand $Query -Parameters $Parameters -ParameterPrefix $ParameterPrefix } else { $cmd = $Connection | New-DbCommand $Query -Parameters $Parameters -ParameterPrefix $ParameterPrefix } $result = $cmd.ExecuteNonQuery(); # Commit only if this command controls the Transaction object. if($Transaction -and $disposeTransaction) { $Transaction.Commit() } return $result; } catch { # always rollback upon error if($Transaction) { $Transaction.Rollback() } Throw $_.Exception } finally { if($cmd) { $cmd.Dispose(); } if($closeConnection) { $Connection.Close(); } if($disposeTransaction) { $Transaction.Dispose(); } if($disposeConnection) { $Connection.Dispose(); } } } |